Marts and serving

A mart is a definition; you still choose its physical form. Three options, trading freshness, cost, and speed:

• View: a saved query, computed at read time. Always fresh, zero storage - and every open of the dashboard pays the full compute. Perfect at Daybreak's size; DuckDB gives you these natively.
• Materialized view: results stored, refreshed on a schedule. Reads are instant; the price is staleness between refreshes plus refresh compute. Cloud engines (Snowflake, BigQuery, Redshift) offer scheduled materialization; the concept is what transfers.
• Table: you materialize it yourself - a CTAS your b6 load rebuilds each night. Maximum control, and now freshness is your pipeline's problem.

Rule of thumb: start as a view. Promote to materialized when a dashboard hits it hundreds of times a day and the compute bill (or latency) says so. You will feel this exact promotion pressure in b8.

A mart's WHERE clause is a policy decision someone must own. "Finance revenue excludes refunded and cancelled orders" sounds technical, but it decides whose Monday number looks better - so get the definition agreed with the team that consumes it, write it into the mart, and document it where the leader track's a3 audience can find it. The alternative - every analyst re-deriving the rule - is how a company ends up arguing about arithmetic in the boardroom.

Spot the three altitudes in the live result: rows with both columns filled (detail), rows where channel is NULL (month subtotal), and the one row where both are NULL (grand total - which lands first, since we sort by revenue).

SELECT d.month_name, f.channel,
       ROUND(SUM(f.line_revenue), 2) AS revenue
FROM fct_order_line f
JOIN dim_date d ON f.date_key = d.date_key
WHERE f.status = 'completed'
GROUP BY ROLLUP (d.month_name, f.channel)
ORDER BY revenue DESC
LIMIT 12;

Pre-aggregation is a cache, and every cache is a freshness debt. The test is arithmetic: a KPI tile hit 1,000 times a day over the same day-old numbers should read from a materialized rollup - computing it 1,000 times is pure waste. An analyst's one-off exploration should hit the star directly - fresh, flexible, and the scan cost is paid once. Daybreak's 53 fact rows make everything instant; the decision pattern is what you are rehearsing, and b8 attaches real costs to it.

The moment one consumer you do not control reads your mart, its definition stops being your private query and becomes an interface. Treat it like one:

• Columns stay. Renaming revenue to net_revenue is a breaking change; every downstream dashboard and sync dies quietly. Add columns freely; remove or rename only with a migration.
• Definitions are documented. "revenue = completed order lines only, refunds excluded" lives next to the mart, in words a CFO can read. This is the exact artifact the leader track's a3 session teaches executives to demand.
• Grain is stated. "One row per month" or "one row per channel" - say it, so nobody double-counts by joining two marts of different grain.

• BI dashboards: the default. Point the tool at the mart, never at raw staging - the mart is where policy lives.
• Scheduled extracts: CSVs on a schedule are unglamorous and everywhere. Same rule: export the mart, not raw tables.
• Reverse ETL: warehouse-computed answers (segments, LTV, churn risk) pushed back into operational tools like the CRM. The warehouse graduates from reporting to running the business.
• ML features: models train on mart-grade tables too - and gain the same reproducibility from stated grain and stable columns.

The retention team wants one row per plan: how many subscriptions ever started, how many are still active, how many cancelled. Hint: cancel_date IS NULL means still active.

CREATE VIEW mart_subscriptions_by_plan AS
SELECT c.plan,
       count(*) AS subscriptions,
       SUM(CASE WHEN s.cancel_date IS NULL THEN 1 ELSE 0 END) AS active,
       SUM(CASE WHEN s.cancel_date IS NOT NULL THEN 1 ELSE 0 END) AS cancelled
FROM stg_subscriptions s
JOIN dim_customer c ON s.customer_id = c.customer_id
GROUP BY c.plan;

SELECT * FROM mart_subscriptions_by_plan
ORDER BY subscriptions DESC;

Merchandising wants product revenue with category subtotals and a grand total - Part 2's ROLLUP, now wearing a mart's name. Completed orders only (merchandising follows finance's policy here).

CREATE VIEW mart_top_products AS
SELECT p.category, p.name AS product,
       ROUND(SUM(f.line_revenue), 2) AS revenue
FROM fct_order_line f
JOIN dim_product p ON f.product_key = p.product_key
WHERE f.status = 'completed'
GROUP BY ROLLUP (p.category, p.name);

SELECT category, product, revenue
FROM mart_top_products
ORDER BY revenue DESC
LIMIT 10;

Look back at dim_customer: customer_key, customer_id, name, city, country, plan, signup_date. The marketing mart is readable by half the company. Which columns stay out?

• name - direct PII. A channel-performance mart has zero analytical need for it, so exposure is pure risk. Aggregate marts should carry counts of people, never people.
• city - quasi-identifier. Alone it seems harmless; joined with plan and signup week it can single someone out in a small market. Keep country, drop city, unless the mart's purpose demands it and access is restricted.
• The principle: marts minimize, by design. Every column in a mart is a disclosure decision - the leader track's governance session (a3) gives your executives the same rule from their side.