Proof of Concept · Streaming Data Engineering & Analytics

Databricks
Transport Analysis Pipeline

A production-grade ride-hailing analytics pipeline built on Databricks Delta Live Tables (DLT). Auto Loader continuously ingests daily trip export files from an S3 landing zone through a Bronze ? Silver ? Gold medallion architecture. DLT Expectations enforce data quality rules at the pipeline level, SCD Type 1 CDC upserts keep driver and vehicle master data current, and city-partitioned Gold views deliver query-optimised analytics across 10 major Indian metropolitan regions.

Delta Live Tables (DLT) Pipeline Medallion Architecture (Bronze · Silver · Gold) Auto Loader · S3 Streaming Ingest 10 Indian Cities · Ride-Hailing Domain SCD Type 1 · CDC Upserts
a

Scope

Auto Loader Streaming Ingestion
Uses Databricks Auto Loader (cloudFiles) to continuously monitor an S3 landing zone for new daily trip export CSV files. Processes only new arrivals on each pipeline run without re-reading historical data, providing efficient incremental ingestion at scale.
Medallion Architecture (Bronze ? Silver ? Gold)
Three-layer Delta Lake architecture managed entirely through DLT declarative pipelines. Bronze stores raw streamed data. Silver applies DLT Expectations for data quality enforcement. Gold holds city-partitioned analytical views optimised for query performance.
DLT Expectations (Pipeline-Level DQ)
Data quality rules are declared natively inside DLT using @dlt.expect and @dlt.expect_or_drop decorators. Expectations validate trip_id uniqueness, non-null fare amounts, valid city codes, and non-negative distances — with quarantine routing for failed records.
SCD Type 1 CDC Upserts
Driver and vehicle master data is maintained using Slowly Changing Dimension Type 1 via Delta Lake APPLY CHANGES INTO. When driver profiles or vehicle assignments change, existing records are overwritten (no history retained) keeping the dimension tables current and lean.
City-Partitioned Gold Views
Gold layer tables and views are partitioned by city_code, enabling predicate pushdown for city-specific analytical queries. Covers 10 major Indian metros: Mumbai, Delhi, Bengaluru, Hyderabad, Chennai, Kolkata, Pune, Ahmedabad, Jaipur, and Surat.
Pipeline Orchestration & Monitoring
The entire medallion flow is orchestrated as a single DLT pipeline with lineage tracking, event log monitoring, and data quality metric dashboards built into the Databricks Pipeline UI. Supports triggered (batch) and continuous (streaming) execution modes.
Medallion Layer Structure — transport Catalog
Bronze
Raw Streaming Layer
Auto Loader streams daily CSV files from S3 into Bronze as append-only Delta tables with file metadata (_metadata.file_path, file_modification_time) and ingest_timestamp.
bronze.raw_trips
bronze.raw_drivers
bronze.raw_vehicles
bronze.raw_payments
bronze.raw_ratings
Silver
Validated & Enriched Layer
DLT Expectations enforce quality rules. Trips joined with drivers and vehicles. City codes validated, fare amounts normalised, timestamps parsed, and distance values verified.
silver.trips
silver.drivers (SCD Type 1)
silver.vehicles (SCD Type 1)
silver.payments
silver.ratings
Gold
Analytics-Ready Layer
Business entities partitioned by city_code. Fact table denormalised with driver, vehicle, and payment dimensions. City-level KPI views expose revenue, trip counts, and driver performance.
gold.dim_drivers
gold.dim_vehicles
gold.dim_date
gold.fact_trips (partitioned by city)
gold.vw_city_performance
gold.vw_driver_kpis
b

Features & Functionality

DLT Table / Module Key Functionality Techniques Used Status
setup_catalog Creates the transport Unity Catalog with Bronze, Silver, and Gold schemas; initialises DLT pipeline configuration and storage locations SQL DDL; Unity Catalog 3-level namespace; DLT pipeline settings Done
bronze.raw_trips (DLT) Auto Loader streams daily trip CSV files from S3 landing zone into a Bronze streaming table. Captures file path, modification time, and ingest timestamp as audit columns @dlt.table (streaming); cloudFiles format; _metadata columns; append-only Delta Done
bronze.raw_drivers / vehicles Auto Loader ingests driver profile and vehicle registration CSV snapshots from S3 into Bronze tables. Basis for SCD Type 1 processing at Silver layer @dlt.table (streaming); cloudFiles; schema inference with mergeSchema Done
silver.trips (DLT + Expectations) Applies DLT Expectations: expect trip_id is not null, expect fare_amount > 0, expect distance_km >= 0, expect city_code is in valid set. Failed records routed to quarantine table. Timestamps parsed, city names standardised @dlt.expect_or_drop; @dlt.expect_or_fail; F.to_timestamp; F.upper; quarantine table pattern Done
silver.drivers (SCD Type 1) Maintains current driver state using APPLY CHANGES INTO with SCD Type 1. When driver name, licence status, or rating changes, the existing Silver row is overwritten. Sequence column (updated_at) determines which change wins dlt.apply_changes(); APPLY CHANGES INTO; SCD Type 1; sequence_by; keys=[driver_id] Done
silver.vehicles (SCD Type 1) Same CDC pattern as drivers. Vehicle registration, type (auto/bike/cab), and city_assignment are kept current via SCD Type 1 upserts. Decommissioned vehicles flagged with is_active = false dlt.apply_changes(); SCD Type 1; conditional is_active flag; sequence_by updated_at Done
gold.fact_trips (City-Partitioned) Denormalised fact table joining Silver trips with dim_drivers, dim_vehicles, and payment data. Partitioned by city_code for predicate pushdown performance. Derives total_revenue, trip_duration_mins, and avg_speed_kmh as computed columns @dlt.table; partitionBy("city_code"); multi-table join; derived metric columns Done
gold.vw_city_performance Aggregated city-level KPI view: total trips, total revenue, avg fare per trip, avg trip distance, driver utilisation rate, and peak hour distribution — grouped by city_code and trip_date SQL GROUP BY; window aggregation; derived KPI metrics; DLT live view Done
gold.vw_driver_kpis Driver performance view: total trips completed, total earnings, avg passenger rating, cancellation rate, and active days in period. Joined with dim_drivers for full driver profile context SQL aggregation; cancellation_rate = cancelled / total; left join with dim_drivers Done
DLT Expectations — Data Quality Rules at Silver Layer
Trips
1
trip_id must not be null — @dlt.expect_or_drop
2
fare_amount must be > 0 — quarantine negatives and zeros
3
distance_km must be >= 0 — drop negative distances
4
city_code must be in valid 10-city set — @dlt.expect_or_fail on unknown cities
5
Timestamps parsed from multiple formats using F.to_timestamp with coalesce
Drivers
1
driver_id must not be null — primary key for SCD Type 1
2
licence_status normalised to uppercase (ACTIVE / SUSPENDED / EXPIRED)
3
avg_rating clamped to [1.0, 5.0] range — out-of-range values replaced with null
4
Duplicate driver_id within batch resolved by latest updated_at via sequence_by
Vehicles
1
vehicle_id must not be null — primary key for SCD Type 1
2
vehicle_type validated against allowed set (AUTO / BIKE / CAB / PREMIUM)
3
registration_year must be between 2000 and current year
4
is_active flag derived from status field (ACTIVE ? true, all others ? false)
Payments
1
payment_method normalised: CASH / UPI / CARD / WALLET
2
payment_status must be COMPLETED or REFUNDED — pending and unknown dropped
3
amount_paid must equal fare_amount ± tolerance (surge pricing allowed)
c

Pictorial Process Flow

?
S3 Landing
Daily CSV trip exports arrive
?
Auto Loader
cloudFiles stream detect
?
Bronze
Raw streaming Delta table
?
Silver
DLT Expectations · SCD Type 1
Gold
City-partitioned views & KPIs
1
Catalog & DLT Pipeline Setup Create the transport Unity Catalog with Bronze, Silver, and Gold schemas. Configure the DLT pipeline (triggered or continuous mode) with the target catalog, storage location, and cluster settings.
2
Auto Loader Bronze Ingestion Auto Loader monitors s3://transport-landing/ for new daily CSV files. On detection, new files are streamed into Bronze Delta tables (raw_trips, raw_drivers, raw_vehicles, raw_payments) as append-only with file metadata columns for full data lineage.
3
Silver — DLT Expectations & Transformations DLT materialised views and streaming tables apply Expectations at the pipeline level. Valid records proceed to Silver; records violating critical rules are dropped or routed to a quarantine table. Timestamps, city codes, and categorical fields are normalised. Bronze trips are enriched with driver and vehicle reference data via joins.
4
SCD Type 1 — Driver & Vehicle Master Data APPLY CHANGES INTO applies CDC upserts to silver.drivers and silver.vehicles. When a driver's licence status changes or a vehicle is reassigned, the existing row is overwritten (Type 1 — no history). The sequence_by updated_at column resolves out-of-order arrival within a batch.
5
Gold — City-Partitioned Fact Table gold.fact_trips is written partitioned by city_code using Databricks partition pruning. The table joins Silver trips with dim_drivers, dim_vehicles, and payments. Derived columns (trip_duration_mins, avg_speed_kmh, total_revenue_inr) are computed at write time for fast analytical reads.
6
Gold Views — City Performance & Driver KPIs vw_city_performance aggregates total trips, revenue, avg fare, and peak-hour distribution per city per day — enabling city-level benchmarking across 10 metros. vw_driver_kpis surfaces trip counts, earnings, rating, and cancellation rate per driver for operational monitoring.
7
Pipeline Monitoring & DQ Metrics The Databricks DLT Pipeline UI provides a real-time lineage graph, event log, and per-Expectation pass/fail metric dashboard. Data quality scores are tracked over time allowing trend analysis of incoming data reliability from source systems.
d

Data Flow Diagram

Source Inputs
trips_YYYY_MM_DD.csv (daily, S3 landing)
drivers_snapshot.csv (daily update)
vehicles_snapshot.csv (daily update)
payments_YYYY_MM_DD.csv (daily)
ratings_YYYY_MM_DD.csv (daily)
10 City Codes: MUM · DEL · BLR · HYD · CHN · KOL · PNE · AMD · JAI · SRT
Databricks DLT Pipeline
PySpark · Delta Live Tables · Unity Catalog
? Auto Loader (cloudFiles)
? Bronze: raw streaming tables
? Silver: DLT Expectations (DQ)
? SCD Type 1 via APPLY CHANGES
? Gold: city-partitioned tables
? Quarantine table for bad records
? Pipeline lineage & event log
? Triggered + continuous modes
Outputs
transport.gold.dim_drivers
transport.gold.dim_vehicles
transport.gold.dim_date
transport.gold.fact_trips (partitioned by city_code)
gold.vw_city_performance (10 cities)
gold.vw_driver_kpis
silver.quarantine_trips (failed DQ)
DLT Event Log (quality metrics)
Gold Layer Schema — Key Fields
dim_drivers
driver_id · driver_name · licence_status · avg_rating · city_code · is_active
dim_vehicles
vehicle_id · vehicle_type · registration_year · city_assignment · is_active
dim_date
date_key · year · month · quarter · day_of_week · is_weekend
fact_trips
trip_id · driver_id · vehicle_id · city_code · trip_date · distance_km · fare_amount · trip_duration_mins · avg_speed_kmh · total_revenue_inr
vw_city_performance
city_code · trip_date · total_trips · total_revenue · avg_fare · peak_hour
vw_driver_kpis
driver_id · total_trips · total_earnings · avg_rating · cancellation_rate · active_days
e

Technology Stack

Pipeline Engine
Databricks DLT
Delta Live Tables declarative pipeline; @dlt.table, @dlt.view, dlt.apply_changes(); triggered and continuous execution modes; built-in lineage graph
Delta Live Tables
Ingestion
Auto Loader
cloudFiles format source; incremental file discovery from S3 with checkpoint-based state; schema inference with mergeSchema; _metadata audit columns
cloudFiles · S3
Processing
Apache Spark (PySpark)
DataFrame API for all transformations; F.to_timestamp, F.upper, F.when, F.coalesce; streaming DataFrame operations within DLT context
PySpark 3.x
Storage Format
Delta Lake
ACID transactions; streaming Delta tables at Bronze; APPLY CHANGES INTO for SCD; partitionBy at Gold; time travel and schema evolution
Delta 3.x
Data Quality
DLT Expectations
@dlt.expect (warn), @dlt.expect_or_drop (filter), @dlt.expect_or_fail (halt pipeline); per-Expectation pass/fail metrics tracked in DLT event log
@dlt.expect_*
CDC Pattern
SCD Type 1
dlt.apply_changes() with stored_as_scd_type=1; keys=[driver_id / vehicle_id]; sequence_by=updated_at; overwrites existing rows — no history retained
APPLY CHANGES INTO
Catalog
Unity Catalog
3-level namespace: transport.bronze / silver / gold; data lineage tracking across pipeline layers; fine-grained access control
3-level namespace
Cloud Storage
Amazon S3
Source landing zone for daily trip file drops; Auto Loader checkpoint state stored in S3; Databricks external table storage
S3 · checkpoints
Language
Python + SQL
PySpark for DLT table definitions and transformations; %sql magic for DDL and Gold view creation; mixed-language DLT notebook pipeline
Python 3 · SQL