In the last chapter you joined nyc_taxi.raw_trips to nyc_taxi.raw_zones and aggregated 57,000 rows into a handful of insights. But every one of those numbers rests on a quiet assumption: that the raw data is trustworthy. It usually is not. Ingestion scripts drop rows, source files arrive with negative fares, and zone codes change without warning. If you build a dashboard on top of dirty data, the dashboard is wrong, and nobody notices until a stakeholder does.
Validation is how you catch the problem before it reaches a report. After ingestion, but before modeling, you run a set of SQL checks that ask blunt questions: are there duplicates? Are required fields missing? Do the foreign keys resolve? Are the numbers physically possible? These are the exact skills Task 1 of this week's assignment asks for, so treat this chapter as the toolbox you will reach into there.
By the end of this chapter, you should be able to:
GROUP BY and HAVING COUNT(*) > 1.LEFT JOIN ... IS NULL (or NOT EXISTS) check.You might think validation belongs in the Python ingestion script. Some of it does. But the SQL layer is where the data actually lands, and checking it there has three advantages.
First, it checks reality, not intent. The ingestion code can promise it filtered negatives; a SELECT against the table proves whether it did. Second, SQL runs the check at full scale against the whole table in one pass, which is far faster than looping in Python. Third, the same query is the diagnosis and the fix-finder: a validation query that returns the bad rows hands you exactly what to investigate.
<aside> 💭 The pattern behind every check in this chapter is the same: write a query that returns only the bad rows. Zero rows back means the data passed. Any rows back means you have a problem to look at. Hold onto that idea, because later, when you reach dbt, it becomes the literal definition of a dbt test.
</aside>
nyc_taxi.raw_trips ships without a unique trip ID, so you cannot just check one column for repeats. Instead you decide what should make a trip unique. A reasonable rule: no two trips share the same vendor, pickup time, and dropoff time. Group by those three columns and keep only the groups that appear more than once.
SELECT
t.vendor_id,
t.pickup_datetime,
t.dropoff_datetime,
COUNT(*) AS duplicate_count
FROM nyc_taxi.raw_trips t
GROUP BY t.vendor_id, t.pickup_datetime, t.dropoff_datetime
HAVING COUNT(*) > 1
ORDER BY duplicate_count DESC;
HAVING filters after the grouping, which is why it can reference COUNT(*). A WHERE clause runs before grouping and cannot see the aggregate. If you only want a single number ("how many duplicate groups exist?"), wrap the whole thing in a counting query.
SELECT COUNT(*) AS duplicate_groups
FROM (
SELECT 1
FROM nyc_taxi.raw_trips t
GROUP BY t.vendor_id, t.pickup_datetime, t.dropoff_datetime
HAVING COUNT(*) > 1
) AS dupes;
<aside>
⚠️ Adding more columns to the GROUP BY makes the uniqueness rule stricter, which finds fewer duplicates, not more. If you group by every column and still get matches, those rows are byte-for-byte identical: a genuine source problem, not a quirk of your column choice.
</aside>
Duplicates rarely originate at the source. More often the pipeline created them.
<aside> 🤓 Curious Geek: Why duplicates sneak in
Most pipeline duplicates are not bad data at the source. They come from a job that ran twice: a retried ingestion, a backfill that overlapped a scheduled load, or an "at-least-once" delivery system that resent a message. This is why analytics engineers obsess over idempotency: a load you can safely run twice without doubling your rows.
</aside>
A NULL in a join key silently drops rows from an inner join. A NULL in a group-by key invents a phantom bucket. So before you trust any aggregate, you measure how complete the key columns are.
Counting NULLs in one column is a WHERE:
SELECT COUNT(*) AS null_pickup_zones
FROM nyc_taxi.raw_trips t
WHERE t.pickup_location_id IS NULL;
But you usually want completeness across several columns at once, expressed as a percentage so the number means something regardless of table size. A FILTER clause counts only the rows that match a condition:
SELECT
COUNT(*) AS total_rows,
COUNT(*) FILTER (WHERE t.pickup_location_id IS NULL) AS null_pickup,
COUNT(*) FILTER (WHERE t.passenger_count IS NULL) AS null_passengers,
COUNT(*) FILTER (WHERE t.payment_type IS NULL) AS null_payment,
ROUND(
100.0 * COUNT(*) FILTER (WHERE t.payment_type IS NULL) / COUNT(*),
2
) AS pct_null_payment
FROM nyc_taxi.raw_trips t;
<aside>
💡 The 100.0 is deliberate. Dividing two integers in Postgres truncates to an integer, so 3415 / 56551 would return 0. Forcing one side to a decimal (100.0) keeps the fractional part.
</aside>
Run that query yourself and read the percentages before moving on.
<aside>
⌨️ Hands on: Run the completeness query above against nyc_taxi.raw_trips (the shared, read-only schema everyone uses), then answer in one sentence: which column is least complete, and would you trust a "trips per payment type" chart on this data as-is?
</aside>
<aside> 🚀 Try it in the widget: Run the validation checks in the SQL playground (real Postgres, no setup). The sample is seeded with deliberate duplicates, NULLs, and orphaned keys, so switch between the Validate: duplicates, nulls, orphans and Validate: orphaned pickup IDs examples to see each check return rows.
</aside>
The result should look like the table below (your exact counts may differ slightly):
total_rows | null_pickup | null_passengers | null_payment | pct_null_payment
------------+-------------+-----------------+--------------+------------------
56551 | 0 | 3415 | 3415 | 6.04
About 6% of trips have a NULL payment_type. That is not a bug you introduced: it is raw-source reality. The point of measuring it is that you now know, and you can decide how a downstream chart should handle the gap instead of being surprised by it.
<aside> 💡 In the wild: The null, uniqueness, and range checks you are writing by hand are exactly what dedicated tools automate. Great Expectations and Pandera wrap these same assertions into reusable "expectations" that run on every pipeline load and fail loudly when the data drifts. Open either repo to see the production version of the queries on this page.
</aside>
When data moves from a source to a destination, the simplest sanity check is: did the same number of rows arrive? If the source export held 57,000 trips and your table holds 56,980, twenty rows went missing during ingestion, and you want to know why.
You confirm the destination count directly:
SELECT COUNT(*) AS row_count
FROM nyc_taxi.raw_trips t;
Then compare it against the count you expected from the source: the number reported by the export, the API's total, or the line count of the source file. A mismatch points at a filter that ran too aggressively, a failed batch, or a load that silently skipped malformed rows. The check is trivial; the discipline of always doing it is what separates a pipeline you trust from one you hope works.
<aside> 💡 A count match is necessary but not sufficient. Equal totals can still hide swapped or corrupted values inside the rows. Row count validation is the cheapest check, so it goes first, not last.
</aside>
Every trip references a pickup_location_id. That number is only meaningful if it exists in nyc_taxi.raw_zones. Referential integrity is the promise that it always does, and raw source data does not always keep that promise. A trip pointing at a zone code that is not in the lookup table is an orphaned foreign key: when you join, that trip either disappears (inner join) or shows a NULL borough (left join). Either way, the data lies.
The clearest way to find orphans is a LEFT JOIN that keeps all trips, then a filter for the rows where the zone side came back empty:
SELECT DISTINCT t.pickup_location_id
FROM nyc_taxi.raw_trips t
LEFT JOIN nyc_taxi.raw_zones z
ON t.pickup_location_id = z.location_id
WHERE z.location_id IS NULL
ORDER BY t.pickup_location_id;
The WHERE z.location_id IS NULL is the trick: after a left join, a NULL on the right-hand table means "no match was found." NOT EXISTS expresses the same intent and reads well when you do not need columns from nyc_taxi.raw_zones:
SELECT DISTINCT t.pickup_location_id
FROM nyc_taxi.raw_trips t
WHERE NOT EXISTS (
SELECT 1
FROM nyc_taxi.raw_zones z
WHERE z.location_id = t.pickup_location_id
);
<aside>
⚠️ Prefer NOT EXISTS over NOT IN for this check. If the subquery behind NOT IN returns even one NULL, the whole NOT IN evaluates to NULL and quietly returns zero rows, hiding every orphan. NOT EXISTS and the LEFT JOIN ... IS NULL pattern both handle NULLs correctly.
</aside>
The last family of checks asks whether each value is physically possible. A fare cannot be negative. A dropoff cannot happen before a pickup. A payment code outside the documented set means the source changed. One query can surface several of these at once with FILTER.
SELECT
MIN(t.fare_amount) AS min_fare,
MAX(t.fare_amount) AS max_fare,
COUNT(*) FILTER (WHERE t.fare_amount < 0) AS negative_fares,
COUNT(*) FILTER (WHERE t.dropoff_datetime < t.pickup_datetime) AS reversed_times,
COUNT(*) FILTER (WHERE t.payment_type NOT IN (1, 2, 3, 4, 5, 6)) AS bad_payment_codes
FROM nyc_taxi.raw_trips t;
For date-range checks, the dataset is January 2024 green taxi data, so any pickup_datetime outside that month is suspect:
SELECT COUNT(*) AS out_of_range_dates
FROM nyc_taxi.raw_trips t
WHERE t.pickup_datetime < '2024-01-01'
OR t.pickup_datetime >= '2024-02-01';
<aside> 🤓 Curious Geek: The TLC payment codes
NYC's Taxi and Limousine Commission documents payment_type as 1 to 6: credit card, cash, no charge, dispute, unknown, and voided trip. Any other value means the feed format drifted or a row landed corrupt. Validating against a known set is called an accepted-values check, and it is one of the most common ways pipelines catch an upstream change early.
</aside>
An LLM can help you stress-test these checks for cases you did not think of.
<aside>
💡 Using AI to help: Paste a validation query and a sample of its output (⚠️ the public TLC dataset is safe; never paste real customer data or PII) into an LLM and ask it to spot edge cases your check missed, such as fare_amount = 0 trips or NULLs slipping past a range filter. Then verify every suggestion against your actual columns before trusting it.
</aside>
Right now each check is a standalone query you run by hand. The natural next step is to collect them into one validation_queries.sql file, run it after every load, and log the results to a small data-quality table so you can track whether completeness is drifting over time.
You cannot fully automate that yet: there is no scheduler wired to these queries, and rerunning a file by hand is still a manual step. That is fine for this week. Later, when you reach dbt, you will rewrite every check in this chapter as a dbt test: a not_null or unique assertion declared in YAML next to the model, run automatically by dbt build, that fails the pipeline when the data breaks. The queries you write here are the exact logic those tests encode; dbt just removes the "someone has to remember to run it" problem.
nyc_taxi.raw_trips by vendor_id, pickup_datetime, and dropoff_datetime with HAVING COUNT(*) > 1. A teammate suggests adding trip_distance to the GROUP BY to "find more duplicates." Why is that the wrong direction?100.0 instead of 100 when computing a NULL percentage?pickup_location_id is missing from nyc_taxi.raw_zones. Explain why WHERE z.location_id IS NULL after a LEFT JOIN returns exactly those rows.NOT IN (SELECT location_id FROM nyc_taxi.raw_zones) and it returns zero orphans, but you know some exist. What is the likely cause?payment_type completeness check shows ~6% NULL. Should you delete those rows before building your charts?Want to check your understanding before moving on? Try the interactive quiz for this chapter.
<aside> 🚀 Try it in the widget: Interactive Quiz: Data Validation Queries
</aside>
https://lasse.be/simple-hyf-teach-widget/mcq.html?bank=week_9_ch3_validation_queries_quiz&embed=1
COUNT, FILTER): the official reference for COUNT(*) FILTER (WHERE ...) used throughout this chapter.GROUP BY and HAVING: how grouping and post-grouping filters evaluate, the basis of the duplicate check.EXISTS, IN): why NOT EXISTS handles NULLs where NOT IN does not.Ready to apply what you just read?
<aside> ⌨️ Hands on: Practice with Exercise 6: Validate the raw data, where you write duplicate, NULL, and orphaned-key checks against the taxi data.
</aside>
Next up: OLAP vs OLTP and Modern Warehouses, where you distinguish transactional OLTP databases built for fast single-row writes from analytical OLAP warehouses built for large scans, and tour the modern cloud warehouse landscape.
The HackYourFuture curriculum is licensed under CC BY-NC-SA 4.0 *https://hackyourfuture.net/*
Built with ❤️ by the HackYourFuture community · Thank you, contributors
Found a mistake or have a suggestion? Let us know in the feedback form.
In the previous chapter, 9.2: Joins, CTEs, and Aggregations, you learned how to transform raw data into insights. But there is a massive assumption in that chapter: that the data is actually correct.
In production data engineering, this is a dangerous assumption. Pipes break, APIs change their format, and CSVs often arrive with "garbage" rows. If you build a dashboard on top of bad data, you aren't providing insights—you’re providing "hallucinations." This chapter is about using SQL to build a "firewall" around your data warehouse.