Sudharsan

Singapore HDB Resale Market Pipeline

End-to-end ELT pipeline and dashboard on 228k HDB resale transactions.

Mar 2026 · Solo build · GCP, Terraform, BigQuery, dbt, Kestra, Python, Looker Studio

OriginalOriginal work, presented as first written.

Problem

Public housing (HDB) resale prices in Singapore are published as open data, but a buyer or analyst who wants to see how prices have moved has to piece the numbers together by hand. I wanted one repeatable pipeline and one dashboard that answer three questions:

  • How have resale prices evolved over time, by town and flat type?
  • What market segments exist across price ranges, room types and affordability levels?
  • Which towns show the highest growth, and which remain the most affordable?

I built it as my capstone for the Data Engineering Zoomcamp (2026).

Approach

HDB resale pipeline architectureData flows from the data.gov.sg API through Python ingestion into Cloud Storage and BigQuery, is enriched with OneMap geocoding, transformed by dbt into marts, and shown in Looker Studio. Kestra orchestrates the middle steps and Terraform provisions the infrastructure.data.gov.sg APIHDB resale transactions, 228,542 rowsPython ingestionRaw CSV to Cloud Storage, then BigQueryOneMap API577 street names geocodedBigQuery raw layerhdb_resale and hdb_locations, all stringsdbtStaging, then marts, with 24 testsBigQuery martsBy month, affordability, quarterly trendsLooker StudioDashboard with town, flat type and date filtersKestrafetch, geocode,dbt run, dbt testTerraform provisions the GCP infrastructure
  • Ingestion. A Python script pulls the full resale dataset from the data.gov.sg API (228,542 records, January 2017 to April 2026) and writes the raw CSV to Cloud Storage and then BigQuery. The raw layer keeps every column as a string, so no casting decisions are baked in before the transform step.
  • Location enrichment. Each of the 577 unique street names is geocoded through the OneMap API and stored as a lookup table, so the dashboard can map prices.
  • Transformation. dbt casts and cleans the data in a staging layer, then builds three marts: monthly prices by town and flat type, an affordability view by town, and quarter-over-quarter price trends.
  • Testing. 24 dbt tests cover the staging and mart layers: not-null checks on key columns, uniqueness of street names, accepted values for flat type, and one row per month, town and flat type in the monthly mart.
  • Orchestration. One Kestra flow runs the whole sequence: fetch, geocode, dbt run, dbt test. Credentials live in Kestra’s key-value store, and the flow carries its scripts inline, so it needs no mounted volumes.
  • Infrastructure. Terraform provisions the GCP resources, so the project can be rebuilt from scratch.
  • Serving. A Looker Studio dashboard reads the marts and offers town, flat type and date-range filters.

Result

All figures come from the project’s own marts in BigQuery and cover January 2017 to April 2026. Prices are average resale prices; the dashboard’s price chart plots medians. “Latest 12 months” means May 2025 to April 2026.

1. Resale prices rose about 51% from their 2019 low. The average resale price across all flat types dipped from S$443,889 in 2017 to S$432,138 in 2019, then climbed to S$652,510 in 2025. The biggest single-year jump was 2021, at +13.1%. For 4-room flats, the average went from S$437,120 in 2017 to S$672,112 in 2025, a rise of 53.8%.

2. Every town rose, but unevenly. Comparing 4-room averages in 2017 with the latest 12 months, Sembawang rose the most, up 78.6% (S$348,198 to S$621,902), ahead of Woodlands (+62.7%) and Pasir Ris (+62.5%). The four cheapest towns in 2017 (Woodlands, Sembawang, Choa Chu Kang and Yishun) all grew between 59.6% and 78.6%. The slowest growth, among towns with at least 50 sales in each period, was in Jurong East (+33.0%), Bukit Merah (+36.7%) and Bishan (+37.9%). Toa Payoh added the most in dollars, up S$345,771 to S$923,128.

3. Price per square metre differs about twofold across towns. For 4-room flats in the latest 12 months, the cheapest towns were Choa Chu Kang (S$5,634 per sqm), Jurong West (S$5,732) and Woodlands (S$5,834). The most expensive were Central Area (S$11,469), Queenstown (S$10,991) and Toa Payoh (S$9,937). The dashboard’s affordability index (median price divided by average floor area) puts the same ends of the market at the extremes: Jurong West, Choa Chu Kang and Jurong East lowest; Queenstown, Toa Payoh and Bukit Merah highest.

Two caveats. Town averages shift with the mix of flats sold in a period, such as age, storey and remaining lease. And price per square metre is a cost measure, not an income-based affordability measure. Towns with very few sales, such as Bukit Timah and Marine Parade, are left out of the rankings.

Reflection

The pipeline does what I set out to do, but a few things I would do differently now:

  • Load incrementally. Every run reloads the full dataset and replaces the raw table. A scheduled flow that fetches only new months, removes duplicates against what is already in BigQuery, and geocodes only new streets would cut both runtime and API calls.
  • Schedule it. The Kestra flow runs in Docker on my machine, so nothing refreshes the data on its own. Moving the orchestration somewhere that runs on a schedule is the step that would make this a live pipeline.
  • Test the data, not only the structure. The 24 tests check for nulls, duplicates, accepted values and the grain of the monthly table. They would not notice a partial API load. A row-count or freshness check would.
  • Make the metrics reproducible. The marts use approximate medians, and the affordability view covers a rolling 24 months counted from the day it was built, so its numbers move on every rerun. The findings above use averages, which can be recombined exactly from the monthly mart. Next time I would pin the window and compute exact medians for any headline figure.
  • Add context to the prices. Nearby MRT and school data was left out of this version, and so was any forecasting. Both would help explain why some towns grew faster than others, which the current marts can show but not explain.