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.

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.

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!

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.

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

Hi, I'm looking for a dataset that has:

- Messy, real data, with enough messiness for proper data cleaning to be needed
- Enough information for actionable interesting insights
- Be related to something a typical business might care about

I did a project with the olist database and it was fine, but that database has been done to death. I tried contacting local small business for their data in exchange of a free data analytics report, but no luck, has someone tried that?

When revenue, active users, retention, or another derived metric is repeated across dashboards and exports, copying the SQL makes every consumer independent but lets definitions drift. Centralizing it in a view, materialized view, semantic layer, dbt model, or stored function creates one definition, but can hide performance costs and make change control harder. What criteria determine where that logic belongs? I would compare ownership, testability, query-plan visibility, versioning, parameter needs, refresh timing, and whether downstream users must inspect the exact calculation. How do you change a widely used definition without silently rewriting historical reports?

Hello, is there a specific system design and software architecture in SQL to build a bioacustics database? The ideia is to relate some vocal recordings with species identifications, localization, acoustics measurements and other informations. it's a bioacoustic information system, where the recordings are the central objects and SQL connects them to biological, spatial, acoustic, and environmental information.

https://github.com/Lezaleas/Reviews-Churn

I had a local llm categorize reviews in the olist database. Then analyzed their correlation to churn.

Should i improve this or is this ready to go?

Currently I am doing projects in SQL i need to do projects for Data Analyst Role so Please recommend project ideas which I need to showcase for project portfolio so I need projects at advanced level .

Anyone interested in doing the CMU (Carnegie Mellon University) Database Management Systems course together?

I’ve already covered the basic DBMS concepts. My main goal with this course is to go deeper and understand how database systems actually work internally—things like storage, indexing, query execution, transactions, etc.

If you're interested, please make sure you have the prerequisites required for the course.

If you have the required background and want to learn DBMS internals seriously, DM me. We can follow the course together and discuss concepts along the way.

Hello, I have a solution already but I think it can be done in a faster/neater way.

Let's say we have a table with: ORDER_ID, Article, Value.

For every order that comes in two rows end up in table, example:

ID_1, shoes, pair
ID_1, shoes, price

where price is being pulled from a different table.

I am looking for a way to find all ORDER_IDs that have only one row, because the price didn't exist in that other table so price row didn't form up. It doesn't throw a null value because of the way it's setup, if there is no price the row won't form at all!

I solved this with a standard left joining the table with itself, but I suspect there is a way to this easier?

So I've been a support dev, a dba and an actually developer and a technical consultant in different jobs. I found out I'm being laid off as part of a larger restructuring at my current company. So I got my stuff together and started applying and interviewing. Took a while and finally I got a nibble after applying for a SQL DBA job.

They seem to like me, but then they said I would be a better fit as a SQL architect. They insist I'm a great candidate as all my SQL work was external facing so I was dealing with clients.

On paper, I agree, and there is training during onboarding so I'm not worried about dropping in blind. I'm just having a hard time picturing the day to day.

Can anyone offer their experience?

When SQL queries are used for ETL transformations, how do you make sure the query is producing the expected results?

For example, how do you validate complex JOINs, filters, aggregations, NULL handling, duplicates, and calculated fields?

Do you usually compare the results with the source data, create separate validation queries, or follow some other approach?

I'd be interested to know how others handle SQL validation in real-world ETL projects.

One of the most dangerous things about SQL:

A query can run perfectly… and still be completely wrong.

Here are 4 mistakes I wish someone had shown me earlier.

1. Accidentally turning a LEFT JOIN into an INNER JOIN

SELECT c.id, o.total
FROM customers c
LEFT JOIN orders o
    ON c.id = o.customer_id
WHERE o.status = 'paid';

Looks fine.

But customers without an order have NULL for o.status, so the WHERE condition removes them.

If you actually want to keep all customers:

SELECT c.id, o.total
FROM customers c
LEFT JOIN orders o
    ON c.id = o.customer_id
    AND o.status = 'paid';

2. COUNT(*) and COUNT(column) are not the same

SELECT COUNT(*)
FROM users;

Counts rows.

SELECT COUNT(phone_number)
FROM users;

Counts only rows where phone_number is NOT NULL.

That difference can quietly destroy a report.

3. JOINs can multiply your rows

Imagine:

• 1 customer
• 3 orders
• 4 support tickets

Joining both tables directly can give you:

3 × 4 = 12 rows

Then you do:

SUM(order_amount)

…and suddenly your revenue is magically much higher than reality.

Always check your row count before and after joins.

4. NOT IN + NULL can ruin your day

SELECT *
FROM customers
WHERE id NOT IN (
    SELECT customer_id
    FROM blocked_customers
);

If that subquery contains a NULL, the result might not behave the way you expect.

I usually prefer:

SELECT *
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM blocked_customers b
    WHERE b.customer_id = c.id
);

The lesson I'm slowly learning:

Writing SQL that runs is easy.
Writing SQL that returns the correct data is the hard part.

What other SQL mistake produces perfectly valid-looking but completely wrong results?

I want to make a list of the dangerous ones.

I built an offline in-browser SQL playground called ExNihilo 95 that runs on SQLite WASM and a custom parser.

Live: https://exnihilo-95.vercel.app
GitHub: https://github.com/Mrityunjai-hue/exnihilo-95

Technical breakdown of the SQL engine:

• Dialect Normalization: Parses PostgreSQL, MySQL, SQLite, and T-SQL. Rewrites dialect-specific functions like STRING_AGG and GROUP_CONCAT ... SEPARATOR to work natively within the WASM execution pipeline.
• Relational Joins: Supports INNER, LEFT, RIGHT, and FULL OUTER JOIN (using a 3-pass matching algorithm: left join + unmatched right row collection).
• CTEs & Recursion: Passes WITH RECURSIVE queries down directly to WASM while registering CTE identifiers to avoid table lookup collisions in the JS catalog.
• DDL & Virtualization: Intercepts TRUNCATE TABLE to clear table row counts while preserving column metadata; handles CREATE VIEW dynamically by recording query definitions in a SessionCatalog without storing physical data rows.
• Triggers: Registers CREATE TRIGGER in the catalog and binds event listeners directly in the WASM layer.

The entire engine has 73 unit tests covering AST extraction, aggregations, joins, DDL, and recursive hierarchy traversal.

Try testing your favorite CTEs or complex queries against it and let me know if you run into syntax it fails to rewrite.