Validating a CSV File Against a Lookup Table With SQL

A list of allowed values is a data file as much as it is a rule. Countries, product codes, status names, plant identifiers, the set of customers an order may refer to: these live in a table that someone other than the programmer maintains. If a CSV's country column must be in that table, the natural check is a join, and SQL is the natural place to run it. This article does it with DuckDB on the files and with PostgreSQL on the loaded data, and spends most of its time on the three ways the obvious query lies to you: wrong line numbers, NOT IN with a NULL and values that differ only in spelling. It extends the enumeration check in the validation guide, where the list was hard-coded.

The lookup and the data

The lookup is as small as lookups get:

code,name
US,United States
IT,Italy
DE,Germany
BR,Brazil

Four codes the business sells in. The data has a country column that should contain one of them or be empty. The question to ask is: which values in the CSV are not in the lookup, and where?

An anti-join

An anti-join keeps the rows on the left that have no match on the right. In DuckDB it is spelled ANTI JOIN. In other databases the same thing is a LEFT JOIN ... WHERE right.key IS NULL, which is what I use in PostgreSQL below. Here the CSV is the left side, with a line number computed from the scan:

duckdb -box -c "
SELECT c.line, c.id, c.country
FROM (SELECT *, row_number() OVER () + 1 AS line FROM read_csv('work/val/customers-lookup.csv', all_varchar=true)) c
LEFT JOIN read_csv('lab/data/val/countries.csv') k ON c.country = k.code
WHERE c.country <> '' AND k.code IS NULL"

┌──────┬──────┬──────────┐
│ line │  id  │ country  │
├──────┼──────┼──────────┤
│ 702  │ 796  │ Atlantis │
│ 1500 │ 1672 │ us       │
│ 2000 │ 2217 │ U.S.     │
└──────┴──────┴──────────┘

Three rows: line 702 (id 796) has Atlantis, line 1500 has us and line 2000 has U.S.. Empty countries were excluded with country <> '' because the contract allows them; if they were not allowed, the rule is separate (country = '' is a different check from country not being in the list).

For a first look at a big column, count by value instead of listing rows:

duckdb -box -c "
SELECT country, count(*) AS rows, (country IN (SELECT code FROM read_csv('lab/data/val/countries.csv'))) AS known
FROM read_csv('work/val/customers-lookup.csv', all_varchar=true) GROUP BY country ORDER BY rows DESC"

┌──────────┬───────┬───────┐
│ country  │ rows  │ known │
├──────────┼───────┼───────┤
│ US       │ 97185 │ true  │
│ DE       │ 38602 │ true  │
│ IT       │ 38573 │ true  │
│ BR       │ 19345 │ true  │
│ Atlantis │ 1     │ false │
│ us       │ 1     │ false │
│ U.S.     │ 1     │ false │
└──────────┴───────┴───────┘

Seven distinct values, four known. The counts for the known ones are what you would expect for the data, and the unknown ones have one row each. A grouped view is how I find out whether an unknown value is a typo (a count of 1) or a new category the lookup is missing (a count of 4,000). The second case is a conversation with the owner of the list. The first is a fix to the data.

The line number that was off by two

The query above computes the line number as row_number() OVER () + 1. It is correct when the scan sees every line. This is what happens when it does not:

duckdb -box -c "
SELECT c.line, c.id, c.country
FROM (SELECT *, row_number() OVER () + 1 AS line FROM read_csv('work/val/customers-bad.csv', all_varchar=true, ignore_errors=true)) c
ANTI JOIN read_csv('lab/data/val/countries.csv') k ON c.country = k.code
WHERE c.country <> ''"

┌──────┬─────┬──────────┐
│ line │ id  │ country  │
├──────┼─────┼──────────┤
│ 700  │ 796 │ Atlantis │
└──────┴─────┴──────────┘

This is the faulty copy of the file, the one with a 13 field row on line 502 and a 10 field row on line 602, read with ignore_errors=true. The query finds Atlantis and reports line 700. The value is actually on line 702. The two ragged rows were skipped by the reader before row_number() was computed, so every row after them is numbered two lower than the file says. The query has no error and the value is correct, and the one number you hand to the data owner is wrong.

Two lessons. First, a line number is a claim about the file, so compute it from the file. A leading column added by the producer, or a line-number column from a tool that reads text lines, is better than numbering what a lenient reader returned. Second, do not read a file for validation with ignore_errors: the reader skipping rows is itself a finding, and it should fail the structure check before any lookup runs.

The NOT IN trap

The most common way to write this check is WHERE country NOT IN (SELECT code FROM countries). It reads like English, it works on a clean lookup, and it fails silently when the lookup contains a NULL. Three queries, same data:

duckdb -box -c "
SELECT 'NOT IN, clean lookup' AS query, count(*) AS unknown_rows
FROM read_csv('work/val/customers-lookup.csv', all_varchar=true) WHERE country <> '' AND country NOT IN (SELECT code FROM read_csv('lab/data/val/countries.csv'))
UNION ALL
SELECT 'NOT IN, lookup with a NULL', count(*)
FROM read_csv('work/val/customers-lookup.csv', all_varchar=true) WHERE country <> '' AND country NOT IN (SELECT code FROM read_csv('lab/data/val/countries-with-blank.csv'))
UNION ALL
SELECT 'NOT EXISTS, lookup with a NULL', count(*)
FROM read_csv('work/val/customers-lookup.csv', all_varchar=true) c WHERE c.country <> '' AND NOT EXISTS (SELECT 1 FROM read_csv('lab/data/val/countries-with-blank.csv') k WHERE k.code = c.country)"

┌────────────────────────────────┬──────────────┐
│             query              │ unknown_rows │
├────────────────────────────────┼──────────────┤
│ NOT IN, clean lookup           │ 3            │
│ NOT IN, lookup with a NULL     │ 0            │
│ NOT EXISTS, lookup with a NULL │ 3            │
└────────────────────────────────┴──────────────┘

Against the clean lookup, NOT IN found 3 unknown rows. Against a lookup with one extra row whose code is empty (a NULL), it found 0. NOT EXISTS found 3 against that lookup, as it should. The reason is SQL's three-valued logic, which the PostgreSQL manual spells out in its NOT IN documentation: x NOT IN (a, b, NULL) is never true, because the comparison with NULL is unknown, and unknown is not true. A single blank line at the bottom of a lookup file, or a row with an empty code, turns the check off, and the result looks like the best possible outcome: everything is valid.

Prefer NOT EXISTS or an anti-join, and test the check against a lookup containing a NULL, as I did here. The same logic applies in every SQL database, since it comes from the language. If you must use NOT IN, add WHERE code IS NOT NULL to the subquery.

Spelling is not membership

Some of the unknown values are not unknown. They are known values spelled differently. How far to bend is the real design question:

duckdb -box -c "
WITH c(country) AS (VALUES ('US'), ('us'), (' US'), ('U.S.'), ('Italy'))
SELECT country,
       country IN (SELECT code FROM read_csv('lab/data/val/countries.csv')) AS exact,
       upper(trim(country)) IN (SELECT code FROM read_csv('lab/data/val/countries.csv')) AS after_upper_trim
FROM c"

┌─────────┬───────┬──────────────────┐
│ country │ exact │ after_upper_trim │
├─────────┼───────┼──────────────────┤
│ US      │ true  │ true             │
│ us      │ false │ true             │
│  US     │ false │ true             │
│ U.S.    │ false │ false            │
│ Italy   │ false │ false            │
└─────────┴───────┴──────────────────┘

An exact comparison accepted only US. After upper(trim(...)), us and US match, and U.S. and Italy still do not. The first two are formatting errors a rule can safely fix. The other two need knowledge: that U.S. is the United States and Italy is IT. That knowledge belongs in an alias table with its own owner (U.S. to US, Italy to IT), applied before the lookup check. Do not let the validator learn aliases by itself, by fuzzy matching or by taking the closest code. The cleaning series covers building that table. For validation, the useful decision is which of these are errors in the data, which are corrected on the way in and which are reported but accepted.

References between files

The same query validates a reference from one file to another. Orders refer to customers, and an order for a customer who does not exist is an orphan:

duckdb -box -c "
SELECT o.line, o.order_id, o.customer_id
FROM (SELECT *, row_number() OVER () + 1 AS line FROM read_csv('lab/data/val/orders-small.csv', all_varchar=true)) o
ANTI JOIN read_csv('work/val/customers.csv', all_varchar=true) c ON o.customer_id = c.id"

┌──────┬──────────┬─────────────┐
│ line │ order_id │ customer_id │
├──────┼──────────┼─────────────┤
│ 5    │ 1004     │ 99999999    │
└──────┴──────────┴─────────────┘

The small orders file has seven rows. One, on line 5, is for customer 99999999, which is not among the 193,708 customers. The anti-join found it. Orphans are the mistake that is most often discovered by a join that quietly drops rows: an inner join between orders and customers would lose order 1004 and the revenue total would be a little off, with no error. Check the orphans first, and the join is then known to be safe.

What the database does

Declaring the rule in the database turns it into a constraint. In PostgreSQL, a column that REFERENCES the lookup rejects a row whose value is missing from it:

psql -v ON_ERROR_STOP=1 -q <<'SQL' 2>&1
DROP TABLE IF EXISTS cust, countries, cust_stage;
CREATE TABLE countries (code text PRIMARY KEY, name text);
\copy countries FROM 'lab/data/val/countries.csv' WITH (FORMAT csv, HEADER)
CREATE TABLE cust (id int PRIMARY KEY, country text REFERENCES countries (code));
CREATE TABLE cust_stage (id int, country text);
\copy cust_stage FROM PROGRAM 'printf "1,US\n2,IT\n3,Atlantis\n4,\n"' WITH (FORMAT csv)
INSERT INTO cust SELECT id, nullif(country, '') FROM cust_stage;
SQL
echo "exit status: $?"
psql -q -c "SELECT count(*) AS loaded FROM cust"

ERROR:  insert or update on table "cust" violates foreign key constraint "cust_country_fkey"
DETAIL:  Key (country)=(Atlantis) is not present in table "countries".
exit status: 3
 loaded 
--------
      0
(1 row)

The insert stopped at Key (country)=(Atlantis) is not present in table "countries", the exit status was 3 and the table had 0 rows afterwards, as the whole statement rolled back. That is the right behavior for a constraint and the wrong one for a diagnostic: it names one value and no line, and the next problem is hidden behind it. Load into a staging table without the constraint, find all the violations with the anti-join, and insert only when the list is empty or you have decided what to do with it:

psql -q <<'SQL'
SELECT s.id, s.country FROM cust_stage s
LEFT JOIN countries c ON c.code = s.country
WHERE s.country <> '' AND c.code IS NULL;
SQL

 id | country  
----+----------
  3 | Atlantis
(1 row)

The left join from the staging table found id 3, Atlantis. Note what a foreign key does with an empty value: the staging insert converted '' to NULL with nullif, and a NULL references nothing, so the constraint does not check it. If the column is required, add a NOT NULL or a separate check. A reference rule and a required rule are different rules, and a constraint on one does not give you the other. The PostgreSQL manual describes foreign keys, including the NULL behavior, and staging tables are covered in the loading series.

What to do with a violation

There are four honest outcomes for a value not in the lookup, and each is a decision for the owner of the data:

1. It is a typo. Fix it at the source, or map it through the alias table.

2. It is a new, legitimate value. Add it to the lookup first, then load. This is why the lookup lives outside the code.

3. It is unwanted. Reject the row to a rejects file with the reason.

4. It is unknown but harmless. Load it into a column with a flag, and report the count.

Put a ceiling on option 4. A report that says "3 unknown countries of 193,708" is fine, and "3,000" means the lookup or the source has changed.

How to tell it worked

Test with a value you know is absent, a value you know is present and a NULL, in the data and in the lookup. Count the unknown rows two different ways (anti-join and NOT EXISTS) and require them to agree. Check the line numbers by opening the file at one of them. After a fix, rerun and expect an empty result, then add the fix's cause to the lookup or the alias table with a note about when and why.

When the lookup table is the wrong one

A validation against a lookup trusts the lookup. If the countries file is out of date, every sale in a new market is reported as an error. If it is too generous, such as a list of every ISO code when the business sells in four, Atlantis fails and AQ (Antarctica) sails through. A lookup that is shared between systems changes under you. Keep a version of the list with each validation report, so that "valid" means valid against a known list, and review it as often as you review the data's contract. And remember what a membership check does not cover: it says the value is allowed, not that it is right for that row.