Data Modeling on AWS: Redshift, DynamoDB, Lake Formation & Schema Evolution

A Redshift schema is designed around one physical reality: data is spread across compute slices, and queries are fast when the rows they join and scan are already on the same slice and physically ordered. You control the first with the distribution style and the second with the sort key.

There are four distribution styles. KEY distribution hashes one column and places rows with the same value on the same slice, so a join on that column is collocated and no data moves across the network. ALL distribution copies the entire table to every node: right for small, slowly changing dimension tables that join everywhere, wrong for large or frequently updated tables because every write is multiplied. EVEN distribution round-robins rows, a safe choice for large tables with no dominant join. AUTO lets Redshift choose and change the style as the table grows, and is the default recommendation when you have no strong reason to override it.

A table has only one distribution key. When a fact table joins several dimensions, you cannot collocate them all: give the fact table and its largest, most frequently joined dimension the same DISTKEY on the join column, and distribute the small dimensions with ALL so every slice already holds a full copy. The classic anti-pattern is a DISTKEY on a low-cardinality or skewed column (a status flag, a country with most of the traffic): rows pile onto a few slices, and the fullest slice sets the pace of every query. SVV_TABLE_INFO shows the skew. VACUUM and ANALYZE do not fix it: VACUUM re-sorts rows and reclaims deleted space within each slice, and ANALYZE refreshes planner statistics, but neither moves rows between slices. Only a new distribution key (ALTER TABLE ... ALTER DISTKEY, or ALTER DISTSTYLE) or a deep copy redistributes them.

Sort keys set on-disk order, so Redshift can skip blocks whose min/max metadata (zone maps) rule them out. A compound sort key sorts by columns in the order listed and helps most when queries filter on the leading column; date is the common choice for time-series facts. An interleaved sort key weights several columns equally but costs heavy maintenance (VACUUM REINDEX rebuilds it as data drifts) and is rarely the right modern answer. AWS steers toward compound keys or AUTO sort keys, where Redshift observes the workload and adjusts.

Redshift is modeled dimensionally: a central fact table holds measurable events (sales lines, clicks) with foreign keys to dimension tables that describe them (customer, product, date). Fewer, bigger joins over denormalized dimensions beat the many narrow joins of a normalized OLTP model.

Flattening a snowflaked dimension means joining its sub-tables once at load time into one dimension table, so queries pay one join instead of three.

Dimensions change over time, and a slowly changing dimension (SCD) type says how history is kept. Type 1 overwrites the old value: simple, but past facts now report under the new value. Type 2 inserts a new row for each change, with effective-from/effective-to dates (and often a current flag) and a surrogate key per row, so each fact points at the version that was true when it happened and history reports correctly. Type 3 adds a column holding the previous value: one level of history, for comparisons such as old versus new sales territory.

Redshift accepts PRIMARY KEY, FOREIGN KEY and UNIQUE constraints, but they are informational only: Redshift does not enforce them, and the query planner uses them as hints. Your ETL must guarantee uniqueness and referential integrity; declaring a constraint the data violates can produce wrong query results. Declaring them does not remove joins or replace a dimension's surrogate-key logic.

DynamoDB modeling inverts relational design: you list every access pattern first, then build keys that answer each one with a direct lookup or a single Query, because DynamoDB has no joins and no ad hoc query planner. The partition key decides where an item lives and must be high-cardinality and evenly accessed; the optional sort key orders items within a partition and enables range conditions (begins_with, between) and one-to-many relationships. A partition key of customerId with an ISO timestamp sort key answers "this customer's orders, newest first" in one Query.

Hot partitions come from physical limits. Each partition serves at most 3,000 read capacity units and 1,000 write capacity units per second, and those limits apply in on-demand mode too: on-demand removes capacity planning for the table, not the per-partition ceiling. A low-cardinality or "celebrity" key therefore throttles however much table capacity you have. Adaptive capacity shifts throughput to busy partitions and can split a hot one, but it will not split usefully when the hot item collection is written in monotonically increasing sort-key order (every new write lands at the end of the range). The design fix is write sharding: append a suffix to the key (random, or calculated from an attribute so you can find the item again), such as 2026-10-04#7, spreading writes across N key values; reads then query each suffix and merge.

Item design limits. An item is at most 400 KB including attribute names. Write capacity is charged on the whole item size (1 WCU per 1 KB written), so updating one counter on a large item costs the full item; split rarely changed bulk (or store it in S3 with a pointer) away from hot, frequently updated attributes. Nesting an unbounded list inside one item eventually hits the 400 KB cap, which is why one-to-many data is modeled as separate items in an item collection.

Single-table design stores several entity types in one table using generic key attributes (PK, SK) with prefixed values such as CUST#123 and ORDER#2026-07-01#456. Related entities share a partition, so one Query retrieves a customer and their orders: the join is precomputed into the keys. The cost is rigidity: the model serves exactly the access patterns it was designed for.

Query, don't filter. A FilterExpression is applied after items are read, so it never reduces read capacity consumed, and in a Query it cannot reference the partition or sort key attributes (those belong in the key condition). To narrow by key, use the key condition (for example begins_with(SK, 'ORDER#')) and ScanIndexForward=false plus Limit for "latest N".

When one key schema cannot serve every pattern, add a global secondary index (GSI): an alternate partition/sort key over the same items, eventually consistent, with its own capacity. GSI key attributes must be scalar (String, Number or Binary), not lists or maps. Two GSI traps: a GSI whose partition key has few distinct values (a status) creates hot index partitions, fixed by sharding the index key the same way; and if a GSI lacks write capacity, writes to the base table are throttled, because every base write that touches indexed attributes must also be written to the index.

A sparse GSI indexes only items that carry the indexed attribute, a cheap way to query a small subset such as orders flagged for review: set the attribute only on those items and remove it afterwards. Writing a placeholder (NONE, false) onto every other item destroys the sparseness, because those items now carry the attribute and get indexed too. Local secondary indexes (LSIs) share the partition key with an alternate sort key and support strongly consistent reads, but they can only be defined when the table is created (you cannot add one later) and they cap each item collection at 10 GB. GSIs are the default unless strong consistency on the alternate pattern is required.

Worked example: an e-commerce platform needs "get customer profile", "list a customer's orders by date" and "find an order by order number". One table: PK = CUST#id, profile at SK = PROFILE, orders at SK = ORDER#date#orderId; a GSI keyed on the order number serves the direct lookup. Three patterns, one table, no scans.

Redshift and DynamoDB sit at opposite ends of the modeling spectrum, and DEA-C01 loves questions that hinge on choosing — or noticing someone has chosen wrong. Redshift is a columnar OLAP warehouse: model for scans, aggregations, and joins across huge history. DynamoDB is a key-value/document OLTP store: model for single-digit-millisecond point reads and writes at any scale.

The connective tissue matters too: a common architecture serves live traffic from DynamoDB and analyzes history in Redshift, moving data via DynamoDB Streams or exports to S3. If a question describes running aggregations with Scans over a DynamoDB table, the model is wrong — that workload belongs in a warehouse or a lake. If a question describes single-item lookups hammering Redshift, wrong in the other direction.

In a Lake Formation data lake, the schema is the combination of the file layout in Amazon S3 and the table definitions in the AWS Glue Data Catalog. Lake Formation registers the S3 locations and manages permissions down to database, table, column, row and cell level, and every engine (Athena, Redshift Spectrum, EMR, Glue) reads the same catalog schema. Good lake modeling is therefore good file design: columnar formats, compression, and reasonably large files (next section).

Partitioning is the lake's substitute for indexes: laying data out as year=2026/month=07/day=29 under the table prefix lets engines prune whole partitions when queries filter on those columns. Partition on low-cardinality columns that queries actually filter by (almost always date, sometimes region or tenant). Partitioning on a high-cardinality column such as a device or customer ID creates millions of tiny partitions and recreates the small-files problem.

Bucketing is the tool for that high-cardinality column. An Athena CTAS with bucketed_by = ARRAY['device_id'] and bucket_count hashes rows into a fixed number of files per partition; a query filtering on one value of the bucketed column reads only the bucket that can contain it. Partition by date, bucket by ID.

Partition projection solves the other partition problem: highly partitioned tables where new partitions arrive constantly. Instead of storing each partition in the catalog (and waiting for a crawler or MSCK REPAIR TABLE to add it), you declare the partition columns' ranges and formats in the table properties, and Athena computes the partition list at query time. That removes the lag before new data is queryable and the catalog lookups that slow planning. It is Athena-only: Redshift Spectrum, EMR and Glue jobs ignore it and still need partitions in the catalog. A Glue partition index is a different aid: it speeds partition lookup in the catalog for filtered queries, but it does not change how many objects a query opens.

A typical zoned design keeps a raw zone (source format, immutable), a curated zone (Parquet, partitioned, cataloged) and consumption-ready marts, with Lake Formation granting analysts the curated tables and only pipeline roles the raw zone. Modern lakes increasingly use an open table format such as Apache Iceberg on Parquet, adding ACID transactions, snapshot time travel and first-class schema and partition evolution (see the evolution section).

Query engines that bill or slow down by bytes scanned (Athena, Redshift Spectrum) are fastest when they can skip data, and file layout decides how much they can skip.

Columnar versus row formats. Parquet and ORC are columnar: a query reads only the columns it selects. Avro, JSON and CSV are row-oriented, and compressing them with a codec makes them smaller but not columnar: every query still reads whole rows. Convert analytical tables to Parquet or ORC.

Splittable codecs. An engine can process one large file in parallel only if it can split it. For text files, bzip2 is splittable and gzip is not, so one large gzip CSV is read by a single worker. Parquet and ORC compress internally per block (Snappy, ZSTD, gzip), so they stay splittable whatever the codec.

Row-group statistics and clustering. Parquet files are divided into row groups, each with min/max statistics per column. A filter on a column lets the engine skip row groups whose range excludes the value (predicate pushdown). This only helps if the values are clustered: if data is sorted on a high-cardinality filter column within each partition (for example sorting by customer_id before writing), each row group covers a narrow range and most are skipped; unsorted, every row group spans everything and nothing is skipped.

The small-files problem. Thousands of tiny objects cost more in S3 requests, file opening and planning than the data itself. The causes are on the writer side: a Spark job writes one file per task per table partition, so raising spark.sql.shuffle.partitions multiplies the file count, and a streaming job emits files on every short micro-batch trigger. Fix both ends:

• Write fewer, larger files: repartition by the table's partition columns before writing (or coalesce), and lengthen the streaming trigger interval or buffer so each batch writes more data (Firehose buffering does the same).
• Compact what is already there: a periodic job rewrites small files into large ones (Iceberg's OPTIMIZE in Athena or the rewrite_data_files procedure in Spark; a Glue job for plain Parquet). Aim for files in the roughly 128 MB to 1 GB range.

A Glue partition index or partition projection makes finding partitions faster, but neither reduces the number of files a query must open; only compaction and better writers do.

Schemas change: sources add columns, types widen, volumes grow. The safe playbook everywhere is additive, backward-compatible change: add new nullable columns, avoid renaming or repurposing existing ones, and never change a column's meaning in place. How safely a store absorbs other changes depends on how it maps columns to data.

Glue crawlers. When a re-crawl finds a changed schema, the crawler's schema change policy decides what happens: update the table definition in the Data Catalog (the catalog follows the data, overwriting column types and any manual edits), add new columns only (new columns are appended but existing columns, their types and your manual edits are kept), or ignore the change and don't update the table (changes are logged only). Choose "add new columns only" when you have hand-tuned the table and want only additions to flow through. Parquet carries its schema per file, so readers can merge schemas across files, with a newly added column reading as null in older files.

Hive-style tables versus Iceberg. A plain (Hive-style) table maps catalog columns to file columns by name or by position, and that is why it breaks on change:

Iceberg assigns every column a permanent ID stored in the data files, so add, drop, rename and reorder are metadata operations that never rewrite data, and partition evolution changes the partition spec for new data while old files keep their layout. Iceberg allows only safe type promotions: int to bigint (long), float to double, and widening a decimal's precision while keeping the same scale. Narrowing a type, changing a string to a number, or changing a decimal's scale is rejected and needs a new column plus a backfill (or a rewrite).

Redshift. ALTER TABLE ADD COLUMN is cheap. ALTER TABLE ... ALTER COLUMN ... TYPE only increases the size of a VARCHAR (and not for a few encodings such as BYTEDICT or RUNLENGTH); any other type change, such as INT to BIGINT, is done by adding a new column, UPDATE-ing it from the old one, dropping the old column and renaming the new one, or by a deep copy into a new table. The UPDATE rewrites every row (Redshift marks the old versions deleted), so run VACUUM afterwards to reclaim space and restore sort order.

DynamoDB. Items are schemaless outside the key attributes, so adding attributes is trivial. The key schema cannot change: partition and sort keys are fixed at table creation, and a new access model means a new GSI, or a new table plus a migration. Changing data characteristics also covers scale: a partition scheme or distribution key chosen at small volume can skew at large volume, so revisit distribution (Redshift AUTO helps), DynamoDB key sharding and lake partition granularity as data grows.

The AWS Glue Schema Registry versions Avro, JSON Schema and Protobuf schemas for streaming producers and consumers (Amazon MSK, Kinesis Data Streams, Apache Kafka) and checks every new schema version against a compatibility mode before registering it, so an incompatible producer change is rejected before it can break consumers. The mode you pick follows from which side upgrades first.

In practice, adding an optional field with a default is compatible in every direction; removing a required field or changing a type is where modes differ. When producers ship changes before consumers, FORWARD-style checks protect consumers; when you cannot control the order at all, FULL checks against the previous version in both directions.

Schema conversion translates database objects (tables, types, indexes, views, procedures) from one engine's dialect to another's, and it is the step before data migration in a heterogeneous move (Oracle to Amazon Aurora PostgreSQL, SQL Server to Redshift). AWS provides two tools: the standalone, downloadable AWS Schema Conversion Tool (AWS SCT), and the managed AWS DMS Schema Conversion, which runs the same job inside the AWS DMS console with no local install. Both convert schemas; AWS DMS then migrates the data, with full load and change data capture (CDC) for near-zero-downtime cutovers.

The workflow: connect source and target, generate a database migration assessment report that marks each object as automatically convertible or needing manual work, apply the converted schema to the target, then run DMS to move the data. The assessment report is how you estimate manual effort before committing; stored procedures, triggers and engine-specific functions are the usual manual items.

Why conversion must come first: when DMS creates target tables itself, it creates only what it needs to move data efficiently: tables, primary keys and, in some cases, unique indexes. Secondary indexes, foreign keys, defaults, and code objects (procedures, triggers, views) are not created. So apply the converted schema first, then run DMS with a target table preparation mode that keeps it:

• Do nothing: leaves existing target tables and data as they are; use it with a pre-created schema.
• Truncate: empties the pre-created tables but keeps their definitions; also safe with a converted schema.
• Drop tables on target: drops the tables and recreates bare ones (columns plus primary key), discarding the indexes, constraints and defaults you applied.

Keep the division of labor straight: schema conversion converts structure; DMS moves data. A homogeneous migration (MySQL to Aurora MySQL) needs no conversion; native tools or DMS alone suffice. SCT also handles data-warehouse conversions to Redshift, including data extraction agents for large warehouse migrations.

Data lineage answers where data came from, what transformed it, and what depends on it: it is how you assess the blast radius of a schema change, trace bad numbers upstream, and satisfy auditors. The exam names Amazon SageMaker Catalog and Amazon SageMaker ML Lineage Tracking.

SageMaker Catalog (in Amazon SageMaker Unified Studio, built on Amazon DataZone) is the business catalog where producers publish curated assets and consumers discover and subscribe to them through governed projects. Its lineage is OpenLineage-compatible. It is captured automatically from supported sources such as AWS Glue and Amazon Redshift. For a custom job (your own Spark on EC2, a third-party tool), you send OpenLineage run events to the catalog with the PostLineageEvent API, and they appear in the same graph. Choose it when lineage is paired with governance, discovery or subscriptions across the data estate.

SageMaker ML Lineage Tracking is narrower: it records the entities of an ML workflow (datasets, processing and training jobs, models, endpoints) and their relationships, so a deployed model can be traced back to the data and code that produced it. Distinguish the two by scope: Catalog lineage spans the data estate; ML Lineage Tracking spans a machine-learning pipeline. Lineage tools only see what runs through instrumented paths, which is an argument for cataloged, orchestrated transformation over opaque scripts.

Optimization levers repeat across stores: compress so there is less to read, and organize data so the engine can skip what the query does not need.

Redshift column encodings. Each column has a compression encoding: AZ64 (efficient for numeric and date/time types), ZSTD (strong general-purpose), LZO, BYTEDICT and others, with RAW meaning none. The modern default is ENCODE AUTO, where Redshift chooses and adjusts encodings. Sort key columns are conventionally left RAW so zone-map pruning stays effective. To manage encodings on an existing table:

• ANALYZE COMPRESSION table reports a suggested encoding and the estimated space saving per column; it changes nothing.
• ALTER TABLE t ALTER COLUMN c ENCODE zstd changes one column's encoding in place.
• ALTER TABLE t ALTER ENCODE AUTO hands a table created with explicit encodings back to automatic management.
• COPY applies automatic compression (COMPUPDATE) only when loading into an empty table; it does not re-encode a table that already holds data.

Keep statistics and sort order fresh. ANALYZE updates planner statistics; ANALYZE ... PREDICATE COLUMNS limits it to columns used in filters, joins and group-bys, which is cheaper on wide tables. VACUUM re-sorts and reclaims deleted space (Redshift runs automatic vacuum and analyze in the background); VACUUM REINDEX is the variant for interleaved sort keys.

Partitioning and indexing everywhere. Partition and bucket lake tables, and cluster files on filter columns; use Redshift sort keys as the warehouse's pruning mechanism (Redshift has no conventional B-tree indexes); use DynamoDB GSIs for additional access patterns; and in relational engines like Aurora, index the predicate columns queries use. Redshift materialized views, and AWS Glue Data Catalog materialized views (Iceberg-backed, created and refreshed with Spark) that Athena can query, precompute expensive aggregations queried repeatedly. In the lake, compression means columnar Parquet or ORC; smaller scans directly cut Athena and Spectrum cost, which bill by data scanned.

Vectorization turns text and other unstructured data into embeddings: numeric vectors whose distance encodes semantic similarity, so similar meaning is found by nearest-neighbor search instead of keyword matching. An Amazon Bedrock knowledge base packages this for retrieval-augmented generation (RAG): it reads documents from a source such as S3, splits them into chunks, embeds each chunk with a Bedrock embedding model, stores the vectors in a vector store (such as Amazon OpenSearch Serverless or Amazon Aurora PostgreSQL with pgvector), and at query time retrieves the most similar chunks to ground a foundation model's answer.

Chunking strategy decides what a retrieved unit is:

Two choices are fixed when the knowledge base is created: the embedding model and the vector store configuration. Vectors from different models are not comparable, and the vector index's dimension must match the model, so switching embedding models means creating a new knowledge base with a vector index of the matching dimension and re-ingesting. Vector indexes (HNSW, IVF) trade a little recall for speed at scale. From the data-engineering side, know the pipeline shape (source, chunking, embedding model, vector store, retrieval) and keep the source in sync by re-running ingestion when documents change.