Column Lineage

Why column lineage matters

Impact analysis - Before changing a source column, see exactly which downstream models and columns are affected. A rename or type change in raw__orders.id can be traced through every model that consumes it, even indirectly.

Debugging data issues - When a column has unexpected values, trace it upstream to find where the data originates and what transformations it passes through. Instead of reading SQL files and mentally joining dependencies, ask SQLBuild to show the path.

Documentation - Column lineage provides machine-readable metadata about your pipeline. The JSON output can feed data catalogs, governance tools, or custom dashboards.

How it works

SQLBuild analyzes column lineage statically at compile time. No warehouse connection is needed. The analyzer parses each model’s SQL, resolves ref() and source() calls, and traces columns through SELECT lists, CTEs, JOINs, subqueries, and expressions.

Column lineage requires SQL analysis to be enabled in project settings (it is by default).

Transform types

Each lineage edge is classified by how the column is transformed:

Transform	Description	Example
direct	Column passes through unchanged	SELECT order_id FROM ...
cast	Column is explicitly cast to a different type	SELECT CAST(id AS BIGINT)
expression	Column is used in a computed expression	SELECT amount * 100 AS amount_cents
aggregation	Column is used inside an aggregate function	SELECT SUM(amount) AS total
star	Column is included via SELECT *	SELECT * FROM ...
constant	Output column is a literal value with no upstream dependency	SELECT 'active' AS status

Transform classification helps you understand the nature of each dependency. A direct edge means the column is a simple passthrough - safe to rename if you rename the source. An expression or aggregation edge means the column is computed - the upstream value is an input to a calculation, not a 1:1 mapping.

Confidence levels

Each edge also carries a confidence level indicating how certain the analyzer is about the traced dependency:

Confidence	Meaning
high	The lineage path is fully resolved through known SQL constructs
medium	The path is likely correct but involves constructs the analyzer handles with heuristics
low	The path is best-effort - complex SQL patterns or unsupported constructs may reduce accuracy

Analysis modes

Column lineage supports two analysis modes that trade off speed against depth of analysis.

Rich mode uses the SQL analysis optimizer to resolve columns through CTEs, subqueries, and multi-level nesting with full transform classification. Thorough, but slower because the optimizer runs per column per model.

Fast mode parses the SQL AST directly to extract column mappings, resolve CTE references, and classify transforms. It handles the same SQL patterns that most column lineage tools support and is fast enough to run on every compile.

sqb compile defaults to fast mode because it runs frequently and analyzes the entire project. sqb lineage defaults to rich mode because it targets a specific column in a scoped slice of the DAG, where the deeper analysis is worth the cost. Both are overridable:

sqb compile --lineage-mode rich
sqb lineage fact_orders.payment_amount_cents --mode fast

Using column lineage

Interactive tracing with sqb lineage

The target syntax is model_name.column_name. Trace a column upstream to see where its values come from:

sqb lineage daily_revenue.total_revenue_cents

Column trace  daily_revenue.total_revenue_cents  upstream

└── stg_payments.amount_cents (aggregation)
    └── raw__payments.amount_cents (direct)

Each hop is annotated with how the value was derived. Here total_revenue_cents is an aggregation of stg_payments.amount_cents, which is a direct passthrough from the source.

Use --direction downstream to trace the other way - every column derived from this one:

sqb lineage stg_payments.amount_cents --direction downstream

Column trace  stg_payments.amount_cents  downstream

├── daily_revenue.avg_order_value_cents (aggregation)
├── daily_revenue.total_revenue_cents (aggregation)
├── daily_revenue.total_revenue_dollars (aggregation)
├── dim_customers.lifetime_spend_cents (aggregation)
└── fact_orders.payment_amount_cents (direct)

Column lineage supports upstream (default) and downstream directions (not both - model lineage supports both).

Model lineage

sqb lineage also traces model-level dependencies when the target has no column (no dot):

sqb lineage fact_orders

Lineage  model  fact_orders  models/marts/fact_orders.sql  upstream

├── model  stg_orders  models/staging/stg_orders.sql
│   └── source  raw__orders  sources/raw.yml
├── model  stg_payments  models/staging/stg_payments.sql
│   └── source  raw__payments  sources/raw.yml
├── seed  waffle_types  seeds/waffle_types.csv
└── udf  udf__is_completed_order

Each node is tagged with its resource type (model, source, seed, udf) and file path. Use --direction both to show upstream and downstream together:

sqb lineage daily_revenue --direction both

Lineage  model  daily_revenue  models/marts/daily_revenue.sql  both

upstream
├── model  stg_orders  models/staging/stg_orders.sql
│   └── source  raw__orders  sources/raw.yml
└── model  stg_payments  models/staging/stg_payments.sql
    └── source  raw__payments  sources/raw.yml
downstream

Options

Flag	Description
--direction	upstream (default), downstream, or both. both is model lineage only.
--depth	How many hops to traverse: an integer or all (default all).
--format	tree (default), list (an edge list of a -> b pairs), or json.
--mode	Column lineage mode: rich (default) or fast.

See the lineage CLI reference for full flag documentation and output format examples.

Batch analysis with sqb compile

Every compile run computes column lineage for analysis and reports a summary:

# Default: fast column lineage
sqb compile

# Skip column lineage
sqb compile --lineage-mode none

# JSON report includes per-model lineage summary
sqb compile --json

In the JSON compile report, each model includes a lineage field with column_count, edge_count, and has_star metadata. It does not contain the full edge graph; use sqb lineage <model>[.<column>] --format json when you need structured lineage details.

See the compile CLI reference for details on the compile report format.

Integration with contract validation

Column lineage feeds into compile-time contract validation. When a model declares columns in its MODEL() header, the compiler uses inferred column information to check that:

• Every declared column exists in the query output
• Column types match the declared types (when type_enforcement is enabled)

These checks run automatically during sqb compile and report diagnostics with source-annotated error messages.

Limitations

• Column lineage requires SQL analysis to be enabled (sql_analysis = true in settings, which is the default)
• Complex SQL patterns (deeply nested correlated subqueries, dynamic SQL, adapter-specific functions) may reduce accuracy or confidence
• SELECT * is tracked as a star transform - the analyzer knows the column passes through but the mapping is less precise than explicit column references
• Column lineage is computed statically from SQL text. Runtime-only column additions (e.g. from dynamic UDFs) are not tracked