SQL for Data Engineers: Building a Dimensional Warehouse

-- Grain: one row per order line, i.e. per (order_id, line_no).
CREATE TABLE fact_sales (
    date_sk      INTEGER       NOT NULL,   -- FK to dim_date
    customer_sk  INTEGER       NOT NULL,   -- FK to dim_customer (-1 = unknown)
    product_sk   INTEGER       NOT NULL,   -- FK to dim_product  (-1 = unknown)
    order_id     VARCHAR(30)   NOT NULL,   -- degenerate dimension
    line_no      INTEGER       NOT NULL,   -- degenerate dimension
    quantity     INTEGER,
    unit_price   DECIMAL(12,2),
    discount_amt DECIMAL(12,2),
    gross_amount DECIMAL(14,2),
    net_amount   DECIMAL(14,2),
    _loaded_at   TIMESTAMP     DEFAULT current_timestamp,
    _batch_id    VARCHAR(50),
    PRIMARY KEY (order_id, line_no)
);

• The grain comes first. "One row per order line" decides every column: measures must be true for one line, and every join and sum has to respect it.
• Surrogate foreign keys (date_sk, customer_sk, product_sk) point at dimension rows. They are NOT NULL because a missing or unknown key points at the -1 Unknown member (Q17), never at NULL.
• Degenerate dimensions (order_id, line_no) are identifiers with no dimension table of their own. They keep every fact row traceable to its source line.
• gross_amount and net_amount are stored, not computed at query time, so every report uses the same arithmetic.
• PRIMARY KEY (order_id, line_no) is the grain written as a constraint. DuckDB and Postgres reject a duplicate, as the step-through shows. Snowflake accepts it silently (it doesn't enforce primary keys), so there the grain is protected by the load logic and the quality gate in Q35.

Engineer check. Grain: one row per (order_id, line_no), enforced only on engines that enforce primary keys. Run twice: a plain reload double-counts unless it's a MERGE or a delete-and-insert (Q28). NULL: foreign keys are NOT NULL by design; unknown means -1.

CREATE SEQUENCE customer_sk_seq START 1;
CREATE TABLE dim_customer (
    customer_sk INTEGER PRIMARY KEY DEFAULT nextval('customer_sk_seq'),
    customer_code VARCHAR NOT NULL, first_name VARCHAR, last_name VARCHAR, email VARCHAR,
    city VARCHAR, state VARCHAR, country VARCHAR,
    effective_from DATE NOT NULL, effective_to DATE NOT NULL, is_current BOOLEAN NOT NULL,
    row_hash VARCHAR, version INTEGER NOT NULL
);

INSERT INTO dim_customer (customer_sk, customer_code, first_name, last_name, email, city, state, country,
                          effective_from, effective_to, is_current, row_hash, version)
VALUES (-1, 'UNKNOWN', 'Unknown', 'Unknown', NULL, 'Unknown', 'Unknown', 'Unknown',
        DATE '1900-01-01', DATE '9999-12-31', TRUE, 'UNKNOWN', 1);

• customer_sk is unique per version, not per customer. When a customer moves, they get a new row and a new surrogate key.
• customer_code is NOT NULL but not UNIQUE. It repeats across versions. "This customer today" is customer_code = … AND is_current.
• effective_from / effective_to are the validity window of a version. They power point-in-time joins (Q25). 9999-12-31 marks "still valid": a real far-future date keeps range predicates simple, where a NULL end date would need special handling in every join.
• is_current duplicates what the window says, but makes "the current version" a cheap filter.
• row_hash fingerprints the tracked attributes, so a load can detect change with one comparison (Q19).
• version counts changes per customer, which helps when debugging.

The Unknown member. Every dimension reserves -1 for "unknown, not applicable or not yet arrived". A fact whose customer can't be resolved points at -1 instead of NULL, so inner joins never drop it, totals stay complete, and "Unknown" shows up as a bucket you can monitor. Its window covers all time, so any fact date falls inside it.

On Postgres, an IDENTITY column declared GENERATED ALWAYS refuses an explicit -1 unless the insert says OVERRIDING SYSTEM VALUE; declaring it GENERATED BY DEFAULT avoids that. Snowflake lets you insert an explicit value into an AUTOINCREMENT column.

Engineer check. Grain: one row per customer version. Run twice: the Unknown-member insert fails on the primary key the second time; in production it's a MERGE or INSERT … WHERE NOT EXISTS. NULL: the window uses a sentinel end date instead of NULL so that BETWEEN works.

CREATE OR REPLACE TABLE stg_customer AS
WITH cleaned AS (
    SELECT
        TRIM(customer_code)  AS customer_code,
        TRIM(first_name)     AS first_name,
        TRIM(last_name)      AS last_name,
        LOWER(TRIM(email))   AS email,
        TRIM(city)           AS city,
        TRIM(state)          AS state,
        UPPER(TRIM(country)) AS country,
        signup_ts,
        updated_at
    FROM src_customer
    WHERE NULLIF(TRIM(customer_code), '') IS NOT NULL
    QUALIFY ROW_NUMBER() OVER (PARTITION BY TRIM(customer_code)
                               ORDER BY updated_at DESC NULLS LAST) = 1
)
SELECT *, md5(concat_ws('|', coalesce(first_name,'<NULL>'), coalesce(last_name,'<NULL>'), coalesce(email,'<NULL>'), coalesce(city,'<NULL>'), coalesce(state,'<NULL>'), coalesce(country,'<NULL>'))) AS row_hash
FROM cleaned;

• Standardise at the staging boundary. TRIM, LOWER and UPPER make equal things equal, so ' in' and 'IN' aren't two countries and ' C-002' isn't a separate customer.
• Partition by the cleaned key (TRIM(customer_code)). Deduplicating on the raw key would leave a padded and an unpadded copy of the same customer.
• NULLIF(TRIM(customer_code), '') IS NOT NULL drops rows you can't key, including blank strings, which IS NOT NULL alone would keep. In production these go to a quarantine table rather than vanishing (post 03, Q51).
• NULLS LAST stops C-003's pull with no timestamp from winning (Q11). C-003's latest real pull says Dallas.
• row_hash is computed once here so every later load compares one column instead of six.

Engineer check. Grain: one row per cleaned customer_code. Run twice: CREATE OR REPLACE rebuilds the same result. NULL: keyless rows are removed and NULL timestamps sort last.

SELECT
    row_id, a, b,
    md5(concat_ws('|', coalesce(a, '<NULL>'), coalesce(b, '<NULL>'))) AS row_hash
FROM pairs
ORDER BY row_id;

• The separator stops boundary collisions: plain a || b turns both ('AB', 'C') and ('A', 'BC') into 'ABC'. With '|' they become 'AB|C' and 'A|BC'.
• The NULL placeholder is needed for a different reason on each engine:
  • DuckDB and Postgres's concat_ws skips NULLs, so ('X', NULL) and (NULL, 'X') both become 'X'. A customer whose city moved into the state column would look unchanged.
  • Snowflake's concat_ws and || return NULL if any argument is NULL (docs). Every row with any NULL then hashes to NULL, and NULL <> NULL is never true, so changes to those rows are never detected.
  • coalesce(col, '<NULL>') fixes both, and also keeps NULL apart from the empty string (rows 3 and 5).
• md5 turns the string into a fixed-length fingerprint that's identical on every engine, which matters when you reconcile across systems. Snowflake's HASH() is faster but engine-specific.

Choose a separator and a placeholder that can't appear in the data, or hash each column on its own and concatenate the hashes.

Engineer check. NULL: the whole question is NULL behaviour. Decide what a NULL means in the hash, and make sure it can't equal an empty string or a value shifted between columns.

INSERT INTO dim_customer (customer_code, first_name, last_name, email, city, state, country,
                          effective_from, effective_to, is_current, row_hash, version)
SELECT
    s.customer_code, s.first_name, s.last_name, s.email, s.city, s.state, s.country,
    COALESCE(CAST(s.signup_ts AS DATE), DATE '2025-03-01') AS effective_from,  -- load date if unknown
    DATE '9999-12-31',
    TRUE,
    s.row_hash,
    1
FROM stg_customer s
WHERE NOT EXISTS (SELECT 1 FROM dim_customer d WHERE d.customer_code = s.customer_code);

• No customer_sk in the column list, so the sequence default numbers each new row. The Unknown member keeps its -1.
• effective_from starts each customer's history at signup; C-003 has no signup time, so it falls back to the load date. History before the warehouse existed is unknowable, and a date is still better than a NULL that breaks range joins.
• 9999-12-31, is_current = TRUE, version = 1: every customer starts as their own first, open, current version.
• WHERE NOT EXISTS is the guard that makes the "first load" safe to re-run: the build ran it twice and the second run inserted nothing. A seed load that can double a dimension is a real incident waiting to happen; ongoing loads are SCD2 (Q22).

Engineer check. Grain: one current version per customer. Run twice: no change, verified. NULL: a missing signup time gets the load date, never a NULL window start.

MERGE INTO dim_product t
USING stg_product s
   ON t.product_code = s.product_code AND t.is_current
WHEN MATCHED AND (t.product_name IS DISTINCT FROM s.product_name
               OR t.category     IS DISTINCT FROM s.category
               OR t.list_price   IS DISTINCT FROM s.list_price) THEN UPDATE SET
    product_name = s.product_name,
    category     = s.category,
    list_price   = s.list_price
WHEN NOT MATCHED THEN INSERT
    (product_code, product_name, category, list_price, effective_from, effective_to, is_current, version)
    VALUES (s.product_code, s.product_name, s.category, s.list_price, DATE '2025-03-01', DATE '9999-12-31', TRUE, 1);

• ON … AND t.is_current matches each staged product to its current row, so the same MERGE stays correct once the table also holds Type 2 history.
• WHEN MATCHED AND (… IS DISTINCT FROM …) updates only rows that actually changed. Without the condition, SKU-001 would be rewritten with identical values every night: wasted writes, misleading "last modified" audit columns, and on Snowflake rewritten micro-partitions that cost storage and Time Travel space. IS DISTINCT FROM is the NULL-safe comparison (Q12), so a change from NULL to a value is caught.
• Type 1 means the old value is gone. The keyboard's old price of 98.00 can't be recovered. That's right for corrections and wrong for anything analysts slice history by; those attributes need Type 2 (Q22).
• WHEN NOT MATCHED THEN INSERT adds SKU-003 as a new current product.

Engineer check. Grain: one current row per product. Run twice: verified unchanged. NULL: IS DISTINCT FROM catches changes to and from NULL that <> would miss.

-- Step 1: close the current version of customers whose attributes changed
UPDATE dim_customer AS t
SET is_current   = FALSE,
    effective_to = DATE '2025-06-01' - 1          -- last day the old version was true
FROM stg_customer s
WHERE t.customer_code = s.customer_code
  AND t.is_current
  AND t.row_hash <> s.row_hash;

-- Step 2: open a new current version for changed AND brand-new customers
INSERT INTO dim_customer (customer_code, first_name, last_name, email, city, state, country,
                          effective_from, effective_to, is_current, row_hash, version)
SELECT
    s.customer_code, s.first_name, s.last_name, s.email, s.city, s.state, s.country,
    DATE '2025-06-01', DATE '9999-12-31', TRUE, s.row_hash,
    COALESCE((SELECT MAX(d.version) FROM dim_customer d WHERE d.customer_code = s.customer_code), 0) + 1
FROM stg_customer s
WHERE NOT EXISTS (SELECT 1 FROM dim_customer cur
                  WHERE cur.customer_code = s.customer_code AND cur.is_current);

• Two statements, in order. A standard MERGE acts once per matched target row, so it can't expire C-001's row and insert C-001's new row. The usual pattern is expire, then insert. (A single-MERGE variant exists, using a source that lists each changed key twice, but it's harder to read.)
• Step 1's t.row_hash <> s.row_hash is the change gate: only C-001 is expired, and C-002 is untouched.
• effective_to = load date - 1 closes the old window the day before the new one opens. The windows are contiguous and never overlap, so exactly one version matches any date (Q25 depends on that).
• Step 2's NOT EXISTS (… cur.is_current) finds every staged customer without a current row. After Step 1 that is exactly the changed customers (just expired) plus the new ones.
• version = MAX(version) + 1 is computed over all of the customer's versions, so numbering stays correct through any number of changes.
• Re-run: the second run finds no hash differences and every customer already current, so it changes nothing. The build verified it.

Engineer check. Grain: one row per customer version, one current. Run twice: verified unchanged. NULL: the hash must be NULL-safe (Q19), or a change from NULL is invisible to <>.

ALTER TABLE dim_product ADD COLUMN IF NOT EXISTS previous_category   VARCHAR;
ALTER TABLE dim_product ADD COLUMN IF NOT EXISTS category_changed_at DATE;

UPDATE dim_product AS t
SET previous_category   = t.category,
    category            = s.category,
    category_changed_at = DATE '2025-06-01'
FROM stg_product s
WHERE t.product_code = s.product_code
  AND t.category IS DISTINCT FROM s.category;

• Type 3 keeps a fixed, small amount of history in extra columns of the same row: here exactly one previous value and when it changed. Type 2 keeps unlimited history as extra rows.
• SET previous_category = t.category, category = s.category works in one statement because every expression in SET sees the row's values from before the update.
• IS DISTINCT FROM limits the update to real changes. Without it, SKU-001's unchanged category would be copied into previous_category, erasing the true previous value.
• Re-run: the second run finds the categories already equal, so nothing changes, and the history stays intact. The build verified it.

Use Type 3 when people only ask "current versus previous", such as sales under the old and new category after a reorganisation. It can't answer "what was it two changes ago".

Engineer check. Grain: unchanged, one row per product. Run twice: verified. NULL: a change from NULL to a category is caught by IS DISTINCT FROM, and NULL becomes the previous value.

INSERT INTO fact_sales
SELECT
    dd.date_key,
    COALESCE(dc.customer_sk, -1),
    COALESCE(dp.product_sk,  -1),
    ol.order_id,
    ol.line_no,
    ol.quantity,
    ol.unit_price,
    COALESCE(ol.discount_amt, 0),
    ol.quantity * ol.unit_price,
    ol.quantity * ol.unit_price - COALESCE(ol.discount_amt, 0)
FROM src_order_line ol
JOIN      src_order    o  ON o.order_id = ol.order_id
LEFT JOIN dim_date     dd ON dd.full_date = CAST(o.order_ts AS DATE)
LEFT JOIN dim_customer dc ON dc.customer_code = o.customer_code AND dc.is_current
LEFT JOIN dim_product  dp ON dp.product_code  = ol.product_code AND dp.is_current;

• JOIN src_order is an inner join on purpose: an order line without its header is broken source data, and the grain (order line) needs the header's date and customer.
• LEFT JOIN to every dimension: an unmatched key must not drop the line. SO-2 belongs to customer C-999 and product SKU-777, neither in the dimensions, and it still loads.
• COALESCE(sk, -1) points unresolved keys at the Unknown member. The $15 of SO-2 stays in every total, as an "Unknown" bucket that a monitor can alert on, instead of silently vanishing.
• COALESCE(discount_amt, 0): SO-1 line 1 has no discount, and x − NULL is NULL, so without it the line's net amount would be NULL and drop out of SUM.
• AND is_current makes this a current-version lookup. That's fine for a first load, but it labels every historical order with the customer's attributes today. Q25 fixes that.

Engineer check. Grain: one row per order line; dimension joins can't multiply rows, because each is_current lookup returns at most one row per key. Run twice: this plain insert doubles the fact; incremental loads use Q27, reloads Q28. NULL: missing keys become -1, missing discounts 0.

SELECT dc.loyalty_tier AS tier_at_purchase, COUNT(*) AS orders
FROM src_order o
JOIN dim_customer dc
  ON dc.customer_code = o.customer_code
 AND CAST(o.order_ts AS DATE) BETWEEN dc.effective_from AND dc.effective_to
GROUP BY dc.loyalty_tier
ORDER BY dc.loyalty_tier;

• order date BETWEEN effective_from AND effective_to picks the version that was true on the day of the order: SO-1 and SO-2 find the BRONZE row, SO-3 the GOLD row. This is a point-in-time (as-of) join.
• It returns exactly one version per order because Q22's windows are contiguous and don't overlap. If they did overlap, this join would duplicate orders.
• Joining on is_current credits all three of C-001's orders to GOLD, as the step-through shows. Any "did customers spend more after upgrading?" analysis is then meaningless, because the history has been rewritten.
• In a real warehouse you resolve this once, at load time: the fact load uses the as-of join to pick customer_sk, and since each version has its own surrogate key, the fact row is pinned to the right version forever. Reports then join on customer_sk with no date logic at all.

Engineer check. Grain: one row per order, because the windows don't overlap. NULL: a window with a NULL end date would match nothing; that's why the sentinel 9999-12-31 exists.

SELECT
    (SELECT SUM(revenue) FROM fact_sales_daily)                                   AS revenue,
    (SELECT SUM(on_hand) FROM fact_inventory
      WHERE snapshot_date = (SELECT MAX(snapshot_date) FROM fact_inventory))     AS month_end_on_hand,
    (SELECT ROUND(100 * SUM(revenue - cost) / NULLIF(SUM(revenue), 0), 1)
       FROM fact_sales_daily)                                                     AS margin_pct;

• Additive (revenue): sums correctly across every dimension, including time. $1,400.
• Semi-additive (stock on hand): you can sum it across products at one moment, but not across dates: the 40 mice on 1 March and the 25 on 31 March are partly the same mice. Across time you take the end-of-period value (or an average). Summing all four snapshots gives 87, an amount of stock that never existed.
• Non-additive (margin %): a ratio can't be summed or averaged. Recompute it from its parts: SUM(revenue − cost) ÷ SUM(revenue) = 290 ÷ 1,400 = 20.7%. The average of the four daily percentages is 27.5%, because it gives the $100 keyboard day the same weight as the $900 one. NULLIF(…, 0) avoids dividing by zero.

The rule for fact design follows: store the additive parts (revenue, cost), never the ratio, so any aggregation can rebuild the ratio correctly.

Engineer check. Grain: know each measure's additivity before you sum it. NULL: NULLIF(denominator, 0) turns divide-by-zero into NULL rather than an error.

-- 1. Fix this run's upper bound BEFORE loading anything
CREATE OR REPLACE TEMP TABLE batch AS
SELECT
    (SELECT watermark_ts FROM load_control WHERE table_name = 'fact_sales') AS lower_ts,
    MAX(_loaded_at)                                                         AS upper_ts
FROM src_sales_feed
WHERE _loaded_at > (SELECT watermark_ts FROM load_control WHERE table_name = 'fact_sales');

-- 2. Load exactly the rows in (lower, upper]
INSERT INTO fact_sales
SELECT f.*
FROM src_sales_feed f, batch b
WHERE f._loaded_at >  b.lower_ts
  AND f._loaded_at <= b.upper_ts;

-- 3. Advance to the bound you loaded up to; an empty run leaves it unchanged
UPDATE load_control
SET watermark_ts = COALESCE((SELECT upper_ts FROM batch), watermark_ts)
WHERE table_name = 'fact_sales';

• Follow the ingestion time (_loaded_at), not the business time (order_ts). A late order has an old order_ts, so a watermark on order_ts has already passed it and skips it forever. _loaded_at says when the row reached you, which is what "new since last run" means.
• Fix the upper bound first, then load (lower, upper]. The common version inserts WHERE _loaded_at > watermark and afterwards sets the watermark to MAX(_loaded_at). A row that lands between those two statements is above the old watermark but below the new one: never loaded, never seen again. Freezing upper_ts in step 1 and loading up to it closes the gap; anything that lands later has _loaded_at > upper_ts and waits for the next run.
• COALESCE(upper_ts, watermark_ts) keeps the watermark when the batch is empty (MAX over no rows is NULL). Don't reach for GREATEST(old, new) here: in Snowflake, GREATEST returns NULL if any argument is NULL (docs), so an empty run would set the watermark to NULL, and the next run's > NULL filter would match nothing, ever. (GREATEST_IGNORE_NULLS exists for this.) DuckDB's and Postgres's GREATEST ignore NULLs, so the bug never appears in local testing.
• Re-run: the second run finds nothing above the watermark and leaves both the fact and the watermark unchanged. The build verified it.

One gap remains, and it's about how _loaded_at is assigned rather than about this SQL: a slow writer can commit a row whose _loaded_at is earlier than one already loaded. If your source can do that, re-read a small safety window (_loaded_at > watermark − 10 minutes) and load with MERGE on the grain, so the overlap is absorbed instead of duplicated.

Engineer check. Grain: one row per order line, provided each run's window (lower, upper] doesn't overlap the last. Run twice: verified unchanged. NULL: an empty batch's MAX is NULL, so the watermark update must be NULL-proof, and in Snowflake GREATEST is not.

BEGIN TRANSACTION;

DELETE FROM fact_sales WHERE sale_date = DATE '2025-03-27';

INSERT INTO fact_sales
SELECT sale_date, order_id, line_no, quantity, net_amount
FROM src_fixed
WHERE sale_date = DATE '2025-03-27';

COMMIT;

• Delete the day, insert the day. Whatever the fact held for 27 March is replaced by exactly what the source now holds. The corrected quantity, the new line and the deletion all come through, and 26 March is untouched.
• The transaction means no reader ever sees the day half-deleted. Without it, a dashboard refreshing mid-load shows 27 March as empty.
• MERGE on (order_id, line_no) handles the update and the new line, but not the deletion: SO-8 line 2 is no longer in the source, so it never matches anything, and it stays in the fact as a $15 leftover (step 2). Some engines can fix this with WHEN NOT MATCHED BY SOURCE … THEN DELETE (DuckDB, SQL Server, Databricks), but Snowflake's MERGE has no such clause (syntax), and you'd also have to limit it to the one day.
• Re-run: deleting and re-inserting the same day gives the same result. The build verified it.

Use delete-and-insert by partition when the source can delete; MERGE is lighter when it only inserts and updates.

Engineer check. Grain: one row per order line within the day. Run twice: verified unchanged. NULL: the delete must target exactly the reloaded partition; a NULL sale_date would survive every day's delete, so keep it NOT NULL.

-- 1. Before the fact load: a placeholder for every unknown product code
INSERT INTO dim_product (product_code, product_name, category, is_inferred)
SELECT DISTINCT ol.product_code, 'UNKNOWN', 'UNKNOWN', TRUE
FROM src_order_line ol
WHERE NOT EXISTS (SELECT 1 FROM dim_product d WHERE d.product_code = ol.product_code);

-- 2. The fact load now resolves every code to a real surrogate key
INSERT INTO fact_sales
SELECT ol.order_id, ol.line_no, d.product_sk, ol.net_amount
FROM src_order_line ol
JOIN dim_product d ON d.product_code = ol.product_code;

-- 3. Later, when the product feed delivers it: fill the placeholder in place
UPDATE dim_product AS t
SET product_name = s.product_name, category = s.category, is_inferred = FALSE
FROM stg_product_late s
WHERE t.product_code = s.product_code AND t.is_inferred;

• Step 1 creates a real dimension row for SKU-009, with the known natural key, a new surrogate key, placeholder attributes and is_inferred = TRUE so it can be monitored.
• Step 2 can now use an inner join safely: every code resolves. The $120 line keeps its link to which product it was.
• Step 3 fills the placeholder in place (a Type 1 fill), keeping the same surrogate key. Every fact row already pointing at product 101 shows "4K Monitor, Displays" immediately, with no fact update.
• Why not -1? Unknown loses the natural key: when the real product arrives, nothing tells you which -1 facts were SKU-009.
• Why not a new surrogate key for the real product? Then every fact pointing at the placeholder must be re-pointed: a large, risky fact UPDATE. Filling in place avoids it entirely.

Engineer check. Grain: unchanged on both tables. Run twice: Step 1's NOT EXISTS and Step 3's is_inferred filter make both dimension steps re-runnable; Step 2 is a plain fact insert. Monitor how many inferred rows stay unfilled, and for how long.

SELECT pc.sale_date, pc.product
FROM fact_promotion_coverage pc
WHERE NOT EXISTS (
    SELECT 1 FROM fact_sales fs
    WHERE fs.sale_date = pc.sale_date AND fs.product = pc.product
)
ORDER BY 1, 2;

• A factless fact records that an event or a condition happened, with keys and no numbers: a promotion ran, a student was enrolled, a product was eligible.
• It answers what didn't happen, which a measure fact can't: the sales fact has no row for the keyboard on 10 March, so you can't ask it "which promotions sold nothing?". Starting from the coverage table and anti-joining to sales (NOT EXISTS) finds the promoted-but-unsold product-days.
• The keyboard sold on 11 March, but it wasn't promoted that day, so it isn't counted as a promotion success. The anti-join matches on both date and product.

Engineer check. Grain: one row per promoted product-day. NULL: NOT EXISTS is safe even if a sales key is NULL; NOT IN wouldn't be (Q14).

CREATE OR REPLACE TABLE bridge_order_coupon AS
SELECT order_id, coupon_code,
       1.0 / COUNT(*) OVER (PARTITION BY order_id) AS weight     -- weights per order sum to 1
FROM order_coupon;

SELECT b.coupon_code,
       ROUND(SUM(fs.net_amount * b.weight), 2) AS attributed_revenue
FROM fact_sales fs
JOIN bridge_order_coupon b ON b.order_id = fs.order_id
GROUP BY b.coupon_code
ORDER BY b.coupon_code;

• The fan-out: joining order lines to an order-level coupon list repeats each line once per coupon. SO-1's $100 appears under SPRING and under FREESHIP, so the coupon totals sum to $250 for $150 of real revenue (step 2).
• The bridge (bridge_order_coupon) resolves the many-to-many relationship and carries a weight: 1 / number of coupons on the order. SO-1's two coupons get 0.5 each.
• SUM(net_amount × weight) splits each order's revenue across its coupons, so the totals sum to exactly the real revenue. An even split is the simplest rule; a business can weight by coupon value instead, as long as each order's weights sum to 1.

Engineer check. Grain: the bridge is one row per order and coupon; the report is one row per coupon. Check: SUM(weight) per order is exactly 1, and the weighted total equals the fact total.

WITH last_snapshot AS (
    SELECT *
    FROM fact_inventory_snapshot
    QUALIFY snapshot_date = MAX(snapshot_date)
            OVER (PARTITION BY date_trunc('month', snapshot_date), store)
)
SELECT CAST(date_trunc('month', snapshot_date) AS DATE) AS month,
       store,
       MAX(snapshot_date)                 AS snapshot_used,
       SUM(on_hand)                       AS month_end_on_hand
FROM last_snapshot
GROUP BY ALL
ORDER BY month, store;

• Stock is semi-additive (Q26): sum across products at one moment, never across days. So each month has to collapse to a single snapshot day first.
• The CTE's QUALIFY snapshot_date = MAX(snapshot_date) OVER (…) keeps the rows of the last available snapshot of each month and store. It has to happen in its own step: QUALIFY runs after GROUP BY, so it can't filter rows before they're summed. The outer query then adds up the products on that one day.
• The calendar-month-end version (join to the last calendar day of each month) loses February completely, because 28 February has no rows (step 2). A month disappearing from a stock report is worse than a slightly stale number, and the last-available rule degrades gracefully.
• snapshot_used shows which day each figure comes from, so a missing month-end snapshot is visible in the report instead of hidden. Alert when it isn't the calendar month end.

Engineer check. Grain: one row per month and store. NULL: a month with no snapshots at all still disappears; join to a month spine if the report must show it.

MERGE INTO fact_order_fulfillment t
USING (SELECT order_id, MIN(CAST(ship_ts AS DATE)) AS ship_date FROM shipping_events GROUP BY order_id) s
   ON t.order_id = s.order_id
WHEN MATCHED AND t.shipped_date IS NULL THEN UPDATE SET shipped_date = s.ship_date;

SELECT
    ROUND(AVG(datediff('day', ordered_date, delivered_date)), 1)          AS avg_days_to_deliver,
    ROUND(100.0 * COUNT(delivered_date) / COUNT(*), 1)                     AS pct_delivered
FROM fact_order_fulfillment;

• An accumulating snapshot has one row per process instance (an order) with a date per milestone. Rows are updated as milestones happen, unlike transaction facts, which only receive inserts.
• The MERGE source is grouped per order, so two shipping events for one order can't make the merge nondeterministic (Q10), and MIN keeps the first shipment.
• AND t.shipped_date IS NULL stamps a milestone once and never overwrites it, which makes the statement safe to re-run with the same events.
• AVG(datediff(…)) skips SO-2, because its delivered_date is NULL and so is the difference. That's correct: an undelivered order has no delivery time yet. The share delivered uses COUNT(delivered_date) (non-NULL only) over COUNT(*).
• The payoff of the design: lag between any two milestones is a single subtraction.

Engineer check. Grain: one row per order, updated in place. Run twice: the IS NULL guard makes the stamp idempotent. NULL: open milestones are NULL, and the averages correctly leave them out.

WITH sales AS (
    SELECT p.category, s.month, SUM(s.net_amount) AS revenue
    FROM fact_sales s JOIN dim_product p USING (product_sk)
    GROUP BY ALL
),
returns AS (
    SELECT p.category, r.month, SUM(r.refund_amount) AS refunds
    FROM fact_returns r JOIN dim_product p USING (product_sk)
    GROUP BY ALL
)
SELECT
    COALESCE(s.category, r.category)               AS category,
    COALESCE(s.month, r.month)                     AS month,
    COALESCE(s.revenue, 0)                         AS revenue,
    COALESCE(r.refunds, 0)                         AS refunds,
    COALESCE(s.revenue, 0) - COALESCE(r.refunds, 0) AS net_after_refunds
FROM sales s
FULL OUTER JOIN returns r ON r.category = s.category AND r.month = s.month
ORDER BY month, category;

• Aggregate each fact separately to the shared grain (category, month), then join the two results. This is drill-across, and it works because both facts use the same, conformed product dimension and calendar.
• Joining the facts directly matches every sale row with every return row of the same product: the Accessories sales ($150 in two rows) meet two refunds, so revenue is counted twice and refunds twice (step 2).
• FULL OUTER JOIN keeps a category-month that has sales but no returns (Displays in March) and one with returns but no sales (Displays in April, a return of an earlier sale).
• COALESCE(…, 0) before subtracting, because revenue minus a missing refund total would otherwise be NULL.

Engineer check. Grain: never join two facts at their own grains; meet at a shared, coarser grain. NULL: COALESCE each side to 0 after the FULL OUTER JOIN.

SELECT 'DUPLICATE_GRAIN' AS rule, order_id, line_no, COUNT(*) AS rows_found
FROM fact_sales
GROUP BY order_id, line_no
HAVING COUNT(*) > 1

UNION ALL
SELECT 'NEGATIVE_NET', order_id, line_no, 1
FROM fact_sales
WHERE net_amount < 0

UNION ALL
SELECT 'ORPHAN_PRODUCT', order_id, line_no, 1
FROM fact_sales f
WHERE NOT EXISTS (SELECT 1 FROM dim_product d WHERE d.product_sk = f.product_sk)

ORDER BY rule, order_id;

• Each UNION ALL branch is one assertion, written as "return the rows that break it", and labelled so a failure says which rule broke and where.
• Uniqueness: GROUP BY the grain, HAVING COUNT(*) > 1. This is the test that stands in for the primary key Snowflake doesn't enforce.
• Business rule: net_amount < 0.
• Referential integrity: NOT EXISTS against the dimension, never NOT IN (Q14).
• How a pipeline uses it: load into a staging copy, run the gate, and only if it returns nothing swap the staging table in (or merge it into the published table). If it returns rows, fail the run and report them. It's far cheaper to block here than to explain a wrong dashboard later. This is what dbt tests and similar tools generate for you.

Engineer check. Grain: the gate checks it. Run twice: read-only. NULL: a NULL net_amount passes < 0 and a NULL order_id groups as one key; add explicit IS NULL rules if those matter.

WITH fact_daily AS (
    SELECT sale_date AS d, SUM(net_amount) AS fact_net, COUNT(*) AS fact_rows
    FROM fact_sales GROUP BY sale_date
),
src_daily AS (
    SELECT order_date AS d,
           SUM(quantity * unit_price - COALESCE(discount_amt, 0)) AS src_net,
           COUNT(*) AS src_rows
    FROM src_order_line GROUP BY order_date
)
SELECT COALESCE(f.d, s.d) AS d, s.src_rows, f.fact_rows, s.src_net, f.fact_net,
       COALESCE(f.fact_net, 0) - COALESCE(s.src_net, 0) AS diff
FROM fact_daily f
FULL OUTER JOIN src_daily s ON s.d = f.d
WHERE ABS(COALESCE(f.fact_net, 0) - COALESCE(s.src_net, 0)) > 0.01
ORDER BY d;

• Two CTEs compute the same measure two ways: from the published fact, and from the source with the same business rule. That is a control total.
• FULL OUTER JOIN on the day surfaces a day missing from the fact (3 March was never loaded) as well as a day the source doesn't know.
• > 0.01 is the tolerance for rounding, so only material differences are reported.
• Why counts aren't enough: 1 March has two rows on both sides, so a row-count check passes, but the fact's line 2 holds 69.00 where the source says 79.00 (the $10 discount was applied twice). Counts catch missing and duplicated rows; sums catch wrong values. Mature pipelines check both.

Engineer check. Grain: one row per day with a discrepancy. Run twice: read-only. NULL: a day missing on one side gives NULL totals, so COALESCE before subtracting, or the missing day is filtered out by the WHERE.

WITH rolled AS (
    SELECT month, category, SUM(net_amount) AS revenue,
           GROUPING(month)    AS g_month,
           GROUPING(category) AS g_cat
    FROM v_sales_mart
    GROUP BY GROUPING SETS ((month, category), (month), ())
),
ranked AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY month, g_cat ORDER BY revenue DESC) AS rnk
    FROM rolled
)
SELECT
    CASE WHEN g_month = 1 THEN 'GRAND TOTAL' ELSE CAST(month AS VARCHAR) END AS month,
    CASE WHEN g_cat = 1 THEN 'ALL' ELSE category END                        AS category,
    revenue
FROM ranked
WHERE g_cat = 1 OR rnk <= 3
ORDER BY g_month, ranked.month, g_cat, revenue DESC;

• GROUPING SETS ((month, category), (month), ()) computes per-category rows, a subtotal per month and the grand total in one scan; three UNION ALL queries would scan the data three times.
• GROUPING(col) returns 1 on rows where that column was rolled up, which tells a subtotal apart from a category that is genuinely NULL.
• PARTITION BY month, g_cat is the fix. The obvious PARTITION BY month puts the month's subtotal row in the same ranking as its categories. The subtotal is always the largest number, so it takes rank 1 and pushes the real third category out: the query returns the top two (step 2). Partitioning by g_cat as well ranks categories only against categories.
• WHERE g_cat = 1 OR rnk <= 3 keeps every subtotal and total row, and the top three categories.
• The subtotal sums all categories (March: 1,800, including Cables), which is usually what a report means by "month total". If you want the total of only the top three, compute it after filtering.

Engineer check. Grain: mixed on purpose (category rows, subtotals, a total), so label each row's level clearly for consumers. NULL: rolled-up columns are NULL; use GROUPING() rather than IS NULL to detect them.