Building the Silver Layer — AUTO CDC and SCD Type 2

In the last lecture we built the bronze layer of the medallion pipeline — two append flows landing raw Debezium CDC events into bronze_orders_cdc and bronze_customers_cdc, untouched. Now we extend the same pipeline with a silver layer: this is where the actual CDC merge logic happens.

Add a new file to the transformations folder — silver.py — for all silver-layer flows, keeping the one-file-per-layer structure established in the bronze lecture.

What Silver Needs to Do

AUTO CDC reads CDC events from a bronze streaming table and applies them to a silver streaming table. Applying them means resolving inserts, updates, and deletes into a coherent current state — and, in this case, doing it with full history preserved via SCD Type 2.

Recall from the core-concepts lecture: AUTO CDC is always created using explicit syntax — there's no implicit form. That means two steps for each table: create the streaming table target, then define the AUTO CDC flow into it.

Silver Orders

Step 1 — declare the target table

pythonCopy
dp.create_streaming_table(
    name="silver_orders",
    comment="SCD Type 2 history of order CDC events from Bronze",
    expect_all_or_drop={
        "valid_order_id": "order_id IS NOT NULL",
        "valid_customer_id": "customer_id IS NOT NULL",
        "valid_amount":      "amount > 0"
    }
)

Silver's job is to guard data quality, so unlike bronze — which barely used any expectations — silver defines three, and uses expect_all_or_drop: every rule must be satisfied, or the record doesn't make it into silver at all. Here, that means order_id and customer_id must both be non-null, and amount must be a genuine positive value.

Step 2 — define the AUTO CDC flow

pythonCopy
dp.create_auto_cdc_flow(
    target = "silver_orders",
    source = "bronze_orders_cdc",
    keys = ["order_id"],
    sequence_by = "ts_ms",
    apply_as_deletes = "op = 'd' or __deleted = 'true'",
    stored_as_scd_type = "2",
    except_column_list = ["_ingest_timestamp", "_source_file",
                          "__deleted", "op", "ts_ms"]
)

A few parameters worth reading carefully:

• keys — the primary key(s) AUTO CDC merges on. Here, order_id alone is sufficient: matched records are treated as updates, unmatched ones as inserts.
• sequence_by — ts_ms, the Debezium processing timestamp. Since CDC tools can produce out-of-order records (network latency, retries, and similar issues), this tells AUTO CDC how to resolve which version of a record is actually the latest, regardless of arrival order.
• apply_as_deletes — after a record matches on the merge key, AUTO CDC still needs to know whether that match represents an update or a delete. This condition — op = 'd' or __deleted = 'true' — covers both the Debezium op field convention and the flattening SMT's __deleted convention, so either signal is honored.
• stored_as_scd_type — "2", meaning full history is preserved rather than overwritten in place.
• except_column_list — bronze carries several columns that don't belong in a business-facing silver table: _ingest_timestamp and _source_file are bronze-specific metadata, and __deleted, op, ts_ms are CDC mechanics that silver has already consumed and doesn't need to expose downstream. Excluding them keeps silver_orders representing just the orders themselves.

One important mechanical detail from stored_as_scd_type = "2": SCD Type 2 automatically adds two columns to the table — __START_AT and __END_AT. A row where __END_AT IS NULL is the current version of that record; a row where __END_AT IS NOT NULL is a historical version that's since been superseded. This is exactly the filter pattern used in later gold-layer queries (__END_AT IS NULL) to select only current records.

Silver Customers

Same two-step shape, same logic, different table:

pythonCopy
dp.create_streaming_table(
    name="silver_customers",
    comment="SCD Type 2 history of customer CDC events from Bronze",
    expect_all_or_drop={
        "valid_customer_id": "customer_id IS NOT NULL",
        "valid_name":        "customer_name IS NOT NULL",
    }
)

dp.create_auto_cdc_flow(
    target      = "silver_customers",
    source      = "bronze_customers_cdc",
    keys        = ["customer_id"],
    sequence_by = "ts_ms",
    apply_as_deletes   = "op = 'd' OR __deleted = 'true'",
    stored_as_scd_type = "2",
    except_column_list = ["_ingest_timestamp", "_source_file",
                          "__deleted", "op", "ts_ms"],
)

The only meaningful differences: the merge key is customer_id instead of order_id, the source is bronze_customers_cdc, and the quality rules check customer_id and customer_name instead of order-specific fields. Everything else — the sequencing, the delete detection, the SCD type, the excluded columns — follows the exact same pattern.

Running It

Running silver.py on its own (via "Run file," since gold doesn't exist yet) updates the pipeline graph to show something new: an actual connection between layers.

Pipeline graph — bronze feeding silver via AUTO CDC

bronze_customers_cdc now visibly flows into silver_customers, and bronze_orders_cdc into silver_orders — with an arrow between them showing the actual lineage, rather than the disconnected nodes bronze produced on its own. silver_customers shows Upserted: 4, and silver_orders shows Upserted: 5 — matching the row counts already sitting in bronze from the last lecture. Since "Run file" only executes the flows defined in silver.py, the bronze nodes here show "Omitted" for output records — they weren't re-run, silver just read their existing table state.

Running the entire pipeline (bronze and silver together, via "Run pipeline" rather than "Run file") is worth doing too, as a sanity check. With no new files added to the landing volume since the last run, every table reports zero new records — which is exactly correct. Auto Loader and AUTO CDC are both fully incremental: with nothing new in the landing zone, there's nothing to reprocess, regardless of how many times the pipeline runs.

Summary

We now have bronze and silver working end-to-end, tested independently and together. Next lecture, we add the gold layer and complete the pipeline.

See you again. Keep learning, and keep growing!