SQL and Data Blog

Why DataHub Loses Column-Level Lineage on dbt Deduplication Macros — and How to Recover It

James — Fri, 01 May 2026 06:24:30 +0000

If your dbt + BigQuery stack lands models in DataHub, you may have hit a quiet failure mode on a subset of your tables: the table-level lineage edge appears, but the column-level lineage is empty. The graph looks complete — until you click into a column and find no upstream.

This is a known issue (datahub-project/datahub#11670, open since October 2024). The root cause is specific: the BigQuery SQL that the dbt-utils deduplicate macro emits uses two BigQuery-specific semantics — row-as-STRUCT and STRUCT.* field expansion — that DataHub’s bundled SQL parser (sqlglot) does not currently model in its column-level lineage walker.

This post explains exactly what’s going on, with an honest accounting of what’s recoverable from the SQL alone versus what needs schema metadata, and shows how to recover the missing edge today using an open-source post-processor — without modifying your DataHub installation.

What dbt-utils is actually doing on BigQuery

The dbt-utils deduplicate macro is dialect-dispatched. Most warehouses get a qualify/row_number() translation, but BigQuery gets a different implementation on purpose — array_agg ... limit 1 is significantly cheaper at scale than row_number() over (...) in BigQuery’s execution engine. The compiled SQL looks like this:

CREATE VIEW analytics.deduplicated_articles AS
SELECT unique.*
FROM (
    SELECT
        ARRAY_AGG(
            original
            ORDER BY article_name DESC
            LIMIT 1
        )[OFFSET(0)] AS unique
    FROM all_articles AS original
    GROUP BY id
);

The variable names are deceptive in a way that matters for parsers, so it’s worth being precise about what each one is:

original is the table alias for all_articles. Used as a value (inside ARRAY_AGG(original)), it’s the whole row of all_articles viewed as a STRUCT — not a column.
unique is a STRUCT-valued column alias in the inner query. ARRAY_AGG(original ...)[OFFSET(0)] returns one STRUCT per group (the deduplicated winner row), and AS unique names that STRUCT.
SELECT unique.* is STRUCT field expansion (per BigQuery’s SELECT expression.* rules), not a star against a table. It explodes the STRUCT’s fields into top-level output columns.

Net effect: analytics.deduplicated_articles has the same top-level column shape as all_articles, with one row per id chosen by article_name DESC.

What lineage is recoverable from the SQL alone

Here’s an honest expected-vs-actual table for what a metadata-free parser can produce on this SQL:

Lineage you’d like	Recoverable without schema?
Table edge: `all_articles → analytics.deduplicated_articles`	Yes — sqlglot already gets this
Whole-row flow: `all_articles.* → analytics.deduplicated_articles.*`	Yes — but only by a parser that models row-as-STRUCT and `STRUCT.*` expansion
Per-column edges: `all_articles.`
No — needs schema for `all_articles` to enumerate the column names
Row-selection dependency on `article_name` and `id`	Yes — the `GROUP BY` and `ORDER BY` keys can be emitted as separate edges into the output (as control inputs, not value lineage)

For the deeper walkthrough on what BigQuery semantics a metadata-free parser needs to implement to recover the row-flow shape (SELECT AS STRUCT, ARRAY(SELECT AS STRUCT ...), row-as-STRUCT in array_agg, [OFFSET(n)], STRUCT.* field expansion), see the companion post: BigQuery Column-Level Lineage Without Metadata.

Where DataHub’s column walker stops short

DataHub’s dbt source runs the model SQL through sqlglot. sqlglot extracts the table-level edge here (all_articles → analytics.deduplicated_articles) and resolves the inner unique alias correctly. The gap is in the outer query: the projection is literally unique.*, and sqlglot’s column-lineage walker does not currently expand a STRUCT-valued column alias into its fields. Without that expansion, there are no concrete target columns to attach column-level lineage to.

You can see the symptom in three lines:

>>> import sqlglot
>>> from sqlglot.lineage import lineage
>>> outer = """SELECT unique.* FROM (
...   SELECT ARRAY_AGG(original ORDER BY article_name DESC LIMIT 1)[OFFSET(0)] AS unique
...   FROM all_articles AS original GROUP BY id)"""
>>> # Tables: works fine
>>> [str(t) for t in sqlglot.parse_one(outer, dialect='bigquery').find_all(sqlglot.exp.Table)]
['all_articles AS original']
>>> # Lineage walker on the outer query
>>> lineage('unique', outer, dialect='bigquery')
SqlglotError: Cannot find column 'unique' in query.

The error message is a side effect, not the root cause. unique isn’t a final output column of the outer query — only unique.* (a star expansion) is. The walker needs to (a) recognize unique as a STRUCT-valued projection in the inner query, (b) carry that STRUCT type through the subquery boundary, and (c) expand unique.* into its field set. None of those steps are wired up today, so the projection drops out of column-level lineage.

This isn’t a defect in sqlglot per se — modeling row-as-STRUCT, array element extraction, and STRUCT.* expansion is genuinely additional surface area, and the upstream maintainers have asked (rightly) for a sqlglot-side issue to track it. But for teams running DataHub today on a BigQuery + dbt stack, the column lineage on every deduplicate-macro model is missing now.

Workaround: a post-processor sidecar

We built gsp-datahub-sidecar, an Apache-2.0 tool that fills this gap. It runs alongside your existing DataHub ingestion — no changes to DataHub itself, and it doesn’t replace anything sqlglot already produced.

The sidecar re-parses SQL that sqlglot’s column walker couldn’t fully resolve using Gudu SQLFlow, whose engine implements the row-as-STRUCT, [OFFSET(N)], and STRUCT.* semantics natively across BigQuery, Snowflake, and other dialects. It then emits the recovered lineage to DataHub via the standard REST API.

DataHub ingestion (unchanged)        gsp-datahub-sidecar
  dbt source -> sqlglot -> lineage     |
                  |                    | re-parse the SQL with Gudu SQLFlow
                  v                    | (handles row-as-STRUCT + STRUCT.*)
       table edge OK, column            v
       expansion missing for #11670 -> DataHub GMS (lineage filled in)

Try it in three commands

Step 1 — Install:

pip install gsp-datahub-sidecar

Step 2 — Dry-run on the dbt-utils deduplicate pattern. The repo ships with examples/bigquery_dbt_dedup.sql — the exact SQL from issue #11670, ready to run:

gsp-datahub-sidecar --sql-file examples/bigquery_dbt_dedup.sql --dry-run

Output (high-level summary, with the per-edge URN lines collapsed for readability — full output is shown in the GitHub repo):

[INFO] Processing 1 SQL statement(s) in 'anonymous' mode...
[INFO] Extracted 1 table-level lineage relationships
[INFO]   ALL_ARTICLES --> ANALYTICS.DEDUPLICATED_ARTICLES (3 columns)
[INFO] Built 2 MCPs for 1 downstream tables (3 column-level mappings)
[INFO] [DRY RUN] Would emit 4 MCPs to http://localhost:8080
[INFO] [DRY RUN]   UpstreamLineageClass (1 upstream table, 3 column-level lineages):
[INFO] [DRY RUN]     all_articles.*            ->  analytics.deduplicated_articles.*
[INFO] [DRY RUN]     all_articles.id           ->  analytics.deduplicated_articles.*
[INFO] [DRY RUN]     all_articles.article_name ->  analytics.deduplicated_articles.*

The three column-level edges map cleanly onto the dbt-utils macro’s structure:

all_articles.* -> analytics.deduplicated_articles.* — the row-flow edge. Every column of the source flows into the same-named column on the target, because unique.* field-expands the row STRUCT.
all_articles.id -> analytics.deduplicated_articles.* — id is the GROUP BY key. Different id values produce different output rows, so id is a row-selection (control) input to every output column.
all_articles.article_name -> analytics.deduplicated_articles.* — article_name is the ORDER BY ... DESC LIMIT 1 key. It picks which row wins per group, so it’s also a row-selection input to every output column.

The two row-selection edges (id, article_name) are the GROUP BY and ORDER BY columns annotated as control dependencies, not value lineage. That’s the right semantics for impact analysis: if article_name changes, the choice of winning row may change, but the value still flows from the row’s other columns.

With schema metadata supplied (via SQLFlow’s database integrations or a supplied schema file), the engine expands the * on the right into concrete same-named column edges per known column of all_articles. Without metadata, you still get the structural row-flow edge in DataHub rather than a silent gap — which is enough for impact analysis even when the leaf column names aren’t enumerated.

Step 3 — Emit to DataHub. Once the dry-run looks right, drop --dry-run and point at your DataHub GMS:

gsp-datahub-sidecar \
  --sql-file examples/bigquery_dbt_dedup.sql \
  --datahub-server http://localhost:8080

You can run the sidecar on real dbt project SQL the same way — point --sql-file at the compiled SQL in target/run/.../.sql, or feed a directory of compiled models with a wrapper script.

Backend modes

Pick the backend based on your security and volume needs:

Mode	Auth	Limit	Data location	Use case
`anonymous` (default)	None	50/day per IP	SQL sent to api.gudusoft.com	Quick evaluation
`authenticated`	userId + secretKey	10k/month	SQL sent to api.gudusoft.com	Extended evaluation
`self_hosted`	userId + secretKey	Unlimited	SQL stays in your VPC	Production
`local_jar`	None (local subprocess)	Per JAR license	SQL never leaves the process	Air-gapped / CI

For production use with regulated SQL, the self-hosted SQLFlow Docker keeps everything in your network — no SQL leaves your infrastructure.

What’s next

The sidecar is a workaround, not a substitute for fixing this upstream. The honest path forward:

Patch sqlglot’s column-lineage walker to model row-as-STRUCT, array element extraction, and STRUCT.* expansion. That unblocks every downstream tool that uses sqlglot, not just DataHub.
Until then, run the sidecar on dbt models that hit the deduplicate macro (or any other array_agg-based dedup pattern), so your column lineage isn’t silently empty.

If you’re affected by #11670 or other lineage gaps in your DataHub instance, browse the GitHub repo for the full example library — BigQuery procedural SQL, Power BI comments, MSSQL stored procedures, Oracle CREATE VIEW. The companion post on BigQuery procedural SQL lineage covers DataHub issue #11654 with the same playbook. Issues, PRs, and feedback welcome on GitHub.

Disclosure: This tool is built by Gudu Software, the team behind General SQL Parser and SQLFlow. We specialize in deep SQL parsing and column-level lineage across SQL dialects.

The post Why DataHub Loses Column-Level Lineage on dbt Deduplication Macros — and How to Recover It appeared first on SQL and Data Blog.

BigQuery Column-Level Lineage Without Metadata: Inferring STRUCT and ARRAY Types from SQL Alone

James — Fri, 01 May 2026 02:19:12 +0000

If you are searching for a column-level lineage solution for BigQuery, you have probably already discovered the part of the problem nobody talks about up front: BigQuery is a typed, nested SQL dialect. The moment your warehouse adopts STRUCT, ARRAY, SELECT AS STRUCT, or array_agg(table_alias), a parser that does not know the schema can stop dead, give up on the column, or — worse — silently produce wrong lineage.

This post walks through two real BigQuery patterns and shows what a SQL parser can actually infer from the SQL text alone, with no access to the BigQuery catalog, no INFORMATION_SCHEMA, no dbt manifest. We will use it to explain why generic SQL parsers struggle with BigQuery lineage, and what it takes to get column-level lineage right when metadata is missing.

The two patterns we will analyze:

ARRAY(SELECT AS STRUCT ...) — building a nested ARRAY column from a flat input row.
array_agg(original ORDER BY ... LIMIT 1)[OFFSET(0)] unique followed by SELECT unique.* — the dbt-utils-style deduplication pattern that flattens a row STRUCT back into top-level columns.

Both produce correct BigQuery lineage even without metadata — but only if the parser understands BigQuery semantics, not just BigQuery grammar.

Why BigQuery lineage is harder than “normal” SQL lineage

A column-level lineage extractor for plain ANSI SQL has one job per output column: walk the SELECT list, resolve each expression back to a base table column. That breaks down in BigQuery for three reasons:

Output columns are not always scalar. A SELECT AS STRUCT expression produces a single output column whose type is itself a record. A downstream view’s column_name may actually expand into a dozen physical fields.
Row variables behave like STRUCTs. BigQuery lets you write array_agg(table_alias) and get back ARRAY — without ever naming the columns in the SQL.
STRUCT. is field expansion, not column projection. The same syntax (alias.) means very different things depending on whether alias is a table or a STRUCT-typed expression.

If your lineage tool was originally built for Postgres or MySQL, every one of those is a hole. Most popular open-source parsers — JSQLParser, sqlglot, sqlparse — handle two of the three at best, and even then only with metadata. Without metadata, generic parsers either drop the column from lineage entirely or attribute it to the wrong source.

The good news: a lot more is recoverable from the SQL alone than people assume, as long as the parser knows BigQuery’s nested-type rules.

Pattern 1: `ARRAY(SELECT AS STRUCT ...)` builds a nested column

Here is a typical BigQuery view that flattens an orders_raw table into a single nested orders column:

CREATE VIEW orders_view AS
SELECT
    ARRAY(SELECT AS STRUCT
        customer.name AS customer_name,
        order_id      AS order_id
    ) AS orders
FROM
    orders_raw;

Read by a metadata-free parser, this SQL says quite a lot.

Step 1 — the inner query is a STRUCT constructor.

SELECT AS STRUCT
    customer.name AS customer_name,
    order_id      AS order_id

SELECT AS STRUCT is a BigQuery-specific keyword. It is not “select two columns” — it is “build one row of type STRUCT“. The parser does not need to know whether customer.name is STRING or BYTES; it knows for certain that customer_name and order_id are fields of a STRUCT. That’s a semantic guarantee from the grammar itself.

Step 2 — ARRAY(...) wraps the STRUCT in an ARRAY.

ARRAY(SELECT AS STRUCT ...)

The result type is ARRAY. Again, the leaf scalar types are unknown without metadata, but the shape is fully determined by the SQL.

Step 3 — the outer alias names a single column.

) AS orders

So orders_view has exactly one top-level column named orders. Its type form is:

orders ARRAY>

What lineage can we extract without metadata?

A BigQuery-aware parser can produce this column-level lineage:

orders_raw.customer.name -> orders_view.orders.customer_name
orders_raw.order_id      -> orders_view.orders.order_id

That is real, navigable, nested column-level lineage from the SQL text — no schema lookup required. The only thing the parser cannot prove from SQL alone is the leaf scalar type (STRING? INT64?) and whether orders_raw.customer is itself a STRUCT in the source table. Those need either metadata or BigQuery’s own type checker at execution time.

What a generic parser typically gets wrong

Without BigQuery semantics, a generic parser tends to do one of these:

Treat ARRAY(SELECT AS STRUCT ...) as an unknown function and drop the lineage.
Emit a single edge from orders_raw -> orders_view.orders with no internal field structure, hiding the fact that customer_name and order_id are separate fields downstream.
Flatten the STRUCT incorrectly, producing two top-level columns customer_name and order_id on orders_view — which is wrong: there is only one top-level column called orders.

If your data catalog tells you orders_view has columns customer_name and order_id, the parser feeding it has misread the SQL.

Pattern 2: `array_agg(row)[OFFSET(0)] unique` followed by `unique.*`

This is the canonical dbt-utils deduplication pattern, which is one of the most common SQL shapes in modern BigQuery warehouses:

CREATE TABLE deduplicated_articles AS
  SELECT unique.*
  FROM (
       SELECT
           array_agg(
               original
                   ORDER BY article_name DESC
                   LIMIT 1
           )[OFFSET(0)] unique
       FROM all_articles original
       GROUP BY id
  );

This SQL is harder for a generic parser because no individual columns are ever named in the projection. And yet, a BigQuery-aware parser can still extract correct lineage from the structure.

Step 1 — original is a row variable, not a column.

FROM all_articles original

original is the table alias for all_articles. When passed as a value expression (instead of with a .column access), it represents the entire row, which BigQuery treats as a STRUCT containing every column of all_articles.

Step 2 — array_agg(original) aggregates whole rows.

array_agg(original ORDER BY article_name DESC LIMIT 1)

This produces ARRAY. The ORDER BY ... LIMIT 1 keeps the top row per group.

Step 3 — [OFFSET(0)] extracts a STRUCT.

array_agg(...)[OFFSET(0)] unique

Now unique is a single STRUCT containing every column of all_articles. Crucially, the parser knows this without knowing what those columns are.

Step 4 — unique.* flattens the STRUCT into top-level columns.

SELECT unique.*

This is the syntactic twist that breaks naive parsers. unique. does not* mean “select the column named unique“. It means “expand every field of the STRUCT unique into its own top-level column”. So deduplicated_articles ends up with the same top-level column set as all_articles.

What lineage can we extract without metadata?

The parser can confidently emit:

all_articles.* -> deduplicated_articles.*

That is, “every column of all_articles flows into deduplicated_articles, picking the row with the largest article_name per id“. The parser can also note that article_name participates in the row-selection logic (for impact analysis), even though it does not change the value lineage.

What the parser cannot do without metadata is enumerate the exact column names. If you need deduplicated_articles.article_name -> all_articles.article_name as a concrete edge, you need either the schema or a metadata-aware second pass.

Why this pattern defeats most generic parsers

Three reasons:

They treat original (a bare table alias used as a value) as an error or as an unresolved column.
They treat [OFFSET(0)] after a function call as unknown syntax.
They treat unique. like table_alias. and look for a table called unique, fail to find one, and drop the projection.

Any one of those failures collapses the entire lineage edge.

What a BigQuery-aware parser needs to know

Stepping back, here is the irreducible set of BigQuery semantics a metadata-free lineage tool must implement:

Syntax	Semantic	What lineage gains
`SELECT AS STRUCT`	Build a single STRUCT row, not multiple columns	Output is one column whose fields are named in the SELECT list
`ARRAY(SELECT AS STRUCT ...)`	Wrap STRUCT rows into ARRAY>	Output column type form is fully recoverable
`array_agg(table_alias)`	Aggregate whole rows; result is ARRAY	Whole-row flow is detectable even without column enumeration
`arr[OFFSET(n)]`	Extract one element of an ARRAY	Result type is the array element type (often STRUCT)
`struct_alias.*`	Expand STRUCT fields into top-level columns	Disambiguates “select all from table” vs “explode this STRUCT”
`nested.path.access`	Walk into nested STRUCT fields	Edges target the specific leaf field, not the parent column

If a parser does not implement at least these six semantics, BigQuery column-level lineage will be either incomplete or misleading whenever your team uses nested types — which, in modern BigQuery warehouses, is most of the time.

A simple memory aid for reading BigQuery SQL

When eyeballing BigQuery SQL for lineage, three patterns are worth memorizing:

See SELECT AS STRUCT → the SQL is constructing a STRUCT.
See ARRAY(SELECT AS STRUCT ...) → the result column is ARRAY.
See table_alias used as a value (e.g. array_agg(original)) → the value is the entire row as a STRUCT.
See alias. where alias is a STRUCT-typed expression → fields are being flattened* into top-level columns, not selecting from a table.

Anywhere those patterns appear, a metadata-free parser can still recover the lineage shape. What it cannot recover is the leaf scalar types, the full column list of a wildcard-flattened STRUCT, or the schema of the underlying table — those still need metadata or a second resolution pass.

How Gudu’s SQL engine handles it

All three of Gudu Software’s products — General SQL Parser (GSP), SQLFlow, and SQL Omni — share one column-level lineage engine. They differ in how you use it:

GSP is the SDK: embed lineage extraction in a Java application or data platform.
SQLFlow is the hosted lineage platform with REST APIs and visualization.
SQL Omni is the VS Code extension for individual developers, 100% offline.

The engine implements BigQuery’s nested-type semantics — SELECT AS STRUCT, ARRAY(...), row-as-STRUCT in array_agg, [OFFSET(n)] array indexing, and STRUCT. field expansion — so that the two patterns in this post produce correct lineage with or without metadata. When metadata is available (via SQLFlow’s database integrations or a supplied schema), the engine fills in concrete column names where wildcards appeared. When metadata is not* available, you still get the structural lineage shown above, rather than silent gaps.

Disclosure: I work at Gudu Software.

Try it yourself

Paste either of the SQL samples in this post into the SQLFlow online demo and pick BigQuery as the dialect. You’ll see column-level lineage rendered as a graph, including the nested orders.customer_name and orders.order_id edges from Pattern 1, and the all_articles. -> deduplicated_articles. whole-row flow from Pattern 2.

If you’d rather embed the engine in your own pipeline, the GSP BigQuery quickstart shows how to extract the same lineage as JSON in Java. For VS Code users on regulated stacks, SQL Omni runs the same analysis 100% offline.

Takeaways

BigQuery column-level lineage is harder than ANSI lineage because of STRUCT, ARRAY, row-as-STRUCT, and STRUCT.* field expansion.
A lot is recoverable from SQL text alone, including the type shape of nested output columns, the field names of SELECT AS STRUCT, and whole-row flows from array_agg(row).
A little is genuinely impossible without metadata: leaf scalar types and the column list of any wildcard-flattened STRUCT.
A generic SQL parser that does not implement these BigQuery semantics will produce either silent gaps or wrong attributions on any modern BigQuery warehouse — which is why a BigQuery-specialized parser matters when you’re choosing a column-level lineage solution.

The post BigQuery Column-Level Lineage Without Metadata: Inferring STRUCT and ARRAY Types from SQL Alone appeared first on SQL and Data Blog.

OpenMetadata + MSSQL Stored Procedures: Why Your Lineage Is Silently Empty — and How to Fix It

James — Tue, 21 Apr 2026 11:42:49 +0000

Author: James Date: 2026-04-21 Categories: gsp, SQLFlow, OpenMetadata Keywords: column-lineage, data-governance, openmetadata, mssql, stored-procedure, sql-server, gsp-openmetadata-sidecar, temp-tables, merge

—

OpenMetadata issues #16737, #25299, and #17586 all report the same frustrating problem: MSSQL stored procedures produce zero lineage in OpenMetadata. No tables. No columns. The lineage graph just stops at the procedure boundary.

These issues have been open since June 2024 — over two years — with users confirming the same behavior across OpenMetadata 1.4.x through 1.11.x. We analyzed the real SQL from these issues, identified three distinct failure patterns, and built a sidecar tool that solves all of them. Here’s what’s happening and how to fix it.

Why OpenMetadata Can’t Parse Stored Procedures

OpenMetadata extracts lineage by reading SQL query logs from your database, then passing each statement through a three-parser chain: sqlglot → sqlfluff → sqlparse (via the collate-sqllineage library). If all three parsers fail, the statement is silently skipped — no error in the UI, just empty lineage.

The problem is that MSSQL stored procedures aren’t plain SQL statements. They’re wrapped in T-SQL procedural syntax that none of the three parsers can handle:

CREATE PROCEDURE ... AS BEGIN ... END — the procedure declaration wrapper
DECLARE — variable declarations
#tempTable — temporary tables used as intermediate staging
MERGE ... WHEN MATCHED ... WHEN NOT MATCHED — upsert patterns
[dbo].[table_name] — square bracket identifiers

These are standard patterns in every MSSQL environment — not edge cases. If your data warehouse uses stored procedures for ETL (and most MSSQL shops do), OpenMetadata is missing your lineage.

Three Failure Patterns, All Solved

The issues in #16737, #25299, and #17586 document three distinct failure modes. Our gsp-openmetadata-sidecar tool handles all of them:

Pattern	Issues	What Happens	With GSP
Pattern A — BEGIN/END blocks	#16737, #25299, #17586	`CREATE PROCEDURE ... BEGIN ... END` breaks all three parsers. Lineage silently dropped.	Full lineage recovered — procedure wrapper parsed correctly
Pattern B — Temp table chains	#25299	`source → #tempTable → target` 3-hop lineage. Parser can’t follow data through temp tables.	Multi-hop lineage recovered — temp tables resolved as intermediates
Pattern C — Square brackets	#16424	`[database].[schema].[table]` identifiers break regex-based bracket handling	Bracket identifiers resolved — cross-database FQNs extracted correctly

—

Pattern A: CREATE PROCEDURE with BEGIN/END (Issues #16737, #25299, #17586)

This is the most common failure. Here’s the exact SQL from issue #17586 — a minimal stored procedure that OpenMetadata cannot parse:

CREATE PROCEDURE myproc
AS
BEGIN
    INSERT INTO test2 SELECT * FROM test1
END

What OpenMetadata sees: The ingestion pipeline reads this from sys.dm_exec_cached_plans. The SQL text starts with CREATE PROCEDURE — and historically, OpenMetadata’s query log reader explicitly filtered these out with:

AND lower(t.text) NOT LIKE '%%create%%procedure%%'

That filter was removed in PR #14586, but even after removal, the parser chain (sqlglot → sqlfluff → sqlparse) still cannot handle the BEGIN...END wrapper. As one user in #16737 confirmed: “commenting commands like CREATE PROCEDURE, BEGIN, END, DECLARE, AS gives correct lineage” — proving the SQL inside is valid, but the parser rejects the T-SQL wrapper.

What GSP extracts:

$ gsp-openmetadata-sidecar --sql-file om_issue_17586_procedure_filtered.sql --dry-run

Analyzing SQL (544 chars)...
Extracted 1 table-level lineage relationships
  test1 --> test2 (1 column: *)

[DRY RUN] Would emit lineage: mssql.dbo.test1 --> mssql.dbo.test2

GSP’s T-SQL parser strips the CREATE PROCEDURE ... AS BEGIN ... END wrapper, recognizes the INSERT INTO ... SELECT inside, and extracts the lineage: test1 → test2.

—

Pattern B: Temp Table Multi-Hop Chains (Issue #25299)

This is the most technically interesting failure. Here’s the exact SQL from issue #25299 — a stored procedure that stages data through a temp table:

CREATE PROCEDURE schName.procName
AS
BEGIN
    DROP TABLE IF EXISTS #tempTable

    CREATE TABLE #tempTable (columnName int)

    INSERT INTO #tempTable (columnName)
    SELECT columnName FROM schName.sourceTable

    INSERT INTO schName.targetTable (columnName)
    SELECT columnName FROM #tempTable
END

The data flow is: schName.sourceTable → #tempTable → schName.targetTable

The reporter in #25299 identified this as a 3-hop lineage problem: “Procedures using temp tables (#tempTable) in the transformation chain: source → #temp → target — only two-hop lineage works, not three-hop through temp tables.”

What GSP extracts:

$ gsp-openmetadata-sidecar --sql-file om_issue_25299_create_procedure_begin_end.sql --dry-run

Analyzing SQL (1007 chars)...
Extracted 2 table-level lineage relationships
  schName.sourceTable --> #tempTable (1 column: columnName)
  #tempTable --> schName.targetTable (1 column: columnName)

[DRY RUN] Would emit lineage:
  mssql.schname.sourcetable --> mssql.dbo.#temptable (columnName → columnName)
  mssql.dbo.#temptable --> mssql.schname.targettable (columnName → columnName)

GSP extracts the complete chain: sourceTable → #tempTable → targetTable with column-level lineage at each hop. OpenMetadata’s lineage graph would show the full data flow through the temp table intermediate.

—

Pattern C: Square Bracket Cross-Database Identifiers (Issue #16424)

MSSQL uses square brackets for identifier quoting, and cross-database queries use 3-part or 4-part names. Issue #16424 reported that views using [database].[schema].[table] syntax produce no lineage.

The root cause was a greedy regex r"\[(.*)\]" in OpenMetadata’s parser.py that matched across multiple bracket pairs, returning "db].[schema" instead of separate identifiers. Here’s a reconstructed example based on the issue description:

CREATE VIEW [ReportDB].[dbo].[vw_CustomerOrders]
AS
SELECT
    [SalesDB].[dbo].[Customers].[CustomerID],
    [SalesDB].[dbo].[Customers].[CustomerName],
    [SalesDB].[dbo].[Orders].[OrderID],
    [SalesDB].[dbo].[Orders].[OrderDate],
    [SalesDB].[dbo].[Orders].[TotalAmount]
FROM [SalesDB].[dbo].[Customers]
INNER JOIN [SalesDB].[dbo].[Orders]
    ON [SalesDB].[dbo].[Customers].[CustomerID] = [SalesDB].[dbo].[Orders].[CustomerID]
WHERE [SalesDB].[dbo].[Orders].[OrderDate] >= '2024-01-01'

What GSP extracts:

$ gsp-openmetadata-sidecar --sql-file om_issue_16424_square_brackets.sql --dry-run

Analyzing SQL (984 chars)...
Extracted 2 table-level lineage relationships
  SalesDB.dbo.Customers --> ReportDB.dbo.vw_CustomerOrders (2 columns)
  SalesDB.dbo.Orders --> ReportDB.dbo.vw_CustomerOrders (3 columns)

[DRY RUN] Would emit lineage:
  mssql.salesdb.dbo.customers --> mssql.reportdb.dbo.vw_customerorders
    CustomerID → CustomerID
    CustomerName → CustomerName
  mssql.salesdb.dbo.orders --> mssql.reportdb.dbo.vw_customerorders
    OrderID → OrderID
    OrderDate → OrderDate
    TotalAmount → TotalAmount

GSP correctly handles the square brackets, resolves cross-database references (SalesDB → ReportDB), and extracts 5 column-level lineage mappings across 2 source tables.

Column-level lineage recovered by GSP for the cross-database view — 2 source tables, 5 column mappings, all square bracket identifiers resolved correctly.

—

A Real-World Example: Everything Combined

Let’s look at a more realistic stored procedure that combines all three patterns — the kind of ETL logic you’d find in any production MSSQL warehouse:

CREATE PROCEDURE [dbo].[usp_UpdateCustomerOrders]
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        c.customer_id, c.customer_name,
        o.order_id, o.order_date, o.total_amount
    INTO #staged_orders
    FROM [dbo].[customers] c
    INNER JOIN [dbo].[orders] o ON c.customer_id = o.customer_id
    WHERE o.order_date >= DATEADD(day, -30, GETDATE());

    MERGE [dbo].[customer_order_summary] AS target
    USING #staged_orders AS source
    ON target.customer_id = source.customer_id
    WHEN MATCHED THEN
        UPDATE SET
            target.last_order_date = source.order_date,
            target.total_amount = source.total_amount,
            target.customer_name = source.customer_name
    WHEN NOT MATCHED THEN
        INSERT (customer_id, customer_name, last_order_date, total_amount)
        VALUES (source.customer_id, source.customer_name,
                source.order_date, source.total_amount);

    INSERT INTO [dbo].[audit_log] (action, record_count, run_date)
    SELECT 'usp_UpdateCustomerOrders', COUNT(*), GETDATE()
    FROM #staged_orders;

    DROP TABLE #staged_orders;
END

This procedure has everything OpenMetadata can’t handle: CREATE PROCEDURE, BEGIN/END, a temp table (#staged_orders), a MERGE statement, and square bracket identifiers.

What GSP extracts — 4 lineage edges, 12 column-level mappings:

$ gsp-openmetadata-sidecar --sql-file mssql_stored_procedure.sql --dry-run

Extracted 4 table-level lineage relationships:
  dbo.customers --> #staged_orders (2 columns: customer_id, customer_name)
  dbo.orders --> #staged_orders (3 columns: order_id, order_date, total_amount)
  #staged_orders --> dbo.customer_order_summary (4 columns)
  #staged_orders --> dbo.audit_log (3 columns)

Source Table	Target Table	Column Mappings
`dbo.customers`	`#staged_orders`	`customer_id` → `customer_id`, `customer_name` → `customer_name`
`dbo.orders`	`#staged_orders`	`order_id` → `order_id`, `order_date` → `order_date`, `total_amount` → `total_amount`
`#staged_orders`	`dbo.customer_order_summary`	`customer_id`, `customer_name`, `order_date` → `last_order_date`, `total_amount`
`#staged_orders`	`dbo.audit_log`	`COUNT(*)` → `record_count`, `GETDATE()` → `run_date`, literal → `action`

The full data flow through the temp table staging layer is preserved, including the MERGE upsert pattern and the audit log insert.

Complete lineage recovered by GSP for the combined stored procedure — 4 table-level edges and 12 column-level mappings through the temp table staging layer, including the MERGE upsert pattern.

—

Summary: All Patterns Solved

Issue	Pattern	OpenMetadata Today	With gsp-openmetadata-sidecar
#17586	A — BEGIN/END wrapper	0 lineage (silently skipped)	1 table-level, 1 column mapping
#25299	B — Temp table chain	0 lineage (3-hop fails)	2 table-level, 2 column mappings
#16424	C — Square brackets	0 lineage (regex bug)	2 table-level, 5 column mappings
Combined example	A + B + C	0 lineage	4 table-level, 12 column mappings

—

How It Works

The gsp-openmetadata-sidecar is a companion tool that runs alongside your OpenMetadata installation. It does not modify OpenMetadata — it uses the public REST API (PUT /api/v1/lineage) to push the lineage that OpenMetadata’s own parser misses.

OpenMetadata ingestion (unchanged)       gsp-openmetadata-sidecar
  query logs → collate-sqllineage          |
              |                            | 1. Parse SQL with Gudu SQLFlow
              v                            | 2. Resolve table FQNs via OM API
        silently skipped                   | 3. Push lineage via PUT /api/v1/lineage
        (lineage lost)                     v
                                     OpenMetadata (lineage restored)

What it does:

Parses your SQL using Gudu SQLFlow — a specialized SQL parser that handles procedural T-SQL natively (including BEGIN/END, DECLARE, temp tables, MERGE, dynamic SQL, cursors, and 20+ dialects)
Resolves table names to OpenMetadata entity UUIDs via GET /api/v1/tables/name/{fqn}
Pushes lineage edges to OpenMetadata via PUT /api/v1/lineage with column-level detail

What it supports beyond MSSQL stored procedures:

BigQuery procedural SQL (DECLARE, IF/THEN, CREATE TEMP TABLE)
Oracle PL/SQL blocks
Snowflake scripting
MERGE INTO column-level lineage (all dialects)
Multi-statement SQL scripts
Cross-database and cross-schema references
20+ SQL dialects total

—

Try It

# Install
pip install gsp-openmetadata-sidecar

# Test with the exact SQL from issue #25299 (dry run — no OM connection needed):
gsp-openmetadata-sidecar \
  --sql-file examples/om_issue_25299_create_procedure_begin_end.sql \
  --dry-run

# Test with your own stored procedures:
gsp-openmetadata-sidecar \
  --sql-file your_procedure.sql \
  --db-vendor dbvmssql \
  --dry-run

# Push lineage to your OpenMetadata instance:
gsp-openmetadata-sidecar \
  --sql-file your_procedure.sql \
  --om-server http://localhost:8585/api \
  --om-token "eyJ..." \
  --service-name mssql_prod \
  --database-name YourDB \
  --schema-name dbo

The tool supports four backend modes: anonymous (no signup, 50 calls/day), authenticated (API key, 10k/month), self-hosted Docker (unlimited, data stays in your network), and local JAR (offline, no network at all).

Source code and documentation: github.com/gudusoftware/gsp-openmetadata-sidecar

—

Related OpenMetadata Issues

#16737 — Data Lineage Not Reflected for MSSQL Stored Procedure (open since June 2024)
#25299 — Stored Procedure lineage is not supported for MS SQL connector (release backlog)
#17586 — MS SQL Procedures Lineage Not Picked Up (partially fixed — filter removed, parser still fails)
#16424 — Square bracket syntax breaks lineage (fixed in sqlfluff, but proc wrapper still fails)
Discussion #23717 — Cross-database MSSQL lineage (unanswered)

Also Affected by SQL Parsing Gaps?

If you’re using DataHub and hitting similar SQL parsing failures, see our gsp-datahub-sidecar — same approach, same engine, built for DataHub’s ingestion pipeline. It addresses BigQuery procedural SQL (#11654), Power BI M-language lineage (#15327), and MSSQL stored procedures (#12606).

—

Disclosure: I work at Gudu Software, the company behind GSP SQL Parser, SQLFlow, and the gsp-openmetadata-sidecar. All lineage results shown were produced by running the SQL examples through GSP’s T-SQL parser and verified against the actual SQL from the referenced OpenMetadata GitHub issues. The example SQL files used in this post are available in the gsp-openmetadata-sidecar repository.

The post OpenMetadata + MSSQL Stored Procedures: Why Your Lineage Is Silently Empty — and How to Fix It appeared first on SQL and Data Blog.

DataHub #15327: Why Power BI M-Language Queries Produce Zero Lineage — 4-Query Deep Analysis

James — Mon, 20 Apr 2026 06:30:33 +0000

DataHub issue #15327 reports a common Power BI + DataHub problem: four Snowflake-connected Power BI datasets produce zero upstream lineage. No tables. No columns. The lineage graph just stops at the Power BI boundary.

We analyzed all four M-language queries from the issue, identified two distinct failure patterns, and ran real lineage extraction against each one using GSP’s Power Query M-language parser. Here’s what we found — and how we solve both patterns.

Why DataHub Can’t Parse These Queries

The root cause is that Power BI doesn’t generate SQL — it generates M-language (Power Query Formula Language). M is a fully functional language (closer to F# than SQL) that Power BI uses behind the scenes for all “Get Data” and “Transform Data” operations.

DataHub’s ingestion pipeline expects SQL. When it receives M-language, no SQL parser (sqlglot, sqllineage, or any other) can extract lineage from it because it’s not SQL.

Two Patterns, Both Solved

The four queries in #15327 fall into two fundamentally different categories. GSP’s Power Query M-language extractor (dbvpowerquery) handles both:

Pattern	Queries	What Happens	GSP Result
Pattern A — M Navigation	Q1, Q3	Table reference encoded in M’s record-access syntax: `{[Name="X", Kind="Database"]}[Data]`. No SQL anywhere.	Table-level lineage recovered via navigation chain parsing
Pattern B — Value.NativeQuery()	Q2, Q4	M wraps actual SQL inside a `Value.NativeQuery()` call. SQL has M-encoded escapes (`#(lf)`, `""`).	Column-level lineage recovered via SQL extraction + parsing

Pattern A: Pure M Navigation (Queries 1 & 3)

These queries use M-language’s navigation syntax to reach Snowflake objects — no SQL at all. GSP’s M-language parser walks the navigation chain {[Name="X", Kind="Y"]}[Data] to extract the full database.schema.table path.

Query 1: Navigation to a Snowflake View

Original M-Language:

let
    Source = Snowflake.Databases(SnowFlakeConnector, SnowflakeWarehouse),
    my_Database = Source{[Name="PROD201_DB_redacted", Kind="Database"]}[Data],
    CONSUMPTION_Schema = my_Database{[Name="CONSUMPTION", Kind="Schema"]}[Data],
    Details_View = CONSUMPTION_Schema{[Name="details", Kind="View"]}[Data],
    #"Changed Type1" = Table.TransformColumnTypes(Details_View, ...),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type1", ...)
in #"Filtered Rows"

GSP analysis: The parser identifies Snowflake.Databases as the connector, then walks the navigation chain through three steps:

my_Database: {[Name="PROD201_DB_redacted", Kind="Database"]} → Database level
CONSUMPTION_Schema: {[Name="CONSUMPTION", Kind="Schema"]} → Schema level
Details_View: {[Name="details", Kind="View"]} → View level

GSP result:

Navigation resolved:
  Step: Details_View | Vendor: dbvsnowflake
  Path: PROD201_DB_redacted.CONSUMPTION.details
  Synthetic SQL: SELECT * FROM "PROD201_DB_redacted"."CONSUMPTION"."details"

Upstream table recovered: PROD201_DB_redacted.CONSUMPTION.details (table-level lineage)

Query 3: Navigation to a Snowflake Table + Column Renaming

Original M-Language:

let
    Source = Snowflake.Databases("redacted.snowflakecomputing.com", #"Warehouse name"),
    my_Database = Source{[Name="PROD201_DB_redacted", Kind="Database"]}[Data],
    CONSUMPTION_Schema = my_Database{[Name="CONSUMPTION", Kind="Schema"]}[Data],
    MY_View = CONSUMPTION_Schema{[Name="MESSAGES", Kind="Table"]}[Data],
    #"Renamed Columns" = Table.RenameColumns(MY_View, {{"DATE","Date"}, ...}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Message Type", ...)
in #"Added Custom"

GSP result:

Navigation resolved:
  Step: MY_View | Vendor: dbvsnowflake
  Path: PROD201_DB_redacted.CONSUMPTION.MESSAGES
  Synthetic SQL: SELECT * FROM "PROD201_DB_redacted"."CONSUMPTION"."MESSAGES"

Upstream table recovered: PROD201_DB_redacted.CONSUMPTION.MESSAGES (table-level lineage). The Table.RenameColumns and Table.AddColumn steps are M-level transformations that don’t change the upstream source.

Pattern B: Embedded SQL (Queries 2 & 4)

These queries wrap real Snowflake SQL inside M’s Value.NativeQuery() function. GSP extracts the SQL string, decodes M-specific escapes, infers the SQL dialect from the inline connector call, and parses the SQL for full column-level lineage.

Query 2: Simple SELECT with #(lf) escape

Original M-Language:

let
    Source = Value.NativeQuery(
        Snowflake.Databases(SnowFlakeServer, SnowFlakeDWH)
            {[Name="PROD201_redacted"]}[Data],
        "SELECT *#(lf)FROM PROD201_DB_redacted.CONSUMPTION.CV_OFFER_redacted_CITY",
        null, [EnableFolding=true])
in Source

Step 1 — Decode M escapes: #(lf) → newline. Step 2 — Infer vendor: The inline Snowflake.Databases() tells GSP this is Snowflake SQL. Step 3 — Parse SQL: SELECT * produces a wildcard column mapping.

GSP result:

NativeQuery resolved:
  Step: Source | Vendor: dbvsnowflake | Parse RC: 0
  Decoded SQL: SELECT *
               FROM PROD201_DB_redacted.CONSUMPTION.CV_OFFER_redacted_CITY
  Column lineage: * → * (all columns flow through)

Upstream: PROD201_DB_redacted.CONSUMPTION.CV_OFFER_redacted_CITY — 1 table-level + 1 column-level (* → *) lineage.

Query 4: Complex Snowflake SQL with GROUP BY ALL, quoted aliases, expressions

Original M-Language (abbreviated):

let
    Source = Value.NativeQuery(
        Snowflake.Databases("redacted.snowflakecomputing.com",
            WAREHOUSE, [Implementation="2.0"])
            {[Name="PROD201_DB_redacted"]}[Data],
        "
SELECT
  SERVICE_START_DATE AS ""Service Date"",
  TO_NUMBER(OPERATIVE_OFFICE) || '_' || statement || '_'
    || VENDOR_CODE || '_' || TO_CHAR(SERVICE_START_DATE, 'YYYYMMDD')
    AS OFFICE_STATEMENT_VENDOR_DATE,
  VENDOR_CODE
FROM PROD201_DB_redacted_DATAMARTS.SALES_MARKETING
     .CV_COMMDX_SALES_TRANSFERS
WHERE SERVICE_START_DATE >= '2023-10-01' ...
GROUP BY ALL
HAVING SUM(COST_OF_SALES_CUR) = 0 AND COUNT(*) > 1
        ", null, [EnableFolding=true]),
    #"TimeFilter" = Table.SelectRows(Source, ...)
in #"TimeFilter"

Step 1 — Decode M escapes: ""Service Date"" → "Service Date" (Snowflake quoted identifier). Step 2 — Infer vendor: Inline Snowflake.Databases() → Snowflake. Step 3 — Analyze SQL:

Column 1: SERVICE_START_DATE AS "Service Date" — simple alias (1 mapping)
Column 2: TO_NUMBER(OPERATIVE_OFFICE) || '_' || statement || '_' || VENDOR_CODE || '_' || TO_CHAR(SERVICE_START_DATE, 'YYYYMMDD') AS OFFICE_STATEMENT_VENDOR_DATE — concatenation with 4 source columns traced through TO_NUMBER() and TO_CHAR() (4 mappings)
Column 3: VENDOR_CODE — direct pass-through (1 mapping)

GSP result — 6 column-level lineage mappings:

Source Column		Target Column	Why
`SERVICE_START_DATE`	→	`SERVICE DATE`	Direct alias: `AS "Service Date"`
`OPERATIVE_OFFICE`	→	`OFFICE_STATEMENT_VENDOR_DATE`	Via `TO_NUMBER()` in concat
`STATEMENT`	→	`OFFICE_STATEMENT_VENDOR_DATE`	Direct in `\|\|` concat
`VENDOR_CODE`	→	`OFFICE_STATEMENT_VENDOR_DATE`	Direct in `\|\|` concat
`SERVICE_START_DATE`	→	`OFFICE_STATEMENT_VENDOR_DATE`	Via `TO_CHAR()` in concat
`VENDOR_CODE`	→	`VENDOR_CODE`	Direct pass-through

Upstream: PROD201_DB_redacted_DATAMARTS.SALES_MARKETING.CV_COMMDX_SALES_TRANSFERS — 1 table-level + 6 column-level lineages.

Summary: All 4 Queries Solved

Query	Pattern	Upstream Table	DataHub Today	With GSP
Q1	A (M Navigation)	`PROD201_DB_redacted.CONSUMPTION.details`	0 lineage	Table-level lineage
Q2	B (NativeQuery)	`PROD201_DB_redacted.CONSUMPTION.CV_OFFER_redacted_CITY`	0 lineage	*1 table + 1 column ()**
Q3	A (M Navigation)	`PROD201_DB_redacted.CONSUMPTION.MESSAGES`	0 lineage	Table-level lineage
Q4	B (NativeQuery)	`...DATAMARTS.SALES_MARKETING.CV_COMMDX_SALES_TRANSFERS`	0 lineage	1 table + 6 columns

How It Works

GSP’s Power Query module (dbvpowerquery) is a lightweight M-language extractor — not a full M parser. It answers exactly 6 questions about any M document:

What are the let bindings?
Which binding is returned by in?
Does a binding contain Value.NativeQuery()? → Extract SQL, decode escapes, delegate to vendor SQL parser
Does a binding represent a navigation chain? → Extract database.schema.table
What connector function is at the root? → Infer SQL dialect (Snowflake, SQL Server, Oracle, PostgreSQL, MySQL, BigQuery, Redshift, Databricks, Hive)
Which bindings reference other bindings? → Follow step references

Everything else in M (closures, each, Table.* transforms, type system) is parsed permissively — unknown constructs produce warnings, never wrong lineage.

Try It

# Install the sidecar
pip install git+https://github.com/gudusoftware/gsp-datahub-sidecar.git

# Test with your Power BI queries
gsp-datahub-sidecar --mode authenticated \
  --user-id YOUR_USER_ID \
  --secret-key YOUR_SECRET_KEY \
  --sql-file your_query.sql \
  --db-vendor dbvsnowflake \
  --dry-run

Sign up for a free API key at docs.gudusoft.com/sign-up. For a full interactive analysis, see our dedicated analysis page.

Related Issues

#15327 — No upstream lineage tracked for specific Power BI tables (this analysis)
#11251 — Power BI SQL comments break lineage (#(lf) encoding) — solved
#11654 — BigQuery procedural SQL drops lineage — solved

Disclosure: I work at Gudu Software, the company behind GSP SQL Parser, SQLFlow, and the gsp-datahub-sidecar. All lineage results shown were produced by running the M scripts through GSP’s Power Query parser (dbvpowerquery) and verified against the actual M-language queries from DataHub issue #15327.

The post DataHub #15327: Why Power BI M-Language Queries Produce Zero Lineage — 4-Query Deep Analysis appeared first on SQL and Data Blog.

Why Power BI SQL Comments Break Your DataHub Lineage — and How to Fix It

James — Sun, 19 Apr 2026 06:24:12 +0000

If you use Power BI with DataHub, some of your lineage is probably missing right now — and you don’t know it.

Background: How Power BI Handles SQL

A common question from newcomers: does Power BI have its own SQL dialect? The short answer is no. Power BI connects to external databases — SQL Server, Snowflake, PostgreSQL, and others — and sends SQL queries written in that database’s native dialect. If your Power BI report pulls from SQL Server, the SQL inside is T-SQL; if it pulls from Snowflake, it’s Snowflake SQL.

However, Power BI does have its own query language called M (also known as Power Query Formula Language). M wraps everything — including embedded SQL. When you write a Value.NativeQuery expression in Power BI, the SQL string lives inside an M expression, and M applies its own encoding rules.

The most important encoding rule: M replaces actual newline characters with #(lf) (M’s literal for “line feed”). So a query that a human writes as:

SELECT name
FROM customers
-- old filter
WHERE active = 1

gets stored by Power BI as:

select#(lf)name#(lf)from customers#(lf)-- old filter#(lf)where active = 1

This is where the trouble starts. The SQL itself is standard — the encoding around it is not.

The Problem: Why Comments Break Lineage

In standard SQL, -- marks a single-line comment: everything from -- to the next actual newline character is ignored. But when Power BI encodes newlines as #(lf), those aren’t real newline characters — they’re just ordinary text. So any SQL parser that receives this encoded string sees the -- comment as running all the way to the end of the entire query, because there is no real newline to terminate it.

This is not a parser bug in sqlglot or any other SQL parser — it’s a missing preprocessing step. Before parsing, someone needs to decode M’s #(lf) sequences back into real newline characters. DataHub’s Power BI ingestion connector does not do this, and neither does GSP (General SQL Parser) on its own — no SQL parser should be expected to understand Power BI M-language encoding.

Here’s a real query from DataHub issue #11251 that demonstrates the problem:

select
upper(cs.customercode) as customercode
, cs.ear2id as ear2id
, db.branch_rollup_name
, db.subregion1
, db.subregion2
from nast_truckload_domain.broker.dim_customer cs
--join ... (commented out)
join nast_customer_domain.broker.dim_customer_ear2_single_ownership_history as so
  on cs.ear2id = so.ear2_id and is_current = true
join nast_customer_domain.broker.ref_branch db
  on db.Branch_code = so.branch_code
-- join ... (commented out)
where cs.customerstatusid = 1 --active
and db.primary_business_line_id in ('62','73')

This query references two upstream tables: dim_customer and ref_branch. But when Power BI encodes it with #(lf) and DataHub’s parser reads it, the --join comment on line 8 swallows everything after it — because #(lf) doesn’t terminate the comment. DataHub sees only one table.

Why It’s Worse Than It Looks

SQL comments are everywhere in Power BI datasets. Developers comment out JOINs while debugging, leave notes like --active next to filter conditions, and disable WHERE clauses during development. This is normal SQL practice — but when Power BI encodes these queries with #(lf), every single -- comment becomes a lineage-destroying landmine.

Any Power BI dataset with even a single commented-out JOIN or WHERE clause will produce incomplete lineage. The lineage graph shows only a fraction of the upstream tables, and DataHub gives no warning. We verified this: sqlglot handles -- comments perfectly when the newlines are real — the problem is entirely that #(lf) is not decoded before parsing.

The issue has been open since August 2024. Three users have confirmed it blocks DataHub adoption in their organizations:

“This is something very important. Especially when SQL comments are very common in PBI M-queries.” — @AntonisCSt

“We also run into this issue and it is a real issue for part of our business to accept Datahub as a common catalog solution.” — @rospe

The Fix: gsp-datahub-sidecar

The gsp-datahub-sidecar solves this with a two-step approach:

Preprocessing: The sidecar decodes Power BI’s M-language escape sequences (#(lf), #(cr), #(tab)) back into real characters before any SQL parsing happens. This is the step that DataHub’s ingestion pipeline is missing.
Parsing: The decoded SQL — now with real newlines — is sent to Gudu’s General SQL Parser (GSP) via SQLFlow, which correctly handles comments, JOINs, and WHERE clauses to extract complete lineage.

We verified this with the exact query from the issue. First, the raw Power BI encoded form (all on one line with #(lf)):

select#(lf)upper(cs.customercode) as customercode#(lf), cs.ear2id ...#(lf)from nast_truckload_domain.broker.dim_customer cs#(lf)--join ...#(lf)join ...#(lf)where cs.customerstatusid = 1 --active#(lf)and ...

Without preprocessing, every SQL parser fails — GSP included. The --join on the encoded “line 8” swallows everything after it. Zero lineage extracted.

With the sidecar’s preprocessing, the #(lf) sequences become real newlines, and GSP correctly parses the result:

$ gsp-datahub-sidecar \
    --sql-file powerbi_comments.sql \
    --db-vendor dbvmssql \
    --dry-run

Extracted 2 table-level lineage relationships
  NAST_TRUCKLOAD_DOMAIN.BROKER.DIM_CUSTOMER --> DBO.CUSTOMER_BRANCHES (2 columns)
  NAST_CUSTOMER_DOMAIN.BROKER.REF_BRANCH --> DBO.CUSTOMER_BRANCHES (3 columns)
Column-level mappings extracted: 5

Both upstream tables found. All five column-level lineages recovered:

customercode ← dim_customer.customercode (through UPPER())
ear2id ← dim_customer.ear2id
branch_rollup_name ← ref_branch.branch_rollup_name
subregion1 ← ref_branch.subregion1
subregion2 ← ref_branch.subregion2

Comments correctly stripped. JOINs preserved. WHERE clause intact.

Quick Start

Three commands to recover your Power BI lineage:

# 1. Install the sidecar
pip install git+https://github.com/gudusoftware/gsp-datahub-sidecar.git

# 2. Verify with the built-in example
gsp-datahub-sidecar \
  --sql-file examples/powerbi_comments.sql \
  --db-vendor dbvmssql \
  --dry-run

# 3. Point at your DataHub instance
gsp-datahub-sidecar \
  --sql-file your_query.sql \
  --db-vendor dbvmssql \
  --datahub-url http://your-datahub:8080

The sidecar emits standard DataHub MCPs (Metadata Change Proposals) via the REST API — DataHub merges the lineage with your existing metadata automatically.

Your Power BI Connects to More Than One Database

The #(lf) encoding problem is Power BI specific, but the SQL inside your Power BI datasets comes from many different databases. One report might query SQL Server, another Snowflake, another Oracle. The sidecar handles all of them — GSP supports 20+ SQL dialects, so you just change the --db-vendor flag to match your backend.

This matters because SQL dialects differ in syntax — T-SQL’s TOP, Snowflake’s QUALIFY, Oracle’s CONNECT BY — and a parser that only handles one dialect will silently drop lineage from the others. The sidecar gives you correct lineage regardless of which database your Power BI report connects to.

Part of a Broader Pattern

The #(lf) comment issue is one example of a broader pattern: DataHub’s parser silently drops lineage on SQL constructs it can’t fully parse. We’ve documented similar gaps with BigQuery procedural SQL (DECLARE, IF/END IF, temp tables) and dbt deduplication macros (array_agg with struct unpacking).

The sidecar fills these gaps without replacing DataHub’s parser — it augments it.

Disclosure: The gsp-datahub-sidecar is built by Gudu Software, the company behind General SQL Parser and SQLFlow. The sidecar is open source under the Apache 2.0 license.

Have a SQL parsing challenge? Try SQLFlow with your own SQL, or visit the sidecar repo to get started.

The post Why Power BI SQL Comments Break Your DataHub Lineage — and How to Fix It appeared first on SQL and Data Blog.

Why Your DataHub BigQuery Lineage Silently Breaks on Procedural SQL — and How to Fix It

James — Sat, 18 Apr 2026 03:42:05 +0000

If you use DataHub to track data lineage from BigQuery, you may have noticed something unsettling: some of your most important queries produce no lineage at all. No upstream tables, no column mappings — just a silent gap in your lineage graph.

This isn’t a DataHub bug. It’s a parser limitation that affects a specific but increasingly common class of SQL: GoogleSQL procedural blocks.

This post explains exactly what breaks, why it happens, and how to recover the missing lineage using an open-source sidecar tool — without modifying your DataHub installation.

What breaks: procedural SQL in BigQuery

BigQuery’s GoogleSQL supports procedural constructs: DECLARE, IF/THEN/END IF, CALL, BEGIN...EXCEPTION...END, and CREATE TEMP TABLE inside scripting blocks. These are widely used in production BigQuery workloads for ETL orchestration, conditional logic, and error handling.

Here’s a simplified example (derived from datahub-project/datahub#11654):

DECLARE current_job_start TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
DECLARE partitions STRUCT>;

CALL `internal_project.get_partitions`(
  ('project.dataset.view_name', 'EventTimestamp'),
  partitions
);

IF ARRAY_LENGTH(partitions.dates) > 0 THEN
  CREATE OR REPLACE TEMP TABLE temp_table AS
  SELECT * EXCEPT (SnapshotTimestamp)
  FROM `project.dataset.view_name`
  WHERE (IDField, FlagField, ForeignKeyField, StartDate)
        IN UNNEST(partitions.dates)
  ORDER BY EventTimestamp;

  IF (SELECT COUNT(1) FROM temp_table_delta) > 0 THEN
    CREATE OR REPLACE TEMP TABLE final_output AS
    SELECT DISTINCT IDField, Email, UserID,
           EventTimestamp, BusinessDate
    FROM temp_table_delta
    WHERE EventTimestamp BETWEEN '2023-01-01' AND '2023-12-31';
  END IF;
END IF;

This SQL contains two clear lineage relationships:

project.dataset.view_name → temp_table (6 columns)
temp_table_delta → final_output (5 columns)

But when DataHub ingests this query, both relationships are lost.

Why it happens

DataHub’s BigQuery ingestion uses sqlglot for SQL parsing and lineage extraction. sqlglot handles standard SQL well — SELECT, INSERT, CREATE VIEW, joins, CTEs, window functions — but its BigQuery dialect does not support procedural language constructs.

When sqlglot encounters DECLARE, IF/THEN, or CALL, it falls back to an opaque Command node. The SQL inside the procedural block is not parsed, and no lineage is extracted. This isn’t a bug in sqlglot — procedural SQL is genuinely hard to parse, and full procedural language support is outside sqlglot’s current scope.

The result: DataHub silently drops lineage for any BigQuery query that uses procedural syntax. No error message, no warning in the UI — the lineage simply doesn’t appear.

This issue was first reported in October 2024 (datahub-project/datahub#11654) and remains open.

The fix: a post-processor sidecar

We built gsp-datahub-sidecar, an open-source (Apache 2.0) tool that recovers the missing lineage. It works as a post-processor alongside your existing DataHub ingestion — no changes to your DataHub installation required.

The sidecar re-parses the SQL that sqlglot couldn’t handle using Gudu SQLFlow, which supports procedural SQL natively across 20+ dialects, including BigQuery’s GoogleSQL. It then emits the recovered lineage to DataHub via the standard REST API.

DataHub ingestion (unchanged)       gsp-datahub-sidecar
  BQ audit log → sqlglot → lineage     |
                    |                   | re-parse procedural SQL
                    v                   | with Gudu SQLFlow
              "Command fallback"        v
              (lineage lost)      DataHub GMS (lineage restored)

The sidecar does not replace or interfere with sqlglot-generated lineage. It adds to it — filling the gaps that procedural SQL creates.

How to use it

Step 1: Install

pip install git+https://github.com/gudusoftware/gsp-datahub-sidecar.git

Step 2: Dry-run (no DataHub needed)

Preview the lineage that would be recovered:

gsp-datahub-sidecar \
  --sql-file examples/bigquery_procedural.sql \
  --dry-run

Output:

Processing 1 SQL statement(s) in 'anonymous' mode...
  Lineage: PROJECT.DATASET.VIEW_NAME --> TEMP_TABLE (12 columns)
  Lineage: TEMP_TABLE_DELTA --> FINAL_OUTPUT (5 columns)
[DRY RUN] Would emit 2 MCPs to http://localhost:8080

The two lineage relationships that DataHub was missing are recovered, along with 11 column-level mappings.

Step 3: Emit to DataHub

Point the sidecar at your DataHub GMS:

gsp-datahub-sidecar \
  --sql-file examples/bigquery_procedural.sql \
  --datahub-server http://localhost:8080

Step 4: Verify in DataHub

Open the DataHub UI and search for temp_table. The lineage tab now shows the upstream relationship from project.dataset.view_name, with column-level lineage arrows:

And for final_output, the lineage from temp_table_delta with all 5 column mappings:

Both table-level and column-level lineage are fully visible in DataHub’s lineage graph — recovered from procedural SQL that was previously invisible.

Three backend modes

The sidecar supports three backends, depending on your security and volume requirements:

Mode	Auth	Limit	Data location	Use case
`anonymous` (default)	None	50/day	SQL sent to api.gudusoft.com	Quick evaluation
`authenticated`	userId + secretKey	10k/month	SQL sent to api.gudusoft.com	Extended evaluation
`self_hosted`	userId + secretKey	Unlimited	SQL stays in your VPC	Production

For production use with sensitive SQL, the self-hosted SQLFlow Docker keeps all data in your infrastructure — no SQL leaves your network.

What’s next

If you’re affected by datahub-project/datahub#11654 or similar lineage gaps in your DataHub instance:

Try the dry-run with your own SQL files to see what lineage you’re currently missing
Check the GitHub repo for full documentation, configuration options, and additional examples (including Oracle stored procedures)
Sign up for the authenticated tier at docs.gudusoft.com/sign-up for higher volume evaluation

The sidecar is Apache 2.0 licensed. Gudu SQLFlow is a commercial product with a free evaluation tier. For questions or feedback, open an issue on GitHub or reach out at support@gudusoft.com.

Disclosure: This tool is built by Gudu Software, the team behind General SQL Parser and SQLFlow. We specialize in deep SQL parsing and data lineage across 20+ SQL dialects.

The post Why Your DataHub BigQuery Lineage Silently Breaks on Procedural SQL — and How to Fix It appeared first on SQL and Data Blog.

Analyzing Oracle PL/SQL Dependencies Before Your Snowflake Migration

James — Mon, 06 Apr 2026 03:59:54 +0000

Oracle-to-Snowflake is the #1 database migration path in 2026. Every major cloud consulting firm has a practice dedicated to it, and the tooling ecosystem has matured significantly: SnowConvert AI (now free), AWS Schema Conversion Tool, and Ora2pg all handle syntax translation from PL/SQL to Snowflake SQL or Snowflake Scripting.

But here is the problem: these tools focus on converting SQL syntax. They skip the critical first step of analyzing what you actually have. If you have 2,000 stored procedures in your Oracle environment, which ones call which? Which columns flow through which procedures? Which procedures contain vendor-specific constructs that no tool can auto-convert? Without answering these questions first, you are converting blind.

Why Pre-Migration Dependency Analysis Matters

Oracle PL/SQL codebases are not flat lists of independent procedures. They are interconnected systems with hidden dependencies that conversion tools do not surface:

Call graphs: Procedure A calls Procedure B, which calls Package C’s function. If you convert A but not B, A breaks in Snowflake.
Cross-procedure column flows: Data flows from a source table through a chain of procedures, each transforming specific columns. Conversion tools translate each procedure in isolation — they do not show you the end-to-end column lineage.
Package-level coupling: Oracle packages group related procedures and functions with shared state (package-level variables, cursors). Snowflake has no direct package equivalent, so each package must be decomposed — but you need to know the internal dependency structure first.
Dynamic SQL: Procedures that build and execute SQL strings at runtime. These create dependencies that are invisible to static conversion tools.

What Conversion Tools Actually Produce

To be fair, conversion tools are good at what they do — translating syntax. But it helps to understand their limitations for complex PL/SQL:

SnowConvert AI (Mobilize.Net, now free): Handles straightforward PL/SQL well. For complex constructs like CONNECT BY hierarchical queries or the MODEL clause, it generates what the documentation calls “compilable but non-functional objects” — the output compiles in Snowflake without errors, but it does not produce the same results as the Oracle original. You need to know which procedures contain these constructs before conversion so you can plan manual rework.

AWS Schema Conversion Tool (SCT): Wraps untranslatable logic in comments with action items. This is honest and helpful, but with hundreds of procedures, you end up with a long list of flagged items and no way to prioritize them by dependency or business impact.

Ora2pg: The open-source workhorse for Oracle-to-PostgreSQL (and by extension Snowflake via Postgres compatibility). It handles most DML well but struggles with PL/SQL-specific patterns like BULK COLLECT + FORALL batch processing, collection types, and pipelined table functions.

None of these tools answer the fundamental pre-migration questions: What depends on what? What is the migration order? What is the blast radius if this table changes?

PL/SQL Patterns That Migration Tools Struggle With

Let us look at concrete PL/SQL constructs that require pre-migration analysis because no tool auto-converts them reliably.

1. CONNECT BY Hierarchical Queries

Oracle’s CONNECT BY syntax for hierarchical/tree queries has no direct Snowflake equivalent. You must rewrite these as recursive CTEs:

-- Oracle: Hierarchical query with CONNECT BY
CREATE OR REPLACE PROCEDURE get_org_hierarchy(p_root_id NUMBER) AS
  CURSOR c_hierarchy IS
    SELECT employee_id, manager_id, first_name, last_name,
           LEVEL AS depth,
           SYS_CONNECT_BY_PATH(last_name, '/') AS path,
           CONNECT_BY_ISLEAF AS is_leaf
    FROM employees
    START WITH employee_id = p_root_id
    CONNECT BY PRIOR employee_id = manager_id
    ORDER SIBLINGS BY last_name;
BEGIN
  FOR rec IN c_hierarchy LOOP
    DBMS_OUTPUT.PUT_LINE(RPAD(' ', rec.depth * 2) || rec.path);
  END LOOP;
END;

This procedure uses LEVEL, SYS_CONNECT_BY_PATH, CONNECT_BY_ISLEAF, and ORDER SIBLINGS BY — all Oracle-specific. Before converting, you need to know: what other procedures call get_org_hierarchy? What tables does it read from? Are there other procedures that also query the employees table hierarchically?

2. MODEL Clause for Spreadsheet-Style Calculations

Oracle’s MODEL clause allows spreadsheet-like inter-row calculations directly in SQL. There is no Snowflake equivalent:

-- Oracle: MODEL clause for financial projections
CREATE OR REPLACE VIEW revenue_forecast AS
SELECT country, product, year, amount
FROM quarterly_revenue
MODEL
  PARTITION BY (country)
  DIMENSION BY (product, year)
  MEASURES (revenue AS amount)
  RULES (
    amount['Electronics', 2027] =
      amount['Electronics', 2026] * 1.12,
    amount['Software', 2027] =
      amount['Software', 2026] * 1.25
      + amount['Services', 2026] * 0.05
  );

The MODEL clause creates column-level dependencies that span across dimension values. In this example, the 2027 revenue for Software depends on both the 2026 Software and 2026 Services rows. Understanding these cross-row data flows is essential before attempting a manual rewrite for Snowflake.

3. BULK COLLECT + FORALL Batch Processing

High-performance PL/SQL uses BULK COLLECT and FORALL for batch operations. This pattern has no Snowflake equivalent — Snowflake Scripting processes rows differently:

-- Oracle: Batch processing with BULK COLLECT + FORALL
CREATE OR REPLACE PROCEDURE sync_customer_scores AS
  TYPE t_customer_ids IS TABLE OF customers.customer_id%TYPE;
  TYPE t_scores IS TABLE OF NUMBER;
  l_ids   t_customer_ids;
  l_scores t_scores;
BEGIN
  SELECT customer_id, calculate_score(customer_id)
  BULK COLLECT INTO l_ids, l_scores
  FROM customers
  WHERE last_updated < SYSDATE - 30;

  FORALL i IN 1..l_ids.COUNT
    UPDATE customer_scores
    SET score = l_scores(i),
        updated_at = SYSDATE
    WHERE customer_id = l_ids(i);

  COMMIT;
END;

This procedure references the customers table, the customer_scores table, and calls the calculate_score function. A conversion tool might translate the SQL syntax, but it will not tell you that calculate_score itself calls three other functions and reads from two additional tables. You need the full call graph to plan the migration.

4. Package-Level Dependencies

Oracle packages bundle related procedures, functions, and shared state. Snowflake has no package concept — you must decompose them:

-- Oracle: Package with internal dependencies
CREATE OR REPLACE PACKAGE customer_mgmt AS
  PROCEDURE onboard_customer(p_name VARCHAR2, p_email VARCHAR2);
  FUNCTION  get_risk_score(p_customer_id NUMBER) RETURN NUMBER;
  PROCEDURE update_credit_limit(p_customer_id NUMBER);
END customer_mgmt;
/

CREATE OR REPLACE PACKAGE BODY customer_mgmt AS
  -- Private package variable (shared state)
  g_default_credit_limit NUMBER := 5000;

  FUNCTION get_risk_score(p_customer_id NUMBER) RETURN NUMBER IS
    l_score NUMBER;
  BEGIN
    SELECT risk_rating INTO l_score
    FROM customer_risk_profiles
    WHERE customer_id = p_customer_id;
    RETURN l_score;
  END;

  PROCEDURE update_credit_limit(p_customer_id NUMBER) IS
    l_risk NUMBER;
  BEGIN
    l_risk := get_risk_score(p_customer_id);  -- internal call
    UPDATE customers
    SET credit_limit = g_default_credit_limit * (1 - l_risk/100)
    WHERE customer_id = p_customer_id;
  END;

  PROCEDURE onboard_customer(p_name VARCHAR2, p_email VARCHAR2) IS
    l_id NUMBER;
  BEGIN
    INSERT INTO customers(name, email, credit_limit)
    VALUES (p_name, p_email, g_default_credit_limit)
    RETURNING customer_id INTO l_id;

    -- Cross-package call
    audit_pkg.log_event('CUSTOMER_ONBOARD', l_id);
    update_credit_limit(l_id);  -- internal call
  END;
END customer_mgmt;

This package has internal function calls (update_credit_limit calls get_risk_score), cross-package calls (onboard_customer calls audit_pkg.log_event), shared state (g_default_credit_limit), and touches multiple tables (customers, customer_risk_profiles). Decomposing this for Snowflake requires understanding all these relationships first.

Using GSP to Analyze PL/SQL Before Migration

General SQL Parser (GSP) is a SQL parsing library that handles Oracle PL/SQL natively, including all the constructs shown above. Here is how it supports pre-migration analysis.

Extracting Table and Column References

GSP parses each stored procedure and extracts every table and column reference, including those inside complex constructs like CONNECT BY, MODEL, and BULK COLLECT:

Direct table references: Which tables does each procedure SELECT from, INSERT into, UPDATE, or DELETE from?
Column-level detail: Which specific columns are read and which are written, even through aliases, subqueries, and CTEs?
Vendor-specific constructs: GSP flags Oracle-specific syntax (CONNECT BY, MODEL, MERGE with error logging, FORALL) so you can identify procedures that need manual rework.

For the sync_customer_scores procedure above, GSP identifies:

Reads from: customers.customer_id, customers.last_updated
Writes to: customer_scores.score, customer_scores.updated_at
Calls: calculate_score() function
Vendor-specific: BULK COLLECT INTO, FORALL, SYSDATE, %TYPE attribute

Building the Call Graph

By parsing all procedures in your Oracle schema, GSP builds a complete call graph showing which procedures call which other procedures. For the customer_mgmt package example:

customer_mgmt.onboard_customer
  ├── audit_pkg.log_event          (cross-package)
  └── customer_mgmt.update_credit_limit
        └── customer_mgmt.get_risk_score

Tables affected:
  customers              ← INSERT (onboard), UPDATE (update_credit_limit)
  customer_risk_profiles ← SELECT (get_risk_score)
  audit_log              ← INSERT (audit_pkg.log_event)

This call graph tells you the migration order: get_risk_score has no procedure dependencies (migrate first), update_credit_limit depends on get_risk_score (migrate second), and onboard_customer depends on both plus the audit_pkg (migrate last, and audit_pkg must also be ready).

Identifying Vendor-Specific Syntax

GSP categorizes Oracle-specific constructs by migration complexity so you can estimate effort:

Oracle Construct	Snowflake Equivalent	Migration Effort
`CONNECT BY` / `START WITH`	Recursive CTE	Medium — mechanical rewrite but must verify edge cases (cycles, NOCYCLE)
`MODEL` clause	None — must redesign as window functions, CTEs, or application logic	High — requires understanding business logic intent
`BULK COLLECT` + `FORALL`	Set-based SQL or Snowflake Scripting loops	Medium — often simplifies to a single SQL statement
Package spec + body	Individual stored procedures + UDFs	High — shared state and internal dependencies must be untangled
`DBMS_OUTPUT`, `UTL_FILE`	Snowflake logging, stages	Medium — different patterns but well-documented
Pipelined table functions	UDTFs or JavaScript UDFs	High — fundamental paradigm shift

When GSP identifies that 15% of your procedures use CONNECT BY and 3% use the MODEL clause, you can estimate the manual rework effort before committing to a migration timeline.

Visualizing Lineage with SQLFlow

SQLFlow takes the parsing results from GSP and renders them as interactive lineage diagrams. For pre-migration analysis, this provides two capabilities that spreadsheets and documentation cannot match.

Column-Level Lineage Visualization

Upload your PL/SQL to SQLFlow and see column-level data flow across procedures. For the examples above, SQLFlow shows:

customers.customer_id flows into customer_scores.customer_id via sync_customer_scores
customer_risk_profiles.risk_rating flows through get_risk_score into customers.credit_limit via update_credit_limit
employees.employee_id and employees.manager_id form a self-referential hierarchy in get_org_hierarchy

This visual lineage map helps migration teams understand data flow before they touch any code.

Impact Analysis: What Breaks When a Table Changes?

The most practical use of SQLFlow during migration planning is impact analysis. Select any Oracle table — say, customers — and SQLFlow shows every procedure, view, and downstream table that depends on it. If you are migrating the customers table first, you can see that onboard_customer, update_credit_limit, and sync_customer_scores all need to be migrated or adapted at the same time.

This answers the question that migration project managers ask most often: what is the minimum set of objects I need to migrate together for a working increment?

For VS Code Users: SQL Omni

If your team works in VS Code and wants to run this analysis locally without uploading SQL to any cloud service, SQL Omni brings GSP’s parsing and lineage capabilities into VS Code as an extension. It supports Oracle PL/SQL natively and runs entirely offline — useful for organizations where SQL code cannot leave the local environment.

SQL Omni provides the same dependency analysis and column-level lineage visualization described above, directly in your editor. You can install it from the VS Code Marketplace.

Putting It Together: Analyze Before You Convert

Oracle-to-Snowflake migration tools have come a long way. SnowConvert AI being free removes a major cost barrier. AWS SCT and Ora2pg are solid open and semi-open options. But all of them work best when you know what you are feeding them.

A practical pre-migration workflow looks like this:

Inventory: Use GSP to parse all PL/SQL objects and extract table/column references, call graphs, and vendor-specific syntax.
Visualize: Use SQLFlow to render the dependency graph and column-level lineage. Identify clusters of tightly-coupled objects.
Assess: Categorize each procedure by migration complexity based on which Oracle-specific constructs it uses.
Prioritize: Use the dependency graph to determine migration order — start with leaf objects that have no downstream dependencies.
Convert: Run SnowConvert AI, AWS SCT, or Ora2pg on each batch, knowing what to expect and where manual rework is needed.
Validate: After conversion, use GSP to parse the Snowflake output and verify that the lineage is preserved.

The tools for converting Oracle SQL to Snowflake are good and getting better. The missing piece for most teams is understanding what they have before they start converting. GSP and SQLFlow fill that gap.

Try It With Your Own PL/SQL

SQLFlow online: Visit gudusoft.com, select Oracle as the dialect, and paste a stored procedure to see its column-level lineage.
GSP: Visit sqlparser.com for the parsing library that powers the analysis.
SQL Omni for VS Code: Install from the VS Code Marketplace for offline analysis in your editor.

Planning an Oracle-to-Snowflake migration? Start with analysis, not conversion. Your migration timeline will thank you.

The post Analyzing Oracle PL/SQL Dependencies Before Your Snowflake Migration appeared first on SQL and Data Blog.

GSP vs JSQLParser vs sqlglot — SQL Parser Comparison 2026

James — Mon, 06 Apr 2026 03:20:32 +0000

If you work with SQL across multiple databases, you have probably hit a wall where your parser just stops understanding your queries. Maybe it chokes on Oracle’s MODEL clause, or silently drops structure from a T-SQL stored procedure. Choosing the right SQL parser matters more than ever in 2026 — data lineage tools, SQL migration pipelines, and AI-powered SQL validation all depend on accurate parsing across dialects.

We tested three widely-used SQL parsers against 14 real-world SQL patterns spanning 6 dialects. Here is what we found.

The Contenders

General SQL Parser (GSP) 4.1.0 — A commercial Java library from Gudu Software that supports 20+ SQL dialects. It focuses on deep parsing of vendor-specific syntax including stored procedures, and provides built-in column-level lineage extraction. (Disclosure: this blog is published by Gudu Software.)

JSQLParser 5.3 — An open-source Java SQL parser (Apache 2.0 / LGPL) widely used in the Java ecosystem. It handles standard SQL well and has growing support for vendor-specific syntax. Available on GitHub and Maven Central.

sqlglot 30.2 — An open-source Python SQL parser and transpiler that has gained significant traction for SQL migration and lineage use cases. It supports reading and writing SQL across 20+ dialects and includes basic lineage extraction. Available on GitHub and PyPI.

Test Methodology

We ran 14 test cases across 6 SQL dialects: standard SQL (ANSI), Oracle, SQL Server (T-SQL), PostgreSQL, BigQuery, and Snowflake. The tests cover three categories:

Standard DML — CTEs, window functions, subqueries (the baseline every parser should handle)
Vendor-specific syntax — Oracle MODEL, MERGE with LOG ERRORS, T-SQL CROSS APPLY, BigQuery UNNEST with STRUCT, Snowflake FLATTEN and QUALIFY
Stored procedures and procedural SQL — T-SQL TRY/CATCH blocks, PL/pgSQL RETURN QUERY functions, Oracle PL/SQL BULK COLLECT/FORALL, BigQuery procedural DECLARE/IF

Versions tested: GSP 4.1.0.10, JSQLParser 5.3, sqlglot 30.2.1. All tests run on 2026-04-05.

A “PASS” means the parser correctly recognized the SQL structure and produced a usable parse tree. A “FAIL” means the parser either threw an error or could not represent the syntax meaningfully.

Results

Test Case	GSP 4.1.0	JSQLParser 5.3	sqlglot 30.2
Standard CTE with JOIN	PASS	PASS	PASS
Window function with PARTITION BY	PASS	PASS	PASS
Subquery in SELECT	PASS	PASS	PASS
Oracle CONNECT BY	PASS	PASS	PASS
Oracle MODEL clause	PASS	FAIL	FAIL
Oracle MERGE with error logging	PASS	FAIL	FAIL
T-SQL CROSS APPLY	PASS	PASS	PASS
T-SQL stored procedure (TRY/CATCH)	PASS	PASS	PASS*
PL/pgSQL RETURN QUERY function	PASS	PASS	PASS
Oracle PL/SQL BULK COLLECT	PASS	PASS	FAIL
BigQuery UNNEST with STRUCT	PASS	PASS	PASS
BigQuery procedural DECLARE/IF	PASS	FAIL	PASS
Snowflake FLATTEN	PASS	PASS	PASS
Snowflake QUALIFY	PASS	PASS	PASS
Total	14/14	11/14	11/14

*sqlglot 30.2 returned “contains unsupported syntax. Falling back to parsing as a ‘Command’” for the T-SQL stored procedure. It technically parsed without error, but the result lost structural understanding of the procedure body — TRY/CATCH blocks, variable declarations, and control flow were not represented in the parse tree. We counted this as a PASS with a caveat.

Analysis

Standard SQL: All Three Handle It Well

For standard ANSI SQL patterns — CTEs with JOINs, window functions with PARTITION BY and ORDER BY, and correlated subqueries — all three parsers performed without issue. If your SQL stays within standard syntax, any of these parsers will serve you well.

Oracle-Specific Syntax: The Dividing Line

Oracle is where the differences become clear. All three parsers handle CONNECT BY (hierarchical queries), which has been around for decades and is widely implemented. But Oracle’s MODEL clause and MERGE with LOG ERRORS are a different story.

The MODEL clause is a spreadsheet-like computation feature unique to Oracle. Both JSQLParser and sqlglot failed to parse it. Similarly, Oracle’s MERGE statement with the LOG ERRORS INTO extension — used in ETL pipelines for error handling — tripped up both open-source parsers.

GSP handled all Oracle syntax including these less common but production-critical patterns.

Stored Procedures: Where Depth Matters

Stored procedure parsing is where things get interesting for lineage and migration use cases. You cannot extract column-level lineage from a data warehouse if your parser skips the business logic inside stored procedures.

T-SQL stored procedures: GSP and JSQLParser both parsed the full procedure including TRY/CATCH blocks. sqlglot accepted the input but fell back to its “Command” mode, which means it treated the procedure body as an opaque string rather than parsing the internal structure. If you need to analyze what happens inside T-SQL procedures — variable assignments, control flow, DML within TRY blocks — sqlglot’s Command fallback will not give you that.

PL/pgSQL functions: All three handled RETURN QUERY functions. PostgreSQL’s procedural syntax has received good attention from all parser projects.

Oracle PL/SQL BULK COLLECT/FORALL: GSP and JSQLParser both parsed this pattern. sqlglot failed. BULK COLLECT is fundamental to performant Oracle PL/SQL and appears frequently in production codebases.

BigQuery Procedural SQL: sqlglot Shines

BigQuery’s procedural extensions (DECLARE, IF/ELSE, loops) are relatively new, and here sqlglot showed its strength. sqlglot parsed BigQuery procedural DECLARE/IF blocks successfully, while JSQLParser could not. This makes sense given sqlglot’s strong focus on modern cloud SQL dialects.

All three handled BigQuery’s UNNEST with STRUCT patterns, which is good news since this pattern is common in BigQuery analytics.

Snowflake: Solid Across the Board

Both FLATTEN (Snowflake’s way of unnesting semi-structured data) and QUALIFY (a Snowflake/Teradata extension for filtering window function results) were handled by all three parsers. Snowflake syntax support has matured well across the ecosystem.

When to Use Which

JSQLParser — Free, Java, Good for Standard SQL

JSQLParser is a solid choice if you are building a Java application that needs to parse standard SQL with some vendor-specific syntax. It handles the majority of real-world SQL patterns and has an active open-source community. It works well for SQL validation, basic query rewriting, and extracting table references.

Best for: Java projects, standard SQL parsing, query validation, table-level lineage where you do not need deep vendor-specific or procedural coverage.

Limitations: Oracle MODEL, MERGE LOG ERRORS, and BigQuery procedural SQL are not supported as of version 5.3.

JSQLParser on GitHub

sqlglot — Free, Python, Best for Transpilation

sqlglot has earned its reputation as the go-to tool for SQL transpilation (converting SQL between dialects). Its Python API is clean, and it includes basic lineage extraction capabilities. It handles BigQuery procedural SQL better than JSQLParser, making it a strong choice for cloud-first data teams.

Best for: Python projects, SQL transpilation/migration, BigQuery-heavy workloads, basic column-level lineage for standard SQL patterns.

Limitations: Oracle MODEL, MERGE LOG ERRORS, and PL/SQL BULK COLLECT are not supported. T-SQL stored procedures parse but lose structural detail. If you need to extract lineage from inside stored procedures, the Command fallback mode will not provide it.

sqlglot on GitHub

General SQL Parser — Commercial, Java, Deepest Dialect Coverage

GSP is the only parser in this comparison that handled all 14 test cases, including the most challenging Oracle-specific syntax and stored procedure patterns. It is designed for use cases where parsing accuracy across every dialect is non-negotiable — data lineage platforms, SQL migration tools, and compliance analysis.

Best for: Enterprise environments with multiple database dialects, stored procedure lineage extraction, Oracle-heavy workloads, column-level lineage that needs to trace through procedural code.

Trade-off: It is a commercial product, so there is a licensing cost. For teams that need the depth, the trade-off is straightforward. For simpler use cases, the open-source options may be sufficient.

General SQL Parser

All test scripts are available on GitHub: sqlparser/sqlflow_public/tree/master/demos/sql-parser-comparison-2026

Try It Yourself

All three parsers are easy to test with your own SQL:

General SQL Parser: Download from gudusoft.com or try lineage visualization with SQLFlow Online Demo
JSQLParser: Add to your Maven/Gradle project from Maven Central
sqlglot: pip install sqlglot and start parsing in Python

If you work in VS Code and want SQL parsing, lineage visualization, and ER diagrams without leaving your editor, take a look at SQL Omni — it uses GSP under the hood and runs 100% offline.

Conclusion

There is no single “best” SQL parser — the right choice depends on your language ecosystem, the SQL dialects you need to support, and how deep you need to go into vendor-specific and procedural syntax.

For standard SQL in Java, JSQLParser is capable and free. For Python-based transpilation and BigQuery work, sqlglot is hard to beat. For full dialect coverage including stored procedures and Oracle-specific syntax, GSP handles patterns that the open-source alternatives currently do not.

The test data speaks for itself. We encourage you to run your own SQL through all three and see which one fits your needs.

The post GSP vs JSQLParser vs sqlglot — SQL Parser Comparison 2026 appeared first on SQL and Data Blog.

Why Most SQL Lineage Tools Skip Stored Procedures (And What You Can Do About It)

James — Sun, 05 Apr 2026 15:00:38 +0000

If you work with enterprise databases, you already know: stored procedures are everywhere. They hold critical business logic, enforce data transformations, and move data between tables in ways that no view or SELECT statement can capture. PL/SQL packages in Oracle, T-SQL procedures in SQL Server, PL/pgSQL functions in PostgreSQL — these are the workhorses of data processing in large organizations.

And yet, when you fire up a SQL lineage tool and point it at your database, those stored procedures are often quietly ignored. The lineage diagram shows tables and views connected by SELECT and INSERT statements, but the procedural code that actually drives data flow? It is either missing entirely or represented as a black box with no column-level detail.

This is not a minor gap. In regulated industries, auditors want to know exactly how data moves from source to target. In migration projects, you need to understand what a stored procedure does before you can rewrite it. And for impact analysis, a schema change that breaks a stored procedure can cascade into production failures that no lineage tool warned you about.

Let us look at why this happens, what the evidence looks like in practice, and what a proper solution requires.

Why Tools Skip Stored Procedures

The core challenge is architectural. Most SQL parsers are built to handle declarative SQL — SELECT, INSERT, UPDATE, DELETE, CREATE VIEW. These statements describe what data to retrieve or modify. Stored procedures, on the other hand, contain procedural logic — IF/ELSE branches, loops, exception handlers, cursor operations, dynamic SQL construction, and vendor-specific control flow.

There are three main reasons tools struggle:

1. Preprocessing vs. Native Parsing

Many lineage tools use a preprocessing approach: they extract the SQL statements embedded inside a procedure body and feed them into a standard SQL parser one at a time. This loses context. A variable assigned in one statement and used three lines later in an INSERT becomes invisible. A cursor that iterates over a result set and inserts rows conditionally cannot be resolved without understanding the control flow.

Native parsing means the parser understands the procedural language itself — DECLARE blocks, BEGIN/END scoping, cursor declarations, loop constructs, exception handlers — as first-class syntax. This is significantly harder to build, which is why most tools take the shortcut.

2. Control Flow Complexity

A stored procedure is not a single data flow — it is a program with branches. Consider a PL/SQL procedure that uses BULK COLLECT to fetch 10,000 rows into a collection, then uses FORALL to insert them into a target table with an exception handler that logs failures to an error table. The lineage from source columns to target columns passes through collection variables, loop indices, and conditional branches. Tracking column-level lineage through this requires something closer to program analysis than SQL parsing.

3. Vendor-Specific Syntax

Each database vendor has its own procedural language with unique syntax. Oracle’s PL/SQL has packages, nested procedures, autonomous transactions, and pipelined table functions. SQL Server’s T-SQL has TRY/CATCH, table variables, the GO batch separator, and MERGE with OUTPUT. PostgreSQL’s PL/pgSQL has PERFORM (execute and discard), RAISE (logging), RETURN QUERY, and dollar-quoted strings. Supporting all of these at production quality means maintaining separate, deep grammars for each dialect.

Evidence From the Field

The pattern is consistent across the ecosystem. Without naming and shaming, here is what we see:

Open-source metadata platforms have acknowledged procedural SQL gaps. One major platform has had open issues for over five months related to stored procedure lineage extraction, with community members requesting support for PL/SQL and T-SQL procedure parsing that the platform’s SQL parser cannot handle.

Python-based SQL transpilers that are popular in the dbt ecosystem work well for SELECT statement analysis but struggle with procedural constructs. T-SQL’s GO batch delimiter, complex PL/SQL blocks with nested BEGIN/END, and vendor-specific syntax like Oracle’s EXECUTE IMMEDIATE are known limitations. The transpiler approach works for the 80% case (queries and views) but hits a wall with procedural code.

Large-scale migration tools at Fortune 500 companies have encountered this directly. One major technology company’s open-source migration utility (issue #2098) documented how their SQL parser could not propagate column-level lineage tokens through procedural logic, and the team discussed replacing their parser entirely due to dialect limitations in handling stored procedures.

Database migration tools — both commercial (cloud provider schema conversion tools) and open-source (Oracle-to-PostgreSQL converters) — frequently fail on specific PL/SQL patterns. BULK COLLECT combined with FORALL, which is one of the most common performance optimization patterns in Oracle, is a known pain point. The migration tool either errors out or produces incorrect target code because it does not fully parse the procedural context.

Data catalog platforms have added stored procedure support in their UI — you can view and edit procedure code — but the actual parsing for lineage extraction is not there. Showing the code is not the same as understanding the data flow within it.

What Good Stored Procedure Parsing Looks Like

To extract accurate column-level lineage from stored procedures, a parser needs to handle the full procedural language, not just the SQL statements embedded in it. Here are three real-world examples that illustrate the complexity.

PL/SQL: Package With Cursor Loops and BULK COLLECT

CREATE OR REPLACE PACKAGE BODY etl_pkg AS
  PROCEDURE load_customer_summary IS
    TYPE t_cust_rec IS RECORD (
      customer_id   customers.customer_id%TYPE,
      total_amount  NUMBER,
      order_count   NUMBER
    );
    TYPE t_cust_tab IS TABLE OF t_cust_rec;
    l_batch t_cust_tab;

    CURSOR c_active IS
      SELECT c.customer_id,
             SUM(o.amount) AS total_amount,
             COUNT(o.order_id) AS order_count
      FROM customers c
      JOIN orders o ON o.customer_id = c.customer_id
      WHERE c.status = 'ACTIVE'
      GROUP BY c.customer_id;
  BEGIN
    OPEN c_active;
    LOOP
      FETCH c_active BULK COLLECT INTO l_batch LIMIT 5000;
      EXIT WHEN l_batch.COUNT = 0;

      FORALL i IN 1..l_batch.COUNT
        MERGE INTO customer_summary cs
        USING (SELECT l_batch(i).customer_id AS cid,
                      l_batch(i).total_amount AS amt,
                      l_batch(i).order_count AS cnt FROM dual) src
        ON (cs.customer_id = src.cid)
        WHEN MATCHED THEN
          UPDATE SET cs.total_amount = src.amt,
                     cs.order_count = src.cnt,
                     cs.updated_at = SYSDATE
        WHEN NOT MATCHED THEN
          INSERT (customer_id, total_amount, order_count, updated_at)
          VALUES (src.cid, src.amt, src.cnt, SYSDATE);

      COMMIT;
    END LOOP;
    CLOSE c_active;
  EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK;
      INSERT INTO etl_error_log (procedure_name, error_msg, error_time)
      VALUES ('load_customer_summary', SQLERRM, SYSDATE);
      COMMIT;
      RAISE;
  END load_customer_summary;
END etl_pkg;

The lineage here flows from customers.customer_id, orders.amount, and orders.order_id through a cursor, into a PL/SQL collection variable, through a FORALL + MERGE statement, and finally into customer_summary.customer_id, customer_summary.total_amount, and customer_summary.order_count. The error path also creates lineage into etl_error_log. A parser that only sees the MERGE statement without understanding the cursor and collection context will miss the source columns entirely.

General SQL Parser handles this by parsing the full PL/SQL package body, resolving variable types through %TYPE and record declarations, tracking data flow through BULK COLLECT into collection variables, and connecting those variables through the FORALL loop to the final MERGE target.

T-SQL: Procedure With Temp Tables, Dynamic SQL, and TRY/CATCH

CREATE PROCEDURE dbo.usp_refresh_product_metrics
    @CategoryID INT,
    @StartDate DATE
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        -- Stage data in temp table
        SELECT p.product_id,
               p.product_name,
               p.category_id,
               SUM(s.quantity) AS total_qty,
               SUM(s.quantity * s.unit_price) AS total_revenue
        INTO #product_staging
        FROM products p
        INNER JOIN sales s ON s.product_id = p.product_id
        WHERE p.category_id = @CategoryID
          AND s.sale_date >= @StartDate
        GROUP BY p.product_id, p.product_name, p.category_id;

        -- Apply business rules
        UPDATE #product_staging
        SET total_revenue = total_revenue * 0.9
        WHERE total_qty > 1000;

        -- Merge into target
        MERGE product_metrics AS tgt
        USING #product_staging AS src
        ON tgt.product_id = src.product_id
        WHEN MATCHED THEN
            UPDATE SET tgt.product_name = src.product_name,
                       tgt.total_qty = src.total_qty,
                       tgt.total_revenue = src.total_revenue,
                       tgt.last_updated = GETDATE()
        WHEN NOT MATCHED THEN
            INSERT (product_id, product_name, category_id,
                    total_qty, total_revenue, last_updated)
            VALUES (src.product_id, src.product_name, src.category_id,
                    src.total_qty, src.total_revenue, GETDATE());

        DROP TABLE #product_staging;
    END TRY
    BEGIN CATCH
        IF OBJECT_ID('tempdb..#product_staging') IS NOT NULL
            DROP TABLE #product_staging;

        INSERT INTO dbo.error_log (proc_name, error_message, error_time)
        VALUES ('usp_refresh_product_metrics', ERROR_MESSAGE(), GETDATE());

        THROW;
    END CATCH
END
GO

This procedure creates a temp table via SELECT INTO, modifies it with an UPDATE, then merges into the final target. The column-level lineage must trace products.product_name and sales.quantity through #product_staging and into product_metrics. The GO batch delimiter, TRY/CATCH error handling, and temp table lifecycle all need to be understood. General SQL Parser resolves the temp table schema from the SELECT INTO statement, tracks the UPDATE transformation, and maps columns through the MERGE into the final target — including the error path to error_log.

PL/pgSQL: Function With PERFORM, RAISE, and RETURN QUERY

CREATE OR REPLACE FUNCTION analytics.refresh_user_engagement(
    p_days_back INTEGER DEFAULT 30
)
RETURNS TABLE (
    user_id BIGINT,
    engagement_score NUMERIC,
    segment TEXT
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_threshold NUMERIC;
    v_count INTEGER;
BEGIN
    -- Cleanup old data
    PERFORM analytics.archive_old_engagement(p_days_back * 2);

    SELECT AVG(login_count)::NUMERIC INTO v_threshold
    FROM analytics.user_activity
    WHERE activity_date >= CURRENT_DATE - p_days_back;

    INSERT INTO analytics.engagement_daily (
        user_id, engagement_score, segment, calc_date
    )
    SELECT ua.user_id,
           (ua.login_count * 0.4 + ua.page_views * 0.3
            + ua.actions_taken * 0.3)::NUMERIC AS engagement_score,
           CASE
             WHEN (ua.login_count * 0.4 + ua.page_views * 0.3
                   + ua.actions_taken * 0.3) > v_threshold THEN 'high'
             ELSE 'standard'
           END AS segment,
           CURRENT_DATE
    FROM analytics.user_activity ua
    WHERE ua.activity_date >= CURRENT_DATE - p_days_back
    ON CONFLICT (user_id, calc_date) DO UPDATE
    SET engagement_score = EXCLUDED.engagement_score,
        segment = EXCLUDED.segment;

    GET DIAGNOSTICS v_count = ROW_COUNT;
    RAISE NOTICE 'Processed % user engagement records', v_count;

    RETURN QUERY
    SELECT ed.user_id, ed.engagement_score, ed.segment
    FROM analytics.engagement_daily ed
    WHERE ed.calc_date = CURRENT_DATE;
END;
$$;

Here, PERFORM calls another function (with side effects), RAISE NOTICE is PostgreSQL-specific logging, and RETURN QUERY makes this a table-returning function whose output can feed into other queries. The lineage traces user_activity.login_count, user_activity.page_views, and user_activity.actions_taken through computed expressions into engagement_daily.engagement_score and engagement_daily.segment, and also through the function’s return type. General SQL Parser understands all of these PL/pgSQL constructs natively.

Column-Level Lineage Through Stored Procedures, Not Around Them

The key distinction is whether a tool extracts lineage through stored procedures or just around them. Working around them means treating the procedure as a black box — you know it reads from table A and writes to table B, but you do not know which specific columns are involved or how they are transformed. Working through them means the parser understands the procedural logic well enough to trace individual columns from source to target, including through variables, cursors, collections, temp tables, and control flow branches.

General SQL Parser takes the “through” approach. It parses the full procedural language for each supported dialect, builds an internal representation of variable declarations, assignments, and data flow, and resolves column-level lineage across procedural boundaries. When you feed it a PL/SQL package or a T-SQL stored procedure, the output is not just “this procedure touches these tables” — it is a complete column-to-column lineage map that shows exactly how data flows from source to target.

You can see this in action with SQLFlow, which visualizes the lineage extracted by General SQL Parser. Upload a stored procedure and you will see the column-level connections rendered as a data flow diagram, with each transformation step visible.

Try It Yourself

If stored procedure lineage is a gap in your current tooling, here is what you can do:

Visualize your procedures: Upload a stored procedure to the SQLFlow online demo and see the column-level lineage diagram it produces.
Test your edge cases: Try the patterns that break other tools — BULK COLLECT + FORALL, temp tables in T-SQL, PL/pgSQL RETURN QUERY. These are the cases where parser quality shows.
Analyze in your IDE: If you use VS Code, Gudu SQL Omni brings column-level lineage, ER diagrams, and impact analysis directly into your editor — 100% offline, 34 dialects, free trial. Install from the VS Code Marketplace.
Evaluate against your codebase: General SQL Parser supports Oracle PL/SQL, SQL Server T-SQL, PostgreSQL PL/pgSQL, MySQL stored procedures, and DB2 SQL PL. Details and downloads are at gudusoft.com.

Stored procedures are not going away. If anything, the push toward database-side logic for performance and security is growing. A lineage solution that skips them is a lineage solution with blind spots — and in an era of data governance and regulatory compliance, blind spots are not acceptable.

The post Why Most SQL Lineage Tools Skip Stored Procedures (And What You Can Do About It) appeared first on SQL and Data Blog.

Understanding SQLFlow Job: Automate Your Data Lineage Analysis at Scale

James — Thu, 05 Feb 2026 11:02:29 +0000

When dealing with large-scale data environments, analyzing data lineage manually becomes impractical. You might have hundreds of SQL files across different repositories, databases, and ETL processes. This is where SQLFlow Job comes into play – a powerful feature that enables batch processing of SQL files for automated data lineage discovery.

In this article, we’ll explore two ways to create and manage SQLFlow Jobs: through the intuitive Web UI and programmatically via the REST API.

Part 1: Creating Jobs Through the SQLFlow UI

The SQLFlow web interface provides a user-friendly way to create and manage jobs without writing any code. This is perfect for ad-hoc analysis, exploration, and users who prefer a visual approach.

Step 1: Access the Job Creation Panel

After logging into SQLFlow, navigate to the Job List section and click the Job Creation button.

Step 2: Choose Your Job Source

SQLFlow supports multiple data sources for job creation:

Upload File: Upload SQL files or a ZIP archive containing multiple SQL files (up to 200MB). This is ideal for analyzing DDL scripts, stored procedures, or any SQL codebase.
From Database: Connect directly to your database server. SQLFlow will read metadata and SQL objects to generate lineage automatically.
Upload File + Database Metadata: Combine uploaded SQL files with database metadata to resolve column ambiguities for more accurate lineage.
dbt: Import lineage from your dbt transformation projects using manifest.json and catalog.json files.
Snowflake Query History: Analyze lineage from Snowflake query history automatically.
Redshift Log: Parse Amazon Redshift logs (.gz or .zip format) for lineage discovery.

Step 3: Configure Job Parameters

Set up your job with these options:

Database Vendor: Select your SQL dialect (Oracle, SQL Server, MySQL, PostgreSQL, Snowflake, etc.)
Default Server/Database/Schema: Set default values for unqualified object references
Job Type: Choose between regular job or summary mode for large datasets
Advanced Settings: Filter specific schemas, stored procedures, or views to include/exclude

Step 4: View and Manage Results

Once the job completes, you can:

Lineage Overview: View table-level lineage across your entire database
Lineage Detail: Drill down into column-level relationships
Job Info: Check metadata like creation time, execution time, and job status
Export: Download results in JSON, CSV, or GraphML formats

Part 2: Creating Jobs Through the REST API

For automation, CI/CD integration, and programmatic access, SQLFlow provides a comprehensive REST API. This approach is ideal for scheduled lineage scans, integration with data pipelines, and enterprise automation scenarios.

API Authentication

First, obtain your API credentials (userId and token) from your SQLFlow account settings. See the prerequisites documentation for details.

Submit a Job

Use the /submitUserJob endpoint to upload SQL files:

curl -X POST "https://api.gudusoft.com/gspLive_backend/sqlflow/job/submitUserJob" \
  -H "Content-Type:multipart/form-data" \
  -F "userId=YOUR_USER_ID" \
  -F "token=YOUR_TOKEN" \
  -F "sqlfiles=@/path/to/queries.sql" \
  -F "sqlfiles=@/path/to/procedures.sql" \
  -F "dbvendor=dbvoracle" \
  -F "jobName=my-lineage-analysis"

The API returns a jobId for tracking:

{
  "code": 200,
  "data": {
    "jobId": "c359aef4bd9641d697732422debd8055",
    "jobName": "my-lineage-analysis",
    "status": "create"
  }
}

Check Job Status

Monitor progress with /displayUserJobSummary:

curl -X POST "https://api.gudusoft.com/gspLive_backend/sqlflow/job/displayUserJobSummary" \
  -F "jobId=c359aef4bd9641d697732422debd8055" \
  -F "userId=YOUR_USER_ID" \
  -F "token=YOUR_TOKEN"

When status becomes success, your lineage is ready.

Export Lineage Results

Download results in your preferred format:

# JSON format
curl -X POST ".../exportLineageAsJson" -F "jobId=..." --output lineage.json

# CSV format  
curl -X POST ".../exportFullLineageAsCsv" -F "jobId=..." --output lineage.csv

# GraphML format (for yEd visualization)
curl -X POST ".../exportLineageAsGraphml" -F "jobId=..." --output lineage.graphml

Incremental Jobs

For evolving codebases, use incremental analysis with /submitPersistJob:

curl -X POST ".../submitPersistJob" \
  -F "incremental=true" \
  -F "jobId=EXISTING_JOB_ID" \
  -F "sqlfiles=@/path/to/updated_queries.sql" \
  ...

This updates the existing lineage graph without re-analyzing everything.

When to Use UI vs API

Scenario	Recommended Approach
Ad-hoc analysis and exploration	UI
One-time database lineage scan	UI
CI/CD pipeline integration	API
Scheduled nightly lineage scans	API
Integration with data catalog tools	API
Quick visualization needs	UI

Real-World Use Cases

Data Governance Automation: Schedule nightly API jobs to scan your data warehouse SQL and detect lineage changes
CI/CD Integration: Trigger lineage analysis when SQL changes are committed to your repository
Migration Projects: Use the UI to batch analyze legacy stored procedures before modernization
Compliance Audits: Generate lineage reports in CSV format for regulatory requirements
Impact Analysis: Before modifying a table, understand all downstream dependencies

Getting Started

Ready to try SQLFlow Job? Here’s how to get started:

Sign up at sqlflow.gudusoft.com for a free trial
Try the UI: Upload a SQL file and explore the interactive lineage visualization
Explore the API: Check the Job API documentation for code samples

Whether you prefer the visual approach or need full automation, SQLFlow Job provides the flexibility to handle data lineage analysis at any scale.

Try SQLFlow today – your data lineage journey starts here.

The post Understanding SQLFlow Job: Automate Your Data Lineage Analysis at Scale appeared first on SQL and Data Blog.

SQL and Data Blog

Why DataHub Loses Column-Level Lineage on dbt Deduplication Macros — and How to Recover It

What dbt-utils is actually doing on BigQuery

What lineage is recoverable from the SQL alone

Where DataHub’s column walker stops short

Workaround: a post-processor sidecar

Try it in three commands

Backend modes

What’s next

BigQuery Column-Level Lineage Without Metadata: Inferring STRUCT and ARRAY Types from SQL Alone

Why BigQuery lineage is harder than “normal” SQL lineage

Pattern 1: ARRAY(SELECT AS STRUCT ...) builds a nested column

What lineage can we extract without metadata?

What a generic parser typically gets wrong

Pattern 2: array_agg(row)[OFFSET(0)] unique followed by unique.*

What lineage can we extract without metadata?

Why this pattern defeats most generic parsers

What a BigQuery-aware parser needs to know

A simple memory aid for reading BigQuery SQL

How Gudu’s SQL engine handles it

Try it yourself

Takeaways

OpenMetadata + MSSQL Stored Procedures: Why Your Lineage Is Silently Empty — and How to Fix It

Why OpenMetadata Can’t Parse Stored Procedures

Three Failure Patterns, All Solved

Pattern A: CREATE PROCEDURE with BEGIN/END (Issues #16737, #25299, #17586)

Pattern B: Temp Table Multi-Hop Chains (Issue #25299)

Pattern C: Square Bracket Cross-Database Identifiers (Issue #16424)

A Real-World Example: Everything Combined

Summary: All Patterns Solved

How It Works

Try It

Related OpenMetadata Issues

Also Affected by SQL Parsing Gaps?

DataHub #15327: Why Power BI M-Language Queries Produce Zero Lineage — 4-Query Deep Analysis

Why DataHub Can’t Parse These Queries

Two Patterns, Both Solved

Pattern A: Pure M Navigation (Queries 1 & 3)

Query 1: Navigation to a Snowflake View

Query 3: Navigation to a Snowflake Table + Column Renaming

Pattern B: Embedded SQL (Queries 2 & 4)

Query 2: Simple SELECT with #(lf) escape

Query 4: Complex Snowflake SQL with GROUP BY ALL, quoted aliases, expressions

Summary: All 4 Queries Solved

How It Works

Try It

Related Issues

Why Power BI SQL Comments Break Your DataHub Lineage — and How to Fix It

Background: How Power BI Handles SQL

The Problem: Why Comments Break Lineage

Why It’s Worse Than It Looks

The Fix: gsp-datahub-sidecar

Quick Start

Your Power BI Connects to More Than One Database

Part of a Broader Pattern

Why Your DataHub BigQuery Lineage Silently Breaks on Procedural SQL — and How to Fix It

What breaks: procedural SQL in BigQuery

Why it happens

The fix: a post-processor sidecar

How to use it

Step 1: Install

Step 2: Dry-run (no DataHub needed)

Step 3: Emit to DataHub

Step 4: Verify in DataHub

Three backend modes

What’s next

Analyzing Oracle PL/SQL Dependencies Before Your Snowflake Migration

Why Pre-Migration Dependency Analysis Matters

What Conversion Tools Actually Produce

PL/SQL Patterns That Migration Tools Struggle With

1. CONNECT BY Hierarchical Queries

2. MODEL Clause for Spreadsheet-Style Calculations

3. BULK COLLECT + FORALL Batch Processing

4. Package-Level Dependencies

Using GSP to Analyze PL/SQL Before Migration

Extracting Table and Column References

Building the Call Graph

Identifying Vendor-Specific Syntax

Visualizing Lineage with SQLFlow

Column-Level Lineage Visualization

Pattern 1: `ARRAY(SELECT AS STRUCT ...)` builds a nested column

Pattern 2: `array_agg(row)[OFFSET(0)] unique` followed by `unique.*`