Grain-Safe Drill-Across Queries Across Conformed Fact Tables

Hello! In the previous lesson, you loaded sales at one row per source sales line and resolved each line to customer and product surrogate keys. That protected the grain within the sales fact table. Now you need to protect it when a report combines sales with another process, such as returns.

This lesson uses PostgreSQL and the sales fact you already loaded. You will add a small returns fact, see why a direct join produces false totals, and write a drill-across query that compares the two processes without changing either fact table’s grain. Allow about 45 minutes, including the short video and reading.

Why shared dimensions do not justify a fact-to-fact join

Suppose two sales lines belong to the same customer and product in July, and two return lines belong to that same customer and product in July. Joining the fact tables on customer, product, and month produces four joined rows. Each sale appears twice, and each return appears twice. A perfectly valid SQL join has produced an invalid financial answer.

The issue is not that the dimensions failed to conform. Conformed dimensions give the two processes a common meaning for customer and product; they do not make individual sale lines correspond one-to-one with individual return lines. Sales and returns record different events, with different source identities and potentially different event dates.

Watch the two indicated portions of Guy in a Cube’s Handling MULTIPLE fact tables in Power BI. The demonstration is in a BI semantic model rather than PostgreSQL, but it makes the shared-dimension requirement visible.

The Kimball Group calls that SQL technique drilling across, or a multipass query. Read its concise definitions before writing the query.

Here is the practical rule: aggregate each fact independently to the same reporting grain, then join those aggregated answer sets. In this lesson the reporting grain is one calendar month, one customer dimension version, and one product. That is coarser than either fact’s event grain, and it is explicitly chosen for the comparison.

Establish a second fact with its own grain

The prior lesson left you with dw.fact_sales_line_demo, dw.dim_customer, and dw.dim_product_fact_demo. It published sales lines 201, 202, and 204–206; line 203 may also be present if you completed the late-customer retry. This lesson does not depend on that retry.

The returns fact below has one row per source return line. Its source_sale_line_id identifies the sale that prompted a return, but it is retained for traceability, not used as a join between facts in the comparison report. A single sale can give rise to more than one return event.

Run this setup once. The additional sale line 207 shares line 202’s customer and product dimension members. Both dates fall in July, after the customer version change described in the previous lesson. Three return lines refer to sale 202: two occurred in July and one in August.

CREATE TABLE dw.fact_return_line_drill_demo (
    source_system TEXT NOT NULL,
    source_return_id BIGINT NOT NULL,
    source_sale_line_id BIGINT NOT NULL,
    return_date DATE NOT NULL,
    customer_key BIGINT NOT NULL
        REFERENCES dw.dim_customer (customer_key),
    product_key BIGINT NOT NULL
        REFERENCES dw.dim_product_fact_demo (product_key),
    refund_amount NUMERIC(14, 2) NOT NULL
        CHECK (refund_amount >= 0),
    PRIMARY KEY (source_system, source_return_id)
);

INSERT INTO dw.fact_sales_line_demo (
    source_system, source_line_id, sale_date,
    customer_key, product_key, quantity, sales_amount
)
SELECT
    source_system, 207, DATE '2025-07-03',
    customer_key, product_key, 2, 20.00
FROM dw.fact_sales_line_demo
WHERE source_system = 'sales_app'
  AND source_line_id = 202
ON CONFLICT (source_system, source_line_id) DO NOTHING;

INSERT INTO dw.fact_return_line_drill_demo (
    source_system, source_return_id, source_sale_line_id,
    return_date, customer_key, product_key, refund_amount
)
SELECT
    s.source_system,
    v.source_return_id,
    s.source_line_id,
    v.return_date,
    s.customer_key,
    s.product_key,
    v.refund_amount
FROM dw.fact_sales_line_demo AS s
CROSS JOIN (
    VALUES
        (501::BIGINT, DATE '2025-07-03', 2.00::NUMERIC(14, 2)),
        (502::BIGINT, DATE '2025-07-04', 3.00::NUMERIC(14, 2)),
        (503::BIGINT, DATE '2025-08-02', 4.00::NUMERIC(14, 2))
) AS v(source_return_id, return_date, refund_amount)
WHERE s.source_system = 'sales_app'
  AND s.source_line_id = 202
ON CONFLICT (source_system, source_return_id) DO NOTHING;

For this example, a return keeps the customer dimension version of its original sale. Consequently, “customer” in the report means customer as classified when the sale occurred, while “month” means the month of the sale or of the return event, respectively. Those are deliberate reporting definitions. If you instead need the customer’s classification on the return date, the returns load must resolve that version separately; sharing a column named customer_key would not by itself settle which interpretation is intended.

We also treat sales_amount and refund_amount as amounts in the same reporting currency, with refunds stored as positive amounts to subtract. Without a common currency definition, even a cardinality-safe query could produce a meaningless net amount.

Observe the multiplication before fixing it

This query is intentionally wrong. It limits the example to the two July sales lines and two July returns so that the join’s cardinality is easy to inspect. Do not use its totals in a report.

SELECT
    COUNT(*) AS joined_pairs,
    SUM(s.sales_amount) AS incorrect_sales,
    SUM(r.refund_amount) AS incorrect_refunds
FROM dw.fact_sales_line_demo AS s
JOIN dw.fact_return_line_drill_demo AS r
  ON r.customer_key = s.customer_key
 AND r.product_key = s.product_key
 AND date_trunc('month', r.return_date)
     = date_trunc('month', s.sale_date)
WHERE s.source_system = 'sales_app'
  AND s.source_line_id IN (202, 207)
  AND r.source_system = 'sales_app'
  AND r.source_return_id IN (501, 502);

The two sales are 12.00 and 20.00; the two refunds are 2.00 and 3.00. The query returns four pairs, incorrect sales of 64.00 and incorrect refunds of 10.00. The true totals for these events are 32.00 and 5.00.

Changing SUM to SUM(DISTINCT s.sales_amount) is not a repair: two different sales can legitimately have the same amount. Joining a return to its original sale ID is useful for lineage or a specifically defined per-sale analysis, but it does not solve the general problem of comparing two independently measured processes by month and customer. Here, the August return for sale 202 belongs in August returns, not in July returns merely because its originating sale occurred in July.

Write the grain-safe drill-across query

The following query gives each fact its own pass. Each common table expression groups by an identical set of row headers: month_start, customer_key, and product_key. Because both facts reference the same conformed customer and product dimension rows, those keys have comparable meanings on both sides.

Only after the two passes have produced at most one row per reporting group do we join them. A FULL OUTER JOIN retains a month/customer/product group found in sales only or returns only. The August return demonstrates why that matters.

WITH sales_monthly AS (
    SELECT
        date_trunc('month', s.sale_date)::DATE AS month_start,
        s.customer_key,
        s.product_key,
        SUM(s.sales_amount) AS sales_amount
    FROM dw.fact_sales_line_demo AS s
    GROUP BY 1, 2, 3
),
returns_monthly AS (
    SELECT
        date_trunc('month', r.return_date)::DATE AS month_start,
        r.customer_key,
        r.product_key,
        SUM(r.refund_amount) AS refund_amount
    FROM dw.fact_return_line_drill_demo AS r
    GROUP BY 1, 2, 3
),
aligned AS (
    SELECT
        COALESCE(s.month_start, r.month_start) AS month_start,
        COALESCE(s.customer_key, r.customer_key) AS customer_key,
        COALESCE(s.product_key, r.product_key) AS product_key,
        COALESCE(s.sales_amount, 0) AS sales_amount,
        COALESCE(r.refund_amount, 0) AS refund_amount
    FROM sales_monthly AS s
    FULL OUTER JOIN returns_monthly AS r
      ON r.month_start = s.month_start
     AND r.customer_key = s.customer_key
     AND r.product_key = s.product_key
)
SELECT
    a.month_start,
    dc.source_customer_id,
    dc.region_name,
    dp.source_product_id,
    dp.product_name,
    a.sales_amount,
    a.refund_amount,
    a.sales_amount - a.refund_amount AS net_sales_amount
FROM aligned AS a
JOIN dw.dim_customer AS dc
  ON dc.customer_key = a.customer_key
JOIN dw.dim_product_fact_demo AS dp
  ON dp.product_key = a.product_key
ORDER BY a.month_start, a.customer_key, a.product_key;

Find the rows for source customer 42 and product P10:

Other sales-only groups will also appear. Their absence of returns is represented as a zero measure, not as an invented return fact row. In August, the negative net amount is appropriate under the chosen definition: this is sales recorded in August less returns recorded in August. It is not the eventual net value of July’s sales cohort.

Notice the placement of COALESCE. Each fact’s month and surrogate keys are non-null in this example, so after the full join one side supplies every reporting key. We replace a missing side’s aggregate measure with zero. We do not turn an unresolved customer into an arbitrary customer by coalescing its source identifier.

The final joins to dim_customer and dim_product_fact_demo add readable labels after alignment. They are safe here because each surrogate key identifies one dimension row. Customer 42 may have multiple Type 2 rows, but a particular customer_key still identifies exactly one version. If you need a report grouped by region rather than customer version, change both independent passes to group by the same governed region attribute before aligning them. Do not first join raw fact rows and hope a later GROUP BY region_name removes their multiplication.

For an automated check, each pass should be unique on its reporting headers. The query below should return no rows for the sales pass; the equivalent check applies to the returns pass.

WITH sales_monthly AS (
    SELECT
        date_trunc('month', sale_date)::DATE AS month_start,
        customer_key,
        product_key,
        SUM(sales_amount) AS sales_amount
    FROM dw.fact_sales_line_demo
    GROUP BY 1, 2, 3
)
SELECT month_start, customer_key, product_key, COUNT(*)
FROM sales_monthly
GROUP BY 1, 2, 3
HAVING COUNT(*) > 1;

That uniqueness is the immediate protection against a many-to-many join of the answer sets. For a production report, also validate each pass’s total against its source fact over the same event-date range and currency, and confirm that shared dimension attributes have the same business definitions. Matching SQL column names alone is not conformance.

Key takeaways

Two fact tables can share customer and product dimensions while still representing different events at different grains. A direct fact-to-fact join on those dimensions can multiply measures. A grain-safe drill-across query independently aggregates each fact to identical conformed reporting headers, then aligns the resulting one-row-per-group answer sets.

The report must also state what its dates, historical customer version, currency, and unmatched groups mean. Correct cardinality protects the arithmetic; those shared definitions protect the interpretation.

Next, you will use EXPLAIN ANALYZE to examine a slow star-schema query and decide whether indexing, partitioning, or pre-aggregation is an appropriate optimization—without sacrificing the grain-safe result established here.