Essay 04
Beyond SQL comprehension
In the last piece I argued that a semantic layer is one point on a spectrum of business modelling - very good at making calculations reusable, less complete as a home for everything those calculations depend on. Much of the understanding underneath still gets authored again: in mart SQL, in a wide table for BI, in semantic YAML, in documentation, in someone's head.
That scattering is not only a coherence problem in the abstract. It shows up as recurring pain in day-to-day dbt development - and in what the next generation of tooling is trying to fix.
Pains core dbt development has lived with
These are familiar if you have shipped dbt models in anger:
1. Compile catches less than you want. dbt compile renders Jinja into SQL text. Newer tooling is getting better at the static class of errors before the warehouse runs - typos, broken refs, syntax, some type mismatches. Grain fan-out, joins at scale, and "is this the right business answer?" still tend to show up on run, or in CI after you queue a rebuild.
2. Impact is a graph of files, not of meaning. "What breaks if I change this?" defaults to dbt run --select model+ or manifest archaeology. The unit of blast radius is a model, because that is the smallest grain dbt gives you - you cannot rebuild or validate half a model. If the business change is "this metric now excludes tax" or "this population rule changed," you still have to work out which SQL files re-implement that idea, then run those models whole to find out.
3. The same idea lives in many places. A common pattern: define total_revenue on fact_sales, again on fact_sales_wide, again in SQL rolling up to customer_total_revenue, again in agg_monthly_sales. The wide table and the monthly aggregate are sensible shapes. What grates is authoring the same measure four times - four places it can drift when someone asks whether refunds are included.
4. CI pays per copy. Slim CI and selective builds help, but the default validation loop still tends toward: rebuild a subgraph, execute SQL, hope tests catch semantic drift. One small change can require re-building some expensive models, even if you have built very careful materialisation discipline by hand.
5. The semantic layer sits beside the pipeline. Transformations produce tables; metrics and entities are configured on top. Two workflows, two places meaning can diverge, one PR that might only touch the mart and leave the semantic definition stale.
I am not listing these to dismiss dbt. I love dbt. It succeeded for good reasons: it put transformation in the hands of analysts and analytics engineers who already lived in SQL, and it made dependency explicit - ref() turned a folder of scripts into a DAG you could reason about, test, and review in git. That was genuinely empowering, and bringing git and CI to analytics engineering was a big step forward.
But I now see that these pains are the cost of a design choice: the canonical description of analytics is a DAG of SQL models.
What newer tooling is attacking
The industry has noticed - though not always with the same answer. dbt Fusion (from dbt's SDF acquisition) is the loudest move on SQL comprehension. SQLMesh (from Tobiko, via Fivetran's acquisition) attacks many of the same pains from a different engine - plan/apply, environments, state-aware skip - built on SQLGlot's parsing rather than Fusion's.
| Pain | What newer tooling does |
|---|---|
| Compile green, run red | Fusion: static SQL analysis, type checking, errors before the warehouse |
| "Where does this column go?" | Fusion: column-level lineage (free dbt platform login required; Advanced CI and other platform features sit behind a paid tier) |
| Rebuild too much | SQLMesh: plan/apply, state-aware skip; dbt: slimmer CI, --select patterns |
| Slow feedback in the IDE | Fusion LSP: hover, refs, inline errors while editing SQL |
| Fuzzy impact | Fusion: impact analysis over the SQL graph |
I think that is directionally right. Teams should catch more before execute and run less dumb SQL.
SQLMesh's comparison with dbt states the starting point bluntly: without semantic SQL understanding, dbt treats queries as raw strings and simple errors wait for a warehouse run. Fusion is largely dbt's answer to that charge. The industry argument has converged on comprehension - mostly on whose engine parses SQL better, not on whether SQL models should remain canonical.
dbt's semantic graph and entities push in a similar direction - governed metrics and dimensions, not only faster SQL. I welcome that. To me it still looks like a semantic layer bolted onto model-backed tables: the dbt model remains the anchor, not a business concept you compile projections from. SQLMesh's runtime differs, but both keep the SQL model as the primary artefact.
Notice what is constant across the fixes: they comprehend the SQL graph better - faster lint, finer lineage, smarter skip, tighter rebuild scope. The program being read is still the program you wrote: models, CTEs, Jinja, YAML beside SQL.
That is why I find the progress both encouraging and slightly frustrating. We are investing heavily in reading a program that may be larger and more redundant than it needs to be.
Where the pains come from
I do not think these pains are random bad habits, or problems we can only solve by piling on more tooling. They are accidental complexity from treating SQL and tables as primary.
Once the fact model is the source of truth for "what an order is," every other shape has to either duplicate or recover that truth:
- The wide table re-selects the same fields in new SQL.
- Schema YAML re-states columns for tests and docs.
- The semantic layer re-declares measures on whichever model it anchors to.
- Impact tools infer dependencies by parsing those hosts.
- CI re-executes them to prove a change safe.
None of that is malice or oversight in the implementation details. It is what happens when business meaning has no single place to live, so it gets copied into every layer that needs it. Fusion makes each copy easier to trace and validate. It does not remove the need for copies.
That is the crux of it for me:
Fusion reads the program better.
The program is still SQL scattered across models.
Fighting the foundation
There is a software-engineering pattern here that analytics has lived with for a long time: retrofit comprehension onto a system that was not designed to expose it.
We did something similar with semantic layers on top of marts, catalogs on top of warehouses, and slim CI on top of full-graph rebuilds. Each layer helps. Each layer also accepts the layer below as canonical and tries to recover what that layer never stored explicitly.
The harder the tooling works, the more I wonder whether we are optimising the wrong layer of the stack.
Analytics engineering borrowed version control, pull requests, and CI from software. As I wrote about earlier, we borrowed less of the design discipline underneath: separation of concerns, clear boundaries, one place for each kind of meaning. Instead we got an unopinionated DAG where a model can be staging, entity, intermediate, mart, and reporting output - and a growing industry of parsers to keep that DAG honest.
Fusion and SQLMesh are serious attempts to make that DAG easier to manage. I am glad they exist. I am not sure they are the ceiling.
The question I'm asking
So I keep returning to a question that sounds simple enough:
What if we modelled the business - grain, relationships, metrics, population rules - and compiled SQL and physical tables from that?
This is not "no SQL." SQL is not going anywhere - it remains what the warehouse runs and what experts inspect, diff, and debug. The inversion is authorship: meaning first, physical shapes second. Star schemas and wide tables become outputs of the model, not sibling definitions that each re-implement the same measure.
Analytics engineers have adapted before. Many of us learned Jinja when dbt arrived, LookML or MetricFlow when semantic layers mattered, DAX or another dialect when the job required it. AI is making that learning faster, not replacing the need for judgement. I am not asking anyone to abandon a skill they have spent years building. I am asking whether the primary artefact we author should stay a DAG of SQL files - or whether SQL becomes the compiled output of something richer, the way we already treat the warehouse tables dbt produces.
We could design for the pains from day one, instead of layering comprehension on top of duplication.
If we approach the problem with business-first modelling, these are not four separate problems waiting for four features:
- Impact is a graph of files, not of meaning
- The same idea lives in many places
- CI pays per copy
- The semantic layer sits beside the pipeline
They are one coordination problem - meaning copied into every layer.
Compile catches less than you want is a different class of fix; Fusion is genuinely useful there. In principle, a business-first approach would not bolt on faster CI or cleaner metrics - it would avoid much of the coordination work in the first place. That trades one kind of complexity for another: modelling discipline, compiler trust, and the cost of migrating what you already have.
If the premise is wrong, better SQL parsing is still the right bet. I may be wrong. But the question feels worth asking before we treat table-first SQL as the permanent centre of gravity for analytics engineering.
AI sharpens this rather than solving it. Duplication and bloat have never been easier to manage - an agent can draft the same fix across every host in a single PR - but the risk of letting definitions drift has never been higher either: asked to reconcile four divergent copies of total_revenue, an agent will just pick one. And when someone actually needs to understand the code, that bloat still slows them down, agent-authored or not.
Looking forward
The pains of core dbt development are well understood. The industry response - comprehend SQL harder, skip smarter, lint earlier - is a rational response within table-first analytics.
My suspicion is that many of those pains will keep resurfacing in new forms until we stop treating SQL models as the primary home of business meaning. Model the business, compile the SQL, and perhaps we stop paying tax on every copy we never meant to author.