Safely drop a column
Check who writes and reads a column before you drop it, and know what the check can't see.
When to use: you want to drop or rename a column, for example legacy_flag on analytics.orders_fact, and need to know what would break.
1. Resolve the table
Type:
Resolve orders_fact with Single Origin. Is it a table or a view?
The agent calls resolve_table_reference with table_ref="orders_fact". Look at table_type and fqn. If it returns ambiguous: true, pick the right name from candidates. If it is a view, the column lives in the tables under physical_tables; read definition to see how the view uses it.
Open Tables and use Search by name or schema. Click the table to open its page.
2. Check the column exists and how it is set up
In your agent (Snowflake only). Type:
Show me the legacy_flag column of analytics.orders_fact with Single Origin.
The agent calls table_column_detail with fqn="analytics.orders_fact" and columns=["legacy_flag"]. Read data_type, and check part_cluster: a column with a partition or cluster role needs that changed first. An empty list means the table is unknown.
3. Check the jobs that write it
Type:
What builds analytics.orders_fact? Use Single Origin for the last 30 days, and show me where legacy_flag appears in the SQL.
The agent calls table_producers with destination_fqn, start_date and end_date. Read each producer's sample_query. Every producer that names the column must change in the same release, or its write fails.
On the table page, How it is written lists the jobs that write it, with their SQL.
4. Check the jobs that read it
On the table page, Jobs that read it lists each reader with its cost per day. Do the filters prune? shows the costliest readers' filter columns. Note every reader job.
For each reader job, type:
Summarize nightly_revenue &&& build_orders_daily with Single Origin, then show me its sample queries. Do any of them use legacy_flag?
The agent calls query_summary_for_job for the job, then query_detail on each sample_query_id. Read column_usage: entries for LEGACY_FLAG show whether the query selects, filters, joins or groups on it. A WHERE or join entry means a rename breaks a predicate, not only a select list.
To see which query shapes filter on it, ask for table_scan_cost on the table (Snowflake only) and read filter_cols per signature.
5. Decide
Drop the column only when no producer writes it and no reader uses it. Otherwise, change those jobs first, or keep the column and stop writing it.
A good final answer
analytics.orders_fact.legacy_flag (BOOLEAN, no cluster role) is written by nightly_revenue &&& load_orders and read by 2 of 5 reader jobs over the last 30 days: weekly_finance &&& orders_rollup filters on it (WHERE legacy_flag = FALSE), and nightly_revenue &&& build_orders_daily selects it. Change those two jobs and the producer first. Untagged and BI reads were not checked query by query; confirm with their owners.
Watch out for
- The history window. Reads are what Single Origin saw in your loaded query history. A monthly or quarterly job that didn't run in the window doesn't show up.
- Samples, not every query.
query_summary_for_jobreturns one sample query per statement type and day; other queries of the same job may use the column differently.- Profiles.
column_usagecomes from the query profile; a query without a profile has an empty list. Read its SQL withquery_detailinstead.- Untagged queries. Queries without a job tag are grouped by query shape, so one entry may be several callers, such as BI tools or notebooks.
- Reads Single Origin can't see: reads from outside the warehouse, and reads through views you don't check.
- Engine limits.
table_column_detailandtable_scan_costare Snowflake only.
Related
Updated about 7 hours ago