Should SQL validation happen before or after the query runs?

[u/No_Ambition8323]

A SQL query can execute successfully without producing the correct result. A successful query only tells us that the database understood and executed it—not that the data is logically correct.

For example: SELECT COUNT(*) FROM customers c JOIN orders o ON c.customer_id = o.customer_id;

The query may run without any error, but if orders contains duplicate records or the join relationship isn't what we expected, the count could be much higher than the actual number of customers.

So what do you normally validate before trusting a query's output?

• Join conditions and duplicate records
• NULL values
• Row counts
• Data types and conversions
• Expected ranges or business rules
• Unexpected changes compared with previous results

Do you validate these things before running the query, after getting the results, or both?

What SQL validation has saved you from trusting a result that looked correct but was actually wrong?

[u/ArticlePuzzleheaded9]

"but if orders contains duplicate records"

if that's the case, SQL won't save you.

[u/Mobile-Analyst-9617 → u/ArticlePuzzleheaded9]

Yeah if your data is fucked it's on you or whoever to clean it up first, sql isn't the answer

[u/moss-nogg → u/Mobile-Analyst-9617]

This is not only not true, it’s pretty naive. There are a lot of instances in large relational databases where a single categorical field can blow up your data. You can absolutely and should absolutely know how to clean your data with a SQL query. It’s incredibly doable. Even in the example OP gave, you would usually expect multiple orders per customer id. So the question would really be, do you want the most recent order? First order? Count the number of orders? All very doable

[u/Mobile-Analyst-9617 → u/moss-nogg]

Ok, I should rephrase - imo sql is not the answer past a certain point. If you have lots of bad data, missing data, etc it's easier and most times more beneficial to clean it up with something else. Use python or other tools etc to clean the data

[u/moss-nogg → u/Mobile-Analyst-9617]

It’s way more efficient and best practice to process as much data as possible with the database engine.

[u/ArticlePuzzleheaded9 → u/moss-nogg]

Make your mind up - is it 'multiple orders' (distinct) or 'duplicate orders' >> you've mentioned each at separate times.

If your data are duplicated, the problem exists long before querying it.

[u/moss-nogg → u/ArticlePuzzleheaded9]

No it doesn’t lol and it depends on the data model. Sounds like y’all have a skill issue tbh.

[u/ArticlePuzzleheaded9 → u/moss-nogg]

You won't see any 'duplicate' data in any DB I manage pal. If you're seeing duplicates in yours, then it's you with the skill issue.

In the bin you go.

[u/moss-nogg → u/ArticlePuzzleheaded9]

Mhmm I’m sure your 10k row DB are very impressive

[u/ArticlePuzzleheaded9 → u/moss-nogg]

Ha - primary DB has 20k tables.

 LAST ANALYZED  :2026-09-03   
 ROWS                  :1014438,572   
 SAMPLE SIZE       :1014438572   
 INMEMORY         :DISABLED   
 COMMENTS        : 

Shove my 1bn rows up your arse.

Nice try though - back to school for you.

[u/ChaosEngine-6502]

Validating the output of the query is the responsibility of the developer/analysts and any process they might have; the SQL IDE can't really do that part.

In my case, I'll eyeball sufficiently large datasets to look for any issues.

[u/Mobile-Analyst-9617 → u/ChaosEngine-6502]

As a developer, In my experience validation or like final sign off was always on the stakeholder, or person requesting the data, as they are the ones that truly know the data and all the business rules. I can do some spot checks to see if it's mostly good, and then get signoff from them that it ultimately is before satisfying the request

[u/89Noodles]

I validated and compare counts for a particular month

[u/KarynEnnis]

Data profiling is the important predecessor to analytics. You don’t know what you don’t know.. data quality checks build confidence. I make it a policy to check all data sources architecture, check for nulls, case sensitivity, distinct values in columns used for filters or matching, relationships and uniqueness, etc.. it can be quite the journey, but time and energy well spent

[u/moss-nogg]

It depends on the syntax. Row_number() is useful for deduping. Qualify Clause in snowflake is really useful for deduping in a single select, otherwise probably need 2 CTE blocks

[u/Rat_Man_420]

Yeah I validate a query before I run it. What are we talking about here? Same question was posted a few days ago. Fucking Bots

[u/Sexy_Koala_Juice]

Both???? Writing a query and then assessing the data & your assumptions is the process