tracing-upstream-lineage

Trace the origins of data - answer "Where does this data come from?"

Determine what we're tracing:

• Table: Trace what populates this table
• Column: Trace where this specific column comes from
• DAG: Trace what data sources this DAG reads from

Tables are typically populated by Airflow DAGs. Find the connection:

1. Search DAGs by name: Use af dags list and look for DAG names matching the table name

   • load_customers -> customers table
   • etl_daily_orders -> orders table

2. Explore DAG source code: Use af dags source <dag_id> to read the DAG definition

   • Look for INSERT, MERGE, CREATE TABLE statements
   • Find the target table in the code

3. Check DAG tasks: Use af tasks list <dag_id> to see what operations the DAG performs

If you're running on Astro, the Lineage tab in the Astro UI provides visual lineage exploration across DAGs and datasets. Use it to quickly trace upstream dependencies without manually searching DAG source code.

Use DAG source code and task logs to trace lineage (no built-in cross-DAG UI).

From the DAG code, identify source tables and systems:

SQL Sources (look for FROM clauses):

# In DAG code:
SELECT * FROM source_schema.source_table  # <- This is an upstream source

External Sources (look for connection references):

• S3Operator -> S3 bucket source
• PostgresOperator -> Postgres database source
• SalesforceOperator -> Salesforce API source
• HttpOperator -> REST API source

File Sources:

• CSV/Parquet files in object storage
• SFTP drops
• Local file paths

Recursively trace each source:

TARGET: analytics.orders_daily
    ^
    +-- DAG: etl_daily_orders
            ^
            +-- SOURCE: raw.orders (table)
            |       ^
            |       +-- DAG: ingest_orders
            |               ^
            |               +-- SOURCE: Salesforce API (external)
            |
            +-- SOURCE: dim.customers (table)
                    ^
                    +-- DAG: load_customers
                            ^
                            +-- SOURCE: PostgreSQL (external DB)

For each upstream source:

• Tables: Check freshness with the checking-freshness skill
• DAGs: Check recent run status with af dags stats
• External systems: Note connection info from DAG code

When tracing a specific column:

1. Find the column in the target table schema
2. Search DAG source code for references to that column name
3. Trace through transformations:
   • Direct mappings: source.col AS target_col
   • Transformations: COALESCE(a.col, b.col) AS target_col
   • Aggregations: SUM(detail.amount) AS total_amount

One-line answer: "This table is populated by DAG X from sources Y and Z"

Describe how data flows and transforms:

1. Raw data lands in raw.orders via Salesforce API sync
2. DAG transform_orders cleans and dedupes into stg.orders
3. DAG build_order_facts joins with dimensions into fct.orders

• Single points of failure?
• Stale upstream sources?
• Complex transformation chains that could break?

• Check source freshness: checking-freshness skill
• Debug source DAG: debugging-dags skill
• Trace downstream impacts: tracing-downstream-lineage skill
• Add manual lineage annotations: annotating-task-lineage skill
• Build custom lineage extractors: creating-openlineage-extractors skill