Project: release a reconciled revenue mart

Last updated: 6 Oct 20265 min read

project

Build an invoice and payment mart with declared fact grain, versioned customer identities, late-dimension repair and one metric contract.

Write the contract

Use one fact row per capture event and a stable capture ID. Carry signed cents, event time, source position, customer source namespace and natural key. A customer dimension has a durable identity plus surrogate keys for historical versions. State which time the revenue report uses and whether reversal events net against the original day or the reversal day.

Build the load

Land raw captures, deduplicate by capture ID and source position, resolve the dimension version valid at capture time, and stage the fact table. Use an explicit unknown member for late customers. Reconcile accepted plus quarantined records with the source interval and compare signed cents before publication. The grain contract prevents many-to-many joins from inflating revenue.

Define a consumer metric

Publish net captured cents and eligible attempt counts as components, then calculate payment success rate from those components. Keep metric version, source snapshot, cutoff and currency rule beside the release. The metric contract should identify a reversal and an ineligible test event unambiguously.

Inject change and lateness

Create two captures for one order, reverse part of one capture, then deliver a customer region change whose effective time falls between the two. Delay the customer dimension for one capture. Verify that its fact remains visible under unknown, a later repair changes only its dimension key, and the total cents remain identical.

Prove the release

Supply source and target schemas, grain and key assertions, as-of lookup tests, the unresolved-member queue, reconciliation by day and currency, a metric fixture, and two versioned snapshots before and after repair. The consumer should see one complete version at a time. Record an operator path for a source correction that arrives after the normal window.

Implementation

captures = [
    {"capture_id": "cap-47", "customer": "cust-7", "day": 47, "cents": 6100},
    {"capture_id": "cap-48", "customer": "cust-7", "day": 47, "cents": 2400},
    {"capture_id": "rev-49", "customer": "cust-7", "day": 48, "cents": -1100},
]

def revenue_release(rows):
    if len({row["capture_id"] for row in rows}) != len(rows):
        raise ValueError("capture grain violated")
    by_day = {}
    for row in rows:
        by_day[row["day"]] = by_day.get(row["day"], 0) + row["cents"]
    assert sum(by_day.values()) == sum(row["cents"] for row in rows)
    return by_day

assert revenue_release(captures) == {47: 8500, 48: -1100}

Performance and operating cost

The reference aggregation is O(N) expected time and O(N + D) memory for N captures and D days. A production mart also pays for version-aware dimension lookups, validation, retained snapshots and restatements. Fast queries do not compensate for an undefined grain or unreconciled source.

Common Mistakes

Do not collapse two captures into one order row without a written measure rule.
Do not overwrite old region history when the customer moves.
Do not silently suppress a fact just because its dimension is late.