Extending the data mart

For the engineer adding to, changing, or retiring a fact in a ClickHouse data mart. It assumes you know what a mart, a fact and staging are (How analytics data flows) and what a good one looks like (Data marts done right); this page is the code-level how. The running example is an order-fulfillment mart, orders that live in Postgres joined to customer identity that lives in Mongo, and the code shapes are the ones a hand-rolled ELT actually uses.

The pipeline in one pass#

The whole job is a registry plus a loop. One config file lists the sources to pull and the facts to build; everything else, extract, load, swap, log, is generic and never changes when you add a table.

registry (datasets.ts)          SOURCES[]  +  FACTS[]
        │
        ▼
each source ──stream {id, doc}──►  stg_<name>(id, doc, loaded_at)   raw JSON, one row per record
        │
        │  fact transform: SELECT over staging, join + flatten
        ▼
   <fact>_new  ──EXCHANGE TABLES──►  mart.fact_<name>               atomic swap, readers never see a half-built table
        │
        ▼
   derived tables (a PII-free mirror, roll-ups)                     built after, from the facts, never the source
        │
        ▼
   mart.etl_runs                                                    one row per run: counts, status, error

The one rule that keeps this cheap: staging is schema-agnostic. Every record, a Mongo document or a Postgres row, lands as {id, doc} where doc is the record serialized to JSON. The transform reads fields with JSONExtract*, so the extract, load and orchestrate layers never learn a table’s shape. Adding a table is a registry entry and a SQL file. Nothing upstream changes. That is the property to protect: if a change makes you edit the loader or the runner to add a fact, you have broken the thing that makes the mart low-maintenance.

Add a fact: a registry entry and a SQL file#

A fact is a SELECT over staging that does the join and the flatten into a typed, flat table with one grain. Adding one is two edits and a test.

1. Write the DDL and the transform (in sql.ts). {{db}} is the mart database; {{target}} is the shadow table the runner builds into before the swap. Type every column, and widen a type where the source can hand you junk.

-- DDL
CREATE TABLE IF NOT EXISTS {{db}}.fact_order_fulfillment (
  order_id             String,
  customer_id          String,
  customer_name        String,
  time_to_pay_seconds  Nullable(Int64),
  is_paid              UInt8
  -- ...
) ENGINE = MergeTree ORDER BY (customer_id, order_id)

-- transform
INSERT INTO {{target}}
SELECT
  JSONExtractString(o.doc, 'id')          AS order_id,
  JSONExtractString(o.doc, 'customer_id') AS customer_id,
  JSONExtractString(c.doc, 'name')        AS customer_name,
  -- ...compute the clocks, join stg_customers for the name...
FROM {{db}}.stg_orders AS o
LEFT JOIN {{db}}.stg_customers AS c ON c.id = JSONExtractString(o.doc, 'customer_id')

2. Register it (in datasets.ts): add a FACTS entry naming the DDL, the transform, and the staging it reads, so a missing dependency is caught in a test, not at 2am.

{ name: 'fact_order_fulfillment',
  ddl: FACT_ORDER_FULFILLMENT_DDL,
  transform: FACT_ORDER_FULFILLMENT_TRANSFORM,
  dependsOn: ['orders', 'customers'] }

3. Add a source if the fact needs one it doesn’t have yet (see below).

4. Test the registry invariants. The suite already asserts that fact names are unique and safe, that every dependsOn names a real source, and that every transform targets {{target}} and {{db}}. A new fact is covered the moment it’s in the list; add a case only for logic a new source kind introduces.

5. Run it and check two numbers: the fact’s row count, and that the run logged success. SELECT * FROM mart.etl_runs ORDER BY started_at DESC LIMIT 1.

The worked example above, fact_order_fulfillment, is one row per order. Its grain is a sentence: one order for one customer, with the fulfillment timestamps folded onto the row. It precomputes three duration clocks (placed to paid, paid to shipped, placed to delivered) so a dashboard never does date maths over millions of rows.

Keep presentation out of the fact. The fact stores raw truth; the dashboard decides how to show it. An order parked in a manual payment-review hold keeps accruing against placed-to-paid for as long as the hold lasts, so a few time_to_pay_seconds values are inflated by review time. The fix lives in the Metabase card (cap the outliers), not the fact. Were the cap baked into the column, every future question would inherit one chart’s judgement and nobody could get back to what actually happened.

Bad: time_to_pay_seconds already capped at 3600 in the transform, because one dashboard wanted it. The real distribution is now unrecoverable, and a fraud model that needed the true tail can’t be built.

Good: the fact holds the raw seconds; the card filters time_to_pay_seconds BETWEEN 0 AND 3600 and says so in its title.

Add a source: two stores, one staging shape#

A source implements one small interface: connect, close, and stream, which yields {id, doc} rows. Two kinds exist, and they land in staging identically, so a fact can join a Mongo collection to a Postgres table without noticing they came from different databases.

// Mongo: a collection and a projection (pull only the fields the facts read)
{ name: 'customers', collection: 'customers',
  projection: { _id: 1, name: 1, tier: 1 } }

// Postgres: kind, a table, and the columns to select (must include id)
{ name: 'orders', kind: 'postgres', table: 'orders',
  columns: ['id', 'customer_id', 'status', 'placed_at', 'paid_at', 'shipped_at', 'delivered_at'] }

Each row is serialized to JSON before it lands, so a Postgres timestamptz and a Mongo date both arrive as ISO strings and parse the same way in the transform. That is what lets the order-fulfillment mart join orders that live in Postgres to the customer identity that lives in Mongo: land both, join in SQL.

Two rules for a source, both from Data marts done right:

• Read-only by credential. The loader connects as a user that can only read, to a replica, never to production with an admin account. For the order-fulfillment mart that is a dedicated read-only user granted SELECT on exactly the orders table, nothing else.
• Fail closed. A missing connection string stops the job at startup, before it opens anything, rather than running half a pipeline and leaving a mart that looks updated and isn’t.

Retire a fact on purpose#

Retiring a fact is a deletion you do on purpose, not a query you quietly stop running. A fact left to rot is a number someone still trusts.

Because a fact is cheap to add, it is easy to abandon. An unused one still rebuilds nightly, still shows in the BI tool, and still looks authoritative to whoever finds the old card. The cost of leaving it is a stale number that disagrees with the live one, and a schema the next person is scared to touch because they can’t tell if it is load-bearing.

• Find who reads it first. Check the BI tool for cards on the fact before you remove anything. A fact with live cards is not dead, it is owned by someone who hasn’t been asked.
• Remove the registry entry, and its source too if nothing else reads it. The fact stops being rebuilt.
• Drop the table, don’t just stop writing it. A table that stops updating but stays queryable is the trap: it looks live and is frozen. Drop it so a query fails loudly instead of returning last-good-looking numbers.
• Retire or repoint the BI cards in the same change, so nobody lands on a card backed by a dropped table.
• Leave the run log alone. The ledger is history.

Bad: a fact nobody uses is kept “in case,” rebuilt nightly for a year; a new hire builds a dashboard on it and reports a number that is quietly wrong.

Good: the fact is removed from the registry, its table dropped, its two cards archived, in one change with a one-line note on why.

Test: if you dropped this fact’s table right now, whose dashboard breaks? “No one’s” means it should already be gone. “Someone’s” means it isn’t dead, and the sunset is a conversation first.

Hand-roll until it hurts#

Hand-roll the pipeline while it is a handful of nightly facts; adopt a tool only when the thing a tool gives you is the thing actually hurting.

A pipeline tool is itself infrastructure to run, learn, upgrade and debug. For a few facts rebuilt once a night from one or two sources, the whole orchestration is a few hundred lines you can read in a sitting, and the registry pattern already gives you what the tools sell: declarative facts, idempotent rebuilds, a run ledger. A tool adds a service, a config language, and a failure surface that is now also yours.

What each tool actually buys, and the pain that earns it:

Adopt a tool when two or more of these hold, not one: more than roughly 15 to 20 facts, or facts layered several deep; more than a couple of source systems, especially SaaS APIs; more than one schedule, or real backfills; more than one or two engineers who need to change it without reading all of it.

Bad: “dbt is the standard, so the first fact goes in dbt.” A one-fact mart now carries a dbt install, a profiles file, a DAG and a CI step, to run one SELECT nightly.

Good: “Forty facts across five sources, three of them SaaS APIs, two schedules, monthly backfills. The orchestration is the hard part now, not the SQL, so transforms move to dbt and extraction to Airbyte.”

The registry pattern migrates cleanly when you get there: facts are already declarative SQL with a stated grain, so moving them into dbt is a port, not a rewrite, and staging-raw means a connector can replace the extractor without touching a fact. Hand-rolling first keeps the expensive option open at no extra cost.

Test: name the specific thing a tool would do that your pipeline can’t, and the week it last cost you. If you can’t, you are buying infrastructure to pre-empt a problem you don’t have.