Marts and the business questions

Three differences, and each one is a decision somebody has to make on purpose:

• The grain is chosen from the questions, not from the fact. fct_order_items is at order-line grain because that is what happened. mart_merchant_daily is at merchant-day grain because that is what gets asked. Nobody ever asks a question at line grain; they ask at the grain of a meeting.
• The metric definitions are baked in. where facts.is_settled lives inside the mart, so "revenue" already means settled money and no dashboard has to remember. That single filter is why the mart total equals the fact total, which is what assert_mart_agrees_with_fact.sql checks on every build.
• The dimension attributes people group by are flattened in. merchant_name, merchant_category and merchant_tier sit in the mart, so the common query has no joins at all. That is the speed everyone wanted, arrived at as a consequence rather than as the goal.

Look at the last two columns of mart_merchant_daily:

• aov_numerator - settled net revenue for that merchant on that day
• aov_denominator - distinct settled orders for that merchant on that day

There is no aov column, and that is deliberate. A stored ratio is only correct at the grain it was computed at. The moment somebody groups the mart up to a month, or across all merchants, a BI tool will happily average the daily ratios and produce a number that is not the average order value of anything.

The size of the error is easy to measure on this project: sum(aov_numerator) / sum(aov_denominator) gives 116.93, the real AOV over 957 settled orders. Averaging the per-row ratios gives 115.30. That is 1.4% wrong on a friendly dataset with 600 rows and no outliers, and the direction of the error changes with the shape of the data, so you cannot even correct for it.

One line in dbt_project.yml decides this for the whole folder:

• staging: +materialized: view - cheap, always fresh, nothing stored. A staging model is read once by the thing above it.
• marts: +materialized: table - built once per run, read constantly, by people and dashboards and exports. Paying the compute once at build time instead of once per viewer is the entire argument.

Incremental materialization is the next step up and this project deliberately does not use it: 1,434 fact rows rebuild in milliseconds, and an incremental model you cannot justify is just a correctness risk with extra configuration. The honest trigger for incremental is a full refresh that no longer fits your build window, not a feeling that tables are wasteful.

The whole model is three aggregations and a join, and the shape is worth memorising because you will write it every month:

• carts aggregates fct_carts to one row per day: carts created, carts converted, cart value, abandoned value. Demand, including demand that never became money.
• sales aggregates fct_order_items to one row per day, filtered to settled: paid orders, net revenue, units.
• payments aggregates fct_transactions to one row per day: attempts, declines, declined amount.
• The final select starts from carts and left joins the other two, because a day can have carts and no sales but never sales with no carts. Every measure is wrapped in coalesce(..., 0), so a quiet day is a row of zeros rather than a row of NULLs that break arithmetic downstream.

Notice what the mart deliberately does not contain: no conversion_rate, no aov, no decline_rate. It ships carts_created and carts_converted, net_revenue and paid_orders, payment_attempts and declines. Six columns instead of three, and all six are additive.

mart_hourly_transactions is built from the payments fact and not the sales fact, and the reason is a sentence you should be able to say out loud about every mart you build: the question is about when money moves, and money moves at a payment attempt.

An order has one timestamp, which is when it was placed. A payment attempt has its own timestamp, and a retried order has several. If you answered the peak-hour question from the sales fact you would get the hour people ordered, which is a different question with a different answer and no way to see the declines. The peak hour on this dataset is 21:00 with 96 attempts; the top decline hour is not the same hour, which is the sort of thing only the payments fact can tell you.

The same logic explains why the hour lives in dim_time_of_day rather than in dim_dates: 24 rows once, instead of multiplying the date dimension by 24, and "what are our peak hours across all 90 days" needs to group by hour independently of the date.

The fortnight boundaries come from vars in dbt_project.yml, so no model and no analysis invents its own idea of "the last fortnight". The last column is the one that turns a merchant story into a market story.

WITH fortnights AS (
    SELECT merchant_id, merchant_name,
           sum(CASE WHEN activity_date >= DATE '2026-06-16'
                    THEN net_revenue ELSE 0 END) AS recent_14d,
           sum(CASE WHEN activity_date >= DATE '2026-06-02'
                     AND activity_date <  DATE '2026-06-16'
                    THEN net_revenue ELSE 0 END) AS prior_14d
    FROM main_marts.mart_merchant_daily
    GROUP BY 1, 2
)
SELECT merchant_id, merchant_name,
       round(prior_14d, 0)  AS prior_14d,
       round(recent_14d, 0) AS recent_14d,
       round(100.0 * (recent_14d - prior_14d) / prior_14d, 1) AS pct_change,
       sum(CASE WHEN recent_14d < prior_14d THEN 1 ELSE 0 END) OVER () AS merchants_down_of_12
FROM fortnights
ORDER BY pct_change
LIMIT 5;

Two answers in one result set, which is the whole reason the merchant-day grain exists. Ranked by percentage change you get M07 at -85.2%. Ranked by absolute change you would get M01 Kettle & Co, which lost more money while falling only 12.5%. Both are true; which one you take to a meeting depends on whether you are protecting revenue or protecting a merchant relationship.

This is the decomposition the funnel mart was built for: revenue equals carts times conversion times AOV, so a drop is always one of those three moving and the arithmetic is checkable in one place. Note that AOV is computed here as sum(revenue) / sum(orders) - the numerator-and-denominator discipline paying off in the query rather than in the model.

WITH fortnights AS (
    SELECT CASE WHEN activity_date >= DATE '2026-06-16'
                THEN 'recent' ELSE 'prior' END AS fortnight,
           sum(carts_created)   AS carts,
           sum(carts_converted) AS converted,
           sum(paid_orders)     AS orders,
           sum(net_revenue)     AS revenue
    FROM main_marts.mart_funnel_daily
    WHERE activity_date >= DATE '2026-06-02'
    GROUP BY 1
)
SELECT fortnight,
       carts,
       round(100.0 * converted / carts, 1) AS conversion_pct,
       orders,
       round(revenue, 0)         AS revenue,
       round(revenue / orders, 2) AS aov
FROM fortnights
ORDER BY fortnight DESC;

The answer is a demand problem plus a basket problem, not a checkout problem. That distinction sends the work to marketing and merchandising rather than to engineering, and it is only visible because the mart keeps carts, conversions, orders and revenue at the same grain with all four denominators intact.

2026-06-10 is the incident day, and it is a var in the project config for the same reason the fortnights are. The scalar subquery reaches into the payments fact for the reason, which is a drill-down from the mart into the star - exactly the path a mart should leave open.

SELECT f.activity_date,
       f.carts_created,
       f.carts_converted,
       f.paid_orders,
       round(f.net_revenue, 0) AS net_revenue,
       f.payment_attempts,
       f.declines,
       round(100.0 * f.declines / f.payment_attempts, 1) AS decline_rate_pct,
       (SELECT t.decline_reason
          FROM main_marts.fct_transactions t
          JOIN main_marts.dim_dates d ON d.date_key = t.date_key
         WHERE d.date_day = f.activity_date AND t.is_declined = 1
         GROUP BY 1 ORDER BY count(*) DESC LIMIT 1) AS top_decline_reason
FROM main_marts.mart_funnel_daily f
WHERE f.activity_date = DATE '2026-06-10';

The gap between 13 converted carts and 8 paid orders is the whole story, and it is only a gap because the mart holds demand and money side by side. Against the 6.2% baseline decline rate for the whole period, a 52.9% day is not a variation - it is an outage. Change the date and re-run to see what a normal day looks like.

The hourly mart pre-aggregates the payments fact to date-hour, so this question is a group-by over 753 rows instead of a scan of 1,060 attempts with a timestamp function in the GROUP BY.

SELECT hour_label, daypart,
       sum(attempts)  AS attempts,
       sum(approvals) AS approvals,
       sum(declines)  AS declines
FROM main_marts.mart_hourly_transactions
GROUP BY 1, 2
ORDER BY attempts DESC, hour_label
LIMIT 4;

Add is_weekend to the GROUP BY and you get the answer that actually changes a decision: the weekday peak is 20:00 with 64 attempts and the weekend peak is 21:00 with 41, so a single deploy freeze window is wrong on both kinds of day. The mart keeps day_name and is_weekend flattened in for precisely this follow-up question.

No SQL. Every number above matches the hand-built warehouse to the cent, and it should - the SQL computing them is barely different. What changed is everything about how a change to those definitions happens now:

• A mart change is a reviewable diff. Moving is_settled, adding a merchant tier, changing the fortnight boundary: all of it shows up as a pull request with a before and an after, seen by a second person before it reaches a dashboard. The hand-written version had a definition change look exactly like a whitespace change.
• The mart cannot silently disagree with the star. assert_mart_agrees_with_fact.sql compares the two totals on every build, and a failure SKIPs everything downstream instead of publishing.
• The rebuild is one command with a blast radius you can see. dbt build --select marts rebuilds all twelve mart models and runs their tests. dbt build --select mart_funnel_daily+ rebuilds just that mart and everything that depends on it.
• The definitions are documented where they are implemented. The grain of each mart is the first line of its file, and the same text is in _marts__models.yml and therefore in the generated docs. It cannot drift, because there is only one copy.