Cloud ELT PipelineBig Data
A cloud pipeline that turns raw Airbnb and census data into analysis-ready tables, on the tools industry data teams run.
The same setup a company data team would run: raw files land in Google Cloud, Airflow triggers the work on schedule, and dbt transforms everything step by step into clean, query-ready tables. History is preserved so last month's numbers never silently change, and the whole pipeline reruns every month without breaking.
The pipeline
Sydney Airbnb listings from May 2020 to April 2021, enriched with ABS 2016 Census data and NSW council-area mapping. Airflow on Cloud Composer lands each month's raw CSVs from cloud storage into its own bronze table, untouched and stamped with source-file and load-time audit columns, so any single month can be rerun without disturbing the others. From there dbt takes over: 17 version-controlled models and one snapshot transform bronze through silver into a gold star schema, and 12 tests on the fact table (uniqueness, not-null, referential integrity, value ranges) run with every build, so a bad load fails loudly instead of quietly corrupting the numbers.
History that does not lie
A host's superhost badge changes over time, and analysis goes wrong quietly if June's numbers get judged with December's status. The host dimension is therefore an SCD Type 2 snapshot: each change opens a new versioned row, and every monthly fact joins to the host's state as of that month. This design also produced the project's best war story: snapshotting across twelve months of raw data first ran for over five hours, until a staging model using a window function reduced the input to genuine change points only, turning the run practical. Preserving history is cheap; preserving it naively is not.
What the data said
The gold layer answered real questions, reproducibly. Median host age correlates with revenue at Pearson r around 0.737, 79.4 percent of multi-listing hosts stay within a single council area, and entire homes for two to four guests maximise stays. The demographic cut shows a roughly sevenfold revenue spread between the best and worst areas. And the census join earns its keep in one finding: even in the strongest area, a single-listing host's annual Airbnb revenue covers only about half the median mortgage, so the report concludes Airbnb income alone rarely carries a mortgage outside top-demand areas.
dag = DAG(
dag_id="bde_load_raw_to_postgres",
description="Load raw CSVs from GCS to Cloud SQL Bronze schema",
default_args={"retries": 1, "retry_delay": timedelta(minutes=3)},
schedule_interval=None,
start_date=datetime(2025, 1, 1),
catchup=False,
max_active_runs=1,
)
def _download_from_gcs(gcs_path: str, **_):
Path(LOCAL_DIR).mkdir(parents=True, exist_ok=True)
local_path = os.path.join(LOCAL_DIR, os.path.basename(gcs_path))
GCSHook().download(bucket_name=BUCKET,
object_name=gcs_path, filename=local_path)
return local_path