Column-level lineage: what it actually shows, and where it stops

Table-level lineage draws the paths between datasets. It is enough to answer scoping questions: which pipelines touch this warehouse, which marts sit downstream of ingestion, where does this domain start and end. What it cannot tell you is anything about the content of the table, because it never looked inside.

Column-level lineage records the derivation of each field: which upstream columns feed it, through which operation, in which model. That turns a vague blast radius into a short list. Renaming customer_email in a staging model is a five-table problem at table grain and a two-column problem at column grain, and only one of those two numbers is worth waking anyone up for.

The practical test: if the answer you need is a list of teams to notify, table level is usually enough. If the answer you need is whether a specific change is safe to merge tonight, you need the column.

An arrow between two columns is not enough on its own. The useful edge carries three facts: the source column, the target column, and the kind of operation between them. A direct copy, an aggregation, a join key, a filter predicate, and a union member all fail differently under a schema change, and a graph that renders them identically hides the part a reviewer needs.

A one-line description of the transformation is the fourth fact, and the one people actually read. "sum over the last 28 days, excluding refunds" is worth more in a review meeting than the SQL it came from, because the reviewer can spot a wrong assumption in a sentence and cannot spot it in a 200-line model.

On Datadef, table nodes carry their named, typed columns, and canvas_add_lineage records the link between two of them with a joinType and that one-line description. The joinType vocabulary is deliberately small: direct, transform, aggregation, lookup, filter, union, or a named join kind. Omit the column names and the same call records a table-level edge, which is how one canvas ends up carrying both grains.

The tool takes between 1 and 100 links in a call, and it resolves each column name against the columns already declared on the node rather than creating one. A name that does not match is refused with the list of the ones that do: Column "net_amount" not found on "stg_orders" (has: order_id, gross_amount, refund_amount). That is slower than accepting whatever it is handed, and it is why the recorded lineage matches the model rather than what someone remembers of it.

{
  "tool": "canvas_add_lineage",
  "links": [
    {
      "sourceNode": "stg_orders",
      "sourceColumn": "net_amount",
      "targetNode": "fct_revenue_daily",
      "targetColumn": "revenue",
      "joinType": "aggregation",
      "description": "sum of net_amount per day, refunds excluded"
    },
    {
      "sourceNode": "raw_events",
      "targetNode": "stg_orders",
      "joinType": "direct",
      "description": "table-level: no column detail needed on this hop"
    }
  ]
}

Automated column-level lineage is SQL parsing, so it inherits every limit of SQL parsing. SELECT star resolves only if the parser knows the schema at that moment. Dynamic SQL built as a string is opaque. Stored procedures, user-defined functions, and anything running inside a Spark job in a language other than SQL usually collapse to a table-level edge or vanish. Semi-structured extraction from JSON columns is often reported at the column that holds the blob, not the field inside it.

The second gap is the BI layer. A large share of real business logic lives in dashboard-level calculations, spreadsheet formulas, and reverse ETL mappings that no warehouse parser sees. A lineage graph that stops at the mart is accurate and still misleading, because the number the executive argues about was computed after the last node.

Neither gap is a reason to skip column-level lineage. It is a reason to know which parts of your graph are parsed, which are declared by a human, and which are simply missing, and to mark them differently rather than pretending the coverage is uniform.

stg_orders.customer_email is becoming stg_orders.customer_email_address. At table grain, five models select from stg_orders, so the review opens with five owners and five test suites. That is the number most teams act on, and most of it is noise.

At column grain the list collapses. fct_orders reads order_id and net_amount. fct_order_lines reads order_line_id. dim_date is joined to the table and reads nothing from it. Two models actually touch the field: dim_customers, which passes it through as the contact attribute, and int_marketing_consent, which lowercases it and joins it to the consent table. Those two are the review.

The column view also decides the rollout order, which the table view cannot. int_marketing_consent joins on a derived value, so it is safe as long as both sides move in the same commit. dim_customers copies the field into a published dimension that a CRM sync reads, which is a consumer outside the warehouse and outside the test suite. One of those two facts is a merge order and the other is a message sent before the merge, and neither was visible at table grain.

• Data lineage on one canvas
• What is data lineage
• Data lineage vs impact analysis
• What to capture in data lineage
• Draw a lineage map on the free canvas