Seeding geometry fixtures for dbt tests

This page shows you how to build dbt seeds and fixture models of deliberately broken geometry — unclosed rings, self-intersections, wrong SRIDs, and null geoms — so your test suite proves it catches spatial failures rather than passing on data that happens to be clean.

When to use this approach

Seed known-bad geometry fixtures — rather than trusting that tests written against clean models will fire when it matters — when any of these hold:

  • Your geometry tests have never gone red. A validity or SRID test that only ever runs against good data is untested itself. Fixtures give it a controlled failure to catch, which is the discipline the spatial testing in CI pipelines gate depends on.
  • You are authoring reusable generic tests. When you build the checks in testing geometry validity with dbt generic tests, a fixture row per failure mode is how you confirm each test flags exactly its own class of bug and nothing else.
  • You want CI to run without a live source. WKT CSV seeds are self-contained, so the DuckDB CI validator can build and test the whole graph on a fresh runner with no database connection to seed from.

Prerequisites

  • dbt Core ≥ 1.7 with dbt-duckdb (for CI) and your production spatial adapter configured.
  • The DuckDB spatial extension loaded in the CI target so ST_GeomFromText, ST_IsValid, and ST_SRID are available.
  • Generic geometry tests already defined — assert_valid_geometry, assert_srid — so the fixtures have something to trip.
  • A ci_only tag and enabled guard so fixtures build in CI but never in production. Set the guard once:
yaml
# dbt_project.yml
seeds:
  dbt_geospatial:
    ci_fixtures:
      +tags: ["ci_only"]
      +enabled: "{{ target.name == 'ci' }}"
      +column_types:
        wkt: varchar
        expected_srid: integer

Step-by-step instructions

1. Author the WKT CSV seed

Store the raw fixtures as Well-Known Text in a seed CSV. Each row carries a stable id, a human-readable description of the defect, the WKT literal, and the SRID it should carry once stamped. Keeping geometry as WKT text means the seed is engine-agnostic — the geometry is only constructed at model build time.

csv
# seeds/ci_fixtures/geom_fixtures.csv
fixture_id,defect,wkt,expected_srid
1,valid_control,"POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))",4326
2,unclosed_ring,"POLYGON((0 0, 0 1, 1 1, 1 0, 0 0.5))",4326
3,self_intersection,"POLYGON((0 0, 1 1, 1 0, 0 1, 0 0))",4326
4,wrong_srid_point,"POINT(500000 4649776)",3857
5,null_geom,,4326
6,zero_area_collapse,"POLYGON((2 2, 2 2, 2 2, 2 2))",4326

Row 1 is the control: a valid unit square that must pass every test, proving the suite does not fail indiscriminately. Rows 2–6 each isolate one defect — an unclosed ring, a bowtie self-intersection, a point whose coordinates are metres (EPSG:3857) tagged as if it belonged in the 4326 project, an empty geometry, and a collapsed zero-area polygon.

Each fixture row is engineered to trip exactly one test, so a green control and five targeted failures together prove the suite discriminates rather than simply passing or failing everything.

Which geometry test each fixture row is designed to trip Six fixture rows on the left map to the test each is expected to fail on the right. The valid control maps to a pass on all tests. The unclosed ring, self-intersection, and zero-area collapse map to the ST_IsValid validity test. The wrong-SRID point maps to the ST_SRID assertion. The null geometry maps to the not_null test. Every defect targets exactly one test class. fixture row test it must trip valid_control unclosed_ring self_intersection zero_area_collapse wrong_srid_point null_geom passes every test the discriminating control assert_valid_geometry not ST_IsValid(geom) assert_srid ST_SRID(geom) <> 4326 not_null

Verify the seed loads and its row count matches the CSV:

bash
dbt seed --target ci --profiles-dir ./ci --select geom_fixtures
# Expect: "1 of 1 OK" and a 6-row relation in the ci target

2. Build a fixture model that constructs geometry from WKT

The seed holds text; a thin staging model turns it into typed geometry with the intended SRID stamped on. This is the surface the tests attach to.

sql
-- models/ci_fixtures/stg_geom_fixtures.sql
{{ config(materialized='table', tags=['ci_only']) }}

select
    fixture_id,
    defect,
    expected_srid,
    case
        when wkt is null or wkt = '' then null
        else ST_SetSRID(ST_GeomFromText(wkt), expected_srid)
    end as geom
from {{ ref('geom_fixtures') }}

Note the SRID is stamped, not transformed — the wrong-SRID fixture keeps EPSG:3857 coordinates under a 3857 label, so an SRID test comparing against the canonical 4326 catches it. Reprojection would hide the very bug the fixture exists to expose.

Verify the geometry constructs and the null row survives as null:

bash
dbt build --target ci --profiles-dir ./ci --select stg_geom_fixtures
# Then, ad-hoc:
#   select defect, ST_IsValid(geom) as ok, ST_SRID(geom) as srid
#   from stg_geom_fixtures;
# Expect ok = false for unclosed_ring and self_intersection, srid = 3857 for wrong_srid_point

3. Assert the tests fail on the bad rows

Point the geometry tests at the fixture model, but scoped so the control row is expected to pass and the defect rows are expected to fail. dbt’s generic tests fail when they return rows, so a direct attachment would fail the build — which is exactly what you want to observe in a dedicated fixture step, separate from the production gate.

yaml
# models/ci_fixtures/_ci_fixtures.yml
version: 2
models:
  - name: stg_geom_fixtures
    columns:
      - name: geom
        tests:
          # Runs against ALL rows; expected to FAIL because defect rows exist.
          # Run this selection in an isolated CI step that inverts the result.
          - assert_valid_geometry:
              config:
                tags: ["fixture_negative"]
          - assert_srid:
              srid: 4326
              config:
                tags: ["fixture_negative"]

Because a passing run on broken data is the real failure, invert the assertion in CI: run the negative tests and fail the pipeline only if dbt reports success.

bash
# a passing (exit 0) dbt test here means the fixtures are NOT being caught — that's the bug
if dbt test --target ci --profiles-dir ./ci --select tag:fixture_negative; then
  echo "FIXTURE REGRESSION: geometry tests passed on known-bad data" >&2
  exit 1
else
  echo "OK: geometry tests correctly caught the bad fixtures"
fi

Verify the inversion works by temporarily removing the self_intersection row from the CSV — the negative step should then flip to reporting a regression, confirming it genuinely depends on the fixtures.

4. Test the control row passes in isolation

Prove the suite is not simply failing everything by running the tests against only the valid control row and expecting a clean pass.

sql
-- tests/singular/assert_control_fixture_valid.sql
-- singular test: must return zero rows
select fixture_id
from {{ ref('stg_geom_fixtures') }}
where defect = 'valid_control'
  and (geom is null or not ST_IsValid(geom) or ST_SRID(geom) <> 4326)

Verify with a scoped build that runs the control assertion green while the negative fixtures fail separately:

bash
dbt test --target ci --profiles-dir ./ci --select assert_control_fixture_valid
# Expect: PASS — the valid unit square is not a false positive

Configuration reference

Fixture defect WKT shape Test it should trip
valid_control Closed unit square None — must pass every test
unclosed_ring Ring whose last vertex ≠ first assert_valid_geometry (ST_IsValid false)
self_intersection Bowtie polygon crossing itself assert_valid_geometry (ST_IsValid false)
wrong_srid_point Point in EPSG:3857 metres, tagged 3857 assert_srid against canonical 4326
null_geom Empty WKT → null geometry not_null on the geom column
zero_area_collapse Degenerate single-vertex polygon assert_valid_geometry or an area > 0 check

Gotchas & edge cases

  • ST_GeomFromText rejects malformed WKT outright. A syntactically broken literal (mismatched parentheses) throws at parse time, failing the model build rather than the test. Keep fixtures topologically invalid but syntactically well-formed WKT so they reach ST_IsValid.
  • ST_MakeValid in the fixture model hides the defect. Do not repair fixtures — the point is that they stay broken. Any cleaning belongs in real staging models, not the fixture path.
  • Stamping vs transforming SRID. Use ST_SetSRID to label the wrong-SRID fixture; ST_Transform would reproject the coordinates and destroy the mismatch you are testing for. Enforcement of the canonical SRID is covered in detecting SRID mismatches with dbt tests.
  • Fixtures leaking into production. Without +enabled: "{{ target.name == 'ci' }}", a production deploy tries to build broken geometry. Gate both the seed and the model, and confirm with dbt ls --target prod --select tag:ci_only returning nothing.
  • Empty-string vs null in CSV. dbt reads an empty CSV cell as an empty string on some adapters; coalesce wkt = '' to null in the model so the null-geometry fixture is genuinely null, not an empty text literal that ST_GeomFromText rejects.

FAQ

Why seed broken geometry instead of just trusting my tests?

A test that only ever runs against clean data is unverified — you have no evidence it would fire on a real defect. Seeding one fixture per failure mode gives each test a controlled bad row to catch, and a valid control row to leave alone. That is the only way to know the validity, SRID, and null checks in your CI gate actually work.

How do I stop the fixtures from failing my normal CI build?

Tag the negative fixtures separately (fixture_negative) and run them in a dedicated CI step that inverts the exit code — the pipeline fails only if the tests pass on bad data. Your production model gate runs a different selection that excludes the fixtures, so a normal green build is unaffected. Both are gated on target.name so fixtures never reach production.

Should I use CSV seeds or SQL models for fixtures?

Use a CSV seed for the raw WKT literals — it is version-controlled, diff-friendly, and needs no live source — then a thin SQL model to construct typed geometry from the text with ST_GeomFromText and ST_SetSRID. The seed is the data; the model is where the SRID gets stamped and the null case is normalized.

Will these WKT fixtures work on both DuckDB and PostGIS?

Yes for the common constructors — ST_GeomFromText, ST_SetSRID, ST_IsValid, and ST_SRID exist on both. The one caveat is that DuckDB and PostGIS can disagree on the validity verdict for a few degenerate shapes, so scope the fixture set to defects both engines flag, and reserve engine-specific edge cases for a nightly PostGIS job as described in the DuckDB validator guide.

Up: Part of Spatial Testing in CI Pipelines.