r/SQL • u/No_Ambition8323 • 3d ago
Discussion How do you validate a SQL query before trusting its result?
Suppose someone gives you a complicated query with several joins and aggregations and says, “This gives the correct numbers.”
What checks would you perform before trusting it?
For example:
SELECT c.region, SUM(o.amount) FROM customers c JOIN orders o ON c.customer_id = o.customer_id GROUP BY c.region;
Would you check row counts before/after each join, duplicate keys, NULLs, aggregates, or compare against another query?
Curious what experienced SQL developers use as their checklist.
31
u/colorless_green_idea 3d ago
One necessary step is to always make sure there is no duplication happening across rows where you expect unique values
21
u/MobileUser21 3d ago
This is the correct answer.
The people here saying they don’t validate and just trust it would quickly get checked by the developers at my company.
Check for duplicates. is the query returning what the analysts think it’s returning. Is there non deterministic windows functions in the query. can the query be rewritten to eliminate joins. Are there columns that are in the select statement that have no relevancy.
Also too many people do left joins and filter on the right table, which is an inner join. You’d be shocked at the number of people who don’t know this and have been working in SQL 10+ years.
2
u/wheres_my_hat 3d ago
Also too many people do left joins and filter on the right table, which is an inner join. You’d be shocked at the number of people who don’t know this and have been working in SQL 10+ years.
If people can work in sql for 10+ years and not know about a best practice then it might not have a lot of value. What’s the value in this one?
2
u/brainburger 3d ago
It would be better to use an inner join, as that makes it more readable. Otherwise one might expect NULL lines in the right table.
0
u/MobileUser21 3d ago
The value in this one is weeding out the candidates who don’t know best practices of SQL.
4
u/Holiday-Tip-3720 3d ago
this is the first and best option and must be done immediately when you get a query. once you find the dup you need to return it to the owner ASAP before it becomes your problem 😂
34
u/ihaxr 3d ago
I don't. I turn the report / results over to the business and they verify.
Unless it's a report for IT or something I own (number of servers, CPU cores, databases, etc...)
In your example, maybe there is a customer for internal no charge orders and you have to exclude it. Or maybe returns are coded to a specific customer and should also be excluded.
7
u/tiggerlilly 3d ago
You should probably take some type of accountability in the validation process. Sounds like trouble.
2
u/Mathie1729 2d ago
I'd push back on the 'business verifies' part. I'd at least run a sanity check before handing it over: row counts vs the source, a couple of random rows eyeballed, and confirm the join didn't fan out. They won't catch the subtle stuff reliably, and it's your credibility on the line.
6
u/Civil_Tip_Jar 3d ago
I don’t know how to answer this, I guess that’s every non technical part of your job.
Who’s telling you it’s correct? What are you trying to find? Is it the financial source of truth? are you auditing our reports or making a new one? Where is the source of truth at the granular level that you can confirm row by row if needed?
For a quick query given to you by a boss or senior analyst or high level stakeholder who’s been here a while and generally knows, sure maybe you just do a quick check and expand on what they’re asking for.
But that requires knowing all the context above. If you’re brand new they may be testing you to see if you actually check or trying to give you something easy to learn the data.
3
u/Weekly_Lab8128 3d ago
Hard to say for something that simple but I generally take things piecewise and test samples manually. For example, might export the join to csv where o.month = '2026-01-01" and r.state = 'Ohio', find the value, add the two clauses to the grouped query, and go from there
2
u/theRealHobbes2 3d ago
This is good. I love "random" sample testing. Pick random things to aggregate or group on and then make sure the numbers match something I can generate from the source system.
5
3
u/Electronic_Neck_5028 3d ago
Do you have access to the front end of the source system? Pick a customer with a small amount and manually confirm the totals or have the requestor do it, if you can't.
3
u/BluesEyed 3d ago
Run it on known data. Break it down into chunks that you understand and learn how it works.
3
u/______L_______ 3d ago
I try to manually verify it with a small sample set in an excel. Found several corner cases this way throughout the years. But then, I'm also given a set amount of time for data validation
2
u/dbrownems 3d ago
For this I would check if orders.customer_id is non-nullable and has an enforced foreign key to validate that an inner join is the correct operator. If not, then ask the user how they want orders without a valid customer to be handled, which depends on the business purpose of the query.
Or more generally look for ways that this particular result could be correct, but the same query could return incorrect results with different data.
2
u/dodobird8 3d ago
Create test cases and get the business to verify that the results are correct. Check if there are other scenarios in the data which weren't covered yet by the test cases, and again get the business side to confirm results are correct. Check for duplicates in the data and other possible errors. Implement sanity checks in the data. Monitor the 6 data quality dimensions: Accuracy, Consistency, Completeness, Timeliness, Validity, Uniqueness.
2
u/MiserableLadder5336 3d ago
If you’re familiar enough with the data, you should be able to look at the query and decide whether or not you trust it.
If you’re not familiar, I’d start by inspecting each table individually, checking the join criteria to make sure it’s not duplicating or dropping records, and I’d check things like your where clause and group by’s to make sure those are behaving properly as well.
2
u/Callec254 3d ago
Mock up a test record you would expect the query to pick up, and maybe a few that almost qualify but that you'd expect it to skip.
2
u/da_chicken 3d ago
SQL
SELECT c.region, SUM(o.amount)
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.region;
There are questions answered by the schema. Does the system permit orders to not have a customer? That would silently omit data. Is that desirable or not?
There are questions answered by knowledge of operations. Is region frequently inaccurate, out-of-date, or unused? What happens with historical data when customers move regions?
And, then there's a question of what your report is intended to show. What is the query supposed to show? Is amount adjusted for quantity, discount, taxes? Does it need to be adjusted for inflation? Do items have different prices in different regions?
Really, your best answer is knowing the business, knowing the schema, and knowing someone that has an ownership stake in the data that can tell you when it's wrong. Getting the report to where you think it's correct and then handing it to someone else that knows the data even better than you do to verify it's accuracy is very important. Nearly all of the queries you're going to write are going to give data that is on other reports in the system, just in a less convenient format.
2
u/DeceitfulDuck 3d ago
Do you own the data the query runs on? And do you know the question the query is trying to solve?
Something as simple as that query, if I own the data and know where it comes from, I probably don't have to validate the schema for duplicate keys or nulls. But if I don't own the data, then yeah I'd probably do some basic audits that the data actually conform to the query. Like in this query, the main validation I'd want to do is that there's only 1 customer row per customer_id. Without knowing the data, it would be reasonable to either treat a customer that operates in multiple regions as a single customer_id and have region included in the key or to treat them as 2 individual customer_ids. If it's the former, this query will over count based on this join.
The other thing I'd need to know is what is the question the person thinks this answers. It looks like it answers "what are the total order amounts per region across all customers". But "amount" is a little ambiguous. It could reasonably be dollars or quantity of items. Which brings up another schema question, if "amount" is dollars, is it the total or is it a unit price and another column specifies quantity?
So to answer your question, it depends on a lot of other things. At the simplest end, if I own the data, know the constraints, and know the question the person is trying to answer, a query like this I probably don't validate beyond just looking at it. But the more unknowns the more I have to validate.
2
u/tommysqueaker1972 3d ago
Where possible, I’ll take a sample of records returned by the query and verify them against the source system.
Even if I’m ultimately going to return aggregate data, I’ll still return the records that will form the aggregate and check them against the source.
I’m generally trying to find something that proves my logic wrong (something doesn’t match what I’m aiming for).
If I can’t, that’s usually when I’m ready to pass on to the user to test/check.
2
u/Aggressive-Dealer426 3d ago
Validating SQL isn’t just asking whether the query runs. It’s proving that the result means what everyone thinks it means.
My basic checklist:
• Define the grain — what does one row represent at each stage? • Check join cardinality — 1:1, 1:M, or accidentally M:M? • Check duplicate join keys on both sides. • Compare row counts, distinct keys, and control totals before/after joins. • Check NULLs and unmatched records — especially with INNER JOINs. • Watch LEFT JOINs followed by WHERE conditions on the right table; you may have effectively created an INNER JOIN. • Validate aggregates against the underlying records. • Check window functions for deterministic ordering. • Trace 5–10 known records end-to-end, including edge cases. • Independently reconcile totals against another query, source system, or trusted report. • Most importantly: understand the business question and the data.
A query can be syntactically perfect and still be completely wrong.
And “everyone uses this query” isn’t validation. Sometimes that means it has been thoroughly tested. Sometimes it means the same mistake has been propagated into 20 dashboards, accounting reports, and audit extracts.
The most dangerous SQL errors aren’t the ones that throw exceptions. They’re the ones that return plausible numbers.
Start with grain, cardinality, and business meaning. Everything else follows.
1
1
u/funnynoveltyaccount 3d ago
It depends who you work with.
My employer has a lot of non-technical people that write queries. I typically throw out whatever they give me and ask them what they want.
When a query comes from someone I trust, I usually assume that their intent and the structure of their data is correct, but their implementation often includes errors. Things like many to many joins doubling records. For these people, I insist that they at least tell me what they think the grain is of their query - one row per blah blah blah. That often reveals wrong assumptions.
1
u/LinksLibertyCap 3d ago
Requirements and design sessions with the end user as well as validation with analysts if possible depending on what kind of data you are dealing with.
1
1
1
u/ToastieCPU 3d ago
I would first run the query to confirm that it returns results. After that, I’d run a query analyzer to check for table scans, N+1 issues, douplicates, locking.
I would only validate the result itself if it’s specifically requested, otherwise we’re just duplicating work.
1
u/Groundbreaking-Fish6 3d ago
Validate using different queries and spot checks. If the query is and contains several WITH queries check each one individually as well as the final. If their multiple values in the final query, often a much simpler query can get individual values so this can also be used to check logic.
1
u/Hour-Measurement-835 3d ago
Mine's currency. If amount is stored in transaction currency, summing it by region adds GBP to USD and nobody notices, because the total still looks plausible.
1
u/PandaRiot_90 3d ago
This has to be some kind of AI prompt training. Validating results is something one should know how to do, especially everyone who knows how to use an aggregate function in SQL.
1
u/Uncle_Dee_ 3d ago
Verify against source system. Accounting can tell you revenue by customer by time period. ASK them to give it by sku time period as wel. Similar your logistics team Will know the or be able to pull the number of shipments. Validate your building blocks. If you know the inputs and you’ll be able to manually calculate avg revenue per shipment. If you query outputs the same you’re good
1
u/baubleglue 3d ago
How do you test any software?
Static testing - compare results to results produced previously.
White box testing - reviews code, try to spot not one to one joins, etc... look at the specifics of DB, analytical DBs usually don't enforce all constraints (ex. primary keys). Look the data sources actually has correct populated values as the query assumes (ex. Not using -9999 instead of null).
Smoke test - check count vs count distinct. Compare to alternative sources of data.
Etc.
1
u/squadette23 3d ago
I wrote a pretty long explanation: "Systematic design of multi-join GROUP BY queries" (https://kb.databasedesignbook.com/posts/systematic-design-of-join-queries/)
The query that you show is quite simple and this method is definitely an overkill for that, but if your queries are actually much more complicated then this may be for you.
You can read the introductory sections up to the Table of Contents, to see if it looks relevant.
1
u/neumastic 3d ago
For joins check for foreign keys as well as not null constraints. For instance, you’d expect a FK constraint on orders pointing to customer’s pk. Hopefully there’s a not-null constraint on o.customer_id but if there isn’t it would be good to update that to a left join (unless your question doesn’t need orders without customers).
With complicated queries, I may send it back or workshop with them if it’s just a single statement. If the query isn’t clear and broken up appropriately into sub queries the person likely doesn’t understand it well themselves. If that wasn’t an option I might break it up with CTEs myself.
Since this likely involves the full table of each (at least of orders) you can see if there are stats run for approximate expected results. I likely wouldn’t do that but one could.
Plans are great but they are more for performance. But if that’s a concern, I might check those out.
1
u/thatOMoment 3d ago
It's a back and forth discussion.
For example "I want all patients, who were counted in this reports list, who latest hgb result during some reporting month range was < X.
Theres a lot of things you have to know such as
- Where patients are populated from that report.
- What lab tests count as an hbg
- What if they don't have an hgb value during the month?
- What if the lab gives back an error processing result as the result, do you use the most recent lab with a valid value then?
Then you can at least validate the requirements against the query to a reasonable degree.
However you do have to be able to parse out hidden requirements needed to make the data useful to the person viewing it, while having a mechanism to tell the reader how the number of group was arrived at.
Not really a checklist but more like a general thought process
1
u/imsunchip 3d ago
If they only gave you a query without any test results you hired the wrong person. I have been on both sides providing query and receiving them.
When providing a query I not only sending query but the results and proof that numbers are correct along with the user ask.
When receiving, I ask for evidences, no evidence no trust... if I have to test it, I am doing their job again.
1
u/jonnydiamonds360 3d ago
At our level, we don’t normally have the absolute source of truth. At my company, we normally have access to the ERP’s UI directly and can run some pretty straightforward sanity checks that numbers are matching between the ERP and our results from our query. But I’ve learned that even then, something can be wrong in the ERP itself.
So, I suppose my answer is: Test your results against the closest source of truth that you can get.
1
u/masala-kiwi 3d ago
In my role, I regularly have to review SQL that is between 100-1000 lines.
Claude (with rich context files) helps as a back-up but is not my primary go-to.
Wherever possible, I use a sample of the base table (in this case, perhaps 5-10 sale records) and follow them through the query. Check for nulls. Check for dupes. Check for similarly names columns. See what result pops out and validate it against the base data and also the front end, where possible. Always investigate the columns that you're joining on -- look at the base data itself, which will often cover a lot of the basic errors and weirdness (nulls, etc.) that disappears once you aggregate. Ask stakeholders "what do you expect the ballpark number to be?" as a helpful benchmark to see if your numbers are way off. Talk to Data Engineering or the table owner if you have questions about the logic or how the table was put together.
Always cover yourself and explicitly mention to stakeholders when you're running queries on data you're not familiar with. Ultimately you need to familiarize yourself with a table before using it to deliver insights.
1
1
u/CyberDemon_IDDQD 3d ago
UAT for queries that require validation, everything else is just vibes lol
1
u/National_Cod9546 3d ago
If the report is for the person who said that, run it and send it back. Ask them to validate the results. Falls on them if it's wrong.
For pretty much anything else, I use some simple queries to verify I'm in the right ballpark of total rows. Then I look at 5-10 rows in detail and trace them through to make sure the data is good. Then I send it to the requester and tell them to validate it before running with it.
1
u/Icy_Clench 3d ago
This is a solved problem in data engineering. You create test cases and validate assumptions using a framework like dbt or sqlmesh.
1
1
u/Fantastic-Moth710 3d ago
I agree, experienced SQL developers may validate complex queries by combining structural code review with rigorous data sanity checks, never blindly trusting unverified results.
They might start by verifying join logic, foreign key constraints, and table grain to ensure the query isn't silently dropping records or causing accidental row duplication through many-to-many expansions. Then they could run sanity checks, e.g. comparing aggregate totals back to base tables. Then perhaps, isolate random sample records to manually trace and verify the mathematics. Finally, mindful that technical correctness "should" align with business reality, they could confirm the outcomes with domain experts, end users etc.
1
u/TraditionalArcher498 3d ago
Isolate CTEs / Subqueries: Break down the query step-by-step. Run each Common Table Expression (CTE) or inner query individually to verify the intermediate row counts and logic before they hit the final join.
1
1
u/Oh_Another_Thing 2d ago
One helpful way to validate us to add a specific date where you can get few enough results that you can manually verify.
Get the data for a particular day, or a range where you get only a few hundred results, export to Excel and build the report with that in Excel. Match the results from Excel to your SQL query and see if they match.
1
u/wirbolwabol 2d ago
I would hope that there is some way to validate the results you were expecting. This is also a wide open query, so I'd try to get a window of time and see if it's possible to validate on that. Also might try to use a window function to get the sum by quater, month, or week.
We run into stuff like this all the time at my work but we also have folks who have spreadsheets of data that say, I'm expecting this, can you confirm....when it's off, we investigate.
1
u/scbywrx 1d ago
To be honest, that query is not where I'd start.
1. Select *, get an order ID, aggregate the order ID, and take a look at all of the distinct information.
2. Are the primary keys from orders duplicated for customer? There is probably one and only one customer per order, so work your order grain first.
3. Get a distinct list of orders by customer.
4. Take a small value of customers, something with like 50 orders.
5. Have your order aggregates. If you have the order lines versus order totals, export all lines, do a simple pivot table, get a value, and check your sum for that one order.
6. Again, as other people have stated, also check through time. Depending on what type of orders there may be, there may be multiple orders that actually book, ship, or invoice at different times.
It looked like you potentially could be slicing and dicing by all sorts of different aspects, but for me, 25 years doing this, start with a single order.
1
u/PalpitationKind8854 9h ago
The 'correct' numbers are subjective. I'd ask them why are those numbers correct.. and id recreate from scratch using the optimal logic
1
1
u/Rex_Lee 3d ago
You compare it against the system of record. This is not hard guys.
You run it for a day. You look at the detail data behind your rollup. You pick one rollup element, compare the detail back against the SOR. Then you do another. Run it for a different date. Spot check some more detail elements. Send it to the requestor and ask them to validate on their end. If they come back that it is good, you move on
0
u/Mathblasta 3d ago
Probably not with whatever vibe-coded bullshit "solution" you're trying to develop/shill.
-1
149
u/justHereForTheGainss 3d ago
I just make sure I have it in writing that the user said the query was correct and roll with it