The data mart: the layer your dashboard should sit on

A data mart is a bounded slice of your warehouse, built for one subject or one department. A marketing mart, a finance mart, an inventory mart.

In practice it is a handful of tables or views that are ready to be queried. Columns are named the way the department names them, the calculations are already done, and the noise that only the source system understands is gone.

Where the warehouse holds everything, the mart holds what one group of people needs to answer a question.

A modern warehouse usually has three layers, and the mart is the top one.

• Raw. Data exactly as it arrived. The GA4 export in BigQuery, your Shopify or WooCommerce tables, ad cost from a connector. You change nothing here, so you can always go back to the source.
• Cleaned. One model per source table that renames columns, pins data types, aligns time zones and drops obviously broken rows. In dbt this is the staging layer, with models like stg_ga4__events. One staging model per source table, nothing more.
• Marts. Here you combine sources into something a human asks questions of. Sessions with orders, orders with ad cost, by day and by channel.

Two things go wrong when your dashboard points straight at the source.

First, it breaks. Someone renames an event parameter in GTM, or your shop platform changes a column name in an update, and your chart falls over. With a staging model in between, you fix one file and everything above it works again.

Second, everyone builds their own calculation. One analyst filters test orders by email address, another by order total, a third forgets. Three reports, three revenue numbers, and a meeting about the numbers instead of about the work.

A mart settles that by making the choice once, somewhere you can read it back.

In practice you build these layers with dbt. You write SELECT statements, dbt turns them into tables or views, and ref() points one model at another. From those references dbt works out the build order.

A marketing mart is usually pre-aggregated. Not every event, but one row per day per channel. That buys you two things: the dashboard loads fast, and you scan megabytes instead of gigabytes in BigQuery, where you pay per byte scanned.

-- models/marts/marketing/mart_channel_performance.sql
{{ config(materialized='table', partition_by={'field': 'date_day', 'data_type': 'date'}) }}

with sessions as (
    select date_day, channel, count(distinct session_id) as sessions
    from {{ ref('int_ga4_sessions') }}
    group by 1, 2
),

orders as (
    select date_day, channel, count(*) as orders, sum(revenue_ex_vat) as revenue
    from {{ ref('int_orders_attributed') }}
    where is_test_order = false
    group by 1, 2
),

spend as (
    select date_day, channel, sum(cost) as cost
    from {{ ref('stg_ads__daily_cost') }}
    group by 1, 2
)

select
    sessions.date_day,
    sessions.channel,
    sessions.sessions,
    coalesce(orders.orders, 0) as orders,
    coalesce(orders.revenue, 0) as revenue,
    coalesce(spend.cost, 0) as cost
from sessions
left join orders using (date_day, channel)
left join spend using (date_day, channel)

Notice what is already decided here: test orders are out, revenue excludes VAT, and channel attribution happens in the model below. Whoever opens this dashboard cannot accidentally make those choices differently.

• Leave your raw data alone. Every cleaning step belongs in a model, not in the source.
• Give each source table one staging model and keep it boring: rename, cast, filter.
• Build a mart per subject, with column names the department already uses.
• Aggregate the mart to the level you report on, usually day by channel by country. Keep the detail in the layer below.
• Partition mart tables in BigQuery on date, so a filter on the last 28 days does not scan your whole history.
• Point your BI tool at the marts only. Anyone who needs raw data writes a model, not a dashboard.