sql-transform

How it is built

Architecture overview

A SQL transformation layer turns a folder of SELECT statements into a set of tables in a warehouse. Each file defines one model: a query whose result is materialised as a table or view. Models refer to each other by name, so the set forms a dependency graph; the tool parses the references, orders the models so that every input is built before its users, runs them in that order (in parallel where the graph allows) and tests the results. For big tables it does not rebuild everything: an incremental model processes only rows that are new or changed since the last run and merges them into the existing table. The one idea that makes it work is that the engine is the warehouse; the transformation layer is a compiler and scheduler that produces SQL, orders it from a graph and keeps state about what has been built. dbt is the open reference; SQLMesh follows the same idea with instant impact analysis of changes and virtual data environments.

Parts:

• Parser and resolver — reads the model files, resolves every table and column reference — Recursive-descent parsing, Name resolution
• Dependency graph and scheduler — builds the graph from references, orders it, runs it with bounded parallelism — Topological sort
• Lineage analyzer — derives which source columns feed each output column, for impact analysis and for safe changes — column-level-lineage (no page yet), built on BFS and DFS, relational-algebra (no page yet)
• Incremental materializer — updates big tables from the new rows only — incremental-view-maintenance (no page yet), built on Change data capture and Incremental sync cursors
• Merge engine — applies new rows to the target by key, deduplicating and handling late data — upsert-merge (no page yet)
• History tracker — keeps past versions of changing dimension rows with validity ranges — slowly-changing-dimensions (no page yet)

 models/*.sql ──► parser + name resolution ──► model graph (nodes = models, edges = refs)
                                                  │
                         column lineage ◄─────────┤ topological order + parallel levels
                                                  ▼
                                    scheduler: for each ready model
                                                  │ compile to SQL (full / incremental / snapshot)
                                                  ▼
                  warehouse: CREATE TABLE AS | MERGE new rows | close old versions, insert new
                                                  │ run tests (unique, not_null, ...)
                                                  ▼
                                    state: run results, high-water marks, manifest

The product is a manifest (the parsed graph with lineage and metadata) and a warehouse state that matches it. Both are reproducible from the code and the sources.

How the parts fit together

Parser and resolver

The tool must understand enough SQL to find what each model reads. Two levels exist. A light approach finds only explicit references: dbt asks authors to write {{ ref('orders') }} instead of a table name, and the template expansion records the dependency and substitutes the right schema per environment. A full approach parses the SQL itself, which is what column-level lineage and static checks need. A hand-written Recursive-descent parser is the usual way to cover several dialects, since each dialect adds syntax a grammar generator handles awkwardly; sqlglot is an open-source parser and transpiler for many dialects.

Parsing alone leaves select a from x join y ambiguous. Name resolution binds every column to the relation it comes from, handling aliases, CTE names that shadow tables, subqueries and * expansion against known schemas. Contract with the rest: the output is a tree in which every table and column reference is resolved to a model, a source or a known external object, or is flagged unresolved.

Dependency graph and scheduler

Each ref is an edge from the referenced model to its user. Topological sort orders a DAG so that every edge points forward, and it detects cycles as a by-product, which is the error "model A depends on itself through B". The scheduler uses the order, but not as a flat list: it keeps a count of unfinished inputs for each model, runs the models with count zero in parallel up to a thread limit, and decrements counts as models finish. A failure marks all descendants as skipped while independent branches continue.

Node selection (build one model and everything downstream; build everything upstream of a model) is a graph traversal from the chosen node. dbt documents ref as the function that builds the dependency graph and deploys models in the right order, and its node-selection syntax as the way to pick sub-graphs.

Lineage analyzer

Model-level edges say "orders depends on payments". Column-level lineage (no page yet) says which columns of payments feed orders.revenue. The analyzer walks the resolved tree with relational-algebra (no page yet) rules (projection maps output columns to expressions, join and filter add condition edges, aggregation merges inputs) and records edges between (model, column) nodes. Uses:

• Impact analysis: before renaming or dropping a column, a BFS downstream lists exactly which models and columns break.
• Pruning: a changed column only requires rebuilding the models whose output columns depend on it.
• Documentation and governance: the same edges feed a data catalog (see Data catalog with lineage). Dynamic SQL and macros that generate SQL at run time cannot be analysed before expansion; analyse the compiled SQL, not the template.

Incremental materializer

For a large fact table, a full rebuild every run is slow and costly. An incremental model keeps the table between runs and processes only new data: the model's SQL is compiled with a filter such as "rows whose timestamp is later than the maximum already in the target". This is the batch form of incremental-view-maintenance (no page yet): apply deltas to the stored result instead of recomputing it.

Where does the delta come from? Three options, from simplest to most exact:

• A watermark column in the source (event time or load time), kept as the target's high-water mark, which behaves like an Incremental sync cursor: a token that says "everything up to here is done". It is simple and wrong for late-arriving rows older than the mark, so a lookback window or periodic full refresh is added.
• Partitions: recompute whole recent partitions. dbt's insert_overwrite strategy works on partitions, not rows.
• A change feed from the source database (Change data capture): inserts, updates and deletes in commit order. It is the only option that sees deletes and updates exactly. Aggregations are the hard case: a changed row changes an aggregate, which must be adjusted by the delta (add, subtract) or recomputed for the affected groups only.

Merge engine

The delta has to be applied to the target. upsert-merge (no page yet) means: for each incoming row, find the target row with the same key; replace it if it exists, insert it if not. dbt exposes this as incremental strategies: merge and delete+insert may use a unique_key, while insert_overwrite replaces partitions and uses none (source: dbt docs, incremental models). Choice criteria: append is the cheapest and only right for immutable events; merge is a single atomic statement where the warehouse supports it; delete+insert is the fallback; partition overwrite is best when data arrives by day and can be fully recomputed.

Contract: the key must be unique in the incoming batch as well, or the statement fails or picks arbitrary rows; deduplicate on the key and the latest timestamp first. Re-running the same batch must give the same table (idempotence), which is what makes retries safe.

History tracker

Some tables describe things that change slowly (a customer's plan, an employee's team) and facts must join to the version that was valid at the time. slowly-changing-dimensions (no page yet) keeps each version as a row with a validity range. In the type 2 pattern a change closes the old row (sets its end) and inserts a new one. dbt snapshots do this with columns dbt_valid_from and dbt_valid_to, where the current row has a null end. A fact join then uses "valid_from <= event time < valid_to", the same as-of logic used for point-in-time joins elsewhere. The snapshot must run often enough to see each intermediate change: a change made and undone between runs is invisible, since the layer sees states, not events (unless a change feed supplies them).

Data and control flow

One run of the whole project:

1. Parse all model files, resolve references, build the graph and manifest. A cycle or an unresolved reference stops here.
2. Choose the sub-graph (everything, or a selection) and sort it.
3. Dispatch ready models to worker threads. Each compiles its SQL for its materialization (view, table, incremental, snapshot) and sends it to the warehouse over one connection.
4. After a model builds, its tests run (unique, not_null, accepted_values, relationships are the generic ones in dbt). A failed test can block descendants.
5. Write run results and state (for example the manifest) so the next run can compare. Almost all time is warehouse time; the tool's own cost is parsing and planning, which stays small relative to the queries.

One incremental model, one run:

1. Read the target's high-water mark (the maximum timestamp, or the stored cursor).
2. Compile the model's SQL with the filter for new rows, plus a lookback window to catch late rows.
3. Deduplicate by key, keeping the latest version of each key.
4. Merge into the target (or append, or overwrite partitions).
5. Store the new mark only after the merge commits, so a crash re-processes the same batch rather than skipping it.

Changing a column safely:

1. Edit the model; the analyzer diffs the old and new compiled SQL.
2. Downstream columns of the changed one are found by traversal; only those models are rebuilt, in a development schema first.
3. Tests and row-count comparisons against production decide whether to promote.

Scaling

In the order they usually bite:

1. Warehouse cost and time of full rebuilds. Levers: incremental models for the few largest tables, partition pruning in filters, views for cheap logic, clustering keys.
2. Graph width and depth. Thousands of models mean long critical paths. Levers: more threads (up to warehouse concurrency), splitting a hub model, rebuilding only changed models and their descendants.
3. Late and out-of-order data in incremental models. Levers: lookback windows, microbatches by time slice, a periodic full refresh.
4. Merge cost. Merging into a huge table scans it unless the key is clustered or partitioned. Levers: partition-aligned merges, delete+insert by partition, smaller target partitions.
5. Parse and compile time for very large projects. Levers: re-parsing only changed files, cached schemas.
6. Team scale. Many authors on one graph. Levers: ownership boundaries and contracts on public models, separate projects with explicit interfaces.

Failure modes

Real implementations

The alternative: orchestrate hand-written SQL scripts with a workflow scheduler, declaring dependencies by hand. It works for a handful of tables and drifts as soon as the SQL and the declared graph disagree, because nothing reads the SQL. Deriving the graph from the code (through ref or by parsing) is what keeps the order, the lineage and the documentation true. For very low latency, replace batch incrementals with a streaming engine that maintains the view continuously.

Sources

• dbt documentation: incremental models, snapshots, the ref function, data tests, node selection, materializations.
• SQLMesh documentation. docs
• sqlglot. repo