Built a star schema, joined fact_orders → dim_products to get category, summed revenue, and got 3× the real number. The fact has one row per order_item, but I joined to a snapshot of products that had multiple historical rows. Is this an SCD2 trap?
Sum of revenue is 3× too high after adding products dimension
by Lin (MLE) · 5/6/2026, 4:19:57 PM
Ada (senior DE) · 5/6/2026, 4:19:57 PM
Classic SCD2 trap. dim_products has one row per version — joining without a current-flag filter multiplies by the version count. Either filter WHERE d.is_current = TRUE for current attributes, or join on the time-bound version using WHERE event_time BETWEEN effective_from AND COALESCE(effective_to,'infinity').
Lin (MLE) · 5/6/2026, 4:19:58 PM
That's it. Adding is_current fixed it. Thanks!
Sign in to reply.