Test Data as Code: Version-Controlled Datasets with dbt Seeds and Fixtures
Test data lives in three places in most engineering teams: CSV files someone emailed around in 2019, a seed script in the repo that nobody trusts, and the production database that developers SSH into "just this once." None of these is managed, versioned, or reproducible.
"Test data as code" is a discipline that treats test datasets with the same rigor as application code: version-controlled in Git, reviewed in PRs, testable, and reproducible. It's not a new idea — infrastructure-as-code made the same case for servers — but it's underadopted in the data and QA world.
This guide covers the practical patterns: dbt seeds for data engineering workflows, pytest fixtures for application testing, and the higher-level discipline of treating your test datasets as first-class artifacts that need their own lifecycle management.
The Problem with "Tribal" Test Data
Before diving into solutions, let's name what we're replacing.
The shared test database. Multiple developers share a single staging database. Data gets created and never cleaned up. Tests pass on Monday and fail on Thursday because someone manually inserted a conflicting record. Nobody knows whose data is whose.
The fixture file nobody understands. A fixtures.json file with 500 records committed in 2021. Half the records reference foreign keys that no longer exist. The schema has been migrated 30 times. Adding a new required column breaks deserialization. Nobody wants to touch it.
The seed script that grew organically. Originally 20 lines, now 400 lines of imperative setup code with magic numbers everywhere. "Don't run this against staging — it'll wipe the auth0 users." No documentation. Everyone's afraid of it.
Test data as code solves all three by making data management explicit, declarative, and reviewable.
dbt Seeds: Data Engineering Test Fixtures
dbt (data build tool) has native support for "seeds" — CSV files that dbt loads into your data warehouse as tables. They're small, static datasets that your models reference: lookup tables, reference data, and crucially, test data.
Basic Seed Setup
project/
├── seeds/
│ ├── test/
│ │ ├── synthetic_customers.csv
│ │ ├── synthetic_orders.csv
│ │ └── synthetic_products.csv
│ └── reference/
│ ├── country_codes.csv
│ └── currency_codes.csv
├── dbt_project.yml
└── models/# dbt_project.yml
seeds:
my_project:
test:
+schema: test_data # Load test seeds to separate schema
+tags: ['test_data']
reference:
+schema: reference_dataLoad seeds: dbt seed --select test.*
# seeds/test/synthetic_customers.csv
customer_id,first_name,last_name,email,country,created_at
1001,Alice,Chen,alice.chen.test@example.invalid,US,2024-01-15
1002,Bob,Martinez,bob.martinez.test@example.invalid,MX,2024-01-16
1003,Yuki,Tanaka,yuki.tanaka.test@example.invalid,JP,2024-02-01Note the .invalid TLD — a useful convention that makes it immediately obvious these are synthetic emails that will never match real records.
Using Seeds in dbt Tests
Seeds integrate naturally with dbt's testing framework:
# models/staging/stg_orders.yml
models:
- name: stg_orders
tests:
- dbt_utils.equality:
compare_model: ref('expected_order_aggregates')
# expected_order_aggregates is a seed with known-good values-- models/expected_output/expected_order_aggregates.sql
-- Or use seeds/test/expected_order_aggregates.csv directlyThe pattern: define a seed with what the output should look like, run the model, compare. This is table-level snapshot testing.
Generating Seeds Programmatically
Hand-crafting 500 CSV rows is painful. Generate them with Python and commit the result:
# scripts/generate_test_seeds.py
import csv
from faker import Faker
import random
fake = Faker()
Faker.seed(42)
def generate_customers(n=200):
customers = []
for i in range(1, n + 1):
customers.append({
"customer_id": 1000 + i,
"first_name": fake.first_name(),
"last_name": fake.last_name(),
"email": f"test.user.{i:04d}@example.invalid", # Always fake
"country": random.choice(['US', 'GB', 'DE', 'JP', 'BR', 'AU']),
"created_at": fake.date_between(start_date='-2y', end_date='today')
})
return customers
def write_seed(data, path):
with open(path, 'w', newline='') as f:
writer = csv.DictWriter(f, fieldnames=data[0].keys())
writer.writeheader()
writer.writerows(data)
write_seed(generate_customers(), 'seeds/test/synthetic_customers.csv')Run this script when you need to regenerate seeds. The seed=42 ensures the output is deterministic — the same CSV is generated every time. Commit the CSV to Git, not just the script.
The "Commit the CSV" Rule
Some teams argue for committing only the generation script, not the generated CSV. This is wrong for test data. You want:
- Reviewable diffs. When a field is added or a value changes, the PR diff shows exactly what changed in the test data. If you only commit the script, reviewers have to mentally execute it.
- No dependency on script success. CI should load seeds even if there's a bug in the generation script. Committed CSVs are unconditionally loadable.
- Pinned state. The test data reflects the state the tests were written against. Regenerating from a script might produce semantically different data if the Faker library updates.
Commit the CSV. Use the generation script to create new versions, review the diff, and commit the update.
pytest Fixtures as Versioned Data
For application testing in Python, pytest fixtures are the equivalent of dbt seeds. They're declarative, composable, and live in the codebase.
From Functions to Fixture Files
Early in a project, fixtures are simple functions:
@pytest.fixture
def test_user(db):
return User.objects.create(
name="Test User",
email="test@example.invalid"
)As the project grows, you want fixtures in separate files, organized by domain:
tests/
├── fixtures/
│ ├── users.py
│ ├── products.py
│ ├── orders.py
│ └── data/
│ ├── bulk_users.json
│ ├── edge_case_orders.json
│ └── product_catalog.json
├── conftest.py
└── test_checkout.py# tests/fixtures/users.py
import pytest
import json
from pathlib import Path
from myapp.models import User
DATA_DIR = Path(__file__).parent / 'data'
@pytest.fixture(scope='module')
def standard_users(db):
"""Load the standard test user set from a versioned JSON file."""
user_data = json.loads((DATA_DIR / 'bulk_users.json').read_text())
users = [User.objects.create(**u) for u in user_data]
yield users
User.objects.filter(id__in=[u.id for u in users]).delete()
@pytest.fixture
def admin_user(standard_users):
"""The admin user from the standard set."""
return next(u for u in standard_users if u.role == 'admin')The data/*.json files are the "test data as code" — versioned in Git, reviewed in PRs, deterministic.
Versioning Fixture Data Files
Treat fixture data files like you'd treat database migrations: forward-only changes, never modify history.
tests/fixtures/data/
├── bulk_users_v1.json # Original 50 users
├── bulk_users_v2.json # Added phone field (migration 42)
├── bulk_users_v3.json # Added multi-locale users (feature branch)
└── bulk_users.json -> bulk_users_v3.json # Symlink to currentOr use a naming convention that ties fixture versions to schema versions:
# conftest.py
import os
SCHEMA_VERSION = os.environ.get('SCHEMA_VERSION', 'current')
@pytest.fixture(scope='session')
def fixture_path():
base = Path('tests/fixtures/data')
versioned = base / SCHEMA_VERSION
return versioned if versioned.exists() else base / 'current'This lets you run your tests against the fixture set that matches the schema version under test.
The Fixture → Seed → Test Pipeline
For full-stack applications with both a data warehouse (dbt) and an application layer (pytest), you want a unified test data pipeline:
scripts/generate_test_data.py
↓
[Generates canonical test data set]
↓
seeds/test/*.csv tests/fixtures/data/*.json
[dbt loads to warehouse] [pytest loads to app DB]
↓ ↓
dbt model tests application testsThe canonical test data set is the source of truth. Both the warehouse and the application database are seeded from the same underlying data.
# scripts/generate_test_data.py
import csv
import json
from faker import Faker
fake = Faker()
Faker.seed(42)
def generate_user(i):
return {
"id": 1000 + i,
"name": fake.name(),
"email": f"synthetic.{i:04d}@example.invalid",
"created_at": str(fake.date_time_this_year())
}
users = [generate_user(i) for i in range(200)]
# Write for dbt seeds
with open('seeds/test/users.csv', 'w') as f:
writer = csv.DictWriter(f, fieldnames=users[0].keys())
writer.writeheader()
writer.writerows(users)
# Write for pytest fixtures
with open('tests/fixtures/data/users.json', 'w') as f:
json.dump(users, f, indent=2, default=str)
print(f"Generated {len(users)} synthetic users")Commit both output files. CI validates they're in sync:
# In CI: regenerate and check for drift
python scripts/generate_test_data.py
git diff --exit-code seeds/test/ tests/fixtures/data/
# Exit code 0 = in sync, exit code 1 = drift detectedFixture Data Schema Validation
Fixture data goes stale when the application schema changes. Automate validation:
# tests/fixtures/validate_fixtures.py
import json
import pytest
from pathlib import Path
from pydantic import BaseModel, EmailStr, ValidationError
from typing import Optional
from datetime import datetime
class UserFixture(BaseModel):
id: int
name: str
email: str
created_at: datetime
phone: Optional[str] = None # Optional — gracefully handles old fixtures
def test_user_fixture_schema():
"""Validate that the user fixture file matches the current schema."""
data = json.loads(Path('tests/fixtures/data/users.json').read_text())
errors = []
for i, record in enumerate(data):
try:
UserFixture(**record)
except ValidationError as e:
errors.append(f"Record {i} (id={record.get('id')}): {e}")
assert not errors, f"Fixture validation failed:\n" + "\n".join(errors[:10])Run this in CI. It fails when a new required field is added without updating the fixture data, surfacing the problem before tests start failing in mysterious ways.
dbt Snapshot Testing
Beyond seeds, dbt has a snapshot feature for detecting row-level changes in source data. For test data management, you can use snapshots to detect when production data you're using as a reference unexpectedly changes:
-- snapshots/reference_prices.sql
{% snapshot reference_prices_snapshot %}
{{
config(
target_schema='snapshots',
unique_key='product_id',
strategy='check',
check_cols=['price', 'currency']
)
}}
SELECT product_id, price, currency, updated_at
FROM {{ source('production', 'product_prices') }}
{% endsnapshot %}If a product price changes in production, the snapshot records the change. Your downstream tests that depend on reference_prices_snapshot use the point-in-time version, not the live price.
Dependency Management for Test Data
Just like application dependencies, test data has dependencies — and they need to be explicit.
# tests/fixtures/orders.py
@pytest.fixture
def test_orders(standard_users, standard_products):
"""Orders depend on users and products existing first."""
user = standard_users[0]
product = standard_products[0]
order = Order.objects.create(
user=user,
product=product,
quantity=2,
status='pending'
)
yield order
order.delete()pytest handles the dependency graph automatically. When test_orders is requested, pytest resolves standard_users and standard_products first. This makes data dependencies explicit and self-documenting.
Review Process for Test Data Changes
Test data changes should go through the same PR review process as code changes. A PR checklist for test data:
- Why is this data changing? (link to the feature or schema migration that requires it)
- Are the email addresses clearly synthetic? (
.invalidTLD orexample.com) - Are edge cases covered? (null values, boundary values, international characters)
- Does the change maintain referential integrity? (all foreign keys valid)
- Has the fixture schema validator been run?
- Is the data generation script updated if the fixture was generated?
Making this a PR checklist ensures test data changes are deliberate, not accidental.
Connecting to End-to-End Testing
Version-controlled test data feeds directly into HelpMeTest's end-to-end testing:
*** Settings ***
Variables tests/fixtures/data/users.json
*** Test Cases ***
User Login With Synthetic Credentials
[Documentation] Test login flow using version-controlled test user fixture
Go To ${APP_URL}/login
Fill In email=${USERS}[0][email]
Fill In password=TestPassword123
Submit Login Form
Verify Dashboard loaded for ${USERS}[0][name]When the test data changes (a PR merges that updates the fixture), the tests automatically use the new data on the next CI run. No manual synchronization, no tribal knowledge.
Summary: The Discipline
Test data as code is three habits:
- Generate, don't hand-craft. Use Faker or domain-specific generators. Set a seed. Commit the output.
- Version everything. Fixture files in Git, not databases. Schema validation in CI. PRs for data changes.
- Declare dependencies. Test data dependencies should be explicit in code, not implicit in setup order or shared state.
The payoff: a test suite where any developer can clone the repo, run dbt seed or pytest, and have a fully working test environment in minutes — without needing access to production data or tribal knowledge about which tables to seed in which order.