Building Staging and Data Marts

A data mart is a table shaped for one purpose: a specific report, dashboard, or model. This article covers how to build one with SQL, from selecting and joining source data to loading it incrementally without creating duplicates.

Mental model#

raw.orders            as received, never edited   ↓  select, clean, conform typesstaging.orders        one clean row per event   ↓  join dimensions, group, aggregatemart.customer_metrics one row per customer, ready to read

Each layer has an owner:

• raw holds source data exactly as it arrived. It is the replay point.
• staging holds cleaned, type-correct, deduplicated rows, still at event grain.
• mart holds the aggregated result a consumer reads. It can always be rebuilt from staging.

Writing a mart with INSERT ... SELECT#

A mart is built by inserting the result of a query into a target table:

INSERT INTO mart.customer_metrics(    customer_id,    order_count,    total_amount,    avg_amount,    first_order,    last_order)SELECT    customer_id,    count()          AS order_count,    sum(amount)      AS total_amount,    avg(amount)      AS avg_amount,    min(created_at)  AS first_order,    max(created_at)  AS last_orderFROM staging.ordersWHERE status = 'completed'GROUP BY customer_id;

The column list after the table name is optional but worth writing: it fixes the mapping between SELECT outputs and target columns and fails loudly if the shapes drift apart.

Enriching with JOIN#

A JOIN combines rows from two tables that share a key. It is how a fact table such as staging.orders gains attributes from a dimension such as staging.customers.

SELECT    o.customer_id,    c.segment,    sum(o.amount) AS revenueFROM staging.orders AS oLEFT JOIN staging.customers AS c    ON o.customer_id = c.customer_idWHERE o.status = 'completed'GROUP BY o.customer_id, c.segment;

Reading it:

• staging.orders AS o and staging.customers AS c give each table a short alias so columns can be written as o.amount, c.segment.
• ON o.customer_id = c.customer_id is the join key: rows are paired where the customer ids match.
• LEFT JOIN keeps every row from the left table (orders) even when no customer matches; the customer columns are NULL for those rows.
• INNER JOIN would instead drop orders that have no matching customer.

Choose LEFT JOIN when the fact rows must all survive even if a dimension is missing. Choose INNER JOIN when a missing match means the row should not be counted. Always group and aggregate on unambiguous, aliased columns.

Structuring a multi-step mart with CTEs#

A CTE (common table expression), written with WITH, names a subquery so the final query reads top to bottom instead of inside out:

WITH customer_revenue AS(    SELECT        customer_id,        sum(amount) AS revenue    FROM staging.orders    WHERE status = 'completed'    GROUP BY customer_id)SELECT    customer_id,    revenueFROM customer_revenueWHERE revenue > 1000ORDER BY revenue DESC;

A CTE can also feed an insert. Define the computation once and reuse it:

WITH customer_revenue AS(    SELECT customer_id, sum(amount) AS revenue    FROM staging.orders    WHERE status = 'completed'    GROUP BY customer_id)INSERT INTO mart.high_value_customers (customer_id, revenue)SELECT customer_id, revenueFROM customer_revenueWHERE revenue > 1000;

Multiple CTEs are separated by commas and can build on each other, which keeps a complex mart readable as a sequence of named steps.

Loading incrementally and safely#

Rebuilding a mart from all of staging every run is simple and correct, but gets expensive. Incremental loading processes only new data, and it introduces a correctness problem: running the same load twice must not change the result. That property is called idempotency.

The watermark#

Find the latest value already in the target, then read only newer source rows:

SELECT maxOrNull(last_order) FROM mart.customer_metrics;

Passing that value as a query parameter (never an f-string) gives the incremental read. But a naive WHERE created_at > {watermark} with a plain MergeTree target is not idempotent: a rerun that overlaps the previous window inserts the same aggregates again.

Making the load idempotent#

Two reliable patterns:

Replace a partition. If the mart is partitioned by day or month, delete the affected partition and reinsert it. Re-running only rewrites the same partition:

ALTER TABLE mart.daily_revenue DROP PARTITION '2026-03-01';
INSERT INTO mart.daily_revenueSELECT    toStartOfDay(created_at) AS day,    sum(amount)              AS revenueFROM staging.ordersWHERE created_at >= '2026-03-01' AND created_at < '2026-03-02'GROUP BY toStartOfDay(created_at);

Use ReplacingMergeTree. Key the target on the true business key, carry a version column, and let the latest version win. Read with FINAL or re-aggregate so duplicates before a merge do not affect results.

Reprocessing late data#

Records sometimes arrive after their timestamp has passed. Move the watermark back by a fixed window on each run so recent periods are recomputed:

-- recompute the last 3 hours every run, not just brand-new rowsWHERE created_at >= now() - INTERVAL 3 HOUR

Combined with an idempotent write (partition replace or ReplacingMergeTree), reprocessing a window is safe and picks up late arrivals.

Putting it together#

An idempotent daily revenue-by-segment mart:

ALTER TABLE mart.revenue_by_segment DROP PARTITION '2026-03-01';
INSERT INTO mart.revenue_by_segment (day, segment, orders, revenue)WITH completed AS(    SELECT customer_id, created_at, amount    FROM staging.orders    WHERE status = 'completed'      AND created_at >= '2026-03-01' AND created_at < '2026-03-02')SELECT    toStartOfDay(o.created_at) AS day,    c.segment                  AS segment,    count()                    AS orders,    sum(o.amount)              AS revenueFROM completed AS oLEFT JOIN staging.customers AS c    ON o.customer_id = c.customer_idGROUP BY day, segment;

The CTE narrows staging to one day of completed orders; the join adds the segment; the aggregate produces the mart grain; the DROP PARTITION makes the whole thing safe to run again.

Common mistakes#

Interpolating the watermark into SQL#

Pass it as a query parameter. See Working with ClickHouse from Python.

A strict > boundary on the watermark#

WHERE ts > max_ts skips rows that share the maximum timestamp. Use a window you recompute (>= now() - INTERVAL n) plus an idempotent write, rather than trusting an exact boundary.

Incremental insert into a plain MergeTree#

Re-running overlapping windows appends duplicate aggregates. Replace a partition, or use ReplacingMergeTree and dedup at read.

Selecting unaliased columns across a join#

customer_id may exist in both tables. Alias every table and qualify every column.

Skipping the staging layer#

Aggregating straight from raw means every mart re-implements the same cleaning. Clean once into staging; build marts from there.

Quick reference#

See also#