Forty-five T-SQL developers, three hundred report users, one gold layer
Litware has 45 T-SQL developers who insist on stored procedures, multi-table transactions and MERGE for the gold layer. Three hundred report authors across 20 dashboards query gold tables through a Direct Lake semantic model. The gold layer is a Warehouse with six stored procedures on a 15-minute schedule; every procedure writes to one shared staging table inside a transaction before merging into gold.
Under load, dashboards run at 25 seconds p90, the stored procedures deadlock on the shared staging table and retry, and last week a developer dropped a column the model depended on. Four dashboards broke and stayed broken until the next morning. The platform lead proposes moving everything to a Lakehouse with Spark notebooks.
1. Evaluate the proposal against the four real requirements: T-SQL DML, multi-table transactions, read-serving performance, and schema-change safety.
Read the worked answer
| Requirement | Lakehouse | Warehouse |
|---|---|---|
| T-SQL DML with stored procedures | Fails. The SQL analytics endpoint is read-only; procedures must become notebooks. | Native. |
| Multi-table transactions | Fails. Delta gives ACID per table, not across tables. Six tables updated by one procedure lose atomicity. | Native. |
| Read-serving performance | Equivalent - Direct Lake reads Delta either way. | Equivalent. |
| Schema-change safety | No better. Both are exposed to a dropped column. | No better. |
The proposal solves none of the actual problems. The 25-second p90 is not caused by the Warehouse, and a Lakehouse would not make dropping a column safe. What the move does do is force 45 T-SQL developers to rewrite procedures and to invent a compensating pattern for cross-table atomicity. That is a large, risky project in exchange for nothing.
The correct reading: keep the Warehouse as the write and transform engine, and treat the three symptoms as three separate design defects.
2. Redesign the gold load so the deadlocks disappear while keeping exactly one copy of gold.
Read the worked answer
The deadlock is structural, not a tuning problem. Six procedures each start a transaction and write to the same staging table, so they queue on the same object and then collide on lock ordering. Snapshot isolation alone will not fix write-write contention on a shared object.
Redesign:
- One staging object per procedure. Either a dedicated staging table per source, or a session-scoped temp table that no other procedure can see. This removes the shared hot spot entirely, which is the actual fix.
- Merge is idempotent. Each procedure MERGEs into gold on the natural key with a version tie-break (last-modified timestamp plus an ingest sequence). Re-running a procedure is then safe, which matters when a retry follows a failure.
- Orchestration instead of racing. Drive the six procedures from a single pipeline so that dependencies are explicit. Where two procedures genuinely touch the same target table, sequence them rather than letting the scheduler decide.
- Read path untouched. Keep the Direct Lake model over the gold tables. A single gold copy still serves 300 report authors and 45 developers.
What not to do: raise the retry count or widen the deadlock priority. That hides a serialisation defect and still leaves you with a shared object that every writer must acquire.
3. What control would have caught the dropped column before four dashboards broke?
Read the worked answer
Nothing in the current setup sits between a developer's DDL and a reader's model, so the break is detected by users. Three layers, cheapest first:
- Contract check as a pipeline step. Immediately after the gold load, compare the deployed column list of every table the semantic model depends on against INFORMATION_SCHEMA and fail the pipeline on any missing column. This turns a morning-long outage into a failed run at 00:30 with an owner attached. Store the expected schema as a versioned artefact, not as code someone must remember to edit.
- Stop SELECT stars in the model path. Expose gold to the model through explicit views or projections. A dropped upstream column then breaks a view at build time rather than silently reshaping a model.
- Permission and promotion boundary. DDL on the production Warehouse should belong to the platform team, not to all 45 developers - and changes should travel through a deployment pipeline from a dev workspace so that a schema change is reviewed against the model before promotion, not discovered by a dashboard.
Layer 1 catches it; layers 2 and 3 stop the class of incident. Note that none of the three require moving off the Warehouse, which is the answer to the original proposal.