Berlin Short-Term Rentals.
dbt + DuckDB pipeline on Inside Airbnb data measuring host concentration and registration-number coverage across Berlin's 12 districts.
- ROLE
- Data Engineer
- TEAM
- Solo
- DATE
- Oct 2026 to Present
- TRACK
- Data
- STACK
- dbt Core, DuckDB, Python, SQL
FIG. 03A ARCHITECTURE
- load_raw.py (Inside Airbnb CSVs), backend. Connects to DuckDB · raw (READ_CSV).
- DuckDB · raw, storage. Connects to stg_listings (SQL). See note 1.
- stg_listings, backend. Connects to mart_host_concentration (REF), mart_registration_coverage (REF). See note 2.
- mart_host_concentration, storage. See note 3.
- mart_registration_coverage, storage. See note 4.
NOTES
Raw means raw
Every source column lands as VARCHAR, with _source_file, _scrape_date and _loaded_at added. All casting happens in dbt, so a bad value can be traced back to the exact file it came from.
Decision
All business rules (host size bands, the license_type regex) live in staging, and the marts only aggregate. The free-text license column is read for classification but never selected, because it can contain names of private individuals.
Trade-off
A registration number is a format check only (NN/A/AA/NNNNNN-NN, e.g. 04/Z/AZ/020562-26). It can't confirm the number exists in Berlin's register, and 4 listings with a valid number inside template text are counted as invalid.
What I got wrong
I built mart_registration_coverage by copying the host-concentration mart, and it shipped without the grain and sum-to-100 tests the first mart has. Next step: a shared share-of-district macro and the same tests on both marts.
RESULTS
- LISTINGS WITH A VALID-FORMAT REGISTRATION
- 34.6%
- LISTINGS MODELLED, 12 DISTRICTS
- 12,376
- DATA TESTS PASSING
- 19/19
PROJECT 03 / 08