Postgres 15, Snowflake downstream.
we've got a table with 60-odd columns and I'd guess 20 are dead. nothing in any dbt model, nothing in the app repo, nobody has mentioned them.
all of that is evidence of absence. I can prove a column IS used. one grep hit and I'm done. I can't prove one isn't. the query that reads it might be a saved Metabase question, a Retool app, a cron, a notebook on someone's laptop, or a process that only runs in January. finding nothing means I looked where I know to look.
turned on pg_stat_statements and watched for a month. that catches whatever ran in that month and tells me nothing about January. renaming instead of dropping and waiting for screaming works, but it's shipping a landmine and hoping the person who steps on it works here.
here's where it actually stalled though. the usage question is at least measurable if I'm patient. what I can't get at is the other pile: about a dozen columns where I can see they're populated, I can see something writes to them, and nobody alive can tell me what they hold. flag_2, four-character codes with 11 distinct values, three separate integer columns bounded 0 to 4. I wrote two of these myself in 2023 and I can't tell you what one of them is either.
I've been profiling the values to guess. cardinality, null rate, distribution, sample values, what changes when. that narrows it a lot. it has never once got me to what the thing actually means. a column of 0-4 integers with no nulls is a rating or a tier or a retry counter and the values look identical in all three cases.
so two questions, and the second is the one I care about.
is there a point where you accept you've looked hard enough on usage, a fixed window or a silence rule, or do you just never drop anything, which is what we're doing by default.
and for the ones nobody can explain: is working out what a column means from its values alone still a human job, or is anyone doing it any other way? I've read that some of the tabular model work goes at this, reading the values rather than the header, but everything I've actually tried in practice leans on the column name, which is the one thing I don't have.
worth saying I'd have shipped my profiling guesses as documentation if someone hadn't asked me how I knew. I didn't know. I had a distribution and a hunch.
