Review this transformation project. 300 models are all views with no materialisations and chains up to 11 deep. Every model has not-null and unique tests on every column - about 2400 tests. CI runs a full build plus the whole test suite on every pull request - 70 minutes. Each developer has a personal schema. What would you remove, what would you change and what would you leave alone?

What is actually required

Three things: dashboards answer in seconds, a change is safe to merge, and a defect is caught before a consumer sees it. Everything in the estate should be justified by one of those, and most of it is not.

What I would remove, and why it is safe to

About 2,000 of the 2,400 tests. A not-null test on a column that the transformation itself constructs - a CASE that always returns a value, a surrogate key built from a hash - tests the database engine, not the pipeline. It can never fail, it takes build time, and its presence creates the belief that the estate is tested.

Keep tests where a source can genuinely violate them: primary keys on ingested tables, enumerations the source controls, referential checks across systems. Then add the three that actually catch incidents: a row-count change threshold per model (an upstream rename that drops a join from 1.2 million rows to 4,000 passes every not-null test), source freshness, and one aggregate reconciled against an independent system.

The one change that matters

Materialise at the fan-out points. A view chain re-executes the entire stack on every query, so an 11-deep chain is paid per dashboard refresh rather than once per build. Five to eight models with more than one downstream consumer carry most of the repeated work; making those tables usually takes dashboard p95 from seconds to sub-second and cuts warehouse compute noticeably. Leave the leaves as views, where a view is free.

Second: CI should build the modified subgraph and its downstream only, selected by comparing against the production manifest. A 70-minute gate on every pull request is not a quality control - it is a thing people learn to work around, by batching unrelated changes into one review.

What I would leave, even though it looks odd

• Personal schemas. They look like sprawl and they are the reason nobody develops against production tables. The cost is storage; the alternative is an incident.
• Views as the default materialisation. The instinct to make everything a table is the opposite error: it turns a 90-second build into an hour and buys speed for models nobody queries.
• The 11-deep chain itself, if each layer is a named concept. Depth is not the problem; recomputation through depth is, and materialising the fan-out points fixes that without a refactor.

How I would argue this in the review

With two numbers: dashboard p95 before and after materialising the fan-out models, and the count of production incidents the 2,400 tests caught last quarter - which is usually zero, and which ends the argument that deleting tests is reckless. Pair the deletion with the row-count and freshness tests so the net change is more detection, not less.

When this is the wrong critique

When the models are small and queried rarely - an internal estate of 300 models serving twelve weekly reports. Then views everywhere is correct, the build is a minute, and materialisation adds storage, staleness and a scheduling dependency for no gain. The critique is driven by query volume and chain depth, not by taste.