Pitfall

Dirty data

A messy file raises no error: every query runs, and the answers are wrong. Check a new table before trusting it.

What to look for in a new table:

Numbers or dates stored as text: they sort like words ('99' > '138'). Check with DESCRIBE, then CAST or TRY_CAST.
Spreadsheet formats: 1 614,00 €, 03/04/2024. Remove the currency and the thousands separator; read dates with strptime.
Placeholders: 1900-01-01, -1, 0, 'N/A' meaning "missing". Look at the extreme and the most frequent values, then NULLIF.
Empty is not NULL: '' and ' ' are counted as values. NULLIF(TRIM(x), '').
Impossible values: a negative stock, a 140 % discount, an event after the load. MIN, MAX and COUNT(*) - COUNT(column) on each column show them.
Keys that almost match: a space, a capital letter, an accent, and the join drops rows in silence. Compare row counts; TRIM, LOWER.
Duplicates: a row imported twice, one person under two spellings.

Clean once, where the data comes in, and set aside the rows you cannot fix (a quarantine table) instead of deleting them.

The trap

WITH stock(sku, quantity) AS (VALUES ('CAF-001', '99'), ('CAF-002', '138'), ('CAF-003', '7'))
SELECT MAX(quantity) AS biggest
FROM stock;

biggest
99

biggest

Try it in Randori

The fix

WITH stock(sku, quantity) AS (VALUES ('CAF-001', '99'), ('CAF-002', '138'), ('CAF-003', '7'))
SELECT MAX(CAST(quantity AS INTEGER)) AS biggest
FROM stock;

biggest
138

biggest

138

Try it in Randori