Your transformation project has 2,400 models. A full build takes six hours and one change rebuilds half the graph. How did this happen and what do you do?
Show the full answer Hide the answer
What the interviewer is testing
Whether you can diagnose an architectural problem in a data codebase rather than optimising the scheduler.
How it happened
No layering discipline. Models reference each other freely, so the dependency graph is deep and wide rather than staged. A change near the base rebuilds everything above it.
No ownership boundaries. Every team adds models wherever convenient, referencing others' models directly, so there is no interface anywhere — every model is effectively public API.
Nothing is ever removed. Models built for a report that was decommissioned two years ago still build nightly.
What to do
Measure usage first. Query logs will show which final models are actually consulted. In estates this size, a large fraction of leaf models have not been queried in months, and deleting them removes their entire upstream branch. This is the cheapest and largest win.
Impose layers with enforced boundaries: staging (one model per source, light cleaning), intermediate (business logic), and marts (consumption). Rules — staging models may only reference sources, marts may not reference other marts, cross-domain references only through a published interface model — enforced as an automated check, not a convention.
Establish ownership per domain, with published models as the contract and everything else private.
Incremental materialisation for the large models, so a rebuild processes new data rather than the full history.
Selective builds — build only what a change affects, which the dependency graph already knows and which should already be how CI works.
What a strong answer adds
Recognising this as the same problem as a distributed monolith: no boundaries, so every change touches everything. The fix is architectural — interfaces and ownership — and scheduling improvements only buy time.
And a standing decommissioning process, since without one the estate returns to this state.
Common weak answers
More compute. Splitting the schedule into more frequent partial runs, which hides the coupling.