# FreightFlow Phase 01 — Engineering Reference Source: https://github.com/toandatawork/freight-data-platform All statements about behavior are based on source code and documentation, not fresh production execution. ## Platform overview End-to-end architecture, layer mapping, orchestration and lineage. End-to-end architecture, layer mapping, orchestration and lineage. ### Why combine Medallion and dbt layers? Perspective Question answered FreightFlow implementation Medallion Bronze / Silver / Gold How far has data been processed and refined? Delta Bronze → logical Silver transformations → Gold analytical marts. dbt model structure Staging / Intermediate / Marts What responsibility does each SQL model have? Source-facing views → reusable transformation tables → facts, dimensions and aggregate. Deployment targets dev / ci / prod Which catalog receives a build or validation run? freight_dev / freight_ci / freight_prod configured in dbt profiles. ### Why Medallion? It communicates the platform-level refinement path, including ingestion outside dbt. It does not prescribe which code framework must implement each step. ### Why dbt model layers? They separate source normalization, reusable SQL logic and published analytical entities. dbt manages dependencies through source() and ref() . ### Trace one delivery event The staged model converts a source flag to boolean. The intermediate model recomputes delivery status and tracks mismatches. The fact model adds date and facility keys. Explore: staging SQL · intermediate SQL · Gold fact SQL ## Bronze · Ingestion engineering PostgreSQL → Databricks PySpark → Delta Bronze. PostgreSQL → Databricks PySpark → Delta Bronze. ### PostgreSQL / oltp Six reference tables and eight transaction or metric tables. ### PySpark + JDBC Databricks notebook constructs source queries and reads them through Spark JDBC. ### Delta Bronze Tables at {catalog}.bronze.{table} , with ingestion metadata. ### Loading policy ### Overwrite — 6 tables customers, trucks, trailers, facilities, routes, drivers ### Watermark — 8 tables loads, trips, delivery_events, fuel_purchases, maintenance_records, safety_incidents, driver_monthly_metrics, truck_utilization_metrics ### Implementation & decisions Source: databricks/nb_ingest_bronze.py ### Actual Spark JDBC ingestion pattern Open complete file Open complete file ### Why JDBC, overwrite and watermark append? - JDBC: a direct batch route from the existing PostgreSQL source. - Overwrite: a simple current-state refresh for relatively small reference entities. - Watermark append: reads rows with a source timestamp later than the latest loaded timestamp. Trade-off: a watermark is not CDC. Corrections to older records, deletions and equal-timestamp arrivals may require reconciliation or lookback + merge. ### Validation scope and failure modes - The notebook logs rows_read and adds _ingested_at and a logical _source_file identifier. - Delta writes configure mergeSchema=true ; this does not constitute a data contract. - The submitted source defines the write logic; ingestion throughput, retry idempotency and late-update recovery are not benchmarked here. ## Silver · dbt transformations Staging prepares source-aligned views; intermediate models apply reusable SQL logic. Staging prepares source-aligned views; intermediate models apply reusable SQL logic. ### 14 dbt views - Read declared Bronze relations with source() . - Apply minimal type normalization and consistent source-facing names. - Example: string-to-boolean conversion for on_time_flag . ### Tables + exception models - Calculate timestamp-based delivery status and mismatch flags. - Enrich load records before marts. - Track timestamp anomalies, flag mismatches and orphan references. ### Why two dbt layers inside Silver? Staging isolates source-facing normalization. Intermediate carries reusable business logic and joins. Keeping these responsibilities separate gives marts cleaner inputs and makes the transformations easier to test independently. Smaller projects can omit intermediate when it adds no useful boundary. Folder: dbt/models/intermediate/ ### Staging: SQL example and source configuration Open complete file Staging views avoid persisting lightweight projections. The trade-off is repeated SQL evaluation when downstream models run. Source declarations and freshness thresholds are configured in dbt/models/staging/_staging__sources.yml . ### Intermediate: SQL example and reusable macro Open complete file The model preserves the original flag and adds a computed flag and mismatch indicator. It calls a reusable dbt macro. Macro: dbt/macros/business/recompute_on_time.sql ### Exception tracking and downstream boundaries The following models record anomalies for inspection: - qtn_time_anomaly - qtn_flag_mismatch - qtn_orphan_fk They do not automatically prevent every flagged record from reaching all Gold facts. An exclusion policy must be implemented in downstream SQL or a publication gate. ## Gold · Data model & marts Four facts, seven dimensions, one aggregate and separate historical snapshots. Four facts, seven dimensions, one aggregate and separate historical snapshots. The Gold layer publishes four fact models, seven dimensions and a daily aggregate. Each fact has a defined intended grain ; production grain uniqueness still requires data tests and runtime evidence. Fact model Intended grain Actual direct dimension joins Materialization fct_load 1 row / load customer, route, date Incremental merge fct_delivery_event 1 row / event facility, date Incremental monthly microbatch fct_fuel_purchase 1 row / purchase truck, driver, date Incremental append fct_shipment_lifecycle 1 row / load lifecycle None (direct Gold joins) Incremental merge + contract ### Dimension models The seventh dimension, dim_trailer , has no direct FK join from these four fact SQL selections. ### Why dimensional modeling? Grain-specific facts support defined event and transaction measures; conformed dimensions organize reusable descriptors for analysis. Join cardinality must be checked to protect aggregations. ### Historical attributes dbt snapshots track selected driver and truck changes using the check strategy. Current mart dimensions still read staging; historical as-of joins are not implemented in these marts. ### Merge, microbatch and append: implementation comparison - Merge / fct_load: configured with unique_key=load_id ; updates intended load-level rows. - Microbatch / fct_delivery_event: configured by actual_datetime in monthly batches. - Append / fct_fuel_purchase: uses a max purchase-date filter; corrected older rows may not be reprocessed. - Materialized view / agg_daily_ontime: daily delivery aggregates. Validation boundary: incremental keys and contracts do not independently prove fact grain uniqueness. Open complete file ### Grain risk in fct_shipment_lifecycle Several CTEs join on load_id . If trips, pickup events or delivery events contain multiple rows per load, the join can multiply lifecycle rows. A unique_key setting does not deduplicate those upstream records automatically. Consider windowed selection, aggregate-by-grain or explicit uniqueness tests when improving the model. Open complete file ### Snapshot and contract code Open complete file The lifecycle contract is declared here: dbt/models/marts/_marts__models.yml . The Gold aggregate definition is dbt/models/marts/agg_daily_ontime.sql . ## Inside the dbt project 16 repository components, from source() and ref() to snapshots, tests and hooks. 16 repository components, from source() and ref() to snapshots, tests and hooks. This section explains dbt mechanisms used across Silver and Gold. Expand any component to inspect its responsibility, design rationale and an excerpt from the submitted repository. ### 01 · Configuration Project configuration Controls model paths, schemas, materializations, access and on-run-end hooks. Reason: A single project config makes model placement and build behavior inspectable. Open complete file ### 02 · Lineage Source definitions Declares Bronze sources in the active catalog; freshness thresholds are configured. Reason: source() references a declared external relation. Open complete file ### 03 · Lineage ref() & model DAG Connects staging to reusable intermediate transformations. Reason: dbt resolves dependencies and model locations through ref(). Open complete file ### 04 · Transforms Materializations Staging views, intermediate tables, incremental facts and one materialized-view aggregate. Reason: Selection changes compute timing, storage and update behavior. Open complete file ### 05 · Transforms Macros & Jinja Centralizes the timestamp-based on-time expression. Reason: Reused SQL expressions can be tested without copying logic to every model. Open complete file ### 06 · Quality Generic tests Contains a custom relationship-quality test with a threshold. Reason: Generic tests parameterize checks across model columns. Open complete file ### 07 · Quality Singular tests Defines a query that returns records violating an assertion. Reason: Keeps checks for specific SQL behavior in version control. Open complete file ### 08 · Quality Unit tests Specifies controlled inputs and expected outputs for transformation logic. Reason: Tests a model transformation independently of the complete dataset. Open complete file ### 09 · Quality Seeds CSV rules metadata read by the quality-rule builder. Reason: Treats rule definitions as versioned project inputs. Open complete file ### 10 · Lifecycle Snapshots check strategy tracks changes to selected driver attributes. Reason: Snapshot history is separate from current-state driver dimension. Open complete file ### 11 · Operations Observability Computes rule results and failure percentages in an observability model. Reason: Profile and rule results support inspection; not automatically a Gold filter. Open complete file ### 12 · Operations End-of-run hooks Checks quality outcomes after dbt has run selected resources. Reason: A hook after execution cannot retroactively prevent earlier table writes. Open complete file ### 13 · Quality Contracts Enforced contract is defined for fct_shipment_lifecycle. Reason: Schema/type contracts and row-level tests solve different problems. Open complete file ### 14 · Metadata Exposures & docs Declares downstream consumers in the dbt manifest. Reason: Exposure metadata represents a declared relationship, not live proof of integration. Open complete file ### 15 · Configuration Schema routing Overrides default schema naming to use the configured custom schema. Reason: Changes the physical destination of dbt models. Open complete file ### 16 · Configuration Groups & access Defines project ownership grouping used by model access configuration. Reason: dbt model access does not establish cloud IAM enforcement. Open complete file ## Orchestration, environments & CI/CD Scheduling, dbt-aware task graphs, validation workflows and deployment targets. Scheduling, dbt-aware task graphs, validation workflows and deployment targets. ### Daily orchestration - freight_daily is scheduled at 0 6 * * * in scheduler timezone. - Airflow triggers a Databricks ingestion job and waits for it. - Cosmos builds the dbt_freight task group and dbt dependencies. - Its config sets TestBehavior.AFTER_ALL . ### Environment targets - dev → freight_dev - ci → freight_ci - prod → freight_prod Catalog configuration alone does not prove compute, network or IAM isolation. Runtime code: airflow/dags/freight_daily.py · dbt/profiles.yml ### Airflow failure behavior: inspect code Open complete file With AIRFLOW_STRICT_INGESTION=false (default), a job-trigger failure can be logged while downstream dbt continues using prior Bronze tables. Strict mode raises the error. ### CI / CD and backfill boundaries CI workflow · docs workflow · backfill DAG ## Execution evidence & decisions Reviewable implementation proof, design consequences and known validation boundaries. Reviewable implementation proof, design consequences and known validation boundaries. ### What the screenshot supports - The named DAG and its task graph were visible in Airflow. - Historical states include both successful and failed/unfinished tasks. - It captures a moment in run history. #### What it does not establish - Production SLA or uptime. - Successful full-data test history. - Throughput, freshness or operating-cost results. ### Design decisions and their consequences Decision in code Reason it can be useful Engineering consequence Direct Spark JDBC Short batch ingestion path No CDC change-log capture Staging views Low duplication for simple SQL normalization View work may repeat downstream Intermediate tables Persist reused derived logic Extra write/storage overhead Mixed fact incrementals Tailor writes to model grain/time Different recovery and late-update risks dbt snapshots Retain selected attribute change history Current mart dimensions do not use historical snapshots on-run-end DQ hook Check rule results after dbt invocation Does not retroactively prevent mart writes Evidence categories: code-defined behavior · documented profiling · captured execution . No performance or runtime reliability benchmark is claimed. ## Source code & documentation Direct links to the repository, model definitions and supporting implementation notes. Direct links to the repository, model definitions and supporting implementation notes. The source repository is the technical reference for the claims on this page. Links below open the corresponding implementation files. ### Ingestion notebook databricks/nb_ingest_bronze.py ### dbt project dbt/dbt_project.yml ### Staging sources dbt/models/staging/_staging__sources.yml ### Gold model tests dbt/models/marts/_marts__models.yml ### Airflow DAG airflow/dags/freight_daily.py ### CI workflow .github/workflows/ci.yml ### Docs workflow .github/workflows/cd.yml ### Architecture decisions docs/architecture_decisions.md ### Profiling findings docs/data_quality_findings.md ## Verified repo links - .github/workflows/cd.yml: https://github.com/toandatawork/freight-data-platform/blob/main/.github/workflows/cd.yml - .github/workflows/ci.yml: https://github.com/toandatawork/freight-data-platform/blob/main/.github/workflows/ci.yml - airflow/dags/freight_backfill.py: https://github.com/toandatawork/freight-data-platform/blob/main/airflow/dags/freight_backfill.py - airflow/dags/freight_daily.py: https://github.com/toandatawork/freight-data-platform/blob/main/airflow/dags/freight_daily.py - databricks/nb_ingest_bronze.py: https://github.com/toandatawork/freight-data-platform/blob/main/databricks/nb_ingest_bronze.py - dbt/dbt_project.yml: https://github.com/toandatawork/freight-data-platform/blob/main/dbt/dbt_project.yml - dbt/macros/business/recompute_on_time.sql: https://github.com/toandatawork/freight-data-platform/blob/main/dbt/macros/business/recompute_on_time.sql - dbt/macros/dq/dq_severity_gate.sql: https://github.com/toandatawork/freight-data-platform/blob/main/dbt/macros/dq/dq_severity_gate.sql - dbt/macros/generate_schema_name.sql: https://github.com/toandatawork/freight-data-platform/blob/main/dbt/macros/generate_schema_name.sql - dbt/macros/generic/test_not_orphaned.sql: https://github.com/toandatawork/freight-data-platform/blob/main/dbt/macros/generic/test_not_orphaned.sql - dbt/models/groups.yml: https://github.com/toandatawork/freight-data-platform/blob/main/dbt/models/groups.yml - dbt/models/intermediate/_unit_tests.yml: https://github.com/toandatawork/freight-data-platform/blob/main/dbt/models/intermediate/_unit_tests.yml - dbt/models/intermediate/int_delivery_events_cleaned.sql: https://github.com/toandatawork/freight-data-platform/blob/main/dbt/models/intermediate/int_delivery_events_cleaned.sql - dbt/models/intermediate/quarantine/qtn_flag_mismatch.sql: https://github.com/toandatawork/freight-data-platform/blob/main/dbt/models/intermediate/quarantine/qtn_flag_mismatch.sql - dbt/models/intermediate/quarantine/qtn_orphan_fk.sql: https://github.com/toandatawork/freight-data-platform/blob/main/dbt/models/intermediate/quarantine/qtn_orphan_fk.sql - dbt/models/intermediate/quarantine/qtn_time_anomaly.sql: https://github.com/toandatawork/freight-data-platform/blob/main/dbt/models/intermediate/quarantine/qtn_time_anomaly.sql - dbt/models/marts/_marts__exposures.yml: https://github.com/toandatawork/freight-data-platform/blob/main/dbt/models/marts/_marts__exposures.yml - dbt/models/marts/_marts__models.yml: https://github.com/toandatawork/freight-data-platform/blob/main/dbt/models/marts/_marts__models.yml - dbt/models/marts/agg_daily_ontime.sql: https://github.com/toandatawork/freight-data-platform/blob/main/dbt/models/marts/agg_daily_ontime.sql - dbt/models/marts/facts/fct_delivery_event.sql: https://github.com/toandatawork/freight-data-platform/blob/main/dbt/models/marts/facts/fct_delivery_event.sql - dbt/models/marts/facts/fct_fuel_purchase.sql: https://github.com/toandatawork/freight-data-platform/blob/main/dbt/models/marts/facts/fct_fuel_purchase.sql - dbt/models/marts/facts/fct_load.sql: https://github.com/toandatawork/freight-data-platform/blob/main/dbt/models/marts/facts/fct_load.sql - dbt/models/marts/facts/fct_shipment_lifecycle.sql: https://github.com/toandatawork/freight-data-platform/blob/main/dbt/models/marts/facts/fct_shipment_lifecycle.sql - dbt/models/observability/dq_rule_results.sql: https://github.com/toandatawork/freight-data-platform/blob/main/dbt/models/observability/dq_rule_results.sql - dbt/models/staging/_staging__sources.yml: https://github.com/toandatawork/freight-data-platform/blob/main/dbt/models/staging/_staging__sources.yml - dbt/models/staging/stg_freight__delivery_events.sql: https://github.com/toandatawork/freight-data-platform/blob/main/dbt/models/staging/stg_freight__delivery_events.sql - dbt/profiles.yml: https://github.com/toandatawork/freight-data-platform/blob/main/dbt/profiles.yml - dbt/seeds/dq_rules.csv: https://github.com/toandatawork/freight-data-platform/blob/main/dbt/seeds/dq_rules.csv - dbt/snapshots/snap_drivers.sql: https://github.com/toandatawork/freight-data-platform/blob/main/dbt/snapshots/snap_drivers.sql - dbt/tests/assert_on_time_flag_matches_computed.sql: https://github.com/toandatawork/freight-data-platform/blob/main/dbt/tests/assert_on_time_flag_matches_computed.sql - docs/architecture_decisions.md: https://github.com/toandatawork/freight-data-platform/blob/main/docs/architecture_decisions.md - docs/data_quality_findings.md: https://github.com/toandatawork/freight-data-platform/blob/main/docs/data_quality_findings.md