Domain 2 of 4 · Chapter 4 of 4

Data Modeling and Schema Evolution

Unlock the complete study guide + 1,040 practice questions across 16 full exams.

Bundled into the existing AWS Certified Data Engineer - Associate premium course — no separate purchase.

Included in this chapter:

  • Access patterns come first, then the schema
  • Redshift: distribution style and sort key
  • DynamoDB: composite keys and secondary indexes
  • Schema and partition evolution in open table formats
  • Schema conversion, lineage, and exam-pattern recognition

Schema-design levers by target store

DecisionAmazon RedshiftAmazon DynamoDBData lake (Iceberg / Lake Formation)
Primary modeling unitStar/snowflake fact + dimension tablesItem collections under a composite keyPartitioned columnar tables over S3
Locality / co-location leverDistribution style KEY / ALL / EVEN / AUTOPartition key (same key = same partition)Partition columns (filter-prune prefixes)
Scan-reduction leverSort key (compound vs interleaved)Sort key + secondary indexes (GSI/LSI)Partition pruning + columnar encoding
Add a new query patternAdd/alter sort or dist key (may rebuild)Add a GSI (LSI only at create time)Add a partition field via partition evolution
Schema change costALTER TABLE; type change may deep-copyPer-item, schemaless; no migrationMetadata-only via Iceberg column IDs

Decision tree

Migrating between engines?Yes (Oracle to PostgreSQL)DMS Schema Conversion / SCTconvert + assessment-report action itemsNoSingle-item key/value lookups?YesDynamoDB composite keysitem collections + GSI/LSINoAnalytical joins + aggregations?YesRedshift dist key + sort keystar/snowflake; KEY/ALL/EVEN, compound sortNo (lake)Records in motion (streaming)?YesGlue Schema RegistryBACKWARD compatibility contractNo (at rest)Iceberg / S3 Tablesschema + partition evolution, by column ID
Cheat sheet

Cheat sheet

  • Model the schema from access patterns, not from the source layout
  • Redshift KEY distribution co-locates a join so it needs no shuffle
  • Use Redshift DISTSTYLE ALL only for small, slow-moving dimensions
  • Leave Redshift DISTSTYLE AUTO unless you know the join column
  • A Redshift sort key lets range scans skip 1 MB blocks
  • Prefer a Redshift compound sort key for tables you update regularly
  • DynamoDB has no joins, so collapse related entities into item collections
  • Add a DynamoDB GSI for a new access pattern; an LSI must exist at create time
  • A DynamoDB GSI serves only eventually consistent reads
  • Use a sparse GSI to index only the items that carry the key
  • Write-shard a hot DynamoDB key to spread it past one partition's limit
  • DynamoDB schema evolution is per-item and needs no migration
  • Iceberg tracks columns by ID, so add, drop, rename, and reorder are metadata-only
  • Iceberg partition evolution changes partitioning without rewriting old data
  • Register streaming schemas in the Glue Schema Registry under BACKWARD compatibility
  • Heterogeneous engine migrations need Schema Conversion, not just DMS
  • The Schema Conversion assessment report flags what needs manual conversion
  • SageMaker ML Lineage Tracking records provenance for reproducibility and audit
  • Vectorize content for retrieval with an Amazon Bedrock knowledge base
  • Indexing, partitioning, and compression all reduce bytes read, per store
  • Schema Registry auto-registration writes new schemas to the default-registry
  • Late-binding views (WITH NO SCHEMA BINDING) survive underlying schema changes

Unlock with Premium — includes all practice exams and the complete study guide.

References

  1. Distribution styles
  2. Sort keys
  3. Best practices for modeling relational data in DynamoDB
  4. Evolve Iceberg table schema
  5. AWS Glue Schema registry
  6. Amazon SageMaker ML Lineage Tracking