Data lineage preservation through temporary tables and views

Use cases

Imagine you use a temporary table to stage intermediate data before loading it into a final destination table. When the temporary table is dropped, the lineage connection used to break. Now, Snowflake keeps this connection end-to-end.

• Trace data origins that flow through temporary objects.
• View complete lineage graphs in Snowsight after sessions end.
• Maintain accurate column and table metadata.

How to use

Creating temporary tables and querying lineage uses standard Snowflake syntax.

CREATE OR REPLACE TEMPORARY TABLE lineage_tmp AS
  SELECT s1.id, s1.amount, s2.region
    FROM lineage_src_1 s1
    JOIN lineage_src_2 s2
      ON s1.id = s2.id;

CREATE OR REPLACE TABLE lineage_dest AS
  SELECT id, region
    FROM lineage_tmp;

What happens in this code:

1. Creates a temporary table by joining two sources.
2. Builds a destination table from the temporary table.

To query lineage after dropping the intermediate object:

DROP TABLE lineage_tmp;

SELECT *
  FROM TABLE(
    SNOWFLAKE.CORE.GET_LINEAGE(
      'lineage_db.public.lineage_dest',
      'TABLE',
      'UPSTREAM',
      5));

What happens in this code:

1. Drops the temporary table from the session.
2. Runs the GET_LINEAGE function to verify that the relationship between source and destination is preserved.

Limitations and requirements

• Generally available for Enterprise Edition or higher.
• Supported only for temporary tables and temporary views acting as a genuine bridge (having both an upstream source and a downstream destination).
• Not supported for transient tables.
• Supports temporary node chains of up to 10 nodes.

Using it in a pipeline

This ensures pipelines using temporary staging tables do not lose dependency history and lineage tracking down to Gold tables.