BigQuery Data Lineage: Column-Level Lineage for Google BigQuery SQL

BigQuery data lineage is the map of how data moves through your BigQuery project: which source tables and columns feed each derived table, view, and scheduled query, and which expressions transform the data along the way. Google’s Dataplex captures lineage automatically for BigQuery jobs at the table level; to see which columns feed which, and through which functions and joins, you have to parse the SQL itself. Gudu SQLFlow does exactly that, with a dedicated BigQuery dialect parser that resolves nested STRUCT fields, UNNEST, star expansion, and view chains down to column granularity.

Why BigQuery lineage is harder than it looks

BigQuery’s SQL is not generic ANSI SQL. Three features in particular break lineage tools that rely on a one-size-fits-all grammar:

• Nested and repeated columns. A single BigQuery column can be a STRUCT containing other fields, or an ARRAY of structs. Real lineage has to reach inside: orders.customer.email is a different lineage node than orders.customer.country, even though both live in one physical column.
• UNNEST. Flattening a repeated field turns one row into many and introduces a derived relation mid-query. A parser that doesn’t model UNNEST loses the thread between the flattened alias and the array field it came from.
• Star expansion. SELECT * through a chain of views means the output columns aren’t written anywhere in the query text. Resolving them requires the table and view definitions, not just the one statement in front of you.

SQLFlow ships a BigQuery-specific parser (one of 39 dialect-specific parsers in the product, not a single generic grammar) that handles all three. It resolves every column reference through CTEs, subqueries, views, and star expansion, and it models nested STRUCT fields as first-class lineage nodes.

Dataplex gives you table-level job lineage. Then what?

Dataplex (Google Cloud’s data governance layer) is genuinely good at what it does: it observes BigQuery jobs as they run and records which tables each job read and wrote. If you want to know that a scheduled query populates dw.daily_revenue from raw.orders, Dataplex already tells you.

What runtime job observation cannot tell you is the transform logic between those tables: which source columns feed which output columns, through which expressions, filters, and joins. That information exists only in the SQL text, so extracting it requires SQL parsing. The two approaches answer different questions:

Question	Dataplex job lineage	SQLFlow parsed lineage
Which tables does this job touch?	Yes, automatically, per job run	Yes, from the SQL
Which columns feed daily_revenue.total?	No — table granularity	Yes, per output column
What expression computes each output column?	No	Yes: functions, casts, aggregates
Which columns only filter or join (indirect lineage)?	No	Yes, as a separate toggleable layer
Lineage for SQL that hasn’t run yet (code review, migration)	No — needs an executed job	Yes — static analysis of the SQL text

Teams typically use both: Dataplex for the always-on job inventory, and SQL parsing when they need to answer “what breaks if I change this column” or “where exactly does this number come from” at column precision.

Worked example: CREATE TABLE AS SELECT with UNNEST

Here is a typical BigQuery pattern: building a flat revenue table from an orders table with a repeated line_items struct.

CREATE TABLE dw.order_line_revenue AS
SELECT
  o.order_id,
  o.customer.email          AS customer_email,
  li.product_id,
  li.qty * li.unit_price    AS line_revenue
FROM raw.orders AS o,
     UNNEST(o.line_items) AS li
WHERE o.status = 'COMPLETE';

Feed this to SQLFlow and the column-level lineage it produces is:

• order_line_revenue.customer_email comes from the nested field raw.orders.customer.email — a struct member, not the whole customer column.
• order_line_revenue.line_revenue comes from raw.orders.line_items.qty and raw.orders.line_items.unit_price, through the UNNEST alias li and a multiplication. Both source fields live inside a repeated struct; SQLFlow traces through the flattening.
• raw.orders.status appears as indirect lineage: it never lands in the output, but the WHERE filter means it shapes every row of the result. SQLFlow models direct dataflow and indirect influence (WHERE, JOIN, GROUP BY columns) as distinct relationship types you can toggle separately in the diagram — a distinction most lineage tools don’t make at all.

Indirect lineage matters more than it first appears. If someone changes the enum values in status, a purely direct-lineage tool says line_revenue is unaffected. It isn’t: every downstream revenue number changes. Impact analysis without indirect lineage is impact analysis with blind spots.

Tracing scheduled queries and view chains

In most BigQuery estates, the interesting lineage isn’t one statement. It’s a chain: raw tables, then a layer of views, then a scheduled query that materializes a reporting table, then more views on top. Any single link is easy to read; the end-to-end path from a source column to a dashboard field is not.

SQLFlow analyzes the whole set together. Give it the view DDL plus the scheduled-query SQL (paste, file upload, live metadata over JDBC, a dbt manifest, or Grabit metadata extraction) and it stitches the chain: view-on-view references resolve, SELECT * expands against the real schemas at each layer, and the resulting diagram lets you click any output column and walk upstream through every intermediate view to the source columns in the raw tables. Export is JSON, CSV, or PNG, or programmatic via the REST API — and enterprise deployments push lineage into DataHub, Microsoft Purview, or OpenMetadata.

ER diagrams from NOT ENFORCED constraints

BigQuery supports primary and foreign keys only as NOT ENFORCED constraints: declared in DDL for the optimizer and for documentation, never checked at write time. Because they’re unenforced, many teams assume they’re useless metadata. They’re not — they are exactly the relationship declarations an ER diagram needs.

SQLFlow’s ER inference understands BigQuery’s NOT ENFORCED PK/FK idiom: run your DDL through it and it draws the entity-relationship diagram from constraints like ADD PRIMARY KEY (order_id) NOT ENFORCED and the matching FOREIGN KEY ... REFERENCES ... NOT ENFORCED declarations. The result is an ER model of your warehouse generated straight from the DDL you already have, for schemas whose relationship documentation probably doesn’t exist anywhere else.

What teams use BigQuery data lineage for

• Impact analysis before schema changes: find every scheduled query, view, and report a column actually feeds — including through UNNEST and struct access — before you rename or retype it.
• Debugging wrong numbers: walk backward from one dashboard metric through the view chain to the exact source fields and expressions that produced it.
• Migration planning: moving into or out of BigQuery, the dependency graph tells you what order to move things in and what you can safely leave behind. SQLFlow builds the same column-level graph for Snowflake and Amazon Redshift, so cross-warehouse migrations can be mapped on both sides with one tool.
• Compliance and audit: prove which source fields flow into a regulated output at the column granularity auditors ask for, generated from the SQL rather than maintained by hand.

Privacy, deployment, and pricing

SQLFlow performs static analysis of SQL code and schema metadata only. It never reads the rows in your BigQuery tables, and it doesn’t need a service account with data access — SQL text and DDL are enough. SQLFlow Cloud has a free tier (premium is $49.99/month). For regulated environments, SQLFlow On-Premise runs on Docker or Kubernetes inside your own network, air-gapped if required, at $500/month or $4,800 one-time per selected database type; full details are on the pricing page. Enterprise deployments batch-scan estates of 100+ databases and over a million columns with incremental scans and a persistent lineage repository.

Frequently asked questions

Doesn’t Dataplex already give me BigQuery lineage?

Yes, at the table level. Dataplex automatically records which tables each BigQuery job reads and writes, which is a solid job inventory. It does not extract the transform logic inside the SQL — which columns feed which, through which expressions. For that, you need a tool that parses the SQL, which is what SQLFlow does.

Can SQLFlow trace lineage through STRUCT and ARRAY columns?

Yes. The BigQuery parser models nested struct fields as individual lineage nodes, so customer.email and customer.country have separate lineage, and it traces fields through UNNEST flattening of repeated columns.

How does SQLFlow handle SELECT * in BigQuery views?

Star expressions are expanded against the actual table and view definitions, so every implicit column gets explicit lineage. This works through multi-layer view chains, where the columns behind a SELECT * may be defined several views upstream.

Can it map lineage across scheduled queries and views together?

Yes. Analyze the scheduled-query SQL and view DDL as one job and SQLFlow stitches the end-to-end chain, so you can trace a reporting column back through the materializing query and every intermediate view to the raw source columns.

Does SQLFlow need access to my BigQuery data?

No. It is static analysis: SQL text plus optional schema metadata. Table row data is never read. With On-Premise, even the SQL text stays inside your network.

What does SQLFlow cost?

SQLFlow Cloud starts free; premium accounts are $49.99/month. SQLFlow On-Premise is $500/month or $4,800 one-time per selected database type, installable on two servers.