SCD Type 2 dimension inflating a finance dashboard and recomputing nightly
A retail team models dim_customer as an SCD Type 2 streaming table in a Lakeflow pipeline, fed by APPLY CHANGES from a bronze CDC stream carrying customer_id, version_ts, and a soft-delete flag. There are 6.2 million customers and roughly 900,000 change rows per day. Two problems appeared after launch. A finance dashboard that joins dim_customer to a 400-million-row sales fact now reports 2.4x the expected revenue, and the analysts insist they only added the dimension for segment names. Second, a cost review shows the pipeline's nightly run reading the entire CDC history every night, while the materialized view agg_sales_by_segment, which joins the dimension to the fact, takes 2 h 40 min even on days with no new sales. The team also plans to remove the deprecated legacy_tier column next sprint.
1. Explain the 2.4x revenue inflation and specify exactly how the join must be written for both a daily snapshot report and a point-in-time report.
Read the worked answer
SCD Type 2 stores one row per customer per version, so the fact rows joins to every historical version of the matching customer. With a customer base that has churned through several tier changes, a 2.4x multiplier is entirely plausible; the inflation is proportional to how many versions the joined customers have.
For a current-state report, join on the natural key and restrict to the open version: fact.customer_id = dim.customer_id AND dim.__END_AT IS NULL. Do not rely on convention - publish a governed dim_customer_current view in the gold layer that already contains that predicate and point BI tools at it, then revoke direct SELECT on the versioned table from analyst groups.
For a point-in-time report where the fact carries an event timestamp, a current-only join is wrong because it attributes past sales to today's segment. Use a range join: fact.event_ts >= dim.__START_AT AND (fact.event_ts < dim.__END_AT OR dim.__END_AT IS NULL). That join is far more expensive, so keep it in a dedicated model rather than the shared dashboard. Also validate how the soft-delete flag is mapped in APPLY AS DELETE WHEN - if deletes physically remove rows instead of closing the version, historic point-in-time joins silently lose customers.
2. Why does the nightly run reprocess full CDC history, and why is the materialized view recomputing everything? What would you change and what would you keep?
Read the worked answer
A streaming table is incremental by construction: it resumes from a checkpoint, so a nightly read of the whole source means the checkpoint or schema location was reset, the target was recreated, or someone scheduled a full refresh. Check the pipeline event log for a full refresh first - that is the most common cause and it also rewrites __START_AT history.
The materialized view is a different contract. Lakeflow materialized views recompute the query against current inputs; incremental refresh is an optimization it may or may not apply, and a large join to a 400-million-row fact will usually be recomputed in full. So the view is doing what it promised.
Decision: if agg_sales_by_segment must be fresh within minutes and the fact is append-only, rebuild it as a streaming table - deduplicate, aggregate in a stateful query, and let it process only new files. If hourly freshness is acceptable and the aggregation is complex, keep the materialized view but decouple it from the ingestion schedule and let it refresh on a longer trigger, and add a segment-level reconciliation against the fact so a bad join is caught by data, not by finance.
3. The `legacy_tier` column must be dropped next sprint. What does that require in this pipeline, and how do you sequence it without breaking consumers?
Read the worked answer
Removing a column from a streaming table is not an incremental schema change. The pipeline will fail on the schema mismatch between the declared query and the existing table, and the only supported remedy is a full refresh of that table, which drops and rebuilds it. For an SCD2 table that means the entire version history, including __START_AT and __END_AT, is regenerated from the source - and if the CDC source only retains 30 days, history older than that is gone permanently.
Sequence it as a blue/green change rather than an in-place edit. Create dim_customer_v2 with the new schema, backfill it from the bronze CDC stream (or from a retained snapshot of the current table if the source cannot replay), validate row counts and per-customer version counts against v1, then swap the gold view dim_customer_current to v2 and drop v1 after a cooling-off period. Announce the schema change to dashboard owners, since any SELECT * consumer will break at the swap instead of at an unpredictable refresh time.
If the history is genuinely needed and the source cannot replay it, the safer option is to keep the column, stop populating it with new values, and document it as reserved.