How ahmednbail/transport-analysis-data-pipeline works
1. What this repo is
- This is a batch data pipeline for transport/taxi analytics.
- The core flow is: source CSVs → GCS data lake → BigQuery warehouse → dbt transformations → analytical marts.
- Apache Airflow orchestrates the pipeline; dbt owns the transformation layer.
2. Orchestration layer
- The Airflow code lives under dags/.
- There are three main DAGs:
- Extract&Transfer-DAG.py for ingestion/loading work.
- DBT-DAG.py for running dbt models.
- Master_DAG.py for coordinating the overall end-to-end sequence.
- dags/upload.py is likely a helper used by the ingestion DAG to move files into the target storage/warehouse.
3. Ingestion and landing zone
- The repo README explicitly positions Google Cloud Storage (GCS) as the data lake.
- The ingestion side appears to be responsible for bringing raw transport CSVs into the pipeline and making them available for warehouse loading.
- This is the first hard boundary: raw or semi-raw data is kept separate from modeled warehouse tables.
4. Warehouse layer
- BigQuery is the central warehouse.
- dbt models are written to build warehouse tables rather than serving files directly from the lake.
- The architecture implies that the DAGs perform load/transfer steps into BigQuery, then dbt reads from BigQuery to create modeled datasets.
5. dbt project structure
- dbt_project.yml defines the dbt project.
- The models/ directory is split into standard analytics layers:
- models/staging/
- models/intermediat/ (intermediate transformations)
- models/marts/ (final business-facing tables)
- This is a classic layered transformation boundary: raw warehouse tables are normalized in staging, reshaped in intermediate models, and published as marts.
6. Concrete modeled outputs
- Staging: models/staging/stg_taxi.sql is the first clean abstraction over source data.
- Intermediate: models/intermediat/int_trips.sql combines or enriches trip-level logic.
- Marts:
- models/marts/fact_trip.sql
- models/marts/dim_location.sql
- models/marts/dim_vendors.sql
- These names show a star-schema-style output: a central trip fact table with supporting dimensions.
7. Reusable SQL logic
- macros/ contains shared dbt logic:
- safe_cast.sql for defensive type conversion.
- get_trip_duration_minutes.sql for derived trip metrics.
- get_vendor_names.sql for vendor lookup/normalization.
- These macros reduce duplicated SQL and centralize domain logic used across models.
8. Reference data and lookups
- seeds/ contains CSV lookup tables:
- payment_type_lookup.csv
- taxi_zone_lookup.csv
- In dbt, seeds typically become small reference tables used to enrich fact/dimension models.
- This means some business context is managed as versioned static data inside the repo, not as an external dependency.
9. Data quality and metadata
- models/source.yaml, macros/macros_properties.yml, and seeds/seeds_properties.yml indicate dbt documentation/tests/schema definitions are part of the project.
- tests/dags/test_dag_example.py and .astro/test_dag_integrity_default.py show DAG integrity checks are used to prevent broken Airflow deployments.
- snapshots/ exists, so the project is prepared for slowly changing dimension / historical change tracking, even if the directory is currently empty.
10. Deployment/runtime packaging
- Dockerfile, requirements.txt, packages.txt, and packages.yml show the project is meant to run in a containerized, reproducible environment.
- The .astro/ directory indicates an Astronomer-managed Airflow setup, which is important for how the DAGs are packaged and validated.
- airflow_settings.yaml suggests environment-specific Airflow configuration and connection management.
11. Analysis and presentation artifacts
- analyses/ is a dbt workspace for ad hoc SQL exploration.
- assets/ contains non-code documentation/media, including the logo used in the README.
- Power BI is mentioned in the README as the final visualization layer, but there is no BI code in the repo; it is a downstream consumer of the marts.
12. How to draw the architecture
- Draw five main boxes in sequence:
- Source data / transport CSVs
- GCS landing zone
- Airflow orchestration
- BigQuery warehouse
- dbt layered models
- then Power BI on top of the marts.
- Inside dbt, show the path staging → intermediate → marts.
- Around the pipeline, show shared assets for macros, seeds/lookups, tests, and containerized deployment.
Main entry points: config.yaml Airflow · Extract&Transfer-DAG.py Extract DAG · fact_trip.sql Marts · Master_DAG.py Master DAG
Components 18
Sources & landing
Raw transport data and the landing zone it first enters
- Source CSVs · Transport/taxi source files entering the pipeline
- GCS Lake · Google Cloud Storage landing zone for raw and semi-raw files
Airflow orchestration
DAGs and helpers that coordinate ingestion and dbt runs
- Airflow · Apache Airflow runtime coordinating the batch pipeline
.astro/config.yaml - Extract DAG · Loads and transfers incoming data
dags/Extract&Transfer-DAG.py - Master DAG · Coordinates end-to-end execution sequence
dags/Master_DAG.py - dbt DAG · Triggers dbt transformation runs
dags/DBT-DAG.py - Upload Helper · Shared file-moving logic used by ingestion tasks
dags/upload.py
Warehouse & dbt
BigQuery-backed transformation layers and analytical outputs
- BigQuery · Central warehouse for loaded and modeled tables
- dbt Project · dbt configuration and model definitions
dbt_project.yml - Staging · Clean abstractions over source tables
models/staging/stg_taxi.sql - Intermediate · Enrichment and trip-level reshaping logic
models/intermediat/int_trips.sql - Marts · Final fact and dimension outputs
models/marts/fact_trip.sql - Macros · Reusable dbt logic for casting, duration, and vendor lookups
macros/safe_cast.sql - Seeds · Versioned reference tables for payments and taxi zones
seeds/payment_type_lookup.csv
Platform & quality
Packaging, metadata, tests, and supporting assets
- Tests · DAG integrity and project validation checks
tests/dags/test_dag_example.py - Container Runtime · Containerized environment and dependency packaging
Dockerfile - Snapshots · Prepared area for slowly changing or historical capture
snapshots/.gitkeep - Power BI · Downstream analytics consumer of the marts
Connections 21
- Source CSVs → GCS Lake: land raw data · Raw transport CSVs are stored in the lake first
- GCS Lake → Extract DAG: ingest · Airflow ingestion tasks pick up landed files
- Airflow → Extract DAG: runs · Airflow executes the ingestion DAG
- Airflow → Master DAG: runs · Airflow executes the coordinating DAG
- Airflow → dbt DAG: runs · Airflow executes dbt transformation runs
- Extract DAG → Upload Helper: uses · The ingestion DAG relies on shared upload logic
- Extract DAG → BigQuery: load · Ingested data is loaded into the warehouse
- Master DAG → Extract DAG: sequence · Master orchestration starts ingestion first
- Master DAG → dbt DAG: sequence · Master orchestration triggers transformation after load
- BigQuery → dbt Project: source · dbt models read from warehouse tables
- dbt Project → Staging: defines · Project configuration includes staging models
- Staging → Intermediate: refine · Cleaned data flows into intermediate transformations
- Intermediate → Marts: publish · Intermediate logic feeds final marts
- Staging → Macros: reuse · Staging and downstream models share SQL macros
- Intermediate → Macros: reuse · Transformation logic reuses macros
- Marts → Seeds: enrich · Marts are enriched with lookup/reference data
- dbt DAG → dbt Project: executes · The dbt DAG runs the project models
- Tests → Airflow: guard · Integrity tests help protect DAG deployment
- Container Runtime → Airflow: packages · Docker and dependency files package the Airflow runtime
- Snapshots → Marts: extend · Snapshots provide a path for historical modeling
- Marts → Power BI: serve · Analytical marts feed downstream BI reporting