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 a column and places rows with the same value on the same slice — choose the column used in your biggest, most frequent join (commonly the fact table's foreign key to the largest dimension) so the join 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 default for large tables that do not participate in collocated joins. AUTO lets Redshift choose and adjust as the table grows, and is the default recommendation when you have no strong reason to override it. The classic anti-pattern the exam tests: a distribution key on a low-cardinality or skewed column (like a status flag) piles rows onto a few slices, and the slowest slice sets the pace of every query.

Sort keys determine on-disk order, letting Redshift skip blocks whose min/max metadata rules them out. A compound sort key sorts by columns in order and shines when queries filter on the leading column — date is the overwhelmingly common choice for time-series facts. An interleaved sort key weights multiple columns equally but carries heavy maintenance cost and is rarely the right answer on the modern exam; AWS now steers toward compound keys or AUTO sort keys, where Redshift observes the workload and adjusts. Schema-wise, Redshift favors a star schema with some denormalization — fewer, bigger joins beat the many narrow joins of a fully normalized OLTP model.

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 to save you. The partition key determines where an item lives and must be high-cardinality and evenly accessed; the optional sort key orders items within a partition and enables range queries (begins_with, between) and one-to-many relationships. A partition key like customerId with a sort key like an ISO timestamp answers all my orders, newest first in one Query. A low-cardinality or celebrity partition key creates a hot partition that throttles regardless of provisioned capacity — the number one DynamoDB exam trap.

Single-table design stores multiple entity types (customers, orders, order items) in one table using generic key attributes (often PK and SK) with prefixed values such as CUST#123 and ORDER#2026-07-01#456. The payoff is that related entities share a partition, so one Query retrieves a customer and their orders together — the join is precomputed into the key design. The cost is rigidity: the model serves exactly the access patterns it was designed for, which is why the pattern-first discipline matters.

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. A sparse GSI (indexing only items that carry the indexed attribute) is a cheap way to query a small subset, such as only orders flagged for review. Local secondary indexes (LSIs) share the partition key with an alternate sort key, support strong consistency, but must be created at table creation time and constrain the partition size — GSIs are the default answer unless strong consistency on the alternate pattern is explicitly required.

Concrete scenario: an e-commerce platform needs get customer profile, list a customer's orders by date, and find an order by order number. Model one: single table, PK = CUST#id with profile at SK = PROFILE and orders at SK = ORDER#date#orderId; add a GSI with PK = ORDER#orderId for the direct order 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, 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 therefore means good file design: store analytical tables in a columnar format such as Apache Parquet, compress them, and aim for reasonably large files rather than millions of tiny ones, which crush query planning and S3 request costs.

Partitioning is the lake's substitute for indexes: laying out data as year=2026/month=07/day=29 under the table prefix lets engines prune entire partitions when queries filter on those columns. Partition on columns queries actually filter by — almost always date, sometimes region or tenant — and avoid over-partitioning on high-cardinality columns, which recreates the small-files problem. A typical zoned design keeps a raw zone (source format, immutable), a curated zone (Parquet, partitioned, cataloged), and consumption-ready marts, with Lake Formation permissions 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 top of Parquet, which brings ACID transactions, snapshot time travel, and — critically for this task — first-class schema evolution to S3 tables. Iceberg matters again in the evolution section below; for modeling, know that it removes the classic lake pain of rewriting directory layouts when the schema or partitioning scheme changes.

Schemas change — sources add columns, types widen, volumes grow, and a model that cannot absorb change breaks pipelines. 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.

Each layer has its own mechanics. In the lake, AWS Glue crawlers detect schema changes on re-crawl and can update the Data Catalog table (you configure whether they update, log, or ignore changes); columnar formats like Parquet carry their schema per file, so readers can merge schemas across files with new columns simply reading as null in old data. For streaming pipelines, the AWS Glue Schema Registry enforces compatibility rules (such as backward compatibility) on Avro/JSON/Protobuf schemas at produce time, so an incompatible producer change is rejected before it corrupts consumers. Apache Iceberg gives the strongest story: full in-place schema evolution — add, drop, rename, reorder columns and even change partitioning — as metadata operations, without rewriting data files, because columns are tracked by ID rather than by name or position.

In Redshift, evolution is DDL: ALTER TABLE ADD COLUMN is cheap and safe, while type changes or column drops on large tables are usually done by creating a new table and copying data. In DynamoDB, items are schemaless outside the key attributes, so adding attributes is trivial — the schema that cannot change is the key schema: partition and sort keys are fixed at table creation, and changing the access model means a new table (or a new GSI) and 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) and partition granularity as data grows.

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 for it: the standalone, downloadable AWS Schema Conversion Tool (AWS SCT), and its managed successor, AWS DMS Schema Conversion, which runs the same job inside the AWS DMS console with no local install. The current exam guide has shifted toward DMS Schema Conversion, but both convert schemas; AWS DMS itself then migrates the data, with full-load and change data capture (CDC) for near-zero-downtime cutovers.

The workflow to know: 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 the exam's favorite detail — it is how you estimate the manual effort in a heterogeneous migration before committing. Objects that embed procedural code (stored procedures, triggers, engine-specific functions) are the usual manual-effort items.

Keep the division of labor straight, because answer options blur it deliberately: Schema Conversion converts structure; DMS moves data. For a homogeneous migration (MySQL to Aurora MySQL) no conversion is needed — native tools or DMS alone suffice. And for warehouse-specific sources, SCT historically also handled data-warehouse conversions to Redshift, including using data extraction agents for large warehouse migrations.

Data lineage answers where did this data come from, what transformed it, and what depends on it — which is how you assess the blast radius of a schema change, debug bad numbers upstream, and satisfy auditors. On the current exam, the named tools are Amazon SageMaker Catalog and Amazon SageMaker ML Lineage Tracking.

SageMaker Catalog (part of the next generation of SageMaker, built on the technology behind Amazon DataZone) is the business-facing catalog where producers publish curated data assets with business metadata and consumers discover and subscribe to them through governed projects. Its lineage capability visualizes how assets flow from sources through transformation jobs to published products — the org-wide who-uses-what view, and the right answer when a question pairs lineage with governance, discovery, or subscriptions.

SageMaker ML Lineage Tracking is narrower and ML-specific: it automatically records the graph of entities in an ML workflow — datasets, processing and training jobs, models, endpoints — so you can trace any deployed model back to the exact data and code that produced it, for reproducibility and audit. Distinguish the two by scope: Catalog lineage spans the data estate; ML Lineage Tracking spans a machine-learning pipeline. More broadly, remember lineage is an argument for centralizing transformation in cataloged, orchestrated services (Glue jobs, Step Functions) rather than opaque scripts — lineage tools can only track what runs through instrumented paths.

Optimization best practices tie the whole task together, and the levers repeat across stores. Compression: Redshift applies a column encoding to each column — encodings such as AZ64 (efficient for numeric and temporal types), ZSTD (strong general-purpose compression), and LZO, with RAW meaning none. The modern default answer is automatic compression (ENCODE AUTO), where Redshift chooses and adjusts encodings; sort key columns are conventionally left RAW so block pruning metadata stays effective. In the lake, compression means columnar Parquet with a splittable codec — smaller scans directly cut Athena and Spectrum cost, since they bill by data scanned.

Partitioning and indexing: partition lake tables on filter columns; use Redshift sort keys as the warehouse's pruning mechanism (Redshift has no conventional B-tree indexes); use DynamoDB GSIs to serve additional access patterns; and in relational engines like Aurora, index the predicate columns queries actually use. Everywhere, the principle is identical — physically organize data so the engine can skip what the query does not need. Materialized views (Redshift and Athena) precompute expensive aggregations when the same rollup is queried repeatedly.

Vectorization is the new v1.1 addition: turning text or other unstructured data into embeddings — numeric vectors whose distance encodes semantic similarity — so that similar meaning can be found with a nearest-neighbor search instead of keyword matching. An Amazon Bedrock knowledge base operationalizes the pattern for retrieval-augmented generation (RAG): you point it at documents in S3, it chunks them, generates embeddings with a Bedrock embedding model, stores the vectors in a vector store (such as Amazon OpenSearch Serverless), and at query time retrieves the most similar chunks to ground a foundation model's answer. From the data-engineering side, know the pipeline shape — source documents, chunking strategy, embedding model, vector store, retrieval — and that vector indexes (HNSW, IVF) trade recall for speed at scale. You are not expected to train models; you are expected to build and feed this pipeline.