Data lineage reconstruction
The data lineage the Google Dataform connector publishes, at table and column level, and how to read it and troubleshoot it in Blindata
The connector publishes lineage as data flows between the tables in your Blindata catalog: from a source table or column to the one a Dataform action builds from it. Each flow names the Dataform action that moved the data, so you can see not only where data comes from but which pipeline step produces it.
Lineage comes from two sources that work together:
- Table level comes from the dependency graph of the deployed Dataform compilation. It is published for every action that reads a table, whatever its SQL looks like.
- Column level comes from Blindata’s SQL lineage. The connector reconstructs the SQL that Dataform runs and loads it into Blindata as routines, which Blindata analyzes into a query bucket, where its parser finds which source column feeds which target column.
Because both land on the same table entities, the flows join the lineage that other jobs publish: a BigQuery crawl, Power BI, or query history.
The example below uses the repository ecommerce-demo described in Dataform to Blindata catalog
, built on the BigQuery public dataset bigquery-public-data.thelook_ecommerce. The view stg_customers reads the declared table users; the connector publishes the table flow, and the SQL of the view gives the column flows.
What a data flow tells you
Open a data flow published by the connector to see:
| Field | What it tells you | Example |
|---|---|---|
| From / To | The source and target table | thelook_ecommerce.users → staging.stg_customers |
| Process | The Dataform action that builds the target, by its stable identity dataformActionId |
dataform:acme-analytics/us/ecommerce-demo:acme-analytics.staging.stg_customers |
| Evidence | How precise the flow is. Flows from the Dataform graph are TABLE. |
evidence=TABLE |
| Origin | Where the flow comes from | dataform graph |
| Environments | The release configurations in which the flow was seen | production |
| Routine result | What SQL lineage made of the action’s SQL: see Routine results | ROUTINE_COLUMN_LINEAGE |
| Gap | Why some detail is missing, if any: see Why is my lineage missing? | gap=QLP_ANALYSIS_ERROR |
Descriptions contain object names only. The SQL is never copied into a flow.
The column flows are created by Blindata’s SQL lineage from the statements it extracts from the routines, in the environment’s bucket, so they are listed in the query parser section of Blindata, under the bucket, as well as on the data flows page. The routines themselves, with the file that defines them, are in the routines list: see Find the routines of a source file .
Table lineage from the Dataform graph
For every enabled action, the connector looks at each dependency in the compiled graph and keeps it as a flow only when it is a data dependency: the dependency is a table the action’s SQL actually reads.
| Dependency in the graph | What the connector does | Counted as |
|---|---|---|
| A table the action’s SQL reads | A TABLE flow, with the action as process |
none |
A dependency declared only to order execution (dependencies: [...] with no read) |
Not a flow | ORDERING_ONLY_DEPENDENCY |
| An assertion | Not a flow | ASSERTION_DEPENDENCY |
| A dependency of an operation that builds no table | Not a flow: there is no table to link to | OPERATIONS_DEPENDENCY |
| An operation that created a BigQuery routine | Not a flow: a routine is not a data source | ROUTINE_DEPENDENCY |
| A dependency that is not an action of the same compilation | Not a flow | UNMAPPED_EXTERNAL_ENDPOINT |
| Any dependency of a disabled action | Not a flow | ACTION_DISABLED |
| Any dependency of a notebook | Not a flow | NOTEBOOK_CODE_NOT_ANALYZED |
A flow is published only when both tables are in the Blindata catalog. The connector catalogs the tables first, in the same run, so this holds unless a table could not be catalogued (TARGET_NOT_CATALOGUED).
The same dependency found in several environments of a repository is one flow that lists the environments.
Column lineage from SQL
With capabilities.columnLineage
on (the default), the connector loads the SQL that Dataform executes into Blindata as routines and asks Blindata to analyze them into a query bucket. It does not read the SQL scripts that Dataform ran: it rebuilds the SQL from the compiled action. Blindata’s parser then finds which source column feeds which target column, and the column flows are created from the statements it extracts.
Every SQL is a routine
Every SQL text that Dataform runs for an action becomes one routine of the Google Dataform System, whether or not it can be analyzed. The routine holds the SQL as its definition and carries the file that defines the action (see System routines
for the name and the properties). An incremental table gets two routines, one for each of its queries, both written as CREATE OR REPLACE TABLE so that the parser links columns by name. A filter on the table itself in the incremental query, such as WHERE ts > (SELECT MAX(ts) FROM self), adds no flow.
| Dataform SQL | Routine definition | Analyzed for columns |
|---|---|---|
| Table, incremental table | CREATE OR REPLACE TABLE `project.dataset.table` AS followed by the action’s query |
Yes |
| Incremental table: incremental query | The same, followed by the incremental query, suffix #incremental. Not created when it is the same as the full-refresh query. |
Yes |
| View | CREATE OR REPLACE VIEW `project.dataset.view` AS followed by the query |
Yes |
| Materialized view | CREATE OR REPLACE MATERIALIZED VIEW ... followed by the query |
Yes |
Operations: each query (plain SQL such as INSERT, MERGE, UPDATE, or a script) |
The query as written, suffix #<i> (i starts at 0) |
Yes |
| Pre and post operations of a table or view | Each one as written, suffix #pre<i> or #post<i> (i starts at 0) |
Yes |
| Data preparation written in SQL (pipe syntax) | CREATE OR REPLACE TABLE `project.dataset.table` AS followed by the preparation’s SQL, whatever its load mode |
Yes |
| Assertion | The assertion’s select, as written | No: it carries no lineage |
| Operations that create a temporary function | The query as written, suffix #<i> |
No |
| Operations that create a persistent procedure or function | No routine on the Dataform System: it is cataloged as a BigQuery routine | No |
| Data preparation in YAML, notebooks, declarations, BigQuery unit tests | No routine: table flows only, where the graph gives them | No |
Comments written with # are turned into -- comments in the routine definition, because the extractor does not read # comments. The routine is created for every action, enabled or disabled, in every environment read.
A routine is analyzed only when all of these hold: column lineage is on, the action is enabled, the SQL can carry lineage (the rows marked Yes above), the checks below pass, and the run is not a dry run. Every other routine is cataloged only: it is uploaded and kept up to date, and never analyzed. That includes every routine when columnLineage is off, and routines of disabled actions.
A routine is held back from the analysis (verdict NOT_POSTED) when the columns of the table it builds and of every table it reads are not in the catalog with their position: the parser needs them to resolve SELECT * and to link columns. The action keeps its table flows and reports COLUMNS_UNAVAILABLE. This is why BigQuery metadata
matters for column lineage.
Query buckets
The connector creates one query bucket for each environment (one release configuration of one repository), named by queryBucketPattern
, by default <projectId>/<repository>/<environment> (Dataform). For example: acme-analytics/ecommerce-demo/production (Dataform).
A bucket is configured by the job:
- its Systems are the BigQuery project System of the repository and every other project System that the environment’s tables belong to, so the parser can resolve
dataset.tableacross projects; - its data flow level is field, so flows are created from column to column, without FDR flows;
- it analyzes no routines of its own: the job asks for the analysis of each routine explicitly, into the bucket of its environment;
- it has pre-processing rules for BigQuery syntax.
Leave the Dataform buckets as they are. You do not create them, and the job rewrites their rules on its next run if they differ. The rules are the Google BigQuery preset of the bucket editor, 32 rules in a form that gives the same result in whatever order Blindata applies them. See BigQuery query pre-processing for what they cover.
| Group | What the rules do |
|---|---|
| Comments and blank lines | Remove comments outside quoted text, the blank lines they leave, and a trailing comma before FROM |
| Names | Turn backticked names (`project.dataset.table`) into quoted names; remove a hyphenated project id from unquoted names |
MERGE and DELETE |
Accept MERGE without INTO, BY TARGET / BY SOURCE clauses and DELETE without FROM |
| Expressions, literals, table DDL | Handle named arguments, INTERVAL and BIGNUMERIC literals, UNNEST … WITH OFFSET, complex column types, OPTIONS, PARTITION BY expressions and similar forms |
| Statements with a different shape | Read CLONE, CREATE TABLE FUNCTION, EXPORT DATA, CREATE MODEL and ASSERT through the query they contain |
| Statements without lineage | End as IGNORED: ALTER, CALL, CREATE FUNCTION, LOAD DATA and similar |
You can see the effect on any statement that the analysis creates in the bucket: its Pre-processed tab compares the stored query with the text the parser receives.
The parser reads the schema and the name of a table, not its project. When two of a bucket’s project Systems hold a table with the same dataset and name, the connector does not analyze the routines that touch it, to avoid linking the wrong one (AMBIGUOUS_TABLE_NAME).
Routine results
After each analysis, Blindata inserts the statements it extracted from the routine into the bucket, a moment later, and the connector reports the outcome as a verdict. The worst verdict of an action decides the gap on its table flows.
- The result shows one run late. The first run that sees a new or changed routine shows
ROUTINE_PENDING. Later runs showROUTINE_COLUMN_LINEAGE, or the reason the analysis failed. The first run after an upgrade from a version that posted query statements showsROUTINE_PENDINGon every analyzed action. - A routine that has not changed is not analyzed again. A routine that moves to another bucket is reset and analyzed again.
- A routine the analysis cannot extract anything from gives
NO_STATEMENT, with the gapSCRIPT_NOT_ANALYZEDfor a scripted operations query andQUERY_NOT_ANALYZEDfor any other SQL. - A routine that contains
EXECUTE IMMEDIATEalso countsDYNAMIC_CODE: the SQL inside the string is never analyzed.
| Verdict | What happened | Gap on the table flow |
|---|---|---|
ROUTINE_COLUMN_LINEAGE |
The analysis found the column flows. | none, or SOURCE_NOT_CATALOGUED if part of it could not be linked, or QLP_ANALYSIS_ERROR if another statement of the routine failed |
ROUTINE_PENDING |
The routine was analyzed in this run. Its statements are inserted a moment later. | none |
FAILED |
The analysis or the parser reported an error on the SQL. | QLP_ANALYSIS_ERROR, or SCRIPT_NOT_ANALYZED / QUERY_NOT_ANALYZED when the routine itself failed |
NOT_POSTED |
The routine was uploaded but not analyzed: a check held it back. | COLUMNS_UNAVAILABLE or AMBIGUOUS_TABLE_NAME |
NO_STATEMENT |
The action has no SQL to analyze, or the analysis found no statement in its routine. | QUERY_NOT_ANALYZED or SCRIPT_NOT_ANALYZED |
PLANNED |
Dry run: the routine would be analyzed. | none |
Cataloged-only routines have no verdict and no gap. With columnLineage off, no SQL has a verdict or a gap.
The verdicts are counted in the run report (queryStatements.byVerdict, and per environment), and the reasons for NOT_POSTED in queryStatements.notPostedByGap. The routines themselves are counted in systemRoutines, by SQL kind.
Keeping lineage up to date
- Each run uploads every routine again, with its properties, so the catalog follows your pipelines as they change: a file that moves updates
dataformFilePathwithout touching the analysis. A routine whose definition did not change is not analyzed again. - For each environment that the run read completely, the connector removes the routines of that environment that the run no longer plans, and the statements of its bucket that do not belong to a routine planned in this run, with the flows they created. So a view you delete or rewrite does not leave stale column flows behind. A routine is reset before it is deleted, so no statement or flow is left orphaned.
- An environment that could not be read completely (a compilation with errors, a graph that failed to load, a table without metadata) is not reconciled: its statements stay as they were until a run reads it completely.
- Routines that were held back only because the columns of a table are not known yet are still uploaded, and their statements are kept, so lineage does not disappear while a permission is fixed.
- One-time migration. Earlier versions posted the SQL of relations and operations as query statements. The first run after the upgrade deletes those statements, with their column flows, and the routines’ statements replace them when the analysis completes. Column lineage therefore has a short gap, until the second run reports the results. Table flows are not affected.
- Table flows that no longer exist stay in Blindata until a cleanup
with
dataFlowsDeletionEnabledremoves the flows of yourdataFlowScopethat were not updated for the number of days you choose. - A disabled action keeps its entity and publishes no new flows.
Why is my lineage missing?
The run report lists every gap, with a count per reason and per environment (gapsByReason). The reasons below can appear in this release. Start with the reason, then open the data flow or the query bucket.
Table flows missing
| Reason | What it means | What to do |
|---|---|---|
TARGET_NOT_CATALOGUED |
One end of the flow is not in the Blindata catalog, so the flow cannot be anchored. | Check that the table was cataloged in this run (see METADATA_UNAVAILABLE), and that its project has a System. |
UNMAPPED_EXTERNAL_ENDPOINT |
The action depends on something that is not an action of the same compilation (for example a table written outside Dataform). | Usually nothing: the dependency points outside the compilation, and no flow is published for it. |
ORDERING_ONLY_DEPENDENCY |
The dependency only orders execution: the SQL does not read the table. | Expected. If the SQL does read it with a name Dataform did not resolve, use ref() so the graph shows the read. |
ASSERTION_DEPENDENCY |
The dependency is an assertion. | Expected. Assertions become quality checks instead. |
OPERATIONS_DEPENDENCY |
The action is an operation that builds no table. | Expected. Give the operation an output table if you want a table flow. Its SQL can still give column flows. |
ROUTINE_DEPENDENCY |
The action depends on an operation that creates a routine. | Expected: a routine is not a data source. |
ACTION_DISABLED |
The action is disabled in Dataform. | Expected. Enable it in Dataform if it should run. |
NOTEBOOK_CODE_NOT_ANALYZED |
The action is a notebook, whose code is not analyzed. | Expected. |
Column flows missing
| Reason | What it means | What to do |
|---|---|---|
COLUMNS_UNAVAILABLE |
The columns, with their position, of the target or of a source table are not in the catalog, so the routine was uploaded but not analyzed. It also appears when the project of the repository has no System. | Grant roles/bigquery.metadataViewer
on the datasets, turn on bigQueryMetadata, or crawl the source. |
AMBIGUOUS_TABLE_NAME |
Two of the bucket’s project Systems hold a table with the same dataset and name, so the parser cannot tell them apart. | The table flow is still published. Column lineage for this action is not available until the names differ. |
SOURCE_NOT_CATALOGUED |
The parser could analyze only part of the SQL: a table it reads is not in the bucket’s Systems, or its columns are not known. | Check that the source project has a System, and that its tables and columns are cataloged. Open the statement to see which. |
QUERY_NOT_ANALYZED |
The analysis of a routine found nothing to link, or the action has no SQL to analyze (a relation with no query). | Open the routine, then the statements of the bucket, to see their status. |
QLP_ANALYSIS_ERROR |
The parser reported an error on the SQL, usually a BigQuery construct it does not support. | Open the statement in the bucket to read the error. The table flow is still published. |
SCRIPT_NOT_ANALYZED |
The analysis of a scripted operations query failed or found no statement in it. | Expected for scripts whose SQL is built as text. Otherwise open the routine and read its analysis status. |
DYNAMIC_CODE |
The script runs SQL built as text (EXECUTE IMMEDIATE). That SQL is never analyzed. |
Expected. |
Whole repositories or environments missing
| Reason | What it means | What to do |
|---|---|---|
ACCESS_DENIED |
Google refused a project or location. The report says the Google reason. | See Check your setup . |
RESOURCE_UNAVAILABLE |
The project or location does not exist, or has no repositories there. | Check projects and locations. |
NO_COMPILATION_RESULT |
The repository, or one of its release configurations, has no deployed compilation yet. | Trigger the release configuration in Dataform. The repository is cataloged without actions until then. |
COMPILATION_ERRORS |
The deployed compilation has errors. Its actions are read, but nothing is reconciled or cleaned up for this environment. | Fix the errors in Dataform and release again. |
RUN_BUDGET_EXHAUSTED |
More runs exist than maxInvocationsPerRepository allows, so older ones were not read. |
Raise the limit if you need the older runs. |
METADATA_UNAVAILABLE |
The service account cannot read the BigQuery metadata of some tables. | Grant the BigQuery metadata role. Stored values are kept in the meantime. |
Other reasons describe the catalog and quality, not lineage: AMBIGUOUS_PROJECT_SYSTEM and WILDCARD_SCHEMA_UNAVAILABLE (see Dataform to Blindata catalog
), and QUALITY_PERMISSION_MISSING and CHECK_CODE_CONFLICT (see Assertions as quality checks
).
If a table flow exists but you expected column flows, open the bucket of the environment in the query parser section of Blindata, and read the statements of the target table. To see the routine behind them, open the routines list and filter by the additional property dataformFilePath (the file of the action; the filter needs the property to be defined as searchable
) or search the name of the routine, <schema>.<name>…@<environment>: its analysis status says whether the analysis ran, failed or is still pending (a routine analyzed in the last run shows its result in the next one). A statement the parser did not understand is listed in the bucket with the error. Routines are not in the global search: see Find the routines of a source file
.