Airbnb Listings ETL and Engine Benchmark

This project builds a robust data pipeline for Airbnb listings and historical rates data, comparing query performance between DuckDB (columnar Parquet) and PostgreSQL (row-based RDBMS).

It serves as a demonstration of:

• ETL Pipelines: Cleaning and transforming multi-table Parquet datasets.
• Data Engineering: Comparing OLAP (DuckDB) vs OLTP (Postgres) performance for analytical workloads.
• SQL Analytics: Complex JOINs, window functions, and time-series aggregations.

Stack

• Python 3.12
• DuckDB
• pandas + pyarrow
• PostgreSQL + SQLAlchemy + psycopg2
• Docker (optional, for Postgres setup)

Project structure

• data/raw/: Original Parquet files.
• data/processed/: Cleaned Parquet files produced by the ETL.
• src/etl.py: ETL implementation (extract, clean, export processed parquets, load PostgreSQL).
• src/benchmark_runner.py: benchmark implementation for both engines using complex JOIN queries.
• tests/test_smoke.py: smoke tests for output quality and integrity.
• docker-compose.yml: Quick setup for a local PostgreSQL instance.
• main.py: Entrypoint for the ETL process.
• benchmark.py: Entrypoint for running benchmarks.
• test.py: Entrypoint for running tests.

Quick start

1. Create and activate a virtual environment.
2. Install dependencies:

   pip install -r requirements.txt

3. (Optional) Start PostgreSQL using Docker:

   docker-compose up -d

4. Configure environment values:

   cp .env.example .env
   # Edit .env if you are not using the default Docker Postgres setup

5. Run the ETL:

   python3 main.py

6. Run benchmarks:

   python3 benchmark.py

7. Run tests:

   python3 test.py

Benchmark Queries

The benchmark now includes more complex analytical queries:

• Q1_Narrow_Agg: Country-level revenue and occupancy.
• Q2_Wide_Agg: Top 20 cities by revenue with multiple metrics.
• Q3_Point_Lookup: Single listing detail retrieval.
• Q4_Date_Filter: Seasonal revenue analysis (June-August 2025).
• Q5_Join_Agg: Multi-table JOIN comparing performance across room types and superhost status.

Environment variables

• POSTGRES_URL: SQLAlchemy connection string.
• LISTINGS_RAW_PATH: Source listings parquet path.
• RATES_RAW_PATH: Source historical rates parquet path.
• LISTINGS_CLEAN_PATH: Cleaned listings output path.
• RATES_CLEAN_PATH: Cleaned rates output path.
• BENCHMARK_OUTPUT_CSV: Benchmark results CSV path.

Why this comparison?

DuckDB is a columnar-vectorized execution engine designed for analytical queries. PostgreSQL is a feature-rich, row-based database. For "wide" analytical queries and JOINs on large Parquet files, DuckDB often provides significant speedups without the overhead of a database server. This project quantifies those differences across various query patterns.