Streaming dimensional modeling — SCD2 dim + fct with temporal (as-of) join

6 h
requires STH-04

From your world. Your core strength: Kimball SCD2 dimensions and point-in-time fact enrichment, now built on a streaming CDC feed

Practice quiz with instant feedback. For a scored run that counts toward your local progress, use uv run de quiz STH-05.

1You build an SCD2 dim_customer from a CDC changelog. How do you derive the valid_to of each version? AIt is always NULL Bvalid_to = LEAD(effective_from) over the versions of the key, ordered by effective time. The valid_to of the newest version stays open Cvalid_to = the processing time when the row arrived Dvalid_to = effective_from + 1 day, fixed

2An order placed at t=30 belongs to customer Alice. She was 'std' until t=100 and 'gold' after. The pipeline reprocesses the order next week. Which tier should fct_orders record? Agold — her current tier Bstd — the tier effective at the event time of the order (t=30) CWhatever tier is current when the pipeline reprocesses the order DBoth, as two rows

3Which join condition expresses a point-in-time (as-of) match between an order and an SCD2 dimension version? Ao.cid = d.cid AND d.is_current = true Bo.cid = d.cid AND o.order_ts >= d.valid_from AND o.order_ts < d.valid_to Co.cid = d.cid (any version) Do.order_ts = d.valid_from

4You order CDC change events to build SCD2 validity intervals. Which timestamp should you order by? AThe processing time when the event arrived BThe business effective time of the change CA random tiebreaker DThe commit time of the Flink checkpoint

5What makes a streaming dimensional model trustworthy (TESTING.md G6)? AIt runs without errors BIts result equals an independent batch rebuild (for example a DuckDB AS-OF join) over the same events CIt uses the newest Flink version DIt has more than one TaskManager

6Why is it unsafe to join a fact to the dimension row where is_current = true (instead of the as-of version)? AIt is always safe BIt stamps every fact, including historical and reprocessed facts, with the latest attributes. This restates history CIt produces duplicate rows DIt drops NULL keys

7Which statements about an SCD2 dimension built from CDC are correct? (choose all) (select all that apply) AEach change event becomes a new version row BThe newest version per key has an open valid_to and is_current = true CA fact matches exactly one version via its validity interval DEvery version must have is_current = true

8In Flink SQL, you use a temporal join FOR SYSTEM_TIME AS OF o.order_ts. What must the versioned (dimension) table have? ANo key BA PRIMARY KEY and an event-time attribute, so versions are ordered per key CExactly one row per key DA processing-time attribute only