Bootstrapping dbt tests from a single sample file
Profile a CSV or Parquet sample of your source table, accept the high-confidence checks, and paste a schema.yml into your dbt project.
New source, empty schema.yml, no idea where to start. The default move — guessing which columns are not_null and which deserve accepted_values — is slow and biased. A better move: profile a sample and let the evidence decide.
1. Export a sample
From your warehouse, COPY (SELECT * FROM raw.orders LIMIT 50000) TO 'orders.parquet'. Parquet is faster than CSV for the in-browser profiler and preserves types. 50k rows is more than enough to surface enums, presence patterns, and length distributions.
2. Profile and review the High band first
The High-confidence band on the review screen is your not_null, unique, and range checks where the engine has overwhelming evidence. Bulk-accept all of them. This alone gives you better coverage than most hand-written schema.yml files.
3. Skim Medium for format inferences
Email, ISO date, UUID, URL — these are inferred from regex coverage and need a human glance. If the column really is "user-facing email", accept. If it's "free-text that usually looks like an email", reject — you don't want a CI test failing on a malformed string when the column is documented as free-text.
4. Export as dbt schema.yml
The export modal generates a v2 schema.yml using built-in tests where possible and dbt_utils.expression_is_true for ranges and regex. Drop the file under models/<path>/schema.yml, rename the model to match yours, and dbt test --select away.
5. Re-profile after every meaningful change
When a source table evolves, re-profile and attach the previous .profile.json as a baseline. The drift panel tells you which columns gained values, lost values, or changed type — the exact diff you'd otherwise discover by reading PRs.