Prepared by: Sandy G. Cabanes
Data: MMDA Annual Average Daily Traffic (AADT) Reports, 2012–2025, pdf formats
Source: MMDA Freedom of Information Portal
Platform: Databricks SQL · Unity Catalog · AI/BI Dashboard Dashboards
- The MMDA publishes its Annual Average Daily Traffic (AADT) report as a PDF every year
- 14 years of traffic count data across Metro Manila — locked in a format that cannot be queried, joined, or trended without manual extraction
- The conventional response — copy-paste into a spreadsheet year by year — does not scale and is error-prone
- Any new year requires repeating the entire process; any trend question requires re-doing the work
- This is not a data availability problem. The data exists. The barrier is access format: published for human reading, not machine consumption
A seven-section ELT pipeline in Databricks SQL that takes raw PDF binary files as input and produces a validated, analytics-ready gold table and a live AI/BI Dashboard. Automated in SQL end-to-end, but with analyst-reviewed corrections applied programmatically in SQL.
- 14 PDF files read as binary from Unity Catalog Volume
ai_parse_document()converts each PDF to structured JSON, tagging every element by type:table,text,title,section_header,page_footer- Ingestion split into 3 batches to stay within compute limits
- Staging table + deduplication by
survey_yearprevents duplicate records on re-runs - Staging table dropped after deduplication
- Parsed JSON exploded element by element via
LATERAL VARIANT_EXPLODE() - Only elements where
type = 'table'are retained - Output: one record per table per document, carrying the full HTML string
- html_tables > extracted CTE > exploded CTE > silver table
- Each HTML table passed to
ai_extract()with an explicit schema: road code, road name, and 10 vehicle classes (car, PUJ, UV, taxi, PUB, truck, trailer, MC, tricycle, total) - All values extracted as STRING to safely handle comma-formatted numbers
- Two correction layers applied:
- Road codes — missing or changed codes assigned using MMDA classification knowledge (e.g., PRES. QUIRINO AVE. →
C:2; MARCOS HIGHWAY →Uncoded) - Road name standardization — variant spellings across years resolved to canonical names (e.g., "EDSA NORTH", "C-4 EDSA" →
EDSA)
- Road codes — missing or changed codes assigned using MMDA classification knowledge (e.g., PRES. QUIRINO AVE. →
- Commas stripped; all vehicle count columns cast to
INTviaTRY_CAST() - Failed casts return
NULLrather than breaking the pipeline LAST_VALUE() IGNORE NULLSforward-fills sparse road codes across rows
- Computed total (
car + puj + uv + ... + tricycle) compared against thetotalcolumn as printed in the source PDF - Discrepancies sorted by absolute value to surface the largest errors first
| Year | Road | Issue | Resolution |
|---|---|---|---|
| 2025 | PRES. QUIRINO AVE. | OCR misread: extracted 79,284; source shows 79,264 | Corrected via UPDATE |
| 2025 | TAFT AVE. | PDF total does not match its own column sums | No edit — error in source document |
| 2022 | EDSA | PDF total does not match its own column sums | No edit — error in source document |
| 2022 | QUEZON AVE. | PDF total does not match its own column sums | No edit — error in source document |
Source document errors are preserved and documented, not silently overwritten.
ai_extract()run a second time targeting only the TOTAL summary row per table- Extracted PDF total compared against
SUM()aggregations from the gold table - Single discrepancy: 2025 tricycle total in the PDF is 1,000 units higher than the sum of individual rows — confirmed as a source document error, no edit required
traffic_metricsaggregates gold data bysurvey_yearandroad_name- Vehicle class groupings and their limitations:
| Column | Includes | Limitation |
|---|---|---|
car_volume |
Private cars + TNVS (Grab, inDrive) | Cannot be separated from source data |
motorcycle_volume |
Private MC + for-hire (Angkas, JoyRide) | Cannot be separated from source data |
puj_pub_taxi_uv_tri |
PUJ, PUB, taxi, UV, tricycle | Definitively public transport — all regulated as for-hire |
truck_volume |
Truck + trailer | — |
The dashboard was built iteratively with Databricks Genie AI:
- Starting from a single natural language prompt, Genie scaffolded the initial layout and generated the underlying SQL
- The SQL Genie produced internally revealed what aggregations and metrics the dashboard needed — informing the design of the traffic_metrics table
- Once traffic_metrics was materialized and added as a dataset, Genie rebuilt the dashboard against the clean, pre-aggregated data. Final widget layout, chart types, and filters were refined manually by human analyst.
- Total AADT grew from 2.2 million (2012) to 3.9 million (2025) — a 76% increase over 13 years
- Reflects sustained urbanization and vehicle ownership growth across Metro Manila
- Volume dropped from 3.09M (2019) to 2.90M (2020) — ~6% decline consistent with community quarantine restrictions
- Growth resumed in 2021, accelerated through 2022–2025, and surpassed pre-pandemic levels by 2022
- MC counts exceeded car counts starting in 2023, driven by last-mile delivery, ride-hailing, and commuting demand as public transport capacity remained constrained
- Car volumes have stabilized; motorcycle volumes continue to climb with no signs of reversal
- As of 2025, motorcycles account for 50.83% of total traffic volume
Top 10 Roads by Volume — 2025
| Rank | Road | 2025 Volume |
|---|---|---|
| 1 | EDSA | 421.74K |
| 2 | COMMONWEALTH AVE. | 400.64K |
| 3 | MARCOS HIGHWAY | 283.42K |
| 4 | SSH | 269.02K |
| 5 | QUEZON AVE. | 256.88K |
| 6 | C.P. GARCIA / KATIPUNAN AVE. / TANDANG SORA | 225.89K |
| 7 | ORTIGAS AVE. | 208.81K |
| 8 | ROXAS BLVD. | 203.27K |
| 9 | PRES. QUIRINO AVE. | 200.14K |
| 10 | MAGSAYSAY BLVD. | 161.80K |
- 14 PDF reports processed without manual data entry
- 2012–2025 unified into a single queryable table
- OCR error identified and corrected programmatically
- Source document errors documented and preserved for auditability
- Dashboard live on the same platform as the data
| Layer | Table | Key Operation |
|---|---|---|
| Bronze | traffic_bronze |
ai_parse_document() — PDF to JSON |
| HTML Extract | traffic_html_tables |
VARIANT_EXPLODE() — filter type = 'table' |
| Silver | traffic_silver |
ai_extract() — HTML to typed rows |
| Gold | fr_ed_traffic_gold |
TRY_CAST() — strings to integers |
| Validation | validation_gold |
Computed vs. reported total check |
| Total Check | traffic_totals_check |
PDF TOTAL row vs. SUM() aggregation |
| Metrics | traffic_metrics |
Aggregation by year and road |
| Dashboard | AI/BI Dashboard | traffic_metrics as data source |
| Component | Tool |
|---|---|
| Storage | Databricks Unity Catalog Volume |
| PDF Parsing | ai_parse_document() — Databricks AI Function |
| Table Extraction | ai_extract() — Databricks AI Function |
| Data Layers | Databricks SQL (Bronze / Silver / Gold) |
| Validation | Databricks SQL |
| Dashboard | Databricks AI/BI Dashboard · exported as .lvdash.json |
Organizations working with periodically published reports in PDF form:
- Healthcare and insurance organizations — PhilHealth utilization reports, HMO claims summaries, multi-facility PDF filings requiring longitudinal analysis across reporting periods
- Local government units — multi-year permit, incident, or compliance data
- Infrastructure and transport firms — regulatory filings across agencies
- Research teams — annual survey results aggregated across multiple sources
- Compliance departments — audit or inspection reports requiring longitudinal analysis
Note: Source data are derived from MMDA public records and are freely available at the link above. This project produced original trend analysis and insights on Metro Manila traffic patterns spanning 14 years — findings that do not exist in any publicly available form. The data engineering pipeline was the means, not the end. Anyone is welcome to process the original MMDA PDFs themselves and arrive at their own conclusions.
Built by Sandy G. Cabanes · Freelance Data Analyst & Pipeline Developer · Philippines
- GitHub: SandyGCabanes
- LinkedIn: linkedin.com/in/sandygcabanes
End-to-end cloud data pipeline and analytics portfolio project demonstrating practical skills in cloud-native ELT, AI-assisted document parsing, data quality engineering, and business intelligence on the Databricks Lakehouse platform.