cleandataengineering.aiData Platform, from the big picture to the code
You are here: Start here › Silver to gold › Dimensions

Data PlatformZoom level 2

Dimensions: from silver's history to members with a validity span

A dimension describes the things a fact is about: dealers, materials, presses. In gold a dimension keeps history, so a question about last March can use the dealer's region as it was last March.

Silver already keeps that history as type 2: one row per version of a business key, each with the time span in which it was true. The gold dimension is that silver table reshaped. One function does this for every dimension, and a dimension's manifest is not allowed to bring its own code.

Silver version 1D-1, north, from 9 NovSilver version 2D-1, south, from 11 NovCommon pipelinekey, span, columnsMember 1: northfrom the beginning to 11 NovMember 2: southfrom 11 Nov to the end of timeReserved: -1 unknown, -2 not applicablealways present
Each silver version becomes one member. The first member's span is stretched back to the beginning of time. Two reserved members are added.
Goes inEvery row silver holds for the dimension's one input, with silver's own columns _valid_from, _valid_to, _is_current and _is_deleted.
What happensEach version gets a stable integer key and a validity span. The contract's columns are kept, in contract order. Two reserved members are added. The keys are checked to be unique.
Comes outThe whole dimension, written in one transaction in place of the old one.

A worked example

Dealer D-1 moved from the north region to the south on 11 November. Dealer D-2 has one version. The first table is silver. Each row is one version.

dealer_coderegion_valid_from_valid_to_is_current
D-1north2026-11-09 00:002026-11-11 00:00false
D-1south2026-11-11 00:00(open)true
D-2west2026-11-09 10:00(open)true

The second table is the gold dimension. Each row is one member. The keys are the real numbers the recipe gives for these inputs.

dealer_skdealer_coderegionvalid_fromvalid_tois_current
80631743438072549D-1north1900-01-01 00:002026-11-11 00:00false
66182239882639827D-1south2026-11-11 00:009999-12-31 00:00true
1003977605158419583D-2west1900-01-01 00:009999-12-31 00:00true
-1unknownunknown1900-01-01 00:009999-12-31 00:00true
-2not applicablenot applicable1900-01-01 00:009999-12-31 00:00true

Three things changed between the two tables. Each version has a key. The first version of each dealer now starts in 1900, not on the day its record arrived. An open end became the year 9999, so a join never has to deal with a missing value.

The settings that drive it

WhereKeyWhat the pipeline takes from it
Gold manifestgrainThe name of the key column, for example dealer_sk.
Gold manifestinputs[0].datasetThe silver table to reshape. The key recipe also uses this name.
Contractthe columnsWhich columns the dimension has, their types and their order.
Metadata storethe input's primary keyThe business key, for example dealer_code.
Silverhistory setting of the inputWhether silver kept versions at all. A silver table with type 2 history gives several members per key. One with type 1 history gives one.

The code

src/dataplatform/adapters/spark_gold.py:98The common pipeline: the whole of how a dimension is shaped.
    def dimension(self, plan: GoldPlan, silver: DataFrame, run_id: str) -> DataFrame:
        bk = list(plan.business_key)
        # nulls first stated (Spark's default): a window order on SQLFrame leaves it to the engine
        by_key = Window.partitionBy(*bk).orderBy(F.col("_valid_from").asc_nulls_first(),
                                                 F.col("_row_key").asc_nulls_first())
        rows = silver.withColumn("_n", F.row_number().over(by_key))
        sk = self.surrogate(plan.inputs[0].dataset, [F.col(c) for c in bk], F.col("_valid_from"))
        own = {plan.surrogate: sk,
               "valid_from": F.when(F.col("_n") == 1, F.to_timestamp(F.lit(BEGINNING))).otherwise(F.col("_valid_from")),
               "valid_to": F.coalesce(F.col("_valid_to"), F.to_timestamp(F.lit(END_OF_TIME))),
               "is_current": F.col("_is_current")}
        cols = [own[c].alias(c) if c in own else F.col(c).cast(t).alias(c) for c, t in plan.columns]
        extra = [F.col("_valid_from").alias("_source_valid_from"), F.col("_is_deleted"), F.lit(run_id).alias("_run_id")]
        members = rows.select(*cols, *extra)
        return members.unionByName(self._reserved(plan, run_id, members.schema))
  1. Number the versions. Within each business key the versions are numbered by their start. The number is only used to find the first one.
  2. Compute the key. The key is computed from the input's name, the business key and the version's start. Keys explains the recipe.
  3. Set the span. valid_from is silver's start, except for version number 1, which gets the beginning of time. valid_to is silver's end, or the end of time when silver's end is open.
  4. Keep the contract's columns. Each contract column is taken from silver and cast to the contract's type. The four columns the pipeline computes itself replace silver's.
  5. Keep three extra columns. _source_valid_from keeps the real start, _is_deleted keeps silver's delete mark, and _run_id names the run.
  6. Add the reserved members. Two rows are appended with keys -1 and -2.
Why the first version reaches back to 1900

A dealer's first record can arrive after the dealer's first order. If the first member only started when its record arrived, that early order would find no member whose span contains its time and would stay on the unknown member for ever. Stretching the first version back means an event before a member's first version joins that first version. The real start is not lost. It is kept in _source_valid_from, and the key is still computed from it.

src/dataplatform/adapters/spark_gold.py:114The two reserved members. Text columns hold the words unknown or not applicable, whole numbers hold 0.
    def _reserved(self, plan: GoldPlan, run_id: str, schema: T.StructType) -> DataFrame:
        rows = []
        for sk, text in RESERVED.items():
            row: dict[str, Any] = {}
            for f in schema.fields:
                if f.name == plan.surrogate:
                    row[f.name] = sk
                elif f.name == "valid_from":
                    row[f.name] = datetime(1900, 1, 1, tzinfo=UTC)
                elif f.name == "valid_to":
                    row[f.name] = datetime(9999, 12, 31, tzinfo=UTC)
                elif f.name == "is_current":
                    row[f.name] = True
                elif f.name == "_is_deleted":
                    row[f.name] = False
                elif f.name == "_run_id":
                    row[f.name] = run_id
                elif isinstance(f.dataType, T.StringType):
                    row[f.name] = text
                elif isinstance(f.dataType, T.LongType | T.IntegerType):
                    row[f.name] = 0
                else:
                    row[f.name] = None
            rows.append(row)
        df = self.spark.createDataFrame(rows, schema=T.StructType([T.StructField(f.name, f.dataType, True)
                                                                   for f in schema.fields]))
        # set the two bounds as the session (UTC) reads them, the same literals the members use
        return df.withColumn("valid_from", F.to_timestamp(F.lit(BEGINNING))).withColumn(
            "valid_to", F.to_timestamp(F.lit(END_OF_TIME)))
src/dataplatform/gold/harness.py:178The harness side: shape, check the keys are unique, replace the whole table, record.
def _dimension(ctx: Ctx, p: GoldPlan) -> GoldTable:
    silver = ctx.tables.silver(p.inputs[0].dataset, _columns(ctx, p.inputs[0].dataset))
    rows = ctx.engine.dimension(p, silver, ctx.run_id)
    n, dup = ctx.engine.count(rows), ctx.engine.duplicates(rows, (p.surrogate,))
    _step(ctx, p, "detect.KEY-01", "failed" if dup else "succeeded", {"members": n, "duplicate_keys": dup},
          f"{dup} surrogate keys repeat in {p.name}" if dup else "")
    if dup:
        raise CheckFailed(f"{p.name}: {dup} surrogate keys repeat (hash collision or a broken history)")
    written = ctx.tables.write_dimension(p, rows)
    counts = {"members": n, "rows_written": written}
    _step(ctx, p, "gold.write", "succeeded", counts)
    ctx.store.finish_dataset_run(ctx.run_id, p.schema, p.name, "succeeded", counts)
    return GoldTable(p.name, "succeeded", counts)

The harness reads silver, calls the pipeline, and counts duplicate keys. A duplicate means two versions got the same key, which happens only if silver's history is broken or two different texts hashed to the same number. Either way the step detect.KEY-01 is recorded as failed and the dimension is not written. Otherwise the whole table is replaced.

src/dataplatform/adapters/spark_gold.py:352The write edge for a dimension on Delta: one overwrite of the whole table.
    def write_dimension(self, plan: GoldPlan, rows: DataFrame) -> int:
        rows = rows.localCheckpoint(eager=True)
        rows.write.format("delta").mode("overwrite").option("overwriteSchema", "true").save(str(self.path(plan)))
        n = int(rows.count())
        _free(rows)  # serverless refuses unpersist (13 Nov)
        return n

Replacing the whole table every run is safe because the keys are computed from the data. A version that did not change gets the same key it had yesterday, so every fact row that points at it still points at it.

The date dimension is generated, not read

No source sends a calendar. The platform makes one row per day itself, with calendar and fiscal columns, in src/dataplatform/gold/calendar.py. It is not built by the harness and has no manifest. Windows and completeness shows the code.

How it is tested

tests/dataplatform/test_gold_engine.py runs the pipeline on the three silver versions of the worked example above and checks each member against the Python key recipe.

tests/dataplatform/test_gold_engine.py:173The silver rows the test starts from. T0 is 9 November 2026 at midnight UTC, and ts(48) is 48 hours later.
VERSIONS = [{"dealer_code": "D-1", "region": "north", "_valid_from": T0, "_valid_to": ts(48), "_is_current": False},
            {"dealer_code": "D-1", "region": "south", "_valid_from": ts(48), "_valid_to": FOREVER},
            {"dealer_code": "D-2", "region": "west", "_valid_from": ts(10), "_valid_to": FOREVER}]
tests/dataplatform/test_gold_engine.py:186One case per member: its key from the Python recipe, and the span it must have.
        ("the first version reaches back to the beginning", "D-1", T0, "north", "1900-01-01 00:00",
         "2026-11-11 00:00"),
        ("a later version starts at its change", "D-1", ts(48), "south", "2026-11-11 00:00", "9999-12-31 00:00"),
        ("a single version", "D-2", ts(10), "west", "1900-01-01 00:00", "9999-12-31 00:00"),
    ],
)
def test_dimension_keys_and_validity(dim, case, code, start, region, valid_from, valid_to):
    """The key is keys.surrogate_key of (source, business key, version start): the same number in Python and Spark."""
    sk = K.surrogate_key("dealer_api.dealer", (code,), start)
    [row] = dim.where(F.col("dealer_sk") == sk).select(
        "dealer_code", "region", F.date_format("valid_from", "yyyy-MM-dd HH:mm").alias("f"),
        F.date_format("valid_to", "yyyy-MM-dd HH:mm").alias("t")).collect()
    assert (row["dealer_code"], row["region"], row["f"], row["t"]) == (code, region, valid_from, valid_to)
tests/dataplatform/test_gold_engine.py:207The same versions in another order, in another run, give the same keys.
def test_a_rebuild_gives_the_same_keys(spark, engine, dim):
    again = engine.dimension(DIM, silver(spark, DEALERS, list(reversed(VERSIONS))), "run-2")
    assert sorted(r[0] for r in dim.select("dealer_sk").collect()) == sorted(
        r[0] for r in again.select("dealer_sk").collect())
Not built yet, or known gaps
  • The pipeline always makes one member per silver version. The manifest's history key is not read by the engine, so a dimension cannot ask for only the current version of each key.
  • A member that was deleted at the source stays in the dimension with _is_deleted set. The key lookup does not look at that flag.
  • The three extra columns the pipeline keeps are not in the dimension contracts.
  • A dimension is rebuilt whole on every run, even when nothing in its silver input changed.
  • A dimension has exactly one input. A dimension that combines two silver tables would need a model, and the plan refuses a model on a dimension.
  • The date dimension is written by the run script, not by the harness. It is not in the metadata store and has no run log rows.