r/SQL • u/donewitheverything26 • 2d ago
Discussion where do you actually write down what a column means
Inherited a schema where roughly half the columns are self-explanatory and the rest are things like flag_3 and val_b. Person who built it left. There's a Confluence page describing six columns, last edited before most of them existed.
I've been using COMMENT ON COLUMN because it lives with the database and can't drift into a stale wiki. Downside is nobody looks at it, it doesn't show up anywhere people work, and I've no way to know if a comment is still true after a migration.
Things I'm unsure about:
does anyone actually keep COMMENT ON up to date at scale, or does it rot the same as the wiki just less visibly
if a column's meaning changes but the name doesn't, is there anything that catches that, or is it purely a review discipline problem
and for the columns nobody can explain at all, do you leave them, drop them, or keep them with a comment saying unknown
I've been profiling the values to guess — cardinality, null rate, distributions — which narrows it but never gets me to what the thing means.
58
23
u/JeLuF 2d ago
where do you actually write down what a column means
Usually in git where all the sql files live that I used to create/alter the tables.
4
u/donewitheverything26 2d ago
that works when you own the history. these tables predate me and the DDL that created them isn't in any repo I can find-- I've got the schema and no record of how it got that way. for the ones you do control, does the git history actually answer 'what does this mean' or does it tell you what changed and leave the meaning to the commit message?
4
u/adamjeff 2d ago
SQLcl can generate all the DDL for you... Not sure it helps but at least you can stick it in a repo and slowly comment it up
2
u/JeLuF 2d ago
At my workplace, we're currently in the process of generating a cross application metadata catalog, including a data lineage documentation (Which batch job copies and processes data from where to where, which ETL jobs exist, etc), similar to what Apache Atlas can do. This projects also implements quality controls that detect deviations from the documentation and will create issue tickets.
8
u/Zestyclose-Turn-3576 2d ago
When I worked with a COTS marketing system it had database tables named as "T0000001", "T00000002" etc, and columns "T0000001C001" blah blah.
We put views over the top of each one to provide meaningful names for the reporting system, and accessed those instead.
6
u/donewitheverything26 2d ago
T0000001C001 is worse than anything I'm dealing with, so thank you for the perspective. views as the documentation layer is clever because the name can't rot separately from the thing -- if the view breaks you notice. the bit I'd want to know is how you worked out what to call them in the first place. did you have a vendor data dictionary or did someone reverse engineer it from the app behaviour?
3
u/Zestyclose-Turn-3576 2d ago
It was detective work, I think – just running queries to see what was in them.
And I suspect that when it was originally installed there were options to assign proper names, but someone hit "skip this step" on some stupid wizard.
2
u/Breitsol_Victor 2d ago
May have been intentional. Obfuscation was a thing. Vendor knows and writes using proper names then changes all the names before delivery. You had to purchase services and reporting from them.
1
2
u/LetUsQuest 1d ago
We do something similar. Almost all of our tables get a corresponding `_view` created alongside it; the view might also conveniently join some secondary tables to extend descriptions and such - nothing too expensive, but it's enough to make the view more convenient for most usage, rather than the base table. And then we can just add whatever comments and explanation to the view code that we need. They become a de facto data dictionary for us. Works pretty well, and the regular usage of the views requires that they're kept up to date with schema changes.
8
u/BrupieD 2d ago
We usually have Excel source to target mappings and use extended properties (SQL Server). Source-to-target documents get cumbersome, but they tend to be really complete. Extended properties show up in table properties and when you script out the create table statements -- they're highly accessible although brief.
3
u/donewitheverything26 2d ago
extended properties showing up when you script out the create is the thing COMMENT ON doesn't quite do for me, that's a genuinely better surface. on the STTM, how do you keep the Excel honest? mine would be accurate the day it's written and wrong within two sprints, and unlike a comment it isn't sitting next to the thing it describes.
6
u/iama_bill 2d ago
I’m a fan of the data dictionary since it’s easier to query. In my case, the database gets deployed in many environments and the schema will vary depending on the code release of an upstream transactional system. Even though there’s a lot of overlap with information_schema.tables and columns, it adds things like the subject area and category of table, how it links to the source, descriptions, etc.
You ultimately need someone to be willing to update it unless changes are made as part of a broader pipeline that reminds people to describe whatever they’re adding.
3
u/donewitheverything26 2d ago
'you ultimately need someone willing to update it' is the actual answer to my first question and I was hoping someone would say something else. the reminder-in-the-pipeline bit is interesting though. is that a hard gate, as in the deploy fails if a new column has no description, or a nudge people can skip? I suspect anything skippable gets skipped and anything blocking gets filled in with "TBD".
5
u/RecordGlobal4338 2d ago
You need a data catalog , I used cloudera one, very useful.
1
u/donewitheverything26 2d ago
catalogs are good at distributing what someone already worked out. my problem is that nobody worked it out -- there's nothing to put in the catalog for flag_3 except a guess. did Cloudera's help you with genuinely unknown columns or mainly with making known ones findable across teams?
1
u/RecordGlobal4338 1d ago
it helped, besides the column name you can enrich your schema with tags/description/owner/sensitivity.. you can also see the tables or the script that use it..
1
u/Healthy_Company_1568 2d ago
We use Alation for our data catalog - it’s a tedious task to get it populated but worth it for future users.
3
u/satans_weed_guy 2d ago
The definition/description of a column belongs in that column's metadata. Documentation can be built from that, and AI can be skilled to read from that. As far as developing psychic powers to figure out what previous developers intended, I can't help you there. But I do know your pain.
7
u/Erasmus_Tycho 2d ago
Honestly many of us wouldn't have a job if the original developers had built sufficient metadata.
2
u/donewitheverything26 2d ago
on building docs from the metadata -- is that a job you run or something in the deploy? my worry with generating from COMMENT ON is that I'd be publishing my own guesses back at myself in a nicer format and eventually forgetting they were guesses.
3
u/creed_1 2d ago
I’ve never used comments on columns. I’ve always made sure the columns name had meaning to it and it would be in the sql or plsql code that shows what the column does there.
1
u/donewitheverything26 1d ago
yeah if the name are right from the start none of this comes up. I didn't build these and the person who did is gone, so I'm a step behind that. the plsql part is worth considering though. some of these columns do get touched by procs and I haven't read through them properly yet.
3
u/ihaxr 2d ago
Create a view and name the columns in the view with more care
I heavily use extended properties, but wouldn't expect it to contain column level information unless a column requires special care
1
u/donewitheverything26 1d ago
fair point on not documenting everything. I've been treating every column as needing a description when really it's the twenty or so weird ones that matter ngl. nobody needs me writing customer id next to customer_id.
2
u/cleverchris 2d ago
I understand your pain. And the reality is there probably isn't a solution you (or me) would be satisfied with. I have found that if you can define the unit of the field it goes a very long way toward providing the context. If a unit is not concievable I try to use a binary (t or f; y or n) if I need more I try and fit it to a 0-1 scale. If all your fields have 1 of these 3 things you should be able to work out their meanings. Of course I am not at all talking about char fields.
1
u/donewitheverything26 2d ago
the unit thing is the most useful thing anyone's said to me about this and I hadn't thought of it as the organising idea. where it stops working for me is two columns with the same unit. I've got three that are all integers 0 to 4, low cardinality, no nulls. one is almost certainly a rating, one might be a tier, one I have no idea. unit is identical across all three and yeah the char fields are where it really falls over. four-character codes, 12 distinct values, no lookup table anywhere. do you treat those as a lost cause or is there something you do?
2
u/cleverchris 2d ago edited 2d ago
You have to go to the users and find the people with the tribal knowledge of the software and have them explain what those things mean. You can then either document and maintain or shift the backend values to something more rational and maintain original values as labels.
Edit/add: bit in the weeds but I wouldn't consider a 0-4 int a unit. It's an incomplete enum. That's just me though.
2
u/feignapathy 2d ago
source to target mapping
STTM
we just use Excel files right now
1
u/donewitheverything26 2d ago
excel STTM seems to be the honest answer for most people here. does yours stay current or is it accurate as of whenever the last person cared? not being snide mine would be the second thing.
2
u/feignapathy 2d ago
we do not update regularly
but it is sort of a "hey, it's been a while" kind of thing where we acknowledge we need to go and refresh them, try to get them in line
last time was back in March
so definitely not diligent
probably go through them in October and make some updates/refreshes
2
u/db_tech_dev 2d ago
honestly COMMENT ON rots too, just slower and quieter than the wiki because nobody's forced to look at it during a review. what actually caught drift for us was adding a dbt model with column descriptions in the yaml - it's still just docs, but since it sits in the PR diff next to the SQL change, people actually update it when they touch the column. for the totally unknown ones we just prefix them unknown_ or add a comment saying "meaning lost, inherited schema, do not trust" instead of guessing, saves the next person from repeating your profiling work.
2
u/kagato87 MS SQL 2d ago
The calling application should have all of this documented in the code.
We have a description field in the xml that orm uses to generate scripts, and enums that explain the contents. (Enums that get exported to an enum table in the database even, so I can support codes that product hasn't even thought of yet.)
2
u/donewitheverything26 1d ago
exporting the enums into an actual table so you can hold codes product hasn't invented yet is neat. stealing that shape even if I can't do the xml half of it and the caling application having it documented is the right answer it's also the exact thing I don't have. no source for the app that writes most of this.
1
u/kagato87 MS SQL 1d ago
Originally they did it so I would quit asking them for an updated copy of systemcode.cs. They eventually gave me repo access anyway though, because so much of what I do ends up in source anyway
The documentation doesn't have to be in XML specifically, just in a central location in the code base. Does you application have an installer or a package of sql install scripts? That location works too.
Even just a standalone database diagram and readme could serve.
2
2
1
1
u/ArielCoding 2d ago
For the columns nobody can explain, try looking at when they change, if you have an updated_at, audit log, or CDC history, find a few timestamps where the mystery column’s values flipped, then check what else changed at the same moment.
1
1
u/Blomminator 1d ago
It's really annoying if a name is off. Recently I had to connect with an API, and the username/token was stored in the db. So.. I keep trying and trying... And after half a day or frustration.. I ask my coworker.. "hey, you use this right? Why can't I connect?"
And the answer was... "Yeah the token is not a token, but a pw. Couldn't get token connection working so I switch to another option, and never renamed the column".
Well.. that's great! Thanks!
1
u/Top_Community7261 1d ago
I use Confluence; it's simple and the features work well for documenting a database. ,
1
u/KatFromSisense 1d ago
For the columns that nobody can explain, I'd keep the guess separate from the definition. Something like: status = unknown, suspected meaning = customer tier, evidence = values 0-4 / changes with X, owner = whoever can confirm it.
The profiling still gives you something useful to work with. But I wouldn't let "probably customer tier" slowly become "customer tier" just because nobody revisited it for a while. If nobody can confirm the column, I'd keep it and leave the uncertainty visible.
1
1
1
u/chaosink 2d ago
I do not envy you your task and wish you good luck. I have gone through this several times and it suckssucksit makes you feel better, the worst one was figuring out a database that was built in Korean, hacked by Turks, and then plopped in my lap to figure out the columns which were a combination of both. Our front end was a MMORPG so we had people in game doing things to leave markers we could find in the columns.Canary in a coalmine stuff. Do you have any control or access to what is putting data into the db?
That was back when Netscape was just getting overtaken by Explorer so it sucked. If I had to do it today, I would use my local LLM to assist since I could feed it the backend, the front end code, and ask it to do a massive reference check with confidence scores. It would have been so much easier with a model trained in the languages.
2
u/donewitheverything26 1d ago
ngl korean schema hacked by turks is going to stay in my head a while and leaving markers in game so you could find them landing in the columns is properly clever I'd never have thought of that.
partial access. the ETL I can read, the legacy app is a jar nobody has source for so I can see what lands but not what decided it...the LLM with confidence scores across front end and back is what I'd try too if I had the front end to feed it.
1
u/chaosink 1d ago edited 1d ago
Then all you can do is guess based on the content of the column. You're doing everything you can in the absence of any context.
Edit: The game involved was called Knight Online. When the game got taken over by Turkish fans, we ran a private server which ended up as big as one of their regional servers. A few thousand players ended up paying for half of the dev team's college and I still use code from back then in my job at the college I dropped out of.
32
u/mac-0 2d ago
How are these tables built? Typically if you're using DBT or something you'd document it in the table's config.