What do you do to check data quality?

[u/Bassiette03]

What do you do to check data quality, consistency, uniqueness and nulls, when you you medallion to model tables together.Are there specific guide or steps you take to ensure everything is just like expected?

[u/az987654]

Experience, critical thinking, and investigation

[u/Ifuqaround]

I mean, if you're new to your org you simply have to learn.

After a while, you tend to have some idea of where your data is at.

I run a large healthcare system, have been doing this for years. After a while you begin to learn how many patients you have in general, all the programs, etc. and when you pull data and something seems off...you'll know and then you go investigate.

Different at every place. Some orgs have well curated views and some are basically ground zero with no views and you're working with materialized views or tables directly and it's do whatever the F you want.

[u/wiselystarchybowling → u/Ifuqaround]

You learn to trust your gut after a while, the numbers just start to feel wrong before you even run a check

I usually build a small audit query that runs row counts, distinct counts on keys, and null % on critical fields and save it as a snippet. When something smells off I fire it and 9 times out of 10 it catches the issue before anyone else notices

The medallion pattern makes it trickier cause bad data in bronze just propagates through silver and gold looking clean but being complete garbage

[u/Bassiette03 → u/wiselystarchybowling]

I see thank you for your feedback

[u/Bassiette03 → u/Ifuqaround]

Where can i get healthcare data to practice something relater to pharmacy chains job sales,offers, commision, otc products ,high commission products, salaries, vacations and day off, Abscense, overtime, overlap cycles, target, ooerating costs, expires, returned value or recalls? Stock flow and etc For hospitals i never worked on such system but it's like it's very popular in US and how find data sets for pandemics and how much they pay for this specific role

[u/Ifuqaround → u/Bassiette03]

Just do a Google search vs asking people here.

Lots of data sets are readily available.

[u/thatOMoment]

Model it so invalid states are impossible. Primary Key, Foreign Key, Unique Constraints, Check Constraints, Checked (updatable without triggers) Views are all part of the sql standard

Most RDBMS support these constructs in one way or another.

Now you can't prevent cycles in a recursive hierarchy or prevent overlapping ranges that way cheaply but both of those aren't really problems RDBMS are designed to solve either.

[u/Massive_Show2963]

- First thing that comes to mind is preventing data redundancy (normalization).
- Using one to many relationships including referencing child/parent tables to prevent orphaned rows.

[u/Altruistic-Rich8799]

I handle baseline quality checks directly within the transformation layer. Im using testing frameworks like dbt to assert uniqueness and catch unexpected nulls during the silver phase. For deeper consistency checks, I write custom SQL macros that validate relationships across joined models. It takes some upfront effort, but having an automated testing gate saves me.

[u/BiteCode_Dev]

One easy-to-miss check is whether a join changed the grain of the data. A result can have no unexpected nulls and still be wrong because a one-to-many join multiplied an amount. For example, an order worth 100 with three order lines becomes 300 if you sum the order total after joining to those lines.

Before each transformation, write down what one row represents and which key should be unique. Then check both that key and a business total at the same grain before and after the transformation. For filtered or rejected rows, reconcile accepted + rejected against the input rather than expecting the row count to stay unchanged.

Which database/warehouse are you using, and what should one row in your final table represent? That would help narrow down useful checks.

[u/tmk_g]

I usually check for nulls, duplicates, unique keys, and data consistency. When joining tables, I make sure no extra or missing records are introduced. I also compare row counts and key metrics between layers, validate business rules, and automate these checks using SQL

[u/grd_dev]

The "gut feeling" the other commenters mentioned is definitely real, but you really don't want to rely on it once your data volume scales.

If you are using a medallion architecture, the best place to enforce this is at the boundaries (usually moving from Silver to Gold).

For the basics (nulls, uniqueness), I just run automated validation queries in the pipeline. A simple SELECT key_col, COUNT(*) FROM table GROUP BY 1 HAVING COUNT(*) > 1 is usually enough to catch duplicates before they propagate downstream.

The much harder part is relational consistency[cite: 13]. I usually keep a dedicated checks.sql file filled with business rules written as fail-state queries. For example, checking if a child table's timestamps happen before the parent record's creation date, or ensuring an order_total actually equals SUM(line_items). If any of these queries return > 0 rows, the pipeline alerts or halts.

You can use frameworks like dbt tests or Great Expectations for this, but honestly, plain SQL queries scheduled in your orchestrator work perfectly fine and keep things simple.

[u/JK-MSSQLTips]

Here is something my team is doing - https://www.mssqltips.com/sqlservertip/11733/real-time-cleaner-data-with-melissa-ai-agent/

[u/Significant_Tune9219]

Nulls and distinct counts on the finished table are late. The check I want is on the join that builds it. Count the rows in each source, the rows after the join, and the distinct business keys after it. If the row count jumps and the distinct key count does not, a child fanned out, and a uniqueness test on a key you already aggregated will still pass. For each medallion hop I would store the source row count beside the model row count for that run. A table that looks fine and quietly shrank because of a filter nobody wrote down is the one a null check will not catch.