Schema Evolution: How dlt Adapts to Changing Data

24.1 What a Schema Is, and Where dlt Keeps It (20 min)

A schema describes your tables: their names, columns, data types, keys and nullability. You wrote schemas by hand in SQL on Day 11 (CREATE TABLE … (rate DOUBLE, …)). dlt infers a schema from the data and stores it:

• inside the destination, in the _dlt_version table (every version ever used);
• in the pipeline's working folder ~/.dlt/pipelines/<name>/schemas/;
• in memory, as pipeline.default_schema.

Create day24/schema_basics.py:

from datetime import date
from common import practice_pipeline, reset

reset()
pipeline = practice_pipeline()

rows = [
    {"date": date(2026, 7, 1), "series_id": "V39079", "value": 2.25},
    {"date": date(2026, 8, 1), "series_id": "V39079", "value": 2.25},
]
pipeline.run(rows, table_name="rates")

schema = pipeline.default_schema
print("Schema version:", schema.version)
print(schema.to_pretty_yaml())

Find the rates table in the YAML output. For each column you'll see data_type, nullable and, where relevant, flags like primary_key. You'll also see the _dlt_* system tables.

Look at the stored versions in DuckDB:

duckdb schema_lab.duckdb -c "SELECT version, engine_version, inserted_at FROM lab._dlt_version"

Key idea: dlt's schema is a living document. Each time your data has a new shape, dlt proposes a new version, migrates the destination tables (adds columns, creates tables) and records the change.

24.2 Type Inference and Column Hints (25 min)

How dlt chooses types

The first row decides, which can be a trap

Add to schema_basics.py (or a new file):

reset()
pipeline = practice_pipeline()

# The first batch happens to contain whole numbers only...
pipeline.run([{"series_id": "X", "value": 2}, {"series_id": "Y", "value": 5}],
             table_name="trap")
print(pipeline.default_schema.tables["trap"]["columns"]["value"]["data_type"])  # bigint

value was typed bigint, because the first values looked like integers. When 2.25 arrives next week, dlt must deal with a type conflict (24.4). The fix is to tell dlt what you mean, using column hints:

pipeline.run([{"series_id": "X", "value": 2}],
             table_name="no_trap",
             columns={"value": {"data_type": "double"}})

Useful column hints

You can give hints in @dlt.resource(columns={...}) (as on Days 21–23) or in pipeline.run(..., columns=...).

Rule for economic data: always hint value as double and dates as date. Never let the first batch decide the type of a measurement.

Try it 24.2

Load [{"obs_date": "2026-07-01", "value": 169}] twice into two tables: once without hints and once with hints making obs_date a date and value a double. Compare with DESCRIBE.

reset()
pipeline = practice_pipeline()
data = [{"obs_date": "2026-07-01", "value": 169}]

pipeline.run(data, table_name="unhinted")
pipeline.run(data, table_name="hinted",
             columns={"obs_date": {"data_type": "date"},
                      "value": {"data_type": "double"}})

duckdb schema_lab.duckdb -c "DESCRIBE lab.unhinted" -c "DESCRIBE lab.hinted"

24.3 Simulating an API That Changes (28 min)

APIs change: new fields appear, others disappear or get renamed. Let's simulate three "versions" of a CPI API and watch what dlt does. Create day24/api_versions.py:

from datetime import date
from common import practice_pipeline, reset

reset()
pipeline = practice_pipeline()
HINTS = {"ref_month": {"data_type": "date"}, "value": {"data_type": "double"}}


def load(rows, label):
    pipeline.run(rows, table_name="cpi_feed", columns=HINTS)
    cols = list(pipeline.default_schema.tables["cpi_feed"]["columns"].keys())
    print(f"{label}: schema v{pipeline.default_schema.version} → {cols}")


# v1 — the original API
load([{"ref_month": date(2026, 5, 1), "geo": "Canada", "value": 167.8}], "v1")

# v2 — a new field appears (a quality flag)
load([{"ref_month": date(2026, 6, 1), "geo": "Canada", "value": 168.2,
       "status": "A"}], "v2")

# v3 — 'geo' is renamed to 'geography'
load([{"ref_month": date(2026, 7, 1), "geography": "Canada", "value": 169.0,
       "status": "A"}], "v3")

python day24/api_versions.py
duckdb schema_lab.duckdb -c "SELECT ref_month, geo, geography, value, status FROM lab.cpi_feed ORDER BY 1"

This behaviour, called evolve, is ideal for a raw layer: nothing is lost and loads never fail. But it's dangerous for anything downstream, where a renamed field can break a model without any error. Day 25 introduces schema contracts to control this.

Detecting renames downstream

The staging layer is the right place to absorb such changes explicitly:

-- Staging copes with both versions of the API
SELECT ref_month,
       COALESCE(geography, geo) AS geo,
       value
FROM lab.cpi_feed;

24.4 Type Changes and Variant Columns (20 min)

What if a field changes type? Suppose the API starts sending the text "NA" instead of a number.

reset()
pipeline = practice_pipeline()
hint = {"value": {"data_type": "double"}}

pipeline.run([{"series_id": "V39079", "value": 2.25}], table_name="variants", columns=hint)
pipeline.run([{"series_id": "V39079", "value": "NA"}], table_name="variants")

print(list(pipeline.default_schema.tables["variants"]["columns"].keys()))

duckdb schema_lab.duckdb -c "SELECT * FROM lab.variants"

dlt can't put "NA" into a DOUBLE column, and it won't throw data away, so it creates a variant column named like value__v_text to hold values of the new type. The original value column stays clean.

Variant columns are a warning light:

• In raw: acceptable, since you can still see what arrived.
• Downstream: a sign that parsing should have happened earlier. Here that means converting "NA" to None (Day 2's try/except), which is exactly what our resources already do.

Find variant columns across your whole warehouse with SQL:

SELECT table_schema, table_name, column_name
FROM information_schema.columns
WHERE column_name LIKE '%\_\_v\_%' ESCAPE '\';

Consider adding this as a warn test in sql/tests/ (Day 18).

24.5 Naming Conventions: How Keys Become Column Names (18 min)

dlt normalises every table and column name to snake_case, so names are valid and consistent in any database.

reset()
pipeline = practice_pipeline()
pipeline.run([{
    "SeriesTitleEn": "Consumer Price Index, Canada",
    "refPer": "2026-07-01",
    "V39079": 2.25,
    "BD.CDN.10YR.DQ.YLD": 3.75,
    "Products and product groups": "All-items",
}], table_name="Naming Demo")

duckdb schema_lab.duckdb -c "DESCRIBE lab.naming_demo"

You'll see names such as series_title_en, ref_per, v39079, a snake_case version of the bond-yield code, and products_and_product_groups. The table itself became naming_demo.

Implications:

• Column names in DuckDB may differ from the keys in the API. When in doubt, check DESCRIBE.
• Series IDs used as keys become column names. That works for 4 series and becomes a disaster for 400 (a new column per series). Day 26 shows how to turn "keys as data" into rows.

24.6 Exporting Schemas to Git · Mini-Project (25 min)

If schema changes matter, they should be reviewed like code. dlt can write the schema to a YAML file after every run:

pipeline = dlt.pipeline(
    pipeline_name="schema_lab",
    destination=dlt.destinations.duckdb("schema_lab.duckdb"),
    dataset_name="lab",
    export_schema_path="schemas/export",
)

After a run, schemas/export/<schema_name>.schema.yaml contains the current schema. Commit it. When the API changes, git diff schemas/ shows exactly which columns appeared or changed type. That makes schema drift a reviewable event instead of a surprise.

dlt also accepts an import_schema_path: a schema file you edit by hand (e.g. to add hints) that dlt applies on the next run. It's useful for large projects, but for this course hints in code are enough.

Mini-project: detect a (simulated) StatCan API change

1. Write a resource vector_titles() that yields StatCan-style metadata records {"vectorId": 41690973, "SeriesTitleEn": "...", "frequencyCode": 6} for two vectors.
2. Load it with export_schema_path="schemas/export" and commit the exported YAML.
3. "Break" the API: rename SeriesTitleEn to seriesTitleEn and change frequencyCode to the string "monthly". Load again.
4. Run git diff schemas/ and explain every change you see.

import dlt
from common import reset

reset("schema_diff")
pipeline = dlt.pipeline(pipeline_name="schema_diff",
                        destination=dlt.destinations.duckdb("schema_diff.duckdb"),
                        dataset_name="lab", export_schema_path="schemas/export")

v1 = [{"vectorId": 41690973, "SeriesTitleEn": "CPI; Canada; All-items", "frequencyCode": 6},
      {"vectorId": 2062815, "SeriesTitleEn": "Unemployment rate; Canada", "frequencyCode": 6}]
pipeline.run(v1, table_name="vector_titles")
# → git add schemas && git commit -m "baseline schema"

v2 = [{"vectorId": 41690973, "seriesTitleEn": "CPI; Canada; All-items", "frequencyCode": "monthly"}]
pipeline.run(v2, table_name="vector_titles")

Day 24 checklist

• I know where dlt stores schemas and how versions are recorded
• I use column hints so measurement types never depend on the first batch
• I can predict what dlt does with new, missing, renamed and re-typed fields
• I recognise variant columns and can find them with SQL
• I understand name normalisation, and why IDs-as-keys is a problem
• I export schemas to Git so changes are reviewable

git add .
git commit -m "Day 24: schema inference, evolution, variant columns, exported schemas"
git push