Data modelling for analytics: star schemas, marts and dbt examples.

Define the question and the grain first

Use the synthetic orders from the companion BigQuery tutorial. The question is: what is the value of completed orders each day? Write the contract first: one row per completed order in fct_orders; one row per order date in daily_orders; amount is the illustrative order value in a single currency.

This is completed order value, not recognised revenue or cash collected. Refunds, tax, shipping, multiple currencies and accounting recognition would need separate definitions and data. A customer count from the fact means customers with completed orders, not every registered customer.

• Build the example project and load the three-order fixture

Move from raw records to business entities

Raw data preserves what arrived. Staging standardises a source without silently removing records. Intermediate models can handle reusable joins or deduplication when the source requires them. Marts expose stable business concepts for reporting. Do not add an intermediate layer just to copy a table.

In the companion project, stg_orders retains all three orders, fct_orders selects the two completed ones, and daily_orders aggregates them to one date. These layers have different grains; naming and documentation must make the difference visible.

• dbt project structure: staging, intermediate models and marts

Use a star schema when shared dimensions earn their place

A fact table records events or measurements at a declared grain. A dimension supplies descriptive attributes, such as customer segment, at its own declared key. A star schema joins facts to dimensions through those keys. The small tutorial needs no customer dimension because it has no customer attributes to describe.

When you add dim_customers, require exactly one row per customer_id for a current-state dimension. Joining an order fact to several customer versions duplicates order value. If historical segment reporting matters, use versioned dimension keys and validity windows instead; join each fact to the version valid at the event time.

Do not join order-level amounts directly to order lines and then sum them. Either aggregate lines to one row per order before joining, or model a line fact with an explicit allocation rule. A distinct count of orders can hide duplicated amounts.

Build a mart whose output you can explain

This SQL belongs in models/marts/daily_orders.sql in the companion project. The cancellation rule lives in fct_orders so downstream reports reuse it. A table with no row for 2026-10-02 means no completed orders in this fixture; it does not distinguish zero activity from missing ingestion. Use a date spine and completeness checks if consumers need that distinction.

select
    order_date,
    count(*) as completed_orders,
    sum(amount) as completed_order_value
from {{ ref('fct_orders') }}
group by order_date

Test the contracts and the joins separately

Use not_null and unique on the declared fact key. If you introduce dim_customers, add not_null and unique on its key and a relationships test from fct_orders.customer_id. Relationships checks alone do not prove the parent is unique, and null foreign keys require a separate not_null rule.

The singular test below is optional until a dim_customers model exists. Save it as tests/customer_join_preserves_orders.sql after creating that dimension. It returns lost or duplicated orders. Key tests still matter: two source rows sharing one order_id would already violate the fact grain.

• Run and deliberately break dbt data tests locally
• Reconcile source, model and report totals

select f.order_id
from {{ ref('fct_orders') }} as f
left join {{ ref('dim_customers') }} as c
    on f.customer_id = c.customer_id
group by f.order_id
having count(*) != 1 or count(c.customer_id) != 1

Document the decisions that tests cannot make for you

For each mart, record the row grain, key, included statuses, time zone, currency treatment, late-arrival policy, refresh expectation and metric owner. For each measure, say whether it can be summed across dates and entities. Daily unique customers cannot simply be summed into monthly unique customers.

Ask the business owner to sign off a small worked example before adding more sources. Review changes to grain and metric definitions like API changes: identify downstream consumers, compare old and new totals, and plan the transition. Test success means the declared rules held on the observed data; it does not prove the business definition is right.