tracing-upstream-lineage_skill

Overview

This skill traces upstream data lineage to answer where data comes from and what feeds a table, column, or DAG. It maps producing DAGs, source systems, and transformation chains into a concise lineage report that highlights sources, connections, and potential health issues.

How this skill works

The skill identifies the target type (table, column, or DAG), locates the producing Airflow DAGs, and inspects DAG source code and tasks for SQL and operator references. It extracts SQL FROM clauses, operator connection names (S3, Postgres, API), and file paths, then recursively follows each upstream source to build a lineage chain. The output includes a one-line summary, an ASCII lineage diagram, source details, and transformation steps.

When to use it

• You need to know which DAG populates a given table or column.
• You want to list upstream systems feeding a dataset (databases, S3, APIs).
• You must audit transformation chains and understand how a value is computed.
• You need to assess upstream health or identify single points of failure.
• You are preparing impact or root-cause analysis for data issues.

Best practices

• Start by confirming the target type (table, column, or DAG) to narrow the search.
• Search DAG names and inspect DAG source code for INSERT/MERGE/SELECT and operator connections.
• Recursively trace each discovered source until you hit an external system or raw file.
• Annotate found connections and owners; record freshness and recent DAG run status.
• Use related checks (freshness, DAG run stats) to evaluate upstream reliability.

Example use cases

• Explain where analytics.orders_daily comes from and which DAGs feed it.
• Trace the origin and transformations behind a specific column like total_amount.
• Map all external systems and files that land data into a target table.
• Produce a lineage diagram and source detail table for a data governance review.
• Identify upstream DAG failures or stale sources causing downstream errors.