A transformation project has grown to 600 models with chains twelve deep. A change at the base has an unknowable blast radius. What do you do?

Treat it as a software architecture problem, because it is one

Six hundred models with twelve-deep chains is a codebase with no module boundaries. The remedies are the ordinary ones and they work here for the ordinary reasons.

Impose layers with a naming convention and enforce it

Three or four layers, named in the model name, with a rule about which may reference which:

• Staging — one model per source table, light typing and renaming only. May reference sources.
• Intermediate — joins and business logic. May reference staging and intermediate.
• Marts — consumer-facing. May reference intermediate and marts.

Then enforce the rule automatically, so a mart referencing a source fails the build. Without enforcement the convention decays within two quarters.

Cap the depth

A chain twelve deep usually contains models created to avoid touching an existing one. Flattening the worst chains is unglamorous and it is where the maintainability comes back.

Test at the boundaries

Tests on every model is expensive and produces noise. Tests at layer boundaries — uniqueness, referential integrity, accepted values, row-count expectations — catch the regressions that matter and keep the suite fast enough to run on every change.

Find and delete what is unused

Cross-reference the models against actual query logs on their outputs. In a graph of this size a substantial fraction has no consumer: built for a report that was replaced, or an analysis that concluded. Deleting them shrinks the graph faster than any refactoring.

Make blast radius visible in review

The framework knows the dependency graph, so a pull request can report which downstream models and which consuming dashboards a change affects. That single addition changes review behaviour more than any documentation, because the reviewer can see that a two-line change touches forty models and one board report.

What not to do

Do not rewrite it. A staged programme of layering, capping depth and deleting unused models is achievable alongside delivery; a rewrite of 600 models is a year during which nothing else improves.