- 90days active after a purchaseactive_window_days
- 180days without a purchase to churnchurn_after_days
- 4error-level checks on the historyevery run
- 1customer version per sale linedbt test fails otherwise
Versions, not overwrites
Ask for “active customers last quarter” twice, a month apart, and a platform that overwrites customer attributes in place can give two different answers. Here an attribute is versioned instead (SCD Type 2). In this dataset the attribute that can change is the customer's country.
A new version starts only when the value actually differs from the previous one. Seeing the same value again does nothing, and a missing value is never treated as a change. Each version is valid from the moment it was observed until the next version starts; the current version has no end.
Four observations of one customer's country: United Kingdom, United Kingdom again, a blank value, then France. Only the change to France starts a new version. Version 1 is valid from the first observation until France is observed; version 2 is valid from then on and is the current version.
Every sale joins to the version that was valid at the time of the sale, so last quarter's report keeps last quarter's country even after the customer moves.
Status from purchase dates
Active, inactive and churned are not flags someone sets in a report. They are derived from purchase dates and two thresholds held in config/customer_status.toml. A purchase inside the active window simply extends it; a purchase after churn starts a new active period flagged as a reactivation, and the earlier states are kept so reactivations can be counted over time.
active_window_days = 90 · churn_after_days = 180 · config/customer_status.tomlAfter a purchase on day d a customer is active until day d plus 90, inactive until day d plus 180, and churned from then until the next purchase. A purchase while churned is flagged as a reactivation. A purchase while active or inactive simply starts a new active window.
Change a threshold, rerun, and the whole history is rebuilt the same way. Status is recomputed for every customer on each run, so a late-arriving purchase is handled without special cases, and states are only asserted up to the last date in the data: the model never claims a customer churned after the data ends.
Gates on the history
The history tables are checked before anything reads them. All four checks are severity error: a failure stops the run. A dbt test then proves the join that every report depends on.
Customer observations from Silver are built into versioned history. Four error checks run on it: unique key per customer and valid-from date, exactly one current version per customer, contiguous intervals with no gaps or overlaps, and positive interval length. Then a dbt test joins every sale line to the customer version valid at the time of sale and fails if any line matches anything other than exactly one version.
- SilverCustomer observationsEvery time a customer is seen, with the attributes seen.
- silver.
customer_ observation
- silver.
- BuildVersioned historyA version starts only on a real change.
- valid_
from - valid_
to - is_
current
4 × errorstops the run - valid_
- CheckInterval gatesUnique key · one current version · contiguous · positive length.dbt testfails if ≠ 1 match
- GoldSale ↔ versionEach sale line joins to exactly one version, at the time of the sale.
- dim_
customer - fct_
transaction_ line
- dim_