Data Modeling and Schema Evolution

What it is

A data model describes how data is organized: tables, columns, keys and relationships. A good model matches how the data will be queried, and stays workable as the source changes.

Schema evolution is the practice of changing a schema over time, such as adding a column, without breaking producers, pipelines or reports.

How it works

Modeling by store type

A star schema has a central fact table with measures (such as sales amount) and foreign keys to dimension tables (such as customer, product and date). A snowflake schema normalizes the dimensions further, which saves space but adds joins. Star schemas are the usual choice for analytics because they keep queries simple and fast.

Normalization and denormalization

Normalization removes duplicated data, which suits transactional systems that update often. Denormalization copies data into wide tables to avoid joins, which suits read heavy analytics and NoSQL stores. Pick based on read and write patterns.

Slowly changing dimensions (SCD) describe how a dimension tracks change. Type 1 overwrites the old value and keeps no history. Type 2 adds a new row with validity dates, so history is kept.

Indexing, partitioning and compression

Choose a model that supports your most common query without scanning everything. Too many indexes slow writes, and too many partitions create tiny files.

Schema conversion

Moving between database engines needs the schema converted first. The AWS Schema Conversion Tool (SCT) and AWS DMS Schema Conversion analyze a source schema, convert tables, views and code to the target engine and report items that need manual work. Then AWS DMS moves the data.

Use this sequence for a heterogeneous migration: convert the schema, create it on the target, run DMS full load and change data capture, then cut over. A homogeneous migration, for example PostgreSQL to PostgreSQL, usually needs no conversion.

Schema evolution in practice

Source systems add, rename and remove columns. Plan for it at each layer.

• Open table formats: Apache Iceberg tracks schemas in metadata and supports adding, dropping and renaming columns, plus type promotion in limited cases, without rewriting data files.
• Avro and the Glue Schema Registry: compatibility modes such as backward, forward and full decide which changes are accepted. Backward compatible means consumers on the new schema can read data written with the old one.
• Glue crawlers: the schema change policy decides whether the table updates when new columns appear.
• Redshift: ALTER TABLE ADD COLUMN is cheap. Changing a column type is limited, often needing a new column or table.
• Parquet and Athena: adding columns at the end is safest. Reading by column name or position behaves differently by format, so test it.

ALTER TABLE orders ADD COLUMN discount DECIMAL(10,2) DEFAULT 0;

Safe changes are adding optional columns. Risky changes are renaming or deleting columns and changing types, which can break consumers. Version breaking changes and announce them to downstream teams.

Data lineage

Data lineage records where data came from and which steps changed it. It supports audits, impact analysis ("what breaks if this column changes") and trust. On AWS you build it from catalog metadata, consistent naming and tags, pipeline run logs and orchestrator history, and the catalog descriptions that people maintain.

Some services offer lineage features of their own, such as Amazon SageMaker ML Lineage Tracking for machine learning workflows.

Reading the exam hint

If the scenario describes frequent new fields from a source, favor a flexible schema approach: Avro or Parquet with a registry or catalog policy, Iceberg tables, or DynamoDB. If it describes rigid reporting, favor a defined star schema in Redshift.