Why is my slowly changing dimension causing duplicates with fact table grain?

[Frank.L]

Ouch — the fact table grain is definitely biting you here. This is a very common issue when a deal moves through multiple stages within the same month and the fact ends up aligning with more than one row in a slowly changing dimension. Once that happens, joins multiply rows and your pipeline metrics get duplicated.

[Andrew_W]

From a modeling perspective, everything hinges on making the fact table grain explicit and stable. If you can flatten the data to one row per deal per stage per month, you eliminate double counting at the source. Window functions should only be applied on top of a clean fact table, not used to compensate for ambiguous grain definitions.

[Michael.M]

I’d challenge the framing slightly: the grain might be a symptom rather than the root cause. If the dashboard is designed to show every stage transition, you will see churn and row proliferation even with a well-modeled fact. In that case, the real question is whether the dashboard needs full stage history or just a summarized outcome.

[Avery_M]

Be careful with simple dedup rules in the transformation layer. They can hide the problem without actually fixing it, leading to hollow or misleading counts. Any deduplication strategy has to align with the business meaning of the grain, otherwise the numbers will look right but tell the wrong story.

[Anthony.G]

Challenging the framing a bit the grain you describe might be a symptom not the cause if the dashboard is asking for every stage to be visible you will see churn even with a clean model maybe rethink the goal rather than chasing a single fix

[RyanL]

Another way to look at it is to separate outcome from journey. The grain becomes a storytelling choice: do you want to analyze motion through stages, or just where deals end up? In many cases, defining separate metrics per stage satisfies reporting needs without forcing the entire journey into a single fact table.

[JonathanJ]

Another take is to frame the problem around the outcome not the path the grain becomes a lens you use to tell a story about motion through stages but maybe a separate metric per stage would satisfy the dashboard without collapsing the journey

[BrianL]

If you keep the current grain, consider modeling stage changes as a separate history dimension or event table rather than letting the SCD inflate the fact. This allows the dashboard to switch between a “current state” view and a “historical progression” view without creating duplicate facts.

[DennisM]

A practical approach is to define a clean core fact at a single, stable grain, then build a per-stage, per-month rollup on top of it. Compare the two datasets side by side to see exactly where duplication appears. Once the grain is clear, the joins will naturally fall into place.