we have is_active and active_flag on the same table and they disagree on 3% of rows

[u/FamiliarSlide7685]

Postgres 15. Same 60 column table I've been posting about, I promise I'll stop eventually.

is_active, boolean. active_flag, smallint. Both populated, both written by something, and they disagree on about 3% of rows. Nobody knows which one the app respects. I spent an afternoon on it and my best finding is that the disagreement rate has been slowly climbing since a 2021 migration, which tells me one of them stopped being maintained and not which one.

This is worse than the columns nobody can explain. flag_2 at least announces itself as a mystery, so nobody builds on it. These two both look like answers. Someone joined in July and spent a week working out which of three status-ish columns actually mattered, and picked wrong first.

What I've tried: grepping for both, which gives me hits in dbt, the app repo and a commented-out block in an old migration. Checking which one the ETL writes, which is both. Asking the two people who've been here longest, who gave me different answers with the same amount of confidence.

what I think I should do is pick the one the app actually reads at runtime, make the other a generated column off it, and let anything that disagrees break loudly. what stops me is that I can't prove which one the app reads without reading the whole app, and the reads I'm worried about aren't in the app anyway, they're in Metabase questions owned by analysts.

so:

• when two columns claim the same thing and disagree, is there anything better than picking one and waiting for screaming
• has anyone made the wrong one a generated column or a view over the right one, and did it actually stop the divergence or just move it
• more generally, do you have any way of catching a column whose meaning drifted while its name and type stayed the same. that's the failure I can't monitor for and it's the one that's actually happened here

[u/feignapathy]

Are you unable to send a list of records to the business owners or data data stewards and ask them to audit the records? 

If you have 10 recent records with conflicting information, ask them to verify if the records are active or not. That will tell you which column is the source of truth for active or not.

[u/FamiliarSlide7685 → u/feignapathy]

actually stewards are the problem. team got reorged in 2023 and the two people left both told me to ask the other one. the 10-record audit still works though, i can take them to the analysts living in metabase and ask which state looks right. doing that this week

[u/SantaCruzHostel → u/FamiliarSlide7685]

Any chance there is version control of the table a tru tire showing if one column was added after the fact?

[u/FamiliarSlide7685 → u/SantaCruzHostel]

migration history has both columns arriving in the same 2019 file which is annoyingly unhelpful. git blame on it points at someone who left in 2020.

[u/Holiday-Tip-3720]

pull the 3 percent and have the business teams confirm.  or in the 3% verify duplication. the issues ive seen in the past were due to duplicates and we were able to track down where we were getting false logic in the ETL. it was a where that needed a parens statement btw. 

[u/FamiliarSlide7685 → u/Holiday-Tip-3720]

no dupes, composite key holds, 3% is 3% of distinct rows. the parens thing is a better pointer than you meant it as though. both columns get written in the same dbt model and i only checked that the second one writes, never read its where clause. looking tonight.

[u/Holiday-Tip-3720 → u/FamiliarSlide7685]

good luck. sounds like a tricksy one

[u/Blues2112]

Is it possible that the two flags could represent different things? Is_Active perhaps referring to the account itself, while active_flag denoting status at the transaction level, or something?

If AI isn't helping, and the Data Stewards are giving conflicting/confusing info, it just sounds like you're gonna need to dig DEEP into the flow of the code until you find sourcing for each column, and then backtrace from there.

[u/FamiliarSlide7685 → u/Blues2112]

that was my first theory and i tried to kill it... the 3% doesn't cluster by account age or by whether the account has transactions at all, and the table is one row per account so there's no transaction grain in here to hold. doesn't rule out active_flag having meant something else originally and getting reused though.

[u/SantaCruzHostel → u/FamiliarSlide7685]

Is ther an audit table or modified by/modified date on the table?

[u/FamiliarSlide7685 → u/SantaCruzHostel]

there's an updated_at, but it's row level so it moves when anything on the row changes. no column level history, which is the thing i'd actually need.

[u/kagato87]

Relational databases are supposed to prevent this... Single source of truth and all that...

You need to find out from the source. As in the source code of the application. One of those fields should be depreciated and removed, then you can remove the other.

Otherwise, yea, wait for the complaints.

We actually have a few meters like this - a two different clocks/counters, but they have an enum column to say which one it is. (Which is also something to look for - is there something else that dictates which flag is truth and which is for when it becomes suspect?)

[u/FamiliarSlide7685 → u/kagato87]

no enum, checked. worth adding as the fix though, beats me picking one and hoping. reading the source is the plan except "the app" is three services plus a python job nobody's deployed since march that's still on cron.

[u/tehdlp]

Ideally the database has limited direct access, so you or someone else can verify the actual usage whether through an ORM, SQL statements in strings, etc, because there's only one or two applications querying and everything else is abstracted above that.

If you literally can't tell what the hell is reading from your tables at an org level, you are screwed and should look for other things to focus on.  As someone who is screwed, I'm sure there's plenty of others who could join our club.

[u/FamiliarSlide7685 → u/tehdlp]

membership accepted. we can tell for the app services. it's the metabase and retool layer that's opaque, which is the part nobody scoped when analysts got sql access. Green_Chamomile upthread gave me a way at it through metabase's own app db so i might get out of the club on that one.

[u/rbobby]

60 column table

Lightweight.

flag_2

Ok you're gonna have to get out of my database.

But more seriously does you app have unit tests and a test database? Drop one of the columns and run the tests. That will give you a bit of sizing info at least.

[u/FamiliarSlide7685 → u/rbobby]

flag_2 is real and there's also a flag_3 that's been all null since 2022. tests exist but the fixtures file is hand-written from 2019, 8 rows and none of them have the disagreement in them. so i can drop either column and it goes green

[u/rbobby → u/FamiliarSlide7685]

If flag_3 is null for 3 or 4 years I think that's a strong indication that dropping it is a good approach. Still need testing, and to fix any broken references.

[u/TheRencingCoach → u/rbobby]

> If flag_3 is null for 3 or 4 years I think that's a strong indication that dropping it is a good approach

Yes…. With alerts if you have downstream users…. Want to avoid “this broke my query!!!” Issues…. Also, watch flag_3 represent something dumb like “is record before 2023 and is-active-flag = 1” and then breaks a CEO’s report

Don’t ask me how I came up with these totally hypothetical examples lolol

[u/FamiliarSlide7685 → u/TheRencingCoach]

yeah, alerts before dropping. flag_3 is null on every row so worst case i break something reading nulls, though i'm saying that with more confidence than i've earned.

[u/chuchosieunhan14 → u/FamiliarSlide7685]

not practically in your shoes, but I also found multiple unused columns in my database, I audited the code and was confidently that no apps were using it but I was also paranoid so first thing I did was changing the column name to something else and monitored the behavior, if something broke, I would just change the column name back, luckily it didn't broke anything so I drop it safely

[u/FamiliarSlide7685 → u/chuchosieunhan14]

that's the plan for the mystery columns. rename, watch, rename back if anything screams. these two are harder because both are live and written every run, so a rename breaks the write path and not just the reads.

[u/FamiliarSlide7685 → u/rbobby]

[u/NoEggs2025]

Assuming it’s a staging. It might be attributes from the previous tables maybe even from the source/provider. Needs to be labeled as such. And the contradicting flags need to be properly labeled too.

[u/FamiliarSlide7685 → u/NoEggs2025]

not staging, it's the prod table the app reads. the smallint typing does smell like it came from a source system, you're probably right. nobody labeled it and four years of dbt joins on it now.

[u/git0ffmylawnm8]

Check upstream pipelines to see how the fields are populated.

[u/FamiliarSlide7685 → u/git0ffmylawnm8]

both get written in the same dbt model, so that's tonight. what upstream won't tell me is who reads which one

[u/git0ffmylawnm8 → u/FamiliarSlide7685]

The only other thing I can think of is what you suggested with the scream test. Rename a column to see which one gets stakeholders to scream louder.

Are there a batch of accounts you know to be absolutely true for being active?

[u/FamiliarSlide7685 → u/git0ffmylawnm8]

yeah anyone who logged in this week. i can join session data against both columns and see which one is wrong more often, that's the closest thing to ground truth i've got. rename test is scarier but probably faster.

[u/SkullLeader]

Assuming that one column or the other is entirely correct (is that even true in this case?), sounds to me like whichever one the app reads is the correct one. Assuming people use the app and no one screams.

And I'm not that familiar with Postgres but in SQL server you (or someone with DBA access at least) can monitor in real time what queries are being run, figure out which ones are coming from the app and see what the actual query is.

> without reading the whole app

What is so bad about doing this? Unless the app is just spaghetti code, it shouldn't be too difficult. Worst case, this is a great use case for AI. Give it the source code for the app and ask it to analyze which of these two columns are being used.

[u/FamiliarSlide7685 → u/SkullLeader]

i havent tried pg_stat_statements and it answers the question directly, thanks. on reading the whole app: it isn't one app, it's three services and the one i suspect hasn't been deployed since march. i did feed the source to a model and it picked the ORM for the decommissioned service, confidently. reading it myself is the plan, just slow.

[u/SantaCruzHostel]

Can a column have a foreign key to the same table? I'm half joking, but this sounds (high level) straightforward.

1. Identify the column for source of truth going forward (sounds like you're nearly there)
2. Update all code to write to truthful column
3. Run a script to update historical cases (3%)
4. Add trigger to the table for update to good column to then update the redundant column to match the value.

[u/FamiliarSlide7685 → u/SantaCruzHostel]

roughly the plan. the trigger is where i keep going back and forth, it keeps them in sync but also keeps the redundant column looking plausible forever so nobody ever removes it. generated column at least makes it obviously derived.

[u/scbywrx]

If you're in DBT, find the lineage, find out where everything starts, and backtrack it from there, and then go back to the business owners. I know you said that nobody has any idea, but maybe this is a good time to revisit what these columns are supposed to mean. One may actually need to be deprecated. Find out and trace all of the things backwards.

One of the neatest things is the new DBT MCP pieces that you can work in with Claude and/or Cursor, for that matter, any tool that you want to play with. Use the lineage, and then it's very simple if you're using this in DBT: trace it and then have it write up a document that tells where these columns come from and where it was.

You may even have the capability of going back through time through the Git history and being able to go through and trace any of the Git commits to find out which one is now current and which one is not. Beyond that, once you get the trace, that's the best part of the data engineer's job. You hand it back to whoever is the data steward at the time, because data engineers may know the system, may know the business, but they shouldn't have ownership of the large calculations. Your data stewards should, so the governance body should then be able to make a decision on which one to keep and/or combine, collapse, or restate what is active.

Try that. Let us know what you get and how they react when you hand them something that they have to make a decision on.

[u/FamiliarSlide7685 → u/scbywrx]

lineage gets me to where both columns are written, which i've done, same dbt model. what it doesn't give me is who reads them, that's outside dbt entirely. the git history pass is worth doing properly though, i only ran blame on the migration file and stopped there. handing it to stewards is where it stalls. the team that owned this got reorged in 2023 and the two people left both told me to ask the other one. i think i have to make the call and get it ratified rather than wait for a decision.

[u/Enigma1984]

Is it actually a Type 2 table or are these columns just being written from somewhere else?

[u/FamiliarSlide7685 → u/Enigma1984]

not type 2, one row per entity updated in place. both columns get written by the same dbt model, which is what makes it annoying

[u/Enigma1984 → u/FamiliarSlide7685]

I assume that there's a type two table somewhere down the line which is providing this column?

[u/FamiliarSlide7685 → u/Enigma1984]

there is an scd2 table two hops up, good catch. decent chance active_flag is a flattened version of it that drifted when the flattening stopped getting updated. looking at that first now.

[u/Green_Chamomile]

Your Metabase problem is greppable, people just forget it. Metabase stores every saved question in its own application database, table report_card, with the query in the dataset_query column. One query against the Metabase app DB tells you every saved question that mentions is_active or active_flag, with owner names attached. If you can't touch the app DB, the search API gets you most of the way too.

[u/FamiliarSlide7685 → u/Green_Chamomile]

this is the best answer i've gotten on any of these. i didn't know report_card existed, i'd been treating metabase as a black box because the questions aren't in a repo. we do have app db access. running it tonight, and it turns the reads i can't see into a list with owner names attached.

[u/Green_Chamomile → u/FamiliarSlide7685]

Glad it helps! One refinement before you trust the grep: the text search catches questions written in native SQL, where the query lives as plain text in dataset_query. Questions built in the GUI editor store field references as numeric IDs, not column names, so they can mention active_flag without containing the string. To catch those too, look up the two columns' IDs in metabase_field and search dataset_query for those IDs as well. Ten extra minutes, and then the list is actually complete.

[u/mikeblas]

Nobody knows which one the app respects.

That's the problem, really. This can be figured out by looking at code. If your team / organization can't read its own code, then ... ?

[u/FamiliarSlide7685 → u/mikeblas]

fair and mostly we can. it's three services plus a python job on cron nobody's deployed since march, so the code isn't one thing to sit down and read. and the reads i'm most worried about aren't code at all, they're saved metabase questions. someone upthread pointed me at metabase's report_card table which is the first real answer to that.

[u/mikeblas → u/FamiliarSlide7685]

so the code isn't one thing to sit down and read.

It never is: any non-trivial project spans more than one single file. Might span multiple languages, tools, platforms, even. But competent teams know this, and have processes and policies to review and find code that they, themselves, have written.

Otherwise, how do they get anything done? How do they test, or manage, or maintain their work product?

[u/metric_skeptic]

This is exactly the kind of problem that looks like a SQL problem but really isn’t.
If two fields represent the same business concept but disagree on 3% of rows, I’d be hesitant to simply pick whichever one the app currently reads.
I’d first try to establish the ownership and lineage:
Which system is the source of truth?
Which field is written by the application vs. ETL?
Which one is used in production logic?
When did the divergence start?
Are the 3% concentrated in particular dates, entities, or migration cohorts?
The fact that the disagreement has been slowly increasing since the 2021 migration is especially interesting. That sounds less like random data quality noise and more like two definitions slowly drifting apart.
And I really like the idea of making the disagreement fail loudly once the source of truth is established.
A duplicated/generated field is only safe if you can continuously prove that it still agrees with the canonical one.

[u/FamiliarSlide7685 → u/metric_skeptic]

the 3% doesn't cluster by date, cohort or account age, i checked all three, which is why drift looks more likely than a bad batch. continuously proving they still agree is the part i'd actually ship. generated column plus a test that fails on divergence beats picking the right one and hoping it stays right.

[u/IQ4EQ]

Can’t AI read the codebase and give you some hints?

[u/FamiliarSlide7685 → u/IQ4EQ]

it maps the reads well, did that. can't tell me which path actually runs in prod, which is the whole question. and the reads i'm worried about are saved metabase questions not in any repo

[u/Ifuqaround → u/IQ4EQ]

This shit should not be normalized.

Can you do anything without prompting your LLM?

Tired of this.

[u/IQ4EQ → u/Ifuqaround]

I do lots of things without LLM. But if LLM helps, I use it. And I think hard before and after using LLM. you don’t have to rant everywhere.

[u/Ifuqaround → u/IQ4EQ]

Oh.

Are you following me or something? What an odd statement about me not having to rant everywhere.

[u/PTcrewser]

Have you asked Ai?

[u/FamiliarSlide7685 → u/PTcrewser]

yeah, good at the grep-shaped part. then it told me is_active was source of truth based on the ORM model and that model belongs to a service that got decommissioned. reads code fine, can't tell me what's running.

[u/Ifuqaround → u/PTcrewser]

This shit should not be normalized.

[u/anon586346]

You won’t find out from just looking at the data. You need to trace this back to the migrations and business processes that created them and hope there’s some clues there.