NYCPlanning / NYCPlanning/data-engineering
KPDB - migrate build SQL to dbt
Nobody has claimed this yet.
- Dominant language
- Python
- Stars
- 43
- Forks
- 3
- Avg merge
- 23h 3m
- Merged PRs (30d)
- 44
Description
Unmet success criteria: 3, 4, 6 — 5 is partially met.
Current state (after #2580)
12 of the 30 .sql files in sql/ are dbt models. The other 18 are build SQL that bash/02_build.sh runs through psql.
| layer | models | what |
|---|---|---|
| staging | 0 | source transforms are still raw SQL |
| intermediate | 1 | kpdb_match_geom |
| product | 11 | seven longform_* aggregations, four future_units_by_* |
Two macros back those models: longform_by_boundary and match_geometry.
Reference CSVs (corrections_main, corrections_project, corrections_dob, zap_record_ids) are still loaded by create_corrections.sql, not seeds — criterion 2 is unmet as well.
Gotcha for whoever picks this up
dbt_project.yml sets model-paths: ["sql"], so every .sql file under sql/ is parsed as a model. dbt reports 30 models even though 18 are raw DDL it never runs. They resolve through ref() only because psql happens to have created tables of the same name first. A half-migrated file sitting in sql/ is silently in the graph, so partial migration is easy to get wrong here.
Contributor guide
No contributing guide indexed for this repository
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
Research direction
Start with dbt_project.yml, the 30 files under sql/, bash/02_build.sh, and create_corrections.sql. Map the 18 remaining build SQL files and the correction CSV loading before changing the dbt graph. Done means the remaining build work and correction data satisfy the migration criteria without half-migrated files being parsed as models.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- shell, sql
- Domain
- build-system, data-engineering
- Issue type
- Refactor
- Difficulty
- 5/5
- Estimated time
- Over a week
- Activity status
- Active
- Clarity
- Mostly clear
- Newbie friendliness
- 38/100