A data-flow map that answers questions

A diagram is not a map

Almost every company we walk into has a data-flow diagram. It lives in a drawing tool, it was made for a board review or an audit, it is somewhere between four and eighteen months old, and nobody has opened it to answer a question in the last quarter. Watch what happens when the weekly revenue number moves. Nobody opens the diagram. They open Slack and start asking who touched what. That is the whole problem in one observation: the artifact exists, it took real time to make, and it is not load-bearing.

The difference between a diagram and a map has nothing to do with drawing quality. A diagram is a picture of the system as it stood at a moment somebody chose. A map is a graph with attributes on the nodes, built from evidence the system already emits, that you can query. One is read by humans in a meeting. The other is read by a script during an incident, by a reviewer on a pull request, and by whoever answers a deletion request with a deadline attached.

The reason hand-drawn diagrams die is mechanical, not cultural. Someone has to remember to update them, and the update has no forcing function. A table gets renamed on a Tuesday and the diagram is wrong that afternoon, silently, with no test failing. Six months later it is wrong in forty places and nobody knows which forty. Once a team has been burned twice they stop trusting the diagram, and an untrusted map still costs time before it gets overridden anyway.

So the design goal is narrow. Build something generated rather than drawn, answering a fixed list of questions people already ask, that fails loudly when it goes stale.

Start from the questions, not the boxes

The usual first move is an inventory. Somebody opens the warehouse, lists the schemas and the pipelines, and starts drawing. Four weeks later there is a wall chart with 400 boxes that is accurate and answers nothing, because the questions people ask are not about boxes. They are about consequences.

Write the questions first, in the words people use, and build only enough map to answer them. In the reviews we run the same seven come up in nearly every company, whatever the stack.

If this number is wrong, who already saw it? The incident question. A field was miscalculated for three days. Which dashboards, which scheduled exports, which customer-facing surfaces, which models trained on it. This decides whether the incident write-up takes forty minutes or two days.

What breaks if I change this column? The change question. An engineer wants to rename a field, tighten a type, or drop something that looks dead. Without an answer they ship and hope, or they do not ship at all, which is how a schema becomes permanent by accident.

Where did this field come from? The trust question. An analyst looking at net_revenue wants to know which source system it started in and what happened on the way. Upstream lineage, not downstream.

Where does personal data go? The obligation question, and the one with a legal clock on it. Not just where customer records land, but every copy, extract, and derived table they reach.

Who owns this, and who gets paged? The operations question. Every node in a production system has an answer or it is an orphan, and orphans are where outages last longest.

Can we turn this off? The cost question. A nightly job costs money and nobody is sure the dashboard it feeds is still used. Without evidence the conversation repeats every budget cycle and never resolves.

What is the source of truth for X? The disagreement question. Two teams have different numbers for the same thing, both computed correctly, off different tables. Half the "data quality" complaints we get called about are this and nothing else.

What a node is, and what has to hang on it

A useful map has more node types than people expect. Source systems and warehouse tables are the obvious ones. The nodes teams leave out and then regret are at the edges: BI dashboards, scheduled exports, reverse-ETL syncs pushing warehouse data back into a CRM, feature tables, model artifacts, external shares. Most of the blast radius lives past the warehouse boundary, so a map that stops there answers the incident question wrong every time.

Edges are the transforms and movements: an ingest connector, a dbt model, a stored procedure, a notebook that writes a table, a dashboard query, a sync job. Each carries its own attributes, mainly what code produced it and how often it runs.

Node attribute	Where it comes from	Which question it answers
Fully qualified identity	Warehouse catalog, tool APIs	All of them. Two nodes sharing a short name is the most common source of a wrong answer.
Owning team and paging target	Declared in code, next to the model	Who gets paged. Set this to a team alias, never a person's name.
Data classification	Declared on source columns, then propagated along edges	Where personal data goes, and what needs review before it leaves.
Tier and freshness expectation	Declared once per node, defaulted by consumer count	Whether a failure is an incident or a note in the morning.
Upstream and downstream edges	Generated, never typed by hand	Blast radius and change impact.
Last write, last read, read count	Warehouse query history and BI usage APIs	Can we turn this off, and does the consumer list still matter.
Storage and compute cost	Warehouse metering views	Which decommissioning candidate is worth the effort.

Two attributes get skipped in almost every first attempt, and they are the two that make the map load-bearing. Classification feels like a governance chore until a request arrives with a deadline on it. Read activity is the only attribute separating a real consumer from a table somebody built once and forgot.

Where the edges come from without anyone drawing them

This is the part that decides whether the project succeeds. If a human has to declare an edge, the map decays. If it is derived from something the system produces anyway, the map stays true on its own. Six sources are worth harvesting, and they overlap deliberately, because each is blind somewhere different.

Transformation code. If the stack runs dbt, the compiled manifest.json is an exact dependency graph, free, with every ref() and source() resolved. It is the highest-value artifact in the exercise. For SQL outside a transformation framework, parse it: the open-source sqlglot library resolves column-level dependencies through a dialect-aware AST, which beats pattern-matching table names out of query text.

Warehouse query history. Every major warehouse records who read and wrote what. Snowflake exposes ACCESS_HISTORY in SNOWFLAKE.ACCOUNT_USAGE, with column-level read and write detail, on Enterprise Edition and above. BigQuery has INFORMATION_SCHEMA.JOBS plus its own lineage service, and Databricks tracks lineage through Unity Catalog. These catch what code inspection misses: ad hoc reads, notebooks, the analyst who scheduled something outside the orchestrator. They also lag, because account usage views are built for daily reporting, so treat query history as a nightly input rather than a live feed.

The orchestrator. Airflow, Dagster, and Prefect know their own task graphs and integrate with OpenLineage, the open specification for emitting lineage events at run time. Run-time events are the only source that tells you an edge was actually exercised last night rather than merely existing in code.

Ingest configuration. Fivetran and Airbyte connector definitions, Debezium topics, Kafka sink config, and object-store paths in job config describe the left edge of the graph. This is where the map learns which application a table actually came from, which is the trust question.

BI and activation metadata. Tableau has a GraphQL metadata API, Looker exposes LookML plus usage history, Power BI has the scanner API, and Hightouch and Census expose their syncs. A day or two of work each, and it is where the highest-value edges live, because these are the nodes humans look at.

Declared edges, kept small. There will always be a vendor SFTP drop, a spreadsheet somebody maintains, or a legacy service nothing instruments. Keep a short YAML file for those, review it quarterly, and treat its length as a debt metric. If it grows past a couple of dozen entries, something that should be emitting evidence is not.

Evidence source	What it yields	Effort	Where it is blind
dbt manifest	Exact model-to-model graph, tests, descriptions	Hours	Anything not modeled in dbt
SQL parsing (sqlglot)	Column-level edges from arbitrary SQL	Days	SELECT *, dynamic SQL, UDF internals
Warehouse query history	Real reads and writes, including ad hoc	Days	Hours of lag; retention window is finite
OpenLineage from the orchestrator	Run-time edges with timing and status	Days to weeks	Work that runs outside the orchestrator
BI metadata APIs	Dashboards, extracts, who viewed and when	Days per tool	Downloaded copies and offline files
Declared YAML	Everything nothing else emits	Ongoing	It is only as fresh as the last person who edited it

Column level or table level

Column-level lineage is the thing everyone wants and the thing that sinks the schedule. It is a parsing problem with a long tail: star expansion, dynamic SQL, user-defined functions, views on views, semi-structured columns unpacked at read time. Chase completeness across the estate and it becomes a compiler project that ships in month nine.

Table-level answers five of the seven questions well enough to act on: blast radius, change impact, source of truth, ownership, decommissioning. Two need columns. Personal-data propagation does, because you need to know that email reached a marketing extract rather than that some table did. So does change impact on wide tables, where "this model depends on that table" is too coarse when the table has 180 columns.

So scope it that way. Table-level everywhere, column-level on two subsets: paths starting at a column with a sensitive classification, and the top tier of models by consumer count. That is usually ten to fifteen percent of the estate, tractable in weeks rather than quarters, and extendable later.

The blast-radius query

Here is the query the whole map exists for. Give it a node and a time window. It walks downstream edges transitively and returns every consumer, with the owning team, the tier, when that consumer last refreshed, and when a human last looked at it. On a real stack the output is rarely what anyone guesses: four dashboards, two of which nobody has opened in three months, a scheduled export landing in a shared drive every Monday, a reverse-ETL sync writing a field sales reps read on an account page, and a feature table behind a production model.

That last item changes the response. Bad data in a dashboard is embarrassing for a day. Bad data in a feature table means a model has been scoring on it, and the fix carries a retraining step and a backfill window. Teams that cannot see model dependencies discover them in the second week of an incident review.

The time attributes matter as much as the edges. A node nobody has read in ninety days is not part of the incident, it is part of the decommissioning backlog. Without read activity the query returns forty rows and someone triages them by hand, which is the manual work the map was supposed to remove. Rank by last-read and tier and the top five lines are the answer.

Build the reverse of the query early too: give it a node, walk upstream to the source systems, collapse the intermediate hops. That is the trust question, and it takes an afternoon once the graph exists. Analysts use it more than engineers do, and their usage is what keeps the work funded.

The deletion question, and why it pays for the map

Every other question is operational. This one has a clock. Under the GDPR a data subject can request erasure under Article 17 or access under Article 15, and Article 12(3) requires a response within one month of receipt, extendable by two further months for complex requests. California's CCPA as amended by the CPRA sets 45 days with a further 45-day extension. Whichever applies, the response is a defensible statement about every place a person's data exists, produced in weeks.

Answering it by asking around does not hold up. What holds up is column-level propagation from the source identifier outward, which is what the constrained column-level scope above is for. Start at the columns classified as personal data, follow every edge, and the map produces the list.

The same graph does double duty on record-keeping. Article 30 requires records of processing activities including categories of recipients and, where applicable, transfers. A generated map produces that from evidence instead of from a workshop, and produces it again next quarter without a second one. That argument is usually what gets the work funded, because it turns a recurring manual exercise into a build step.

Ownership is an attribute, not an org chart

Every node needs an owning team and a paging target, declared in code beside the model definition so it moves when the model moves. Not a person. People change teams, and an owner field with a name in it is an error that surfaces mid-incident.

The useful metric is the orphan rate: the fraction of nodes with no owner, weighted by downstream consumer count. An unowned table with no consumers is housekeeping. One feeding a customer-facing surface is the top of the queue. Sort by that weighting and the remediation list writes itself, instead of a flat list of 300 objects that makes everyone tired.

Run one review a month against the map, not against a spreadsheet. Fifteen minutes, three lists: new unowned nodes, nodes whose classification changed, and edges that appeared between a sensitive source and an external destination. Everything else waits. The review survives because it is short enough that people keep doing it.

The map is built in CI or it is a screenshot

Regeneration cadence decides whether any of this survives the first quarter. Build the graph nightly from every evidence source, store it as a versioned artifact, and diff it against yesterday. The diff is worth more than the map: it tells you an edge appeared overnight between a table classified as personal data and a dashboard shared outside the company, which is a review comment nobody writes by hand because nobody knew.

Run a lighter version on pull requests. When a change adds or removes an edge, post the delta on the PR: what the model now depends on, what depends on it, and whether classification propagated somewhere new. Engineers act on that because it is in front of them at the moment of the decision, not in a portal they have to remember to visit.

Then measure drift. The metric that matters is how many objects appeared in query history last week that the map does not know about. It should be near zero. When it climbs, a new tool arrived and nobody wired it in, and you want to know in week one rather than during the next audit.

What the map lets you turn off

Decommissioning is the part that pays cash, and it cannot be done safely without evidence. The candidate list is mechanical once the graph carries read activity: nodes with zero reads in ninety days, sorted by compute and storage consumed, minus anything under a retention requirement.

Then use a ladder rather than a delete. Announce it to the owning teams of any remaining consumers. Stop the refresh but leave the object in place. Rename it with a deprecation suffix. Wait a full business cycle, which for most companies means a month end. Then drop it, keeping a tombstone in the map: what it was, who owned it, when it went.

The rename rung is the one people skip and the one that earns its place. If an unknown consumer exists, a rename breaks it loudly and reversibly on a Tuesday afternoon with an engineer watching. A straight drop breaks the same consumer silently, and it surfaces as a wrong number weeks later with no obvious cause.

Where these projects go wrong

• Inventorying first. Four weeks of drawing produces a wall chart that answers none of the seven questions.
• Buying a catalog before deciding who maintains the attributes. Ownership, classification, and tier are yours to populate, and an empty catalog is a subscription with a login page.
• Stopping at the warehouse boundary. Most of the blast radius lives in the BI tool and the activation layer.
• Column-level lineage everywhere. The long tail of SQL parsing turns a ten-week project into a nine-month one.
• Owner fields containing a person's name. Correct on the day it was written, wrong within two reorgs, and wrong exactly when it is needed.
• Mapping without read activity. Without last-read the answer is unranked, so a human triages it, so nobody uses it.
• No diff and no drift metric. You find out the map is stale the first time it gives a confident wrong answer, and that is the last time anyone trusts it.
• Treating it as a governance deliverable. If engineers do not use it during incidents and pull requests, it gets defunded at the first budget review, and it should be.

A thirty-day build

Thirty days is realistic because the expensive-looking parts are not the slow parts. Harvesting a dbt manifest and a week of query history is an afternoon. The slow parts are human: agreeing what classification levels mean, getting owners assigned to nodes nobody wants, and deciding which of two tables is the source of truth when both have advocates. Start those conversations on day one and run them alongside the harvesting. They are the critical path and they do not compress.

Ship something people query in month one, with visible gaps, rather than something complete in month six that lands after the incident it would have solved.

What good looks like

• Blast radius for any node returns in seconds, ranked by tier and last-read
• Every tier-1 node has an owning team alias and a paging target, declared in code
• Sensitive columns have column-level paths to every destination, including BI and activation
• The graph rebuilds nightly and diffs against the previous version
• Pull requests get an automatic comment when the change adds or removes an edge
• The drift metric, objects seen in query history but absent from the map, sits near zero
• The declared-edge file is short and shrinking
• A deletion or access request is answered from a query, not from a meeting

Bottom line

A data-flow map is not a documentation exercise. It is an index over a system you already have, and its only measure of quality is whether someone can ask it a question under pressure and act on the answer. Build it from evidence the stack already emits, scope column-level work to where it is legally or operationally necessary, put ownership and read activity on every node, then rebuild and diff it in CI. Do that and the map is still correct a year later with nobody having maintained it, which is the property no drawing tool has ever had.

Frequently asked questions