Why adding layers makes reports faster

A report that takes forty seconds to open is almost never an engine problem. It is a problem of when the work gets done.

If the report reads straight from the source tables, everyone who opens it pays for the same work again: join five tables, drop the reprocessed rows, sort out that one system sends amounts in cents and another in units, apply the business rules, and only then group. None of that changes between one run and the next. It gets paid in full every time, and at the worst possible moment: with someone watching the screen.

A medallion architecture — bronze, silver and gold, or whatever you call the layers — fixes that by running the work once, in batch, and writing the result down. The report stops computing and starts reading.

The cost nobody mentions first

Better to say it before the benefits: medallion adds latency. A row that lands at 9:05 is not in the report at 9:05. It shows up when the layer that processes it runs: fifteen minutes later, an hour later, or the next morning.

That is the whole trade-off. You are buying query speed and consistency with freshness, and there is no way around it.

The question is not which of the two is better. It is which one hurts less. For most management reporting, someone reading numbers from an hour ago changes no decision. A report that takes forty seconds and every so often disagrees with yesterday’s number does.

What makes a report slow when it reads from the source

Look at the query. This is usually what sits behind a dashboard that “feels slow”:

-- The report, reading straight from the source.
-- All of this runs again on every open.
select
    c.segment,
    date_trunc('month', o.order_date) as month,
    sum(
        case
            when o.currency = 'USD' then o.amount
            else o.amount * fx.rate
        end
    ) as total
from orders o
-- the source keeps reprocessed rows: keep the latest version
join (
    select order_id, max(updated_at) as latest
    from orders
    group by order_id
) last
  on last.order_id = o.order_id
 and last.latest = o.updated_at
join crm_customers c on c.customer_id = o.customer_id
-- reconcile two systems
left join erp_customers e on e.tax_id = c.tax_id
join fx_rates fx on fx.rate_date = o.order_date
                and fx.currency = o.currency
where o.status not in ('draft', 'void')
group by 1, 2;

Nothing here is badly written. The problem is where it lives: every time the report is opened it deduplicates by max(updated_at) again, crosses CRM with ERP by tax id again, looks up the exchange rate again.

There are two costs, and the second is worse than the first.

The compute cost. The dedup subquery scans all of orders before it can be joined. An engine may well handle that fast on small volumes, but it grows with the table, not with what the report shows.

The consistency cost. That left join between CRM and ERP on tax id is a business rule: what happens when the id is mistyped, when two customers share one, when a record exists in one system and not the other. The rule is written inside the report. The next report that needs the same thing will write it again, and not necessarily the same way. That is where two dashboards start showing different numbers and nobody knows which one is right.

The same work, moved somewhere else

The cleanup layer handles what belongs to each source: types, duplicates, names. One row per order, no reprocessed leftovers:

-- cleanup layer: one row per order, types settled
create table clean.orders as
select distinct on (order_id)
    order_id,
    customer_id,
    order_date::date      as order_date,
    amount::numeric(18,2) as amount,
    upper(currency)       as currency,
    status
from source.orders
where status not in ('draft', 'void')
order by order_id, updated_at desc;

The model layer handles what crosses sources: reconciliation between systems and the business rules, written once and only once:

-- model layer: CRM/ERP reconciliation lives here and nowhere else
create table model.enriched_orders as
select
    o.order_id,
    o.order_date,
    coalesce(c.segment, 'no_segment') as segment,
    -- conversion already settled: the report does not decide again
    case
        when o.currency = 'USD' then o.amount
        else o.amount * fx.rate
    end as amount_usd
from clean.orders o
left join clean.customers c on c.customer_id = o.customer_id
left join clean.fx_rates fx
       on fx.rate_date = o.order_date
      and fx.currency = o.currency;

And the report ends up like this:

select segment,
       date_trunc('month', order_date) as month,
       sum(amount_usd)                 as total
from model.enriched_orders
group by 1, 2;

A group by over one table. No subqueries, no joins, no decisions.

What you actually gained

Total compute did not drop. The work still exists, it just runs once per batch instead of once per open. If the report is opened thirty times a day, the arithmetic works out on its own. If it is opened once a month, it does not.

The time saved is the least interesting part, though. What really changes is that reconciliation at run time disappears.

When every report crosses CRM with ERP on its own, each one does it a little differently: one uses left join, another inner, one normalizes the tax id, another leaves it as it comes. None is wrong on its own, and the four of them produce different numbers. Then come the meetings to decide which number is the good one.

With the rule in a single table, the argument moves from “which report is right” to “is the rule right”. That second argument can be settled: fix it in one place and every report changes together. The first one never quite ends.

When it is not worth it

This is not free, and it does not always pay off:

• When the data has to be current. An operations dashboard watching what is happening right now cannot read a table from an hour ago. Go to the source, or to a stream.
• When the report is barely opened. Layers cost something to keep: running them, watching them, fixing them when they break. For something consulted once a quarter, that cost never comes back.
• When there is one source and one query. With nothing crossing systems there is no reconciliation to centralize, and most of the benefit goes away.
• When nobody will maintain the layers. A stale intermediate layer is worse than not having one: it gives fast answers that are wrong.

What is left to look at

Two things this design leaves open, worth deciding on purpose.

The first is how often each layer runs. They do not have to share a schedule: cleanup can run frequently and the model a few times a day. The more often, the less latency and the more cost.

The second is what happens when late data arrives. If yesterday’s order lands today, someone has to decide whether the affected period is reprocessed or whether yesterday’s number is closed for good. That is a business decision before it is a technical one, and it is better taken deliberately than discovered the day the numbers stop matching.

Neither has a universal answer, but both get answered once and written down. Which is, in the end, what all of this is about.