all SQL builds use dbt

All SQL data product builds should use dbt to build and test their tables

Success criteria

valuable prerequisite work

• build export comparison supports .gdb.zip files
• exported geometry is compared spatially, not by serialized bytes
• dbt project properties are linted from a single shared configuration

build migrations

for all product builds that rely on SQL for core transformations:

1. is a dbt project that runs as part of the build
2. reference csv files are seed models
3. initial transformations to source data are tested staging models
4. transformations between staging and product are intermediate models
5. tables exported as product files are tested product models
6. no SQL that runs as part of the build lives outside the dbt project

Scope/current state

Data products in products/, grouped by how much of their build SQL runs through dbt.

Counts are the number of .sql files. Files under models/, macros/, analyses/, and tests/ are dbt artifacts; anything else is build SQL that dbt doesn't run.

Exempt

• edde — no .sql files at all (Python build)
• factfinder — Python build with one incidental SQL file (geolookup/2020/build.sql); its dbt project is QA-only (factfinder_dbt_qa)

Done

All layers present, tested at staging and product, and no build SQL outside dbt.

product	staging	intermediate	product	other models	outside dbt	staging tests	product tests
cscl	44	39	131	73	2	9	240
cdbg	4	11	11	0	0	6	49
template	6	3	3	0	0	10	4
zap-opendata	6	3	1	2	0	7	1

• cscl's two outside files are ad-hoc, not build steps (scripts/side_ap_geom_query.sql, poc_validation/reduce_dbt_output_to_fields.sql), so it passes criterion 6. Its 73 "other" models are etl_dev_qa/, qa_int/, log_files/.
• zap-opendata uses models/marts/ rather than models/product/.

Needs tests

All layers present and no build SQL outside dbt, but a required layer has no tests.

product	staging	intermediate	product	outside dbt	staging tests	product tests	fails
green_fast_track	35	23	41	0	0	25	3
zoningtaxlots	7	8	8	0	1	0	5

• green_fast_track's staging/_staging_models.yml defines no tests. Its 97 test blocks are all in _sources.yml.
• zoningtaxlots has no schema .yml under product/ at all — product/zoningtaxlot_db.sql and seven product/qaqc/*.sql are untested. Its 19 test blocks are likewise on sources.

Partially done

Meaningful dbt across layers, with build SQL still outside.

product	staging	intermediate	product	outside dbt	fails
cpdb	28	24	12	12	6
pluto	40	15	1	81	4, 5, 6
facilities	0	2	1	67	3, 4, 5, 6

• cpdb is closest to done — all three layers are real; only criterion 6 remains.
• pluto is staging-heavy with effectively no product layer. Its 64 models are not evenly spread: 40 staging, 15 intermediate, 1 product. The 81 files in pluto_build/ are the product logic.
• facilities has no staging models at all, and 67 of its 72 SQL files are in facdb/.

Not started

No dbt models, or a token model with no layer structure.

product	dbt project	models	outside dbt	where
ceqr	yes	1 (unlayered)	32	ceqr_app/
cbbr	yes	2 (1 staging, 1 intermediate)	12	sql/
developments	scaffold, no models	0	39	sql/
knownprojects	scaffold, no models	0	29	sql/
colp	scaffold, no models	0	10	sql/
checkbook	none	0	2	sql/

Start in products/ and compare the dbt artifacts under models/, macros/, analyses/, and tests/ with SQL outside those directories. Read the success criteria and the current-state tables, then choose a specific product migration such as cpdb, pluto, or facilities. Done means the product has the required dbt layers and tests, and no build SQL remains outside its dbt project.