Vertica Data Lineage: Column-Level Lineage from Projections and SQL

linhagem de dados Vertica is the column-level map of how data flows through your Vertica SQL: which source columns feed each target table, projection, and report, and which joins, filters, aggregates, and analytic functions transform the data along the way. Gudu SQLFlow builds that map automatically with a dedicated Vertica dialect parser: it reads your SQL statically, resolves every column reference, and renders an interactive lineage diagram without ever touching the rows in your tables.

Why Vertica lineage is a SQL parsing problem

Vertica is unusual among analytical databases in how much of its physical design is expressed as SQL. A projection, Vertica’s core storage structure, is defined by a CREATE PROJECTION ... AS SELECT statement. A flattened table denormalizes dimension attributes through DEFAULT USING e SET USING column expressions that are themselves queries against other tables. Gap-filling and interpolation over event data happens in the TIMESERIES clause, right inside a SELECIONAR.

That design has a consequence for lineage: if a tool can genuinely parse Vertica’s dialect, it can recover the data flow: not just through your ETL scripts and views, but through the storage layer itself. If it can’t, it silently drops exactly the statements that make a Vertica estate a Vertica estate. Generic ANSI-grammar parsers choke on TIMESERIES, treat projection definitions as noise, and never see the query hidden inside a SET USING expression.

SQLFlow ships a dialect-specific Vertica parser, one of 39 dialect parsers, each with its own grammar rather than a shared ANSI approximation. It handles projections defined by SELECTs, flattened-table expressions, and TIMESERIES clauses as first-class SQL, and it resolves column references through CTEs, subqueries, views, and SELECIONE * expansão.

Column-level lineage from a real Vertica statement

Consider a typical Vertica pattern: an INSERIR ... SELECIONAR that aggregates fact data and ranks it with an analytic function.

INSERT INTO mart.region_daily_rank
    (sale_date, region_name, total_amount, region_rank)
SELECT
    s.sale_date,
    r.region_name,
    SUM(s.amount)                                            AS total_amount,
    RANK() OVER (PARTITION BY s.sale_date
                 ORDER BY SUM(s.amount) DESC)                AS region_rank
FROM   fact.sales   s
JOIN   dim.regions  r ON s.region_id = r.region_id
WHERE  s.sale_date >= '2026-01-01'
GROUP  BY s.sale_date, r.region_name;

For every output column, SQLFlow identifies the source columns that feed it and the functions they pass through:

• region_daily_rank.total_amount é alimentado diretamente por fact.sales.amount através SOMA().
• region_daily_rank.region_rank é derivado de sales.amount via the RANK() SOBRE window, with sales.sale_date shaping the partition.
• sales.region_id e regions.region_id never appear in the output, but they determine which rows meet through the join. SQLFlow records them as linhagem indireta, along with the ONDE filter on sale_date e o AGRUPAR POR keys.

That direct-versus-indirect distinction matters more than it first appears. If someone changes the semantics of id_da_região, no dataflow-only lineage tool will flag region_daily_rank as affected — yet every number in it changes. SQLFlow models indirect influence (columns used in ONDE, JUNTAR, e AGRUPAR POR conditions and inside aggregates) as a separate, toggleable relationship type in the diagram. Most competing tools do not make this distinction at all.

Vertica constructs the dialect parser covers

Vertica construct	Where the lineage hides
Projections (CREATE PROJECTION ... AS SELECT)	The defining SELECT maps anchor-table columns to the projection’s columns, including sort and segmentation choices expressed in SQL.
Flattened tables	DEFAULT USING / SET USING expressions embed queries against dimension tables directly in the DDL — denormalization logic invisible to tools that only scan DML.
TIMESERIES clause	Gap-filling and interpolation rewrite the shape of the output; the clause is parsed as SQL, so interpolated columns trace back to their true sources.
Analytic functions	RANK, ROW_NUMBER, LAG, and window aggregates: SQLFlow tracks both the function inputs and the partition/order columns that shape the result.
Views, CTEs, subqueries, SELECIONE *	Column references are resolved through every layer, so a report column traces to physical source columns rather than stopping at the first view boundary.

Audit and migration planning for aging Vertica estates

Most Vertica installations are not new. They carry a decade or more of accumulated SQL: nightly INSERIR ... SELECIONAR loads, reporting views layered on views, projections tuned by engineers who have since left. Two situations force teams to finally map all of it.

Audit and compliance. Regulators and internal auditors ask column-level questions: which source fields flow into this regulated figure, and through what transformations? Answering from tribal knowledge does not survive scrutiny. SQLFlow produces the provenance chain for any output column: walk backward from the number in the report through every view and load script to the physical source columns, and export the evidence as JSON, CSV, or PNG.

Migration planning. Teams moving Vertica workloads to Snowflake, Databricks, or BigQuery need the true dependency graph before they can sequence the move: which tables are load-bearing, which views are dead, which downstream jobs consume each target. Because SQLFlow parses all of these dialects with the same engine, you can map the Vertica estate before migration and verify the rebuilt lineage on the target platform afterward, and confirm nothing was orphaned in between. The same applies to consolidations from other MPP platforms; see the companion pages on linhagem de dados Greenplum e linhagem de dados Netezza.

How to generate Vertica data lineage from SQL

1. Colete o SQL. Paste individual queries, upload script files, pull schema metadata live over JDBC, or use the Grabit/Ingestor de fluxo de SQL utilities to extract metadata in bulk. For a one-off question, pasting a single statement into the free visualizer is enough.
2. Select the Vertica dialect. SQLFlow parses the SQL with its Vertica-specific grammar and runs data-flow analysis over the resulting semantic model. The engine underneath is the Analisador SQL geral, developed commercially since the mid-2000s and validated against roughly 13,600 per-dialect SQL test fixtures.
3. Explorar e exportar. Drill into the interactive diagram, trace any column upstream or downstream, toggle indirect lineage on or off, and export the graph as JSON, CSV, or PNG, or query it through the REST API from your own tooling.

Since version 8.2.3 you can also interrogate the graph in plain English, with questions like “which outputs depend on sales.amount?”, and every table and column the AI cites is validated against the analyzed lineage graph before it is shown.

Where to run it

SQLFlow Cloud has a free tier for pasting queries in the browser; premium is $49.99/month. Vertica estates in regulated industries typically choose SQLFlow no local: Docker or Kubernetes inside your own network, air-gap capable, at $500/month or $4,800 one-time per selected database type, installable on two servers. Either way, the analysis is static: SQLFlow reads SQL text and schema metadata, never the data in your tables.

At enterprise scale, SQLFlow batch-scans estates of 100+ databases and over a million columns, keeps a persistent lineage repository with incremental scans, and exports to DataHub, Microsoft Purview, and OpenMetadata, so Vertica lineage lands in the catalog your organization already runs.

E quanto às ferramentas de linhagem de código aberto?

Analisadores de código aberto, como linhagem sql e sqlglot are genuinely useful for extracting lineage from standard SELECT and INSERT statements, and if that is all your estate contains they may be enough. On Vertica specifically, four things are worth checking before you commit: whether the tool parses dialect constructs like TIMESERIES and projection DDL, whether it resolves columns through deep view stacks using schema metadata, whether it models indirect lineage through join and filter conditions, and whether it renders a navigable diagram over thousands of scripts rather than emitting tuples per query. A fair evaluation is cheap: take your most tangled Vertica load script, run it through both, and compare what each one actually resolves.

Perguntas frequentes

Does SQLFlow understand Vertica projections?

Yes. A Vertica projection is defined by a SELECT statement, and SQLFlow’s Vertica dialect parser reads that definition as SQL — so lineage flows from the anchor table’s columns through the projection like any other query-defined object.

Can it parse the TIMESERIES clause and analytic functions?

Yes. The Vertica parser handles the TIMESERIES clause and window/analytic functions as native grammar, not as unparseable extensions. Output columns produced by RANK() OVER (...) or interpolation trace back to their real source columns, with partition and order columns captured as indirect lineage.

Do I have to connect SQLFlow to my Vertica cluster?

No. You can paste SQL or upload script files and get lineage with no database connection at all. Connecting over JDBC or using the Grabit metadata extractor adds schema metadata, which improves resolution of SELECIONE * and unqualified column names across large estates.

Does SQLFlow read the data in my Vertica tables?

Never. SQLFlow performs static analysis of SQL code and optionally reads table and column definitions. It does not query row data. With the On-Premise edition, even the SQL text stays inside your network.

Can SQLFlow help us plan a migration off Vertica?

Yes. Map the full column-level dependency graph of the Vertica estate first, use it to sequence what moves and identify dead objects, then re-run lineage on the target platform — Snowflake, Databricks, BigQuery, and 35 other dialects are supported by the same engine — to verify the rebuild.

Qual o custo do SQLFlow?

SQLFlow Cloud starts free; premium is $49.99/month. On-Premise is $500/month or $4,800 one-time per selected database type, on up to two servers, with additional database types at $100/month or $1,000 one-time each. Details on the página de preços.