Concept Library

Data Transformation: dbt, SQLMesh, Coalesce

The transformation layer turns raw warehouse data into modeled, tested, documented tables that the rest of the business consumes. dbt defined the category; SQLMesh and Coalesce are credible challengers.

Data Engineering
Core
10 min
Last reviewed 2026-Q2

What it is

Transformation is the layer that turns raw warehouse tables into the tables business users and applications actually read. It is mostly SQL, occasionally Python, and the discipline around it — modular models, tests, version control, documentation, lineage — is what separates a data team from a folder of stored procedures.

dbt invented the category as it exists today. Models are SQL files, the framework compiles them into the right execution order, runs them in your warehouse, and ships with testing, documentation, and lineage. The model has been imitated, extended, and challenged.

Why it matters

The transformation layer is where most data quality issues are caught or created. It is also where logic gets duplicated across dashboards, where definitions of “active customer” diverge, and where a renamed column breaks production three weeks later.

A good transformation framework gives you four things: a single source of truth for business logic, automated testing, lineage that explains where every column came from, and a deployment process that lets you ship changes safely. Without these, you end up with the modern equivalent of a 4,000-line stored procedure that nobody dares to touch.

How it works

The pattern is consistent across tools. You write SELECT statements that reference other models. The framework parses the SQL, builds a dependency graph, and figures out the right order to execute. It compiles your models into the dialect of your warehouse and runs them. It then runs tests against the output — uniqueness, not-null, referential integrity, custom assertions — and fails the build if any test fails.

Materializations control whether each model is rebuilt as a view, a table, or incrementally (only new rows). Snapshots capture slowly-changing dimension history. Macros let you write reusable SQL fragments. Documentation lives next to the code and ships as a navigable site.

The differences between tools are mostly in the execution model and what they do beyond SQL compilation.

Vendor comparison

Tool	Best for	Strength	Weakness
dbt Core (open source)	Most teams, default choice	Massive community, every warehouse supported, deep ecosystem (packages, integrations)	Imperative `dbt run` model — you tell it what to build; no native virtual data environments
dbt Cloud	Teams that want the managed IDE, scheduler, semantic layer	Managed runtime, semantic layer, IDE for analysts	Pricing climbs steeply, semantic layer adoption mixed
SQLMesh	Teams hitting dbt’s limits at scale	Virtual data environments (zero-copy), column-level lineage, native Python models, strong typing	Younger, smaller community, fewer integrations
Coalesce	Enterprise teams with mixed SQL/non-SQL contributors	Visual + code hybrid, Snowflake-native, governance-first	Snowflake-centric, less open than dbt

dbt Core is the default. If you cannot articulate a specific reason to choose otherwise, choose this. The ecosystem alone is a structural advantage — dbt-utils, dbt-expectations, and the long tail of community packages save real time.

dbt Cloud is worth it when you want the managed IDE for analysts and a managed scheduler. The semantic layer is a separate decision (see Semantic Layer entry) — strong on paper, deployment varies in practice.

SQLMesh is the most interesting challenger. Its virtual data environments solve a real dbt pain — staging changes without copying terabytes — and column-level lineage is genuinely better. I have moved teams from dbt to SQLMesh when scale or environment management became blocking. For most teams, dbt is still the right answer.

Coalesce is the right answer if you are Snowflake-only, enterprise, with a mix of SQL-fluent and SQL-shy contributors who need a visual layer. It is not a fit if you want open ecosystem or multi-warehouse portability.

Yoann’s take

dbt is the right default in 2026. Most arguments against dbt that I hear in interviews — “it does not scale,” “the SQL is repetitive,” “the macros are ugly” — are arguments against the way a specific team has used dbt, not against the framework. I reach for SQLMesh when I am running a platform that genuinely needs virtual environments and column-level lineage as first-class primitives, typically at 500+ models with a multi-team contributor base. I reach for Coalesce in regulated Snowflake-only environments where governance and a visual layer are non-negotiable. The thing that matters more than the tool is the discipline: a tested, documented, modular project on dbt is worth more than a beautiful one in any other tool that nobody trusts.

— Yoann

External references