Redshift Data Lineage: How to Build It From Query History

Amazon Redshift does not build a data lineage graph on its own, but it records the raw material for one: every query that ran, with its full SQL, in the SYS_QUERY_HISTORY view and the older STL_QUERY system tables. To build column-level lineage you parse that SQL to resolve which columns fed which, walk the dependencies transitively, and persist the result, because Redshift keeps its STL logs for only about seven days. AWS offers native lineage through Amazon SageMaker Unified Studio and DataZone, which run scheduled captures against those same system tables, and dedicated tools parse the history directly and keep the graph.

This covers where Redshift lineage actually comes from, the seven-day catch that trips teams up, and the three routes to a usable column-level map.

Redshift gives you the logs, not the lineage

The good news is that Redshift is meticulous about recording what runs. The modern place to read it is SYS_QUERY_HISTORY, the unified monitoring view that carries the query text, timing, database, and user for every statement. The older STL_QUERY and the wider STL family expose similar detail and predate the SYS views. Between them, the complete record of every CREATE TABLE AS, INSERT, and MERGE your platform has run is available to query.

The catch is that a log of queries is not a lineage graph. Lineage is the derived answer to "which columns produced this column, and which reports depend on it," and getting there from the raw SQL takes real work: parsing each statement, resolving columns through views and CTEs, and connecting the individual queries into a graph you can walk. Redshift does none of that assembly for you. It hands you the evidence and leaves the reconstruction as an exercise.

The seven-day catch

Here is the detail that turns a manageable project into an urgent one: the STL system views retain only about seven days of log history, and under heavy cluster activity it can be closer to two to five days. SYS_QUERY_HISTORY gives you a cleaner window into recent activity, but it is still a monitoring view, not a long-term archive.

That retention window is the opposite of what lineage is for. You almost never need to trace a pipeline you built this morning. You need to trace the one nobody has touched in six weeks, the one whose logic everyone has forgotten, the one that just started producing a wrong number. And that lineage is precisely what Redshift has already deleted. Any serious approach to Redshift lineage has to capture and persist the history before the window closes, which rules out "just query the system tables when you need it."

Route 1: build it yourself from the system tables

You can reconstruct lineage from Redshift directly. Schedule a job that reads SYS_QUERY_HISTORY and the STL views on a cadence shorter than the retention window, store the raw queries somewhere durable, and build a parser that turns each statement into column-level edges. Done well, this gives you a table-level graph fairly quickly and a column-level one with a lot more effort.

The parser is where it gets expensive. To resolve columns rather than tables you have to handle joins, aliases, CTEs, window functions, CASE expressions, and SELECT * expansion against the schema as it existed when the query ran. That is a genuinely hard piece of software, it needs maintenance as your SQL patterns evolve, and nobody on a data team was hired to write it. It is a legitimate build if lineage is core to your product; it is usually the wrong use of an analytics engineer's quarter if it is not.

One more thing to watch on the DIY route: querying the system tables and running capture jobs consumes cluster resources, and Redshift bills on cluster time. It is a minor cost relative to your transformation workload, but on a busy cluster it is worth keeping an eye on, and teams that watch where their warehouse and cloud spend actually goes tend to notice capture jobs that are polling more aggressively than they need to.

Route 2: Amazon SageMaker and DataZone

AWS has a native lineage story, it just lives in the governance layer rather than in Redshift itself. Data lineage became generally available in Amazon DataZone and the next generation of Amazon SageMaker in December 2024, and it automatically captures lineage from AWS Glue and Amazon Redshift. In SageMaker Unified Studio, lineage runs execute queries against the Redshift system tables on a schedule, commonly daily, and store the resulting events in the catalog, showing column-level detail where it can be resolved.

If you are standardizing on SageMaker Unified Studio or DataZone as your data governance platform, this is a sensible path and it is built in. The trade-offs are worth naming: you are adopting a broad catalog and governance platform to get lineage, the capture runs on a daily cadence rather than something you can check against a pull request in the moment, and the graph is scoped to the AWS services those products integrate with, so a transformation that happens in dbt or a dashboard in a third-party BI tool may sit outside it.

Route 3: a dedicated lineage tool

The third route is a tool built specifically for lineage and impact analysis, which connects to Redshift read-only, parses the query history into a persistent column-level graph before the seven-day window erases it, and extends the graph past AWS into dbt and the BI layer. This is the approach Datatrail takes for Redshift: a read-only role, column-level lineage parsed from SYS_QUERY_HISTORY and the STL views, a graph that outlives the log, and impact analysis that lists every downstream model and dashboard before you change a field.

The reason to buy rather than build here is the same reason you bought Redshift instead of running your own MPP database: the parser and the persistence are undifferentiated heavy lifting. If lineage is a means to shipping safer changes, not your product, a tool that already handles the SQL edge cases and keeps the graph current is almost always the better use of the team.

Which route fits your team

• All-in on the AWS governance stack? SageMaker Unified Studio and DataZone give you native, no-extra-vendor lineage, at a daily cadence and scoped to AWS services.
• Lineage is core to what you build? The DIY route from the system tables is legitimate, as long as you budget for the parser and its maintenance tail.
• You want the answer without the project? A dedicated tool connects read-only, keeps the graph, and reaches the dashboards, so you can check a schema change against a pull request instead of waiting for the next run.

The short version

Redshift records every query in SYS_QUERY_HISTORY and the STL system tables, which is everything you need to reconstruct column-level lineage, except that it deletes the STL logs after about a week and never assembles them into a graph. You get lineage by capturing that history before it expires and parsing the SQL into a persistent map, whether you build that yourself, adopt AWS DataZone and SageMaker, or use a dedicated tool. Datatrail does the last of those for Redshift and every other major warehouse, so lineage outlives the log and reaches the dashboard. The full walkthrough is on the Redshift data lineage page, and the wider field is in our comparison of data lineage tools.