Customer dimension, about 40 million rows, sourced from three systems with imperfect keys. A full rebuild takes 25 minutes and is dead simple. A merge takes 3 minutes but I am nervous about drift and about the key quality. We are a team of two with no dedicated data engineer. Which do I regret less in a year?
Do both for a fortnight. Build the merged version into a shadow table, compare row counts and a few aggregates against the rebuild every day, and you find out whether your keys hold up without betting anything on it.
This is how you earn the right to switch later without a leap of faith.
Whichever you choose, snapshot the dimension daily if history matters at all. Rebuilds quietly destroy your ability to answer what something looked like in March.
Twenty five minutes is not slow. It only feels slow because you watched it.
@dbt_and_dust · 11mo ago · 2 replies
Full rebuild, at your size and your team size.
Twenty five minutes for a dimension that is correct by construction is a very good trade when there are two of you. Merge logic drifts silently, and the failure mode is not a red pipeline, it is a slightly wrong customer count nobody catches for a quarter. Rebuilds have no drift because there is no state to drift.
The threshold to switch is when the rebuild starts affecting an SLA, or when it gets expensive enough that somebody asks about it. At 40 million rows in 25 minutes you are nowhere near either.
Also, you have told us the keys are imperfect across three systems. The merge is asking you to be certain about the one thing you have said you are unsure about.
Reply
Report
@sheetsmith · 11mo ago
That last line is really the whole answer.
Reply
Report