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

Data PlatformZoom level 2

The facts the harness builds

A fact holds the numbers people add up. The harness builds every fact the same way: it calls the fact's model over the silver inputs, and replaces the windows whose content changed.

A reader coming from other platforms may expect a setting that names the kind of fact, such as transaction, periodic snapshot or accumulating snapshot. This harness has no such setting. There is one kind of fact build. What makes facts differ is the model, the grain and the window. The tyre company has three facts, and between them they cover three common shapes.

fact_otifone row per open order linewindow: promised date, dayfact_salesone row per dealer, material, monthwindow: sale monthfact_scrapone row per press, material, hourwindow: cure hourOpen: The same harnessThe same harnesskeys, windows, checks, replacezoom in ›
Each top box is one model written by a person. All three go through the same highlighted box.

The three facts side by side

Each row is one fact. The columns say what one row of the fact means, which silver tables it reads, the window it is rebuilt by with the lookback in brackets, and what kind of model it has.

FactOne row meansInputsWindow (lookback)ModelShape
fact_otifOne open order line, judged on time and in fullorder lines, deliveriespromised date, by day (35)SQLA row that is updated as its story unfolds: ordered, then delivered
fact_salesUnits sold in and sold out for one dealer, material and monthorder lines, dealer sell-out filessale month (3)SQLA total per period
fact_scrapTyres cured and scrapped on one press, for one material, in one hourproduction, inspection resultscure hour (48)dataframe functionA total per period, with rows that can still be waiting

How a model is called

src/dataplatform/adapters/spark_gold.py:166The harness calls the model through one method, whatever kind it is.
    def build(self, plan: GoldPlan, inputs: dict[str, DataFrame], params: dict[str, str]) -> DataFrame:
        if plan.build_kind == "sql":
            text = (self.models_root / plan.build_ref).read_text()
            for name, df in inputs.items():
                df.createOrReplaceTempView(_safe(name))
            return self.sql(text, {k: v for k, v in params.items() if f":{k}" in text})
        fn = self._function(plan.build_ref)
        return fn(dict(inputs), dict(params))

For a SQL model, each input frame is registered as a view under its name, the file is read, and the query is run with the parameters the text actually uses. The parameters are bound by the engine, never pasted into the text. For a dataframe model, the function is loaded from the models folder and called with the frames and the parameters. Both kinds return one frame and neither may read or write anything.

fact_otif: a row that changes as deliveries arrive

OTIF stands for on time in full. One row is one open order line. It says whether the line was delivered by the date first promised, and whether the full quantity arrived.

models/tyre/gold/sales/fact_otif.sql:31The judging step and the final select of the model.
judged AS (
    SELECT o.order_id, o.line_no, o.dealer_code, o.material_code, o.ordered_qty, o.order_ts,
           f.promised_date,
           COALESCE(d.delivered_qty, 0) AS delivered_qty,
           COALESCE(TO_DATE(FROM_UTC_TIMESTAMP(d.delivered_ts, :business_tz)) <= f.promised_date, FALSE) AS on_time,
           COALESCE(d.delivered_qty, 0) >= o.ordered_qty AS in_full
    FROM open_lines AS o
    INNER JOIN first_promise AS f ON o.order_id = f.order_id AND o.line_no = f.line_no
    LEFT JOIN delivered AS d ON o.order_id = d.order_id AND o.line_no = d.line_no
)

SELECT order_id, line_no, dealer_code, material_code, promised_date, ordered_qty, delivered_qty,
       on_time, in_full,
       CASE WHEN on_time AND in_full THEN 1 ELSE 0 END AS on_time_in_full,
       order_ts
FROM judged

The row for an order line exists as soon as the order does, with a delivered quantity of 0 and both flags false. When deliveries arrive, the same row is rebuilt with the new quantity and flags. A delivery counts on its local date: the delivery time is moved to the business time zone before it is compared with the promised date. Cancelled and rejected lines are left out, because the earlier step keeps only lines whose status is open.

An example. The order line and its deliveries are on the left. The fact row is on the right.

Order lineDeliveriesdelivered_qtyon_timein_fullon_time_in_full
A, 10 tyres, promised 12 Nov10 on 10 Nov10truetrue1
A, 10 tyres, promised 12 Nov10 on 13 Nov10falsetrue0
A, 10 tyres, promised 12 Nov6 on 10 Nov6truefalse0
A, 10 tyres, promised 12 Nov6 and then 4, both before 12 Nov10truetrue1
A, 10 tyres, promised 12 Novnone yet0falsefalse0
A, first promised 12 Nov, later moved to 14 Nov10 on 13 Nov10falsetrue0

How it is rebuilt. The window is the promised date, one day at a time, and each run recomputes the last 35 days of promised dates. A delivery that arrives three weeks after its promised date still lands in the right day. A promised date in the future is published but flagged incomplete, because the day is not over. The fact allows corrections, so a recorded correction to a promised date is laid over silver before the model runs.

fact_sales: a total per month from two sources

One row is one dealer, one material and one month. It holds what the company sold to the dealer, called sell-in, and what the dealer reported selling on, called sell-out.

models/tyre/gold/sales/fact_sales.sql:6Two totals per dealer, material and month, joined so that a row exists if either side has one.
WITH sell_in AS (
    SELECT dealer_code, material_code,
           CAST(DATE_TRUNC('MONTH', FROM_UTC_TIMESTAMP(order_ts, :business_tz)) AS DATE) AS sale_month,
           SUM(ordered_qty) AS units_sold_in,
           SUM(net_value) AS net_value
    FROM erp__sales_order_line
    WHERE _is_current AND NOT _is_deleted AND line_status = 'open'
    GROUP BY 1, 2, 3
),

sell_out AS (
    SELECT dealer_code, material_code,
           CAST(DATE_TRUNC('MONTH', sale_month) AS DATE) AS sale_month,
           SUM(units_sold) AS units_sold_out
    FROM partner__dealer_sellout
    WHERE _is_current AND NOT _is_deleted
    GROUP BY 1, 2, 3
)

SELECT COALESCE(i.dealer_code, o.dealer_code) AS dealer_code,
       COALESCE(i.material_code, o.material_code) AS material_code,
       COALESCE(i.sale_month, o.sale_month) AS sale_month,
       COALESCE(i.units_sold_in, 0) AS units_sold_in,
       COALESCE(o.units_sold_out, 0) AS units_sold_out,
       CAST(ROUND(COALESCE(i.net_value, 0), 2) AS DOUBLE) AS net_value
FROM sell_in AS i
FULL OUTER JOIN sell_out AS o
    ON i.dealer_code = o.dealer_code AND i.material_code = o.material_code AND i.sale_month = o.sale_month

Sell-in comes from order lines, counted in the local month of the order time. Sell-out comes from the file each dealer sends once a month. A dealer who resends a month replaces that month in silver, so the model reads only the current rows. The two sides are joined with a full outer join. A dealer with orders but no file yet still gets a row, with sell-out 0, and the other way round.

An example. The inputs are on the left and the fact row on the right.

Order linesDealer fileunits_sold_inunits_sold_out
D-1, M-1, 7 tyres and 5 tyres in Novembernone120
D-1, M-1, 7 tyres in NovemberD-1, M-1, November, 3 sold73
noneD-2, M-2, October: an older file with 50, then a newer one with 60060

How it is rebuilt. The window is the sale month and each run recomputes the last 3 months. A month stays flagged incomplete until every dealer who owes a file for it has sent one. When the last file arrives, the month is replaced and logged as a restatement. Windows and completeness follows that case through.

fact_scrap: a total per hour, where single rows can wait

One row is one press, one material and one hour. It holds the tyres cured and the tyres scrapped at inspection. Reworked tyres are not scrap.

models/tyre/gold/manufacturing/scrap.py:20The one dataframe model of the tyre company.
def build(inputs: dict[str, DataFrame], params: dict[str, str]) -> DataFrame:
    """The build signature every dataframe build shares (D18); scrap needs no parameter (cure_hour is an instant)."""
    del params
    production = _current(inputs["mes__production"])
    results = (
        _current(inputs["lims__inspection_result"])
        .groupBy("production_id")
        .agg(F.sum("tyres_scrapped").alias("scrapped"))
    )
    rows = production.join(results, "production_id", "left")
    return (
        rows.groupBy("press_id", "material_code", F.date_trunc("hour", "cured_ts").alias("cure_hour"))
        .agg(
            F.sum("tyres_cured").cast("long").alias("tyres_cured"),
            F.sum(F.coalesce(F.col("scrapped"), F.lit(0))).cast("long").alias("tyres_scrapped"),
            F.min(F.col("scrapped").isNotNull().cast("int")).cast("boolean").alias("_input_complete"),
        )
    )

Production rows are joined to the inspection results by a left join, so an hour of production appears even before its inspection is filed. The last aggregate is the interesting one. It computes _input_complete, which is true only if every production row of that press and hour has an inspection result. The harness reads that column. A row that says it is still waiting is flagged incomplete on its own, even if its window is otherwise complete. The column is not in the contract and is dropped before the write.

An example. The inputs are on the left and the fact row on the right.

Production in the hour from 10:00Inspection resultstyres_curedtyres_scrapped_input_complete
Press X: 30 tyres2 scrapped302true
Press X: 30 tyresnot filed yet300false
Press X: 30 tyres0 scrapped, result rework300true
Press X: 30 tyres and 20 tyres1 scrapped for the first batch only501false

How it is rebuilt. The window is the cure hour, an instant in UTC, and each run recomputes the 48 hours up to the end of the business date. This fact does not allow corrections.

Why the row-level flag exists

The full-scale attempt of 4 November 2026 found the reason. One plant's lab files were held back by a planted incident, and the whole hour was flagged incomplete, including the rows of another plant whose files had arrived. The numbers were equal to the oracle and only the flag differed. The fix was to let a model say per row whether its own input has arrived. The run of 5 November at one tenth of full scale confirmed it. Both are recorded in docs/W1D-GOLD.md.

The settings that drive a fact

All of them are in the fact's manifest. Manifests and models lists every key. The ones that decide how a fact behaves are these.

Keyfact_otiffact_salesfact_scrap
grainorder_id, line_nodealer_code, material_code, sale_monthpress_id, material_code, cure_hour
windowpromised_date, day, 35sale_month, month, 3cure_hour, hour, 48
dimensions[].as_oforder_tssale_monthcure_hour
build.kindsqlsqldataframe
correctionstruetruefalse
checks.orphan_rate_max0.010.010.005
checks.reconcileordered_qtyunits_sold_outtyres_cured, tyres_scrapped

How it is tested

Each model has its own table of cases in tests/dataplatform/test_gold_engine.py: 11 for on time in full, 5 for sales and 6 for scrap. A case is a few silver rows going in and the fact rows expected out. The same tables run on Spark and on DuckDB.

tests/dataplatform/test_gold_engine.py:59The cases of the on-time-in-full model. Each is order lines, deliveries, and the expected row.
OTIF_CASES = {
    "on time and in full": ([line("A", d(12))], [dlv("A", 10, ts(30))], {"A": (d(12), 10, True, True, 1)}),
    "late by a day": ([line("A", d(12))], [dlv("A", 10, ts(100))], {"A": (d(12), 10, False, True, 0)}),
    "counted on the local date (19:00 UTC is the next day in India)":
        ([line("A", d(12))], [dlv("A", 10, datetime(2026, 11, 12, 19, 0, tzinfo=UTC))],
         {"A": (d(12), 10, False, True, 0)}),
    "partial delivery is not in full": ([line("A", d(12))], [dlv("A", 6, ts(30))], {"A": (d(12), 6, True, False, 0)}),
    "two deliveries make it in full": ([line("A", d(12))], [dlv("A", 6, ts(30)), dlv("A", 4, ts(40))],
                                       {"A": (d(12), 10, True, True, 1)}),
    "not delivered yet": ([line("A", d(12))], [], {"A": (d(12), 0, False, False, 0)}),
    "a cancelled line is left out": ([line("A", d(12), status="cancelled")], [dlv("A", 10, ts(30))], {}),
    "a deleted line is left out": ([line("A", d(12), _is_deleted=True)], [], {}),
    "the date first promised, not the rescheduled one": (
        [line("A", d(12), _seq_from=1, _is_current=False, _valid_to=ts(5)), line("A", d(14), _seq_from=2,
                                                                                 _valid_from=ts(5))],
        [dlv("A", 10, ts(100))], {"A": (d(12), 10, False, True, 0)}),
    "a late change (zero-length version) does not move the first promise": (
        [line("A", d(12), _seq_from=1, _is_current=False, _valid_to=ts(5)),
         line("A", d(13), _seq_from=3, _valid_from=ts(5)),
         line("A", d(14), _seq_from=2, _is_current=False, _valid_from=ts(5), _valid_to=ts(5))],
        [dlv("A", 10, ts(30))], {"A": (d(12), 10, True, True, 1)}),
    "a deleted delivery does not count": ([line("A", d(12))], [{**dlv("A", 10, ts(30)), "_is_deleted": True}],
                                          {"A": (d(12), 0, False, False, 0)}),
}
tests/dataplatform/test_gold_engine.py:85The test hands the model two silver frames and compares what comes back.
@pytest.mark.parametrize("case", OTIF_CASES)
def test_fact_otif_build(spark, engine, case):
    lines, deliveries, want = OTIF_CASES[case]
    p = plan("fact_otif", "sql", "gold/sales/fact_otif.sql", "promised_date", "day")
    out = engine.build(p, {"erp__sales_order_line": silver(spark, SOL, lines),
                           "erp__delivery": silver(spark, DLV, deliveries)}, {"business_tz": TZ})
    got = {r["order_id"]: (r["promised_date"], r["delivered_qty"], r["on_time"], r["in_full"], r["on_time_in_full"])
           for r in out.collect()}
    assert got == want
tests/dataplatform/test_gold_engine.py:139The cases of the scrap model, including an hour that waits because one of its rows waits.
SCRAP_CASES = {
    "an hour with its inspection": ([prod("P1", "X", H10.replace(minute=55), 30)], [insp("P1", 2)],
                                    {("X", H10): (30, 2, True)}),
    "the inspection has not arrived": ([prod("P1", "X", H10.replace(minute=55), 30)], [], {("X", H10): (30, 0, False)}),
    "rework is not scrap": ([prod("P1", "X", H10.replace(minute=50), 30)], [insp("P1", 0, "rework")],
                            {("X", H10): (30, 0, True)}),
    "two presses, one hour": ([prod("P1", "X", H10, 30), prod("P2", "Y", H10, 20)], [insp("P1", 1), insp("P2", 3)],
                              {("X", H10): (30, 1, True), ("Y", H10): (20, 3, True)}),
    "a deleted production is left out": ([prod("P1", "X", H10, 30, _is_deleted=True)], [insp("P1", 1)], {}),
    "one hour waits if one of its rows waits": (
        [prod("P1", "X", H10, 30), prod("P2", "X", H10.replace(minute=40), 20)], [insp("P1", 1)],
        {("X", H10): (50, 1, False)}),
}
Not built yet, or known gaps
  • There is no setting for the kind of fact, and no build that appends new rows only. Every fact is recomputed by window and replaced. A very large fact of pure events would pay for that.
  • No fact reads the press readings stream. A press-hour fact from readings would be a fourth model with an hour window.
  • The cure hour is the UTC hour of the curing time. The plants' time zones are declared nowhere the platform reads, so plant-local hours and shifts cannot be built.
  • Net sales value is built into fact_sales, but its measure in the semantic layer is still a draft.
  • A model's purity is not enforced at run time. It is checked by a static scan of the models folder before a change is merged.