Implement Dimensional Models, SCDs/Snapshots, Incremental/CDC Loads, Data Quality, Orchestration, and Semantic Metrics

Learning outcomes

1. Problem frame: a green DAG can still publish the wrong 665 USD

AtlasMart’s capstone pipeline can complete every technical task and still be wrong. If a semantic query silently excludes the sales channel, the executable fixture returns 665 USD instead of governed 820 USD. The pipeline is therefore designed around state transitions and certification evidence, not task color.

2. Implement the atomic star with enforceable local constraints

The local SQLite harness uses three core structures: fact_sales, dim_product, and dim_customer_scd. The implementation is deliberately small enough to inspect, yet it preserves production concerns: composite fact grain, dimension relationships, accepted values, SCD windows, and additive measures.

PRAGMA foreign_keys = ON;CREATE TABLE dim_product(  product_id   TEXT PRIMARY KEY,  product_name TEXT NOT NULL,  category     TEXT NOT NULL);CREATE TABLE fact_sales(  order_id     TEXT NOT NULL,  line_no      INTEGER NOT NULL,  order_ts     TEXT NOT NULL,  customer_id  TEXT NOT NULL,  product_id   TEXT NOT NULL REFERENCES dim_product(product_id),  channel      TEXT NOT NULL CHECK(channel IN ('web','store','sales')),  status       TEXT NOT NULL CHECK(status='paid'),  qty          INTEGER NOT NULL CHECK(qty > 0),  revenue_usd  INTEGER NOT NULL CHECK(revenue_usd >= 0),  cost_usd     INTEGER NOT NULL CHECK(cost_usd >= 0),  PRIMARY KEY(order_id, line_no));

SQLite constraints are executable local evidence, but managed analytical engines vary in which constraints are enforced, informational, or optimizer-only. Production deployment must verify the actual engine.

3. SCD2 history: half-open windows prevent double matches

Customer C001 has three synthetic versions: SMB before 2026-09-18 08:30 UTC, Growth until 2026-09-19 00:00 UTC, then Mid-Market. The validity rule is effective_from <= event_time < effective_to, with NULL end for the current row. Half-open boundaries let adjacent versions meet without overlap.

SELECT segmentFROM dim_customer_scdWHERE customer_id = 'C001'  AND effective_from <= '2026-09-18T12:00:00Z'  AND (effective_to IS NULL OR '2026-09-18T12:00:00Z' < effective_to);-- Growth

The executed test proves adjacent C001 windows are non-overlapping and exactly one row is current. It does not prove all future source change patterns are valid; production still needs overlap/gap tests for every business key.

4. Quality gate: schema constraints are necessary but not sufficient

The capstone stages 13 rows: the ten canonical facts plus an exact duplicate, an orphan P404 product, and a negative quantity. The controlled gate accepts ten and quarantines three. The canonical fact remains unchanged.

quality: 13 staged -> 10 accepted / 3 quarantinedaccepted fact controls: (10, 8, 12, 820, 495, 325)production controls changed by quarantine? no

Quarantine preserves evidence and permits bounded repair/reprocessing. Silently dropping rows would make source-to-target reconciliation impossible.

5. CDC is a transactional state machine, not “insert newer rows”

The disposable CDC target begins at waterline 208. Event e209 inserts a new synthetic line; an injected crash occurs before commit, so both the row and watermark roll back. Retry applies once. Duplicate redelivery is ignored. Event e210 deletes the same synthetic row, returning controls to the baseline; its duplicate is ignored.

rolled_backappliedduplicate_ignoredappliedduplicate_ignoredbaseline            (10, 8, 12, 820, 495, 325)after e209 insert    (11, 9, 13, 870, 525, 345)after e210 delete    (10, 8, 12, 820, 495, 325)

def apply_event(conn, event_id, seq, op, payload):    conn.execute("BEGIN")    watermark = read_watermark(conn)    if seen(event_id) or seq <= watermark:        conn.rollback()        return "duplicate_ignored"    mutate_target(conn, op, payload)    remember_event(conn, event_id, seq)    update_watermark(conn, seq)    conn.commit()  # target + event identity + watermark become one state transition

The accepted production waterline remains 208 because the capstone CDC exercise uses a disposable copy. That distinction is essential: a recovery test must not mutate course continuity just to demonstrate restart behavior.

6. Snapshots remain separate when measures are semi-additive

Sales events and inventory state must not be forced into one universal fact. The course established periodic inventory snapshots separately because on-hand units are additive across products/locations but generally not additive across snapshot dates. The capstone bus matrix preserves this separation. If an inventory snapshot is backfilled, its exact date/location/product grain and source evidence must be reconciled independently from sales.

7. Orchestration expresses dependencies; certification proves data state

The capstone DAG uses the logical sequence below. A scheduler can implement it in many products; the semantics are vendor-neutral.

source_ready   -> ingest_immutable   -> contract_and_quality_gate   -> resolve_dimensions_history   -> merge_atomic_fact_and_watermark   -> metric_and_reconciliation_tests   -> publish_materializations_marts   -> certify_and_notify

A task can return exit code 0 while the source is incomplete or a metric drifts. Certification therefore inspects manifest completeness, atomic controls, history validity, metric golden results, access policy, and freshness. Scheduling alone never establishes correctness.

8. Governed semantic metric: same fact, one versioned meaning

gross_revenue_usd.v1 sums paid line revenue in USD under the governed contract. The fixture’s deliberately wrong query excludes channel='sales', reproducing the earlier semantic incident pattern.

-- governed metric v1SELECT SUM(revenue_usd)FROM fact_salesWHERE status='paid';-- 820-- deliberate dashboard driftSELECT SUM(revenue_usd)FROM fact_salesWHERE status='paid' AND channel <> 'sales';-- 665

The 155 USD difference is not a performance issue or a source delay. It is semantic drift. Fixing it requires restoring the governed metric contract or explicitly creating a new metric/version—not relabeling 665 as “revenue.”

9. Controlled failure: unbounded append on retry

Repair: stable grain keys plus stable event identity, atomic transaction boundaries, bounded source scope, duplicate/delete semantics, and a watermark committed with the target. Then inject crash/retry/duplicate tests before relying on the mechanism.

10. Local lab: execute the capstone state transitions

# Expected deterministic checkpoints from the executed harnessassert controls == (10, 8, 12, 820, 495, 325)assert c001_segment_at_noon_sep18 == "Growth"assert quality == {"staged": 13, "accepted": 10, "quarantined": 3}assert cdc_states == [    "rolled_back", "applied", "duplicate_ignored",    "applied", "duplicate_ignored"]assert governed_revenue == 820assert drifted_revenue == 665assert final_disposable_cdc_controls == controls

11. Verification checklist

• Atomic fact uniqueness is enforced at the declared line grain.
• SCD2 windows do not overlap and exactly one current row exists per current member.
• Defects are quarantined with reasons; control totals do not silently change.
• Crash before commit leaves row state and watermark unchanged.
• Retry and duplicate redelivery converge; explicit delete restores the baseline.
• Certification checks data/metric state, not only task completion.
• Metric v1 reproduces 820 USD; the 665 USD negative case is detected.

12. Production judgment and bridge to Lesson 3

The capstone now has reproducible logical state and restart semantics. Only after correctness is stable is it meaningful to choose indexes, materializations, marts, workload isolation, or cloud/lakehouse serving boundaries. Lesson 3 makes those choices from measured workload evidence while preserving the exact results established here.

Knowledge check

Authoritative references

• SQLite — Transactions for the local atomic state-transition model.
• SQLite — Foreign Key Support for local relationship enforcement.
• Kimball Group — DW/BI resources for SCD, snapshot, grain, and fact/dimension techniques.
• Big Data Academy — Data Warehousing and Dimensional Modeling curriculum.