Writing Data Quality Checks for CSV Files in dbt and DuckDB
Read a CSV in a dbt model with DuckDB and test it with unique, accepted_values, relationships and SQL tests: 6 of 8 failed on a faulty file.
If your team already uses dbt, the quickest way to check a CSV may be to write the checks the way you write everything else: as SQL, in version control, run by the same command that builds the models. dbt reads from a warehouse, not from files, but the DuckDB adapter lets a model read a CSV directly, which turns a delivered file into a table the standard tests can look at. This article builds that for the customer export, runs it on a good and a faulty file, and spends some time on the two ways it can mislead you. It is one of the options in the validation guide.
How tests work in dbt
A dbt data test is a SQL query that returns the rows that violate a rule. The test passes if the query returns no rows. The built-in ones are unique, not_null, accepted_values and relationships, and a singular test is just a .sql file in the tests directory that you write yourself. That model is a good fit for CSV validation, since "which rows are bad?" is the question you want answered anyway, and the answer is the rows.
The project
Five small files. The project config names the project and sets a default path for the CSV, which a command can override with --vars:
name: csv_checks
version: "1.0.0"
config-version: 2
profile: csv_checks
model-paths: ["models"]
seed-paths: ["seeds"]
test-paths: ["tests"]
vars:
csv_path: /batches/csv-0001-0200/work/val/customers.csv
models:
csv_checks:
staging:
+materialized: tableThe profile points the DuckDB adapter at a database file:
csv_checks:
target: dev
outputs:
dev:
type: duckdb
path: /tmp/csv_checks.duckdb
threads: 1The model is the part that matters. It reads the CSV with a declared dialect and declared columns, all as text:
-- The dialect and the columns are declared, not sniffed, so a file that does not match fails here
-- with the reader's own message. Everything is text; casts happen below so a test can see what failed.
with raw as (
select *
from read_csv(
'{{ var("csv_path") }}',
auto_detect = false,
header = true,
delim = ',',
quote = '"',
escape = '"',
columns = {
'id': 'VARCHAR', 'first_name': 'VARCHAR', 'last_name': 'VARCHAR', 'email': 'VARCHAR',
'phone': 'VARCHAR', 'phone_unparsed': 'VARCHAR', 'signup_date': 'VARCHAR',
'balance': 'VARCHAR', 'balance_currency': 'VARCHAR', 'units': 'VARCHAR',
'country': 'VARCHAR', 'status': 'VARCHAR'
}
)
)
select
id,
first_name,
last_name,
email,
phone,
signup_date as signup_date_raw,
try_cast(nullif(signup_date, '') as date) as signup_date,
units,
country,
status
from rawTwo decisions are in there. **Everything is read as VARCHAR**, so that a value like 12x loads as text and can be caught by a test, instead of making the read fail. Casts happen afterwards with try_cast, which returns NULL when the cast fails, and the raw text is kept alongside so a test can tell "empty" from "did not cast". **The dialect and columns are declared, with auto_detect = false.** On a clean file the sniffer would have worked. On a file with a malformed row, DuckDB's sniffer can pick a different delimiter and treat the whole line as one column, as the counting article found, and the error you get is about a missing column instead of the real problem. Declaring the columns turns the file's shape into a precondition and gives the reader's own message when it fails. The DuckDB read_csv options are covered in the DuckDB series, and the DuckDB CSV documentation lists them all.
The tests
The built-in tests go in a YAML file next to the model:
version: 2
models:
- name: stg_customers
description: Customer export as delivered, one row per line of the file.
columns:
- name: id
data_tests:
- not_null
- unique
- name: email
data_tests:
- unique
- name: status
data_tests:
- accepted_values:
arguments:
values: [active, pending, inactive]
- name: country
data_tests:
- relationships:
arguments:
to: ref('countries')
field: code
config:
where: "country != ''"id must not be null and must be unique. email must be unique. status must be one of three values. And country must exist in the countries seed, which is the same 4 row lookup file as in the lookup article, loaded with dbt seed; the where config skips empty countries, because an empty country is allowed.
The built-in tests do not include a pattern check or a "does it cast" check, so those are singular tests, plain SQL files:
-- returns the rows whose date text is not empty and did not cast; a test passes when it returns nothing
select id, signup_date_raw
from {{ ref('stg_customers') }}
where signup_date_raw != '' and signup_date is nullselect id, units
from {{ ref('stg_customers') }}
where units != '' and not regexp_full_match(units, '-?\d+')select id, email
from {{ ref('stg_customers') }}
where not regexp_full_match(email, '[^@\s]+@[^@\s]+\.[^@\s]+')The first returns rows where the raw date text is not empty and the cast gave NULL. The other two use regexp_full_match, a full-match test as in the pattern article. That makes eight tests: five from the YAML file (not_null and unique on id, unique on email, accepted_values on status, relationships on country) and three singular tests.
Running it on the good file
dbt build runs the models, loads the seeds and runs the tests, in dependency order:
/tmp/dbtenv/bin/dbt build --project-dir lab/val/dbtproj --no-version-check --log-format text --no-use-colors --vars '{csv_path: work/val/customers.csv}' 2>&1 | grep -E "OK|ERROR|FAIL|WARN|Completed|Done|PASS|Got" | sed 's/^[0-9:]* *//'1 of 10 OK created sql table model main.stg_customers .......................... [OK in 0.31s]
2 of 10 OK loaded seed file main.countries ..................................... [INSERT 4 in 0.03s]
3 of 10 PASS accepted_values_stg_customers_status__active__pending__inactive ... [PASS in 0.03s]
4 of 10 PASS assert_emails_match_pattern ....................................... [PASS in 0.02s]
5 of 10 PASS assert_signup_dates_parse ......................................... [PASS in 0.01s]
6 of 10 PASS assert_units_are_integers ......................................... [PASS in 0.01s]
7 of 10 PASS not_null_stg_customers_id ......................................... [PASS in 0.01s]
8 of 10 PASS unique_stg_customers_email ........................................ [PASS in 0.03s]
9 of 10 PASS unique_stg_customers_id ........................................... [PASS in 0.02s]
10 of 10 PASS relationships_stg_customers_country__code__ref_countries_ ........ [PASS in 0.02s]
Completed successfully
Done. PASS=10 WARN=0 ERROR=0 SKIP=0 NO-OP=0 REUSED=0 TOTAL=10The model was created, the seed loaded 4 rows, and all eight tests passed, which dbt counts as PASS=10 with the model and the seed. The exit status was 0:
/tmp/dbtenv/bin/dbt build --project-dir lab/val/dbtproj --no-version-check --log-format text --no-use-colors --vars '{csv_path: work/val/customers.csv}' >/dev/null 2>&1; echo "good file: dbt exit status $?"
/tmp/dbtenv/bin/dbt build --project-dir lab/val/dbtproj --no-version-check --log-format text --no-use-colors --vars '{csv_path: work/val/customers-dbt.csv}' >/dev/null 2>&1; echo "faulty file: dbt exit status $?"
/tmp/dbtenv/bin/dbt build --project-dir lab/val/dbtproj --no-version-check --log-format text --no-use-colors --vars '{csv_path: work/val/customers-bad.csv}' >/dev/null 2>&1; echo "ragged file: dbt exit status $?"good file: dbt exit status 0
faulty file: dbt exit status 1
ragged file: dbt exit status 1Running it on a faulty file
I planted six faults in a copy: a bad email, a misspelled status, an impossible date, a repeated id, a country that is not in the lookup, and 12x in a numeric column. All are value faults, so the file is a valid table.
/tmp/dbtenv/bin/dbt build --project-dir lab/val/dbtproj --no-version-check --log-format text --no-use-colors --vars '{csv_path: work/val/customers-dbt.csv}' 2>&1 | grep -E "OK|ERROR|FAIL|WARN|Done|Got" | sed 's/^[0-9:]* *//'1 of 10 OK created sql table model main.stg_customers .......................... [OK in 0.30s]
2 of 10 OK loaded seed file main.countries ..................................... [INSERT 4 in 0.03s]
3 of 10 FAIL 1 accepted_values_stg_customers_status__active__pending__inactive . [FAIL 1 in 0.04s]
4 of 10 FAIL 1 assert_emails_match_pattern ..................................... [FAIL 1 in 0.02s]
5 of 10 FAIL 1 assert_signup_dates_parse ....................................... [FAIL 1 in 0.01s]
6 of 10 FAIL 1 assert_units_are_integers ....................................... [FAIL 1 in 0.01s]
9 of 10 FAIL 1 unique_stg_customers_id ......................................... [FAIL 1 in 0.02s]
10 of 10 FAIL 1 relationships_stg_customers_country__code__ref_countries_ ...... [FAIL 1 in 0.02s]
[ERROR]: in test accepted_values_stg_customers_status__active__pending__inactive (models/staging/schema.yml)
Got 1 result, configured to fail if != 0
[ERROR]: in test assert_emails_match_pattern (tests/assert_emails_match_pattern.sql)
Got 1 result, configured to fail if != 0
[ERROR]: in test assert_signup_dates_parse (tests/assert_signup_dates_parse.sql)
Got 1 result, configured to fail if != 0
[ERROR]: in test assert_units_are_integers (tests/assert_units_are_integers.sql)
Got 1 result, configured to fail if != 0
[ERROR]: in test unique_stg_customers_id (models/staging/schema.yml)
Got 1 result, configured to fail if != 0
[ERROR]: in test relationships_stg_customers_country__code__ref_countries_ (models/staging/schema.yml)
Got 1 result, configured to fail if != 0
Done. PASS=4 WARN=0 ERROR=6 SKIP=0 NO-OP=0 REUSED=0 TOTAL=10Six tests failed and two passed (the other two passes in PASS=4 are the model and the seed). The failures were accepted_values on status, the email pattern, the date cast, the integer check, unique on id and relationships on country. Each failure prints Got 1 result, configured to fail if != 0, and the exit status was 1, so a CI job stops. Note what "1 result" counts. For the unique test it is one repeated value, and for the others one row. The count is the number of rows the test query returned, not the number of faults in the file.
The failing rows are available afterwards with --store-failures, which saves each test's result as a table:
/tmp/dbtenv/bin/dbt build --project-dir lab/val/dbtproj --no-version-check --log-format text --no-use-colors --vars '{csv_path: work/val/customers-dbt.csv}' --store-failures >/dev/null 2>&1
duckdb /tmp/csv_checks.duckdb -box -c "SELECT schema_name, table_name FROM duckdb_tables() WHERE schema_name LIKE '%audit%' ORDER BY 2"
duckdb /tmp/csv_checks.duckdb -box -c "SELECT id, signup_date_raw FROM main_dbt_test__audit.assert_signup_dates_parse"┌──────────────────────┬─────────────────────────────────────────────────────────────────┐
│ schema_name │ table_name │
├──────────────────────┼─────────────────────────────────────────────────────────────────┤
│ main_dbt_test__audit │ accepted_values_stg_customers_status__active__pending__inactive │
│ main_dbt_test__audit │ assert_emails_match_pattern │
│ main_dbt_test__audit │ assert_signup_dates_parse │
│ main_dbt_test__audit │ assert_units_are_integers │
│ main_dbt_test__audit │ not_null_stg_customers_id │
│ main_dbt_test__audit │ relationships_stg_customers_country__code__ref_countries_ │
│ main_dbt_test__audit │ unique_stg_customers_email │
│ main_dbt_test__audit │ unique_stg_customers_id │
└──────────────────────┴─────────────────────────────────────────────────────────────────┘
┌─────┬─────────────────┐
│ id │ signup_date_raw │
├─────┼─────────────────┤
│ 339 │ 2025-13-45 │
└─────┴─────────────────┘The audit schema has one table per test, and querying the date test's table returned the row: id 339, signup_date_raw 2025-13-45. That is the kind of detail you send to the provider. dbt reports the id, not the file line, so if your contract wants line numbers, keep a line number column in the model (row_number() over () on a freshly read file, with the caveats in the lookup article).
The test that tested the wrong data
The first time I ran this, I used dbt test on the faulty file and got a clean result:
/tmp/dbtenv/bin/dbt test --project-dir lab/val/dbtproj --no-version-check --log-format text --no-use-colors --vars '{csv_path: work/val/customers-dbt.csv}' 2>&1 | grep -E "Done|Got" | sed 's/^[0-9:]* *//'Done. PASS=8 WARN=0 ERROR=0 SKIP=0 NO-OP=0 REUSED=0 TOTAL=8PASS=8 on a file with six faults. The reason is that dbt test does not run the models. It tests the tables that exist, and the table stg_customers had been built from the good file on the previous run. Passing --vars with a different path changed nothing about that table. I had tested yesterday's data and been told it was fine.
Use dbt build, which runs the model and then the tests in a single invocation, or dbt run followed by dbt test. More generally, in any workflow where the data is replaced under a test, check that the thing being tested is the thing that arrived. A table that records which file it was built from (the file name and its checksum as columns) makes this checkable.
When the file is not a table
The faulty file above was chosen to be a valid CSV. A file with a ragged row is not, and dbt never reaches the tests:
/tmp/dbtenv/bin/dbt build --project-dir lab/val/dbtproj --no-version-check --log-format text --no-use-colors --vars '{csv_path: work/val/customers-bad.csv}' 2>&1 | grep -E "ERROR|Error|Expected|Found|Done|Line" | sed 's/^[0-9:]* *//' | head -n 8Found 1 model, 8 data tests, 1 seed, 500 macros
1 of 10 ERROR creating sql table model main.stg_customers ...................... [ERROR in 0.07s]
[ERROR]: in model stg_customers (models/staging/stg_customers.sql)
Runtime Error in model stg_customers (models/staging/stg_customers.sql)
Invalid Input Error: CSV Error on Line: 502
Original Line: 575,Ed,Riga,[email protected],+17404004938,,2025-01-07,12317.32,EUR,3230,IT,pending,extra
Expected Number of Columns: 12 Found: 13
Done. PASS=1 WARN=0 ERROR=1 SKIP=8 NO-OP=0 REUSED=0 TOTAL=10The model failed with Invalid Input Error: CSV Error on Line: 502, the line itself, and Expected Number of Columns: 12 Found: 13. That message is useful, and it comes from the reader. The remaining tests were skipped (SKIP=8) because there was no table to test, and the exit status was 1. This is how dbt sees the structure layer: as a model that does not build. It cannot check the bytes (a checksum, a missing final newline), it does not know a file is cut between rows and it cannot compare two deliveries. Those checks belong before the model, as in the contract article, and dbt picks up from "the file is the shape we agreed".
Severity and thresholds
Not every failing test should fail the build. dbt lets a test warn instead of error with a severity setting, and thresholds like warn_if and error_if, so a few unknown countries can warn while a thousand fail. The dbt data test documentation describes the options. I did not run those settings here, so check the syntax for your version before copying it. The policy is the interesting part, and it is the same as in the validation guide: decide beforehand which failures reject the delivery and which are reported.
How to tell it worked
Run the project on a file you know is good and expect all tests to pass and exit status 0. Plant one fault per test in a copy and expect exactly those tests to fail. Run dbt test alone after swapping the file, and make sure you understand why it passes. Delete a column from the file and confirm the model fails with a reader error, not a quiet pass. And when you add a test, add a planted fault for it to the test file at the same time, so the test is known to be able to fail.
What dbt tests cannot see
They see a table, and only after it has been built. The read can fail before they run, or succeed in a way that hides the problem: if you let DuckDB skip bad rows, the model builds from the rows that survived and every test passes. They run against the whole table, each test scanning the data once, which is fine at this scale and needs thought at a billion rows. And they check what you wrote. No test notices a column whose scale changed by a factor of 100 unless someone writes a test on its range. Treat the passing build as the evidence that the rules you remembered were met, and keep adding rules from each incident.
