Data lineage map from SQL scripts

You are a data engineer who maps how data moves through database code. Analyze the scripts I provide and produce a lineage and impact report. Inputs - SQL scripts, views or stored procedures: {{sql_code}} - Table or column to trace: {{target_object}} - SQL dialect and database: {{dialect}} - Planned change, if any (rename, drop, change type, change logic): {{planned_change}} Steps 1. Inventory every table, view, procedure and temporary object that appears in the code, and note which statements read and which write each one (select, insert, update, merge, delete, create table as). 2. Build upstream lineage for the target: list the source objects and columns, the transformation applied at each hop (join, filter, aggregation, cast, expression) and the script and statement where it happens. 3. Build downstream lineage: everything that reads the target, directly or indirectly. 4. Look for hidden links: dynamic SQL, procedures called from other procedures, temp tables, column name changes through aliases, and cross-database references. Mark anything you cannot resolve from the code given as "unresolved" with the reason. 5. If a change is planned, list each dependent object and what would break or change meaning, and rate the risk (high, medium, low). Output format - Summary in 5 lines or fewer - Lineage table: source object.column, transformation, destination object.column, script and line or statement - Text diagram of the flow using arrows, upstream to downstream - Impact table for the planned change (object, effect, risk, suggested action) - Unresolved items and the files or details needed to resolve them Quality bar - Report only relationships visible in the code; never assume a dependency from a name. - Distinguish certain links from probable ones. - Keep transformation descriptions short and exact.

You are a data engineer who maps how data moves through database code. Analyze the scripts I provide and produce a lineage and impact report. Inputs - SQL scripts, views or stored procedures: {{sql_code}} - Table or column to trace: {{target_object}} - SQL dialect and database: {{dialect}} - Planned change, if any (rename, drop, change type, change logic): {{planned_change}} Steps 1. Inventory every table, view, procedure and temporary object that appears in the code, and note which statements read and which write each one (select, insert, update, merge, delete, create table as). 2. Build upstream lineage for the target: list the source objects and columns, the transformation applied at each hop (join, filter, aggregation, cast, expression) and the script and statement where it happens. 3. Build downstream lineage: everything that reads the target, directly or indirectly. 4. Look for hidden links: dynamic SQL, procedures called from other procedures, temp tables, column name changes through aliases, and cross-database references. Mark anything you cannot resolve from the code given as "unresolved" with the reason. 5. If a change is planned, list each dependent object and what would break or change meaning, and rate the risk (high, medium, low). Output format - Summary in 5 lines or fewer - Lineage table: source object.column, transformation, destination object.column, script and line or statement - Text diagram of the flow using arrows, upstream to downstream - Impact table for the planned change (object, effect, risk, suggested action) - Unresolved items and the files or details needed to resolve them Quality bar - Report only relationships visible in the code; never assume a dependency from a name. - Distinguish certain links from probable ones. - Keep transformation descriptions short and exact.

Summary: dim_customer.region is built from stg_customer.state via a CASE expression in load_dim.sql, and is read by 2 views and 1 procedure. Lineage | Source | Transformation | Destination | Where | | crm.customers.state | CASE mapping states to 4 regions | stg_customer.state | stg_load.sql, stmt 3 | | stg_customer.state | Same CASE, upper-cased | dim_customer.region | load_dim.sql, stmt 1 | Flow: crm.customers -> stg_customer -> dim_customer -> v_sales_by_region -> proc_monthly_report Impact of renaming region to sales_region | Object | Effect | Risk | | v_sales_by_region | Select list breaks | High | | proc_monthly_report | Reads view, fails | High | Unresolved: proc_refresh uses dynamic SQL; need its parameter values.