Loading...
Star and snowflake schemas, slowly changing dimensions, and dbt-style transformation layering.
4 questions in Data & AI Engineering.
Settle the grain first - what one fact row means. A star still earns its place for conformed dimensions and type-2 history, while a wide table is a serving artefact for one known pattern. A warehouse differs from a lake by having a modelled contract, not by where the bytes live.
The dimension was overwritten, so historical facts joined to it now report the customer's current region rather than the region they were in when they bought. Keeping history means versioning the dimension row and joining facts to the version that was current at the time, not to the customer.
Reconcile the two numbers row by row before touching the pipelines: the cause is almost always a grain mismatch that fans out a join, a filter difference over refunds or test accounts, or two timestamps meaning different things. The fix is one certified definition that both dashboards are forced to call.
Two time axes, not one: when the fact was true in the world, and when this warehouse first learned it. Corrections append a new version with a knowledge timestamp instead of updating in place, so a report becomes a query with an as-of predicate and reproduces for ever - and the figure you published should be snapshotted alongside it rather than reconstructed later.