The silver layer is not a serving layer

A report that times out usually isn't a visuals problem - it's a grain problem.

The slow semantic model I spent this week on traced to a clean root cause: measures reading directly from a silver-layer table holding millions of interaction-level rows, over DirectQuery. Every visual on the page was recomputing aggregates over the full grain, on every filter click. And my first attempts went where everyone's do - into the model. I flipped relationship directions so the client filter could reach the fact table; every measure broke. I made a relationship bidirectional; the report broke differently. No DAX or join cleverness survives the underlying shape: the questions on screen were about a few dozen key clients, and every answer was being recomputed from millions of rows at render time.

Medallion architecture already has the answer; it just has to be respected at the boundary. In the canonical formulation, silver is the validated, conformed enterprise view - the layer you build from - and gold is the layer of "highly refined views" aggregated and shaped for a specific consumer, which is where dashboards are supposed to read. Silver holds cleansed detail. What reports touch should be gold - pre-filtered to the relevant population and pre-aggregated to the grain the business actually asks about. Kimball made the same argument decades before the metals: aggregate fact tables are "simple numeric rollups of atomic fact table data built solely to accelerate query performance," meant to behave like indexes - invisible to the consumer, decisive for the workload.

The irony in this model: the right table already existed. A gold table held exactly the client-by-period grain the pages were asking about - interaction counts, prior-period comparison, status, all baked in upstream. The slow pages were the ones whose measures had bypassed it to aggregate silver directly, because silver was where the detail columns lived. If a question is about dozens of entities, the table behind the visual should have row counts shaped like dozens-of-entities, not millions-of-events.

The test I'll reuse: for each measure, ask how many rows it scans to answer the question on screen. When the ratio of rows-scanned to facts-displayed runs into the tens of thousands, the work belongs upstream - in the lakehouse, materialized once - not in the model at render time, recomputed per click.