> ## Documentation Index
> Fetch the complete documentation index at: https://docs.sqlbuild.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Column Lineage

> Trace individual columns through your SQL pipeline - understand where data comes from and where it goes.

<Note>
  Using SQLBuild alongside a dbt project? See [dbt column lineage](/concepts/dbt-compatibility/column-lineage) for tracing columns across the combined dbt and SQLBuild graph with `sqb dbt lineage`.
</Note>

## 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:

```bash theme={null}
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:

```bash theme={null}
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:

```bash theme={null}
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):

```bash theme={null}
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:

```bash theme={null}
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](/cli/lineage) 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:

```bash theme={null}
# 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](/cli/compile) 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