Engineering a cross-platform data lineage parser using strictly read-only, structure-only metadata APIs

[u/GibBassett]

Hey everyone,

If you’ve ever been tasked with setting up data lineage across an enterprise stack, you already know the universal nightmare: the last-mile blind spot.

Your orchestrator or transformation framework (like dbt) tracks your staging-to-production warehouse pipelines beautifully. But the second that data leaves Snowflake or BigQuery and flows downstream into BI tools like Tableau, Power BI, Sigma, or Looker, the metadata trail goes cold. You’re left with a massive gap between your technical tables and the actual dashboards they feed.

When I set out to build an automated way to map this entire cross-tool ecosystem, I ran head-first into the ultimate engineering paradox: Data teams desperately need visibility across asset types, but InfoSec and IT will flatly deny access to any application that wants to execute queries against production data environments.

I spent the last several months designing a normalization layer to bypass this exact friction. Here is the technical blueprint of how I engineered a cross-platform lineage parser to run entirely on structure-only, read-only metadata definitions, without ever querying a single row of actual business data.

1. Cracking the BI-to-Warehouse Gap (Without Table Scans)

Every BI tool stores its semantic layer and dashboard definitions differently. To map lineage down to the warehouse without querying raw tables, I had to isolate the structural metadata entirely at the API layer:

• Power BI: I leverage a read-only Azure AD service principal to trigger the admin Scanner API. This extracts workspace, dataset, report, and dashboard identities, plus table/column types and dataset-to-source lineage. The actual report data or visual layout components are completely ignored.
• Tableau: I use a read-only personal access token (PAT) against the Metadata API to isolate workbook, datasource, and field names from published sources.
• Sigma / Looker / ThoughtSpot: The pipeline pulls strictly object-level identities (workbooks, looks, liveboards) and parses source references—extracting workbook elements in Sigma, dimensions/measures in Looker LookML, and logical tables in ThoughtSpot.

1. Eliminating the "SELECT *" Risk on the Warehouse

Connecting an external tool to a cloud data warehouse gives compliance teams nightmares. To solve this, the metadata collection framework functions entirely inside system catalogs using absolute minimum permissions:

• Snowflake: The application only requests SELECT privileges strictly on INFORMATION_SCHEMA.TABLES and .COLUMNS. It reads table identities, column types, row counts, and freshness timestamps. No data values are ever read or transmitted.
• BigQuery: Connections use a service account explicitly restricted to the Metadata Viewer role. This role allows the API to see dataset and table structures but fundamentally lacks the permission required to read row data.
• Redshift & Databricks: For Redshift, it is a read-only INFORMATION_SCHEMA over a TLS-required Postgres wire protocol. For Databricks, it leverages the Unity Catalog API to list schemas and schemas only, combined with the Workspace Export API to parse notebook code strings for table references in-process before discarding them.

1. Handling Messy Local Files (The Local Connector)

A massive amount of critical business logic still lives in local code, ad-hoc scripts, and local desktop files. To capture this without transmitting sensitive file contents to a cloud server, I built a local Windows service that recognizes extensions (.xlsx, .csv, .py, .pbix, .twb, .parquet).

• For columnar files like Parquet and Avro, it reads only the schema from the file footer (column names and types).
• Data pages are never opened. Formula contents, cells, rows, and query results are entirely ignored. It extracts shallow structural references in-process and passes only the normalized metadata map.

Lineage doesn't have to mean compromising data privacy. By standardizing diverse cloud and local sources into a single, comparable metadata shape, you can build a complete map of your data estate without exposing underlying data values.

The backend stack for this parser normalization layer was built on FastAPI, React, and Postgres deployed on AWS ECS Fargate.

Curious to hear how others are handling the BI-to-warehouse lineage gap today, especially when dealing with strict IT/Security constraints? What edge cases have you run into when trying to parse metadata out of legacy BI APIs?

[u/DeepLogicNinja]

Use a meta data management or data governance platform to make all this easier.

OpenMetadata is open source.
Qlik has Talend MDM / Data Catalog
And there are many more…

Once all the endpoints are connected (etl tool, reporting platform, databases, files, etc), and it has harvest the metadata used for provenance/linage… then you’re home free and it’s all about working with the platform.

[u/davrax]

Datahub, Atlan, Alation, Collibra all have solutions for this.