Modeling For Consumption: The BI Handoff

Your model is correct and it runs at scale. Now someone opens a BI (business intelligence) tool and drags two fields onto a chart. They get a number and never read your model.

Here is the question for this chapter. The dashboard shows a different revenue than finance. Why, and how do you design the model so BI users cannot get it wrong?

Recap: a fact table holds events, a dimension table describes them, and the grain says what one row means. You also know history rows, a semantic layer that defines paid revenue once, and fan-out, where a join repeats a row.

What you will learn

1. What a BI tool does to your model: it writes SQL for you.
2. Three causes of a different total, each with real numbers.
3. Design rules: grain and names, additive measures, one definition for each metric.
4. Dates, unknown values, row-level security, and retiring a dataset.
5. A checklist for a metric that disagrees, and an interview answer.

A BI tool draws charts and tables for people. The person picks fields. The tool writes the SQL and sends it to the warehouse.

Many BI tools make three choices without asking. They join tables on keys and add a SUM to a number column. They add only the filters the person picks.

A person drags country and amount onto a chart. The tool writes SQL like this. The exact text differs between tools:

SELECT c.country, SUM(o.amount) AS amount
FROM fct_orders AS o
JOIN dim_customer AS c
  ON c.customer_key = o.customer_key
GROUP BY c.country
ORDER BY amount DESC;

Nobody chose these results. The join was on keys and SUM was a default. No one filtered on status, so the refund counts as revenue.

Your model cannot say "refunds are not revenue" by itself. That rule must live somewhere the tool reads. The next sections show where.

Finance says paid revenue for January is 160.50. A dashboard can show other numbers. Each different number has one cause.

Cause 1: a missing filter

Group the orders by status to see where 220.50 comes from:

SELECT status, COUNT(*) AS orders, SUM(amount) AS revenue
FROM fct_orders
GROUP BY status
ORDER BY revenue DESC NULLS LAST;

Cause 2: a join that repeats rows

Now a person adds the product from order_items. The tool joins the two tables. One order with two items becomes two rows:

SELECT SUM(o.amount) AS revenue,
       COUNT(*) AS rows_summed,
       COUNT(DISTINCT o.order_id) AS orders
FROM fct_orders AS o
JOIN order_items AS i ON i.order_id = o.order_id
WHERE o.status = 'paid';

Cause 3: two meanings of country

Recap: Modern Warehouse Modeling showed two country questions. Asha moved from IN to DE on 2026-01-08. A dashboard that joins the customer’s current row puts both of Asha’s paid orders under DE:

SELECT c.country, SUM(o.amount) AS revenue
FROM fct_orders AS o
JOIN dim_customer AS k ON k.customer_key = o.customer_key
JOIN dim_customer AS c
  ON c.customer_id = k.customer_id
 AND c.valid_to = '9999-12-31'
WHERE o.status = 'paid'
GROUP BY c.country
ORDER BY revenue DESC NULLS LAST;

Neither country view is wrong. They answer two questions: where was the customer, and where is the customer now.

Rule 1: every table has one grain, and its name says the grain. Put each money column only on the table where it belongs. In the model, order_items becomes fct_order_items, with the same rows.

Fan-out came from putting an order amount next to an item row. So do not let a tool join fct_orders to fct_order_items. Each table still answers its own questions:

SELECT product, SUM(quantity) AS units
FROM fct_order_items
GROUP BY product
ORDER BY units DESC, product;

These units include refunded order 103. With paid orders only, Lamp is 1 and the total is 9.

Names carry the grain to people who never read your notes. Use names like fct_orders, fct_order_items and daily_sales. Avoid names like sales or data.

Recap: Dimensional Modeling: Facts & Dimensions showed that averages break. Here is what a BI tool does with them.

A measure is a number you calculate with, such as amount. A BI tool adds measures up as you group them. That is only safe for some measures.

A sum of averages

Say a table stores the average paid order for each customer. A person charts that table. The tool adds its default SUM to the column:

SELECT customer_id, AVG(amount) AS avg_order
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
ORDER BY customer_id;

The right answer uses the parts. Add all paid amounts, then divide by all paid orders: 160.50 / 4 = 40.125.

A sum of distinct counts

A distinct count has the same problem. Count the paying customers in each country at order time:

SELECT country, COUNT(DISTINCT customer_id) AS customers
FROM paid_orders_with_country
GROUP BY country
ORDER BY country;

For an average, keep the parts (a sum and a count) and divide at the end. For a distinct count, count again from the order rows at the level you show. Define both once, in the model, so a tool cannot add them up.

Rule 3: write each shared metric one time, in a place that many dashboards read. A metric is a named calculation, such as paid revenue.

There are three places to put it. Pick the one that fits how many tools read it:

Start with a view that already joins the tables. It has one row for each order, and the money columns already carry the rule. Here the column names say what they hold:

CREATE VIEW mart_orders AS
SELECT
  o.order_id,
  o.order_date,
  c.country AS country_at_order,
  CASE WHEN o.status = 'paid'     THEN o.amount END AS paid_amount,
  CASE WHEN o.status = 'refunded' THEN o.amount END AS refunded_amount
FROM fct_orders AS o
JOIN dim_customer AS c ON c.customer_key = o.customer_key;

Now the default SUM is safe. Any field from this view keeps paid revenue at 160.50:

SELECT SUM(paid_amount) AS paid_revenue,
       SUM(refunded_amount) AS refunded
FROM mart_orders;

A semantic layer goes one step further. It stores the metric rule once, and many tools ask it for paid revenue. Modern Warehouse Modeling showed the idea, and tool support differs.

Two small details change totals without any error. The first is which date a chart uses. The second is a missing value.

One date table, many roles

Recap: Dimensional Modeling: Facts & Dimensions joined one dim_date twice. Each join had a role: order date or ship date. There, order 101 was ordered on 2026-01-03 and shipped on 2026-01-10.

Imagine the other paid orders get ship dates too. Order 106 ships on 2026-02-02. Group paid revenue by ship month:

SELECT d.month, SUM(o.amount) AS revenue
FROM paid_orders AS o
JOIN dim_date AS d ON d.full_date = o.ship_date
GROUP BY d.month
ORDER BY d.month;

Build one view on dim_date for each role, such as dim_order_date and dim_ship_date. The name says which date a person picks. Then no one joins the wrong one.

Unknown values

Dev has no country. In the view, country_at_order is NULL. A filter such as country <> 'IN' drops Dev’s order, because NULL <> 'IN' is not true:

SELECT SUM(paid_amount) AS revenue
FROM mart_orders
WHERE country_at_order <> 'IN';

The fix is to write a real value, such as Unknown, when the country is not known. Recap: Dimensional Modeling: Facts & Dimensions did this, and added an unknown row with key 0. This dim_customer still holds NULL, so the view must fix it.

-- In the view: coalesce(c.country, 'Unknown') AS country_at_order
SELECT SUM(paid_amount) AS revenue
FROM mart_orders
WHERE country_at_order <> 'IN';

From here on, mart_orders uses this line. coalesce(c.country, 'Unknown') gives the country, or Unknown when the country is NULL.

Row-level security (RLS) shows each person only some rows. Many warehouses and BI tools support it, but the setup differs. Here is the idea as a plain view.

Imagine a table that says which country each analyst may see. Meera may see IN. The view checks the login of the person who asks:

CREATE VIEW mart_orders_secure AS
SELECT m.*
FROM mart_orders AS m
WHERE m.country_at_order IN (
  SELECT a.country
  FROM analyst_access AS a
  WHERE a.analyst = CURRENT_USER
);

-- Meera runs:
SELECT country_at_order, SUM(paid_amount) AS paid_revenue
FROM mart_orders_secure
GROUP BY country_at_order;

This creates a new reason for two numbers to differ. Two people can both be right and see different totals. Label dashboards that use RLS.

One day you replace a dataset. Imagine forty dashboards read old_sales, which sums every status. You want them to use mart_orders, but you do not own those dashboards.

So retire in steps:

1. Find the real users. Use the warehouse query log and the BI tool’s usage report.
2. Tell each owner the new dataset, the date, and which numbers will change.
3. Run old and new side by side, and compare the totals.
4. Move the busiest dashboards first.
5. Mark the old dataset as deprecated (still there, but people should stop using it). Later, remove access to it. Drop it last.

Step 3 shows the numbers that change. Compare the old total with the new one:

SELECT 'old_sales'  AS dataset, SUM(amount)      AS revenue FROM old_sales
UNION ALL
SELECT 'mart_orders' AS dataset, SUM(paid_amount) AS revenue FROM mart_orders;

The cheapest fix is an agreement before the first dashboard. Write it in the model description, where BI users can read it.

The handoff is the moment BI users start to use your model.

A certified dataset is one that its owner has approved for shared use. Many BI tools show a "certified" or "endorsed" label for this. People can then pick the checked dataset instead of a copy.

Here is a description for mart_orders in dbt YAML. It states the grain, the money rule, and the owner:

models:
  - name: mart_orders
    description: >
      One row for each order. Country is the customer's
      country at order time. Unknown means not recorded.
    meta:
      owner: finance-data
      certified: true
    columns:
      - name: paid_amount
        description: Amount of orders with status paid. Finance revenue.
      - name: refunded_amount
        description: Amount of orders with status refunded.

The agreement should answer six questions:

1. What is the grain of each table?
2. What does each metric mean, and where is it defined?
3. Which date and which country version does a chart use?
4. What shows when a value is not known?
5. Who owns the dataset, and when does it refresh?
6. Who may see which rows?

Two numbers disagree. Go through these checks in order. Most of them match a cause from this chapter:

1. Write both definitions in words. Do they use the same measure?
2. Compare the date: order date or ship date (160.50 against 80.50)? Then compare the time window, time zone and refresh time.
3. Compare the status filter. Is a refund counted? (220.50 against 160.50)
4. Compare the grain. Did a join repeat orders? (280.50)
5. Compare the dimension version: country at order time or today? (IN 40.00 + DE 25.00 at order time, against DE 65.00 today)
6. Look for rows lost to NULL filters, or to inner joins that keep only matching rows. (Dev’s 80.00)
7. Ask who is viewing. Does row-level security change the rows?

To find a repeated row, group the joined rows by the key and count them. Any key with a count above 1 was repeated:

SELECT o.order_id, COUNT(*) AS copies
FROM fct_orders AS o
JOIN order_items AS i ON i.order_id = o.order_id
WHERE o.status = 'paid'
GROUP BY o.order_id
HAVING COUNT(*) > 1
ORDER BY o.order_id;

The same method works for every check. Split both totals by order, by status or by date. Then find the first row where they differ.

Six ideas to keep:

1. A BI tool writes SQL. It joins, sums and filters by default.
2. Totals differ for a reason: a missing filter, a repeated row, or a different country version.
3. Give every table one grain, and put each measure on its own grain.
4. Do not add averages or distinct counts. Keep the parts and divide last, or count again from the rows.
5. Define each metric once, and expose only safe columns.
6. Use Unknown values, certified datasets and an agreement written before the first dashboard.

Check yourself

• Why is paid revenue 280.50 after the item join, and what is the right number?
• Why does a SUM of the average paid orders give 128.00?
• Why does country <> 'IN' drop Dev’s paid order of 80.00?

Each exercise opens a canvas for an entity-relationship diagram (ERD). You draw the tables, and the review checks them against sample rows.

Start at the top. The last one is harder, so it is optional.