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.

Hey everyone,

I’m currently setting up a new project involving SQL and I’m looking for some AI tools that could help make the development process easier and more efficient.

I’m particularly interested in tools that can help with things like:

• Writing and improving SQL queries
• Designing database schemas
• Debugging SQL errors
• Generating or optimizing queries
• Understanding existing databases/tables
• Creating test data
• Documentation
• Connecting SQL databases with other development tools
• Anything else that can save time during development

I know there are a lot of AI tools out there, but I’d rather hear from people who have actually used them in real projects.

What AI tools are you currently using for SQL/database work, and which ones have genuinely made your workflow easier?

Also interested in hearing about any tools you tried but wouldn't recommend, and why.

Thanks!

Hands down, man, seriously!

At some point, after writing JOIN after JOIN, SUM, RANK, CTE, Subqueries, Window Functions, LAG, LEAD, WHERE vs HAVING, DATETIME, ORDER BY DESC...

There has to be a moment where you say:

“Alright bro… enough SQL gymnastics. Let’s actually use this thing.

So what’s that point? When do you believe that you can start applying it like a real analyst.

been dealing with duplicate customer records in a project for uni and kept rewriting the same GROUP BY/HAVING query every time so i just built a cli tool for it in the end - works with both mysql and postgres, dry run by default so nothing gets deleted unless u explicitly pass --confirm and it backs up to json first just in case. still a student so the detection logic is prob missing some edge cases; that's the part i actually want feedback on tbh. happy to share the repo if anyone's curious, can drop the repo link

I am a Ruby on Rails developer. I’m looking for some skills that can help me self code review for sql part. I use Claude. Like that can guide me not to write sql that are anti patterns etc

For example, if a customer dataset has nulls, duplicates, invalid emails, or incorrect values, how do you identify and flag these records as good or bad? What tools or query approaches do you use?

How do u handle query performances when the same tables are being hit by both app queries and AI generated SQLs from any AI tools such as Codex, CLaude, Genie etc
Curious if you separate workloads or optimize at the query level.

this is a distinct list of warehouse names from a table in the db im using to practice data cleaning in mysql. i want to capitalize the initials of all words in the column. i made my own logic for this whihch is (dont judge pls im a self learner)

and this is the output i get:

i do get what im doing wrong to get this output, but i can not figure out how to go about the standardization. how can i correct my query? and is there a more efficient way of capitalizing initials than this?

I’ve come across queries that are extremely compact and technically efficient, but difficult for someone else to understand or modify later.

For example, a query might use nested window functions, multiple conditional expressions, and several transformations to solve something that could also be written as a few simpler steps.

Where do you personally draw the line between elegant SQL and over-engineered SQL?

Do you prioritize fewer lines, query performance, or maintainability when these three goals conflict?

Im working with some large tables in spark sql that has around 300 mil records and they are quite wide as well maybe around 80-90 columns.

We frequently have to perform filtering and aggregations for reporting purposes, I'm trying to understand when should I use partitioning and when should I use indexes.

For instance if I want to filter by date will it be better to partition by date or should I use an index on the date column, which one would help with performance?

I figured it's worth a shot to ask here since I asked it already in the SQL Server subreddit...

Is there a way to use Microsoft SQL Server management Studio 22 without writing anything at all (no folders, shortcuts, junctions, symbolic links) in My Documents? I just installed the latest client from the official website without launching the client yet, and I want the SSMS workspace (configuration files, data files, etc.) to be transferred to a different location in the C drive. I failed in my first try, and both Claude and Gemini gave me conflicting answers.

To summarize, I don't want the folders below or anything related to it appearing inside My Documents.

C:\<My Documents Folder path>\SQL Server Management Studio

C:\<My Documents Folder path>\SQL Server Management Studio 22

I’ve been revisiting SQL Server PIVOT for reporting scenarios where row-based data needs to be transformed into columns.

A few things I find useful about PIVOT:

• Cleaner reporting output
• Easy aggregation across categories
• Useful for summary-style queries
• Can be more readable than complex conditional aggregation in some cases

I’ve also added the working SQL examples here:

GitHub:
https://github.com/laxmikant-geek/sql-server-examples/tree/main/pivot

And I wrote a more detailed explanation here for anyone who wants the walkthrough:

https://geeksarray.com/blog/how-to-pivot-data-in-sql-server

For those working regularly with SQL Server — do you prefer PIVOT, or do you usually use SUM(CASE WHEN...) for these kinds of transformations?

Probably breaks rules but a quick guide on what not to name things I less you want accidents

Hey, I’ve been working on an open source project called pgfathom. It came from a problem I ran into quite a lot at work with legacy databases.

You’ll sometimes have something like orders.customer_id -> customers.id that the application has treated as a relationship for years, but there’s no actual foreign key in PostgreSQL. So it won’t show up properly in an ERD, and nothing is stopping orphaned rows from getting in.

pgfathom looks for those relationships using the catalog, column names, indexes, existing FKs and JOINs found in views/functions. It then checks the candidates against the actual data.

If it finds orphans, it gives you a query to inspect them. If the relationship checks out, it generates the FK DDL and an index when needed. It never applies any of it. The CLI runs read-only and only generates SQL for you to review.

One thing that helped a lot with weird legacy schemas was letting it learn naming conventions from the database itself instead of assuming everything looks like customer_id.

I tested this on a municipal schema with 277 foreign keys. With half of the FKs left in place so pgfathom could learn the naming pattern, recovery went from 17.3% to 84.9%. If I remove all of them, it drops to 16.6%.

There are also some safeguards for running it against real databases: read-only sessions, query timeouts, limited concurrency, and tests to make sure table values don’t end up in output, logs, JSON or errors.

I used AI as part of my workflow too, mostly for research and implementation, so I’d rather mention that upfront.

It’s still early and I’d really like to test it against schemas that look nothing like the ones I’ve been using.

GitHub:
https://github.com/lvcas-dotcom/pgfathom

If you work with old PostgreSQL databases and feel like giving it a try, I’d appreciate the feedback. Finding cases where it gets the relationship wrong would actually be very useful.

(English isn’t my first language, so apologies if anything in the post sounds a bit off)

So I’m a student and my background is not technical or STEM based at all. I am a fast learner and I know I will need SQL for future jobs, however, I’m intimidated by it because I’m not the strongest person when it comes to math. So I get seriously intimidated by it. It feels like I have to memorize hundreds of commands

I built this because I wanted to react to PostgreSQL changes from Python without polling, without triggers, and without pulling in a whole CDC platform.

It consumes PostgreSQL logical replication and exposes committed transactions as an async stream in Python.

What it does:

• Keeps a durable checkpoint. If the process dies, it resumes from the last transaction it actually finished, not the last one it started.
• Bounded delivery queue, so a slow handler doesn't let memory grow without limit.
• Reconnects automatically after the connection drops.
• One dependency: psycopg3.

The transactional outbox is one use case, but it works with any published table.

GitHub: https://github.com/mochams/walbox

Curious to hear where this wouldn't fit your setup, or what's missing if you've solved this problem a different way.

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.

My company uses superbase, a software from 1980's for relational database...

Our computers are on windows 7, emulating windows xp. Safe to say our computers are dying.

The company is a TPA service for pension plans. We use superbase for client Tombstone information, work in progress, entering the clients assets, contributions, employee information etc. A lot of our work function is reliant on superbase.

I am very new to this world, don't have any experience in coding and don't know anything about any of these different programs. I just want to learn so we can convert and we don't lose our entire lifeline.

We are a team of 5 people. Small firm. We have our own servers, would not want to be on cloud, need everyone to be able to access the servers at the same time as we all have different clients and need to reach information. Not sure if its possible but would love for it to work on PC desktops and remotely on mac if possible, however, not a dealbreaker.

I have done some baseline research, this is what most redditors have suggested. Which would be the best for me to learn and hopefully be successful at transitioning over our information?

Any help in finding a direction to go to would be very helpful.

Thank you in advance.

If there is any more information needed that can help you in assisting me, please comment and i'll answer!

Found this question on a reddit post, thought I should share it in the community

Postgres 15, if that matters for the answer.

got handed a partner export to reconcile against our customer table. different ID schemes entirely, no overlap, no mapping doc, and the person who built theirs left last year.

what I did was fuzzy match on lower(trim(email)) plus last name, then eyeball a sample of a few hundred rows. it shipped. it is also clearly bad, because I picked those two fields because they were populated, not because I had any reason to think they were good discriminators. I have no idea what my false match rate is. I just know nobody has complained.

the part that bothers me is that a bad join doesn't announce itself. a wrong customer count looks exactly like a right customer count. at least a syntax error tells you something happened.

so what do people actually do here.

do you block on something cheap first to cut the comparison space down, or just run the full cross product and filter? does anyone compute a match score and hold back anything below a threshold rather than committing it? and if you do that, what threshold and did you pick it for a reason or the way I picked my fields.