How myke-ajayi/dbt_pipeline works
Project Type and Purpose
• Project Type: This is a data transformation and pipeline project. It leverages DBT (data build tool) for managing SQL-based transformations and DuckDB for data storage and querying.
• Purpose: To ingest source datasets related to NYC farmers markets and community gardens ("farms"), load them using Python scripts into DuckDB, and then use DBT to transform the raw data into well-defined tables (markets, farms, and farm_to_market). Additionally, the project supports data validation, testing (with integration tests and reusable macros), and CI/CD practices.
File Structure for Key Architectural Insights
• Dbt_pipeline Directory:
• Contains the majority of DBT-specific components: models (organized into staging, intermediate, and product layers), profiles, packages, integration tests, macros (both generic tests and jinja helpers), and configuration files (dbt_project.yml).
• The presence of folders such as “models”, “target”, “dbt_packages”, and “packages.yml” clearly indicates the use of DBT best practices with a modular, layered architecture.
• DuckDB Directory:
• Contains Python scripts (export.py, load.py, transform.py) and SQL files that define raw data ingestion and preprocessing. This indicates that part of the ETL/ELT process is handled outside DBT.
• Data Directory:
• Stores the raw source files (in data/source_data) and the final outputs (CSV, parquet, shapefiles) produced by the pipeline.
• CI/CD and Testing:
Mapping Out the Main Components and Their Relationships
• Data Sources:
• External raw data from NYC open data portals, manually downloaded and placed in data/source_data.
• Data Ingestion and Preprocessing:
• Python scripts (export.py, load.py, transform.py in the DuckDB folder) process and load the raw CSV/GeoJSON files into databases.
• Database Layer:
• DuckDB used as the underlying database engine. The data is stored in files (data/duckdb.db, data/farm_to_market_dbt.db) and managed through SQL scripts.
• DBT Layer (ETL/ELT Pipeline):
• dbt models organized into staging (raw or lightly transformed data), intermediate (aggregated or joined data), and product (final curated tables: markets, farms, farm_to_market).
• Reusable macros and generic tests (located in dbt_packages/dbt_utils and macros folders) help maintain consistency.
• The DBT DAG (as referenced by the included "Dbt_pipeline/docs/dbt-dag.png") quickly communicates the transformation dependencies.
• Testing and Validation:
• Integration tests and unit tests within the dbt_utils packages and integration_tests directories ensure that each transformation is correct.
• CI/CD Integration:
• Configuration files (CircleCI, GitHub workflows, tox.ini, pytest.ini) denote that automated testing and deployment pipelines are in place.