The Trip Your Revenue Report Dropped: SCD2 History and Unknown Members in Gold

Picture finance asking why yesterday's revenue in the dashboard is lower than the payments ledger. The business is RideFlow, a fictional ride-hailing company, and its trip rows are all in Silver. The difference is in Gold: one passenger's rider profile failed a quality rule that morning, so that rider never reached dim_rider, and the report's inner join on rider_sk quietly dropped every trip they took. No job failed. Nothing on the report said rows were missing.

Row validation cannot catch this, because no single row is wrong. The fault sits between tables: a fact that cannot find its dimension, a dimension whose history was overwritten, or a calendar table generated by a side script. In a common pattern, these parts of Gold sit outside any contract.

This article covers three Gold patterns that go beyond row checks: keeping history with SCD Type 2, giving unmatched facts a visible home with an unknown member, and keeping complex logic such as a date dimension in ordinary tested code. It compares the usual ways to do each, then shows how RideFlow declares them in its contracts. It does not claim a contract makes the business data correct.

Why Gold breaks between tables

Three common habits cause it:

• Dimensions overwrite. A rider moves city, the profile row is updated, and every past trip now reports under the new city. History is lost unless someone built versioning.
• Quarantine creates orphans. A good gate holds back a bad dimension row. The facts that reference it still pass their own rules, so they arrive pointing at a key that does not exist.
• Logic hides in side scripts. Fiscal calendars, holiday tables and similar generators often run outside the governed pipeline, so their output is never checked against an agreed shape.

Adverts for data engineers and architects regularly ask for this mix: dimensional modelling, quality checks, schema evolution and enforcement, and quality controls that hold across layers. The patterns are old; keeping them consistent across many teams is the hard part.

The ways teams solve it

All of these can work. The gap the first four leave is the same: the policy for history and for unmatched facts lives in code, one dimension at a time, so there is no one place to see which dimensions keep history, which facts fall back to an unknown member, and how many rows did so today.

How RideFlow declares it

RideFlow runs on LakeLogic, an open-source contract runtime, and its reference repository holds every contract below.

History: SCD Type 2 as a declaration

The Gold rider dimension states which columns create a new version and how versions are keyed:

# contracts/marketplace/rideflow/contracts/gold/gold_dim_rider_profiles.yaml (excerpt)
primary_key:
- rider_id

materialization:
  strategy: scd2
  scd2:
    surrogate_key: rider_profiles_sk
    surrogate_key_strategy: hash
    timestamp_field: updated_at
    effective_from_field: effective_from
    effective_to_field: effective_to
    current_flag_field: is_current
    version_column: version_number
    track_columns:
    - city_code
    - status
    - preferred_payment_method
    unknown_member:
      enabled: true
      surrogate_key_value: '-1'

When city_code, status or preferred_payment_method changes, LakeLogic's materializer closes the current version and writes a new one. Current rows carry an open-ended effective_to (by default 9999-12-31). A dataset rule in the same contract checks that rider_id plus effective_from is unique, so two versions starting at the same moment show up as a failed check on the run rather than passing silently.

Unmatched facts: an unknown member you can count

With unknown_member.enabled, the materializer adds one reserved row with surrogate key -1 to the dimension, and does so idempotently on reruns. The completed-trips fact then looks up each dimension as of the drop-off time and falls back to that row:

-- contracts/marketplace/rideflow/contracts/gold/gold_fact_trip_completed.yaml (excerpt of its transformation)
COALESCE(d2.rider_profiles_sk, '-1') AS rider_sk,
...
LEFT JOIN gold_dim_rider_profiles d2
  ON  r.rider_id = d2.rider_id
  AND r.dropoff_at >= d2.effective_from
  AND r.dropoff_at < COALESCE(d2.effective_to, CAST('9999-12-31 00:00:00' AS TIMESTAMP))

Two details matter. The join is a LEFT JOIN, so the trip stays in revenue totals. The COALESCE on effective_to keeps a current version matchable even if its end date is null; leave it out and every trip by a current rider can fall to the unknown member.

Falling back is only half of it. The fact contract also counts how often it happened:

quality:
  dataset_rules:
  - name: rider_sk_unknown_member_rows
    sql: SELECT COUNT(*) FROM {dataset} WHERE rider_sk = '-1'
    must_be_less_than: 1
    severity: warning
    category: consistency

The trip in the opening now reports under "unknown rider" instead of disappearing, and the run records a warning that says how many rows did so. Analysts can trace those rider_id values back to the dimension's quarantine table. The rule records and alerts; it does not stop the run. A product that must stop on a failed dataset rule can set quality.fail_pipeline_on_dataset_error: true in its contract.

Complex logic: keep it in code, call it from the contract

A date dimension needs fiscal years, ISO weeks and labels. That belongs in Python with tests, not in a YAML expression. RideFlow's contract points at a generator:

# contracts/reference/internal/contracts/gold/gold_dim_date.yaml (excerpt)
external_logic:
  type: python
  engine: spark
  path: ../../_generators/dim_date.py
  entrypoint: run
  args:
    start_date: '2020-01-01'
    end_date: '2030-12-31'
    fiscal_year_start_month: 1

The generator returns a frame; LakeLogic then validates it against the contract's declared fields and rules before writing it. The code does the work, and the contract decides whether its output is acceptable. External logic is still code: it needs review, pinned dependencies and its own tests.

When you might not need this

• A handful of dimensions owned by one team. A well-tested MERGE per dimension, or dbt snapshots, may be all you need.
• Change-data-capture sources already in declarative pipelines. AUTO CDC handles SCD2 there; add the unknown member and counts yourself.
• Dimensions that never change and facts that always match. Reference data with strict upstream keys may not need an unknown member at all.

The contract approach earns its keep when many teams publish dimensions and facts to each other and you want one place to see the policy and one count of the misses.

Limits worth knowing

• Declared is not proven. An SCD2 block states the policy. Late-arriving changes, duplicate timestamps, deletes and null business keys still need fixtures that show the history comes out right (test contracts locally first).
• The lookup is written, not inferred. In RideFlow the fact's SQL performs the as-of join; the contract records the foreign key and counts unknowns, but a wrong join condition is still a wrong join.
• An unknown member hides detail. It keeps totals whole but moves the rows out of every rider breakdown. Watch the count; a rising number usually means a dimension problem upstream.

Key takeaways

1. Facts that cannot find their dimension should land somewhere visible, not vanish in an inner join.
2. Count unknown-member rows on every run; the count is the early signal that a dimension is losing rows.
3. Declare which columns create history, so every dimension's policy can be read in one place.
4. As-of joins against SCD2 need care at the open end of the current version.
5. Keep complex generators in tested code, and validate their output like any other source.

See it in the reference project

The RideFlow repository contains the rider dimension, the completed-trips fact and the date generator shown here. For how bad rows reach quarantine in the first place, read Who owns a negative fare? A governed data mesh on Databricks.