redshift-lineage-from-stl

Redshift Lineage from STL_QUERY

Reconstructs actual lineage by mining query history + sqlglot parsing. Ships a helper script — the only one in this plugin that does, justified because LLM-driven SQL parsing is unreliable.

STL retention warning (mention upfront in chat):

• Provisioned: ~2-5 days; older queries gone.
• Serverless: longer; uses SYS_QUERY_HISTORY.
• Permissions: STL needs SYSLOG ACCESS UNRESTRICTED or admin.

When to use / NOT

• Use to find ad-hoc / BI-tool consumers, audit deprecation candidates, diagnose freshness gaps.
• NOT for dbt-internal lineage (manifest is authoritative); NOT for declared FKs (use erd); NOT real-time.

Inputs

Flow

1. Detect cluster type: execute_sql("SELECT version()"). Look for "Redshift Serverless" → use SYS_QUERY_HISTORY; else STL.

2. Pull queries — provisioned (STL_QUERY + STL_QUERYTEXT):

   WITH recent AS (
       SELECT q.query, q.userid, q.starttime,
              u.usename AS user_name
       FROM STL_QUERY q LEFT JOIN PG_USER u ON u.usesysid = q.userid
       WHERE q.starttime >= '<since>'::timestamp AND q.aborted = 0
         /* if --user */ AND u.usename = '<user>'
       ORDER BY q.starttime DESC LIMIT <limit>
   )
   SELECT r.query, r.user_name, r.starttime,
          LISTAGG(qt.text, '') WITHIN GROUP (ORDER BY qt.sequence) AS sql_text
   FROM recent r JOIN STL_QUERYTEXT qt ON qt.query = r.query
   GROUP BY r.query, r.user_name, r.starttime;
   

   Caveat: LISTAGG result is VARCHAR(65535) — long queries truncate. Mention to user; truncated SQL parses worse.

   Serverless (SYS_QUERY_HISTORY — full SQL is one column already):

   SELECT query_id AS query, user_id AS user_name,
          start_time AS starttime, query_text AS sql_text
   FROM SYS_QUERY_HISTORY
   WHERE start_time >= '<since>'::timestamp
     AND status NOT IN ('failed', 'aborted')
     /* if --user */ AND user_id = '<user>'
   ORDER BY start_time DESC LIMIT <limit>;