Project 3: Data Warehouses

Building and Exploring an Airbnb Data Warehouse for Analytics & Data Understanding

Temp Text

The Problem/Opportunity:

Organizations often store operational data in raw CSV files without a clear analytical structure. While this data contains valuable business information, it becomes difficult to query, analyze, and scale for reporting purposes when it lacks proper modeling and relationships.

In this project, the goal was to transform raw Airbnb datasets into a structured and reliable Data Warehouse that supports analytics, SQL exploration, and future reporting needs.

The project focused on:

  • Building a clean and organized Airbnb Data Warehouse
  • Understanding and implementing Fact and Dimension table architecture
  • Ensuring data consistency, integrity, and scalability
  • Creating a strong analytical foundation for future dashboards and reporting
  • Practicing SQL development, data modeling, and business-oriented querying

The key stakeholders include:

  • Data teams (data architecture & modeling)
  • Business stakeholders (analytics & reporting)
  • New analysts and team members (learning SQL & understanding Data Warehousing concepts)

Key Insights:

Database Setup & Data Preparation

  • Created a dedicated database called Greek Airbnb
  • Configured the database collation to Greek_CI_AI to properly support Greek characters and text
  • Imported raw CSV datasets into SQL Server Management Studio (SSMS):
    • Listings data
    • Calendar/Bookings data
    • Reviews data
  • Built the Stage layer tables:
    • ListingsStage
    • BookingsStage
    • ReviewsStage
  • Standardized and validated data types for all columns using the provided data dictionary
  • Prepared the raw datasets for Data Warehouse transformation and loading

Data Cleaning & Quality Checks

  • Performed duplicate checks on key identifiers using:
    • COUNT()
    • COUNT(DISTINCT ...)
  • Applied deduplication logic in the final Fact and Dimension tables using:
    • ROW_NUMBER()
    • Common Table Expressions (CTEs)
  • Maintained raw data integrity in Stage tables while cleaning data during Data Warehouse loading
  • Ensured consistency between related entities and transactional records

    • Data Warehouse Architecture

    Designed and implemented a simple analytical Data Warehouse structure by separating data into:

    Dimension Tables
  • Created Dimension tables to store descriptive business information, including:
    • Property information
    • Property types
    • Room types
    • Host details
    • Locations and neighborhoods
    • Review-related attributes
  • Used:
    • Primary Keys
    • IDENTITY(1,1) surrogate keys where necessary
    Fact Tables
  • Created Fact tables to store measurable business events and transactional data such as:
    • Bookings and availability
    • Pricing information
    • Review activity
  • Established relationships between Facts and Dimensions through Foreign Keys to support analytical queries and reporting.

    • Data Integrity & Constraints

  • Implemented multiple database constraints to ensure reliability and consistency:
    • Primary Keys for all Dimension and Fact tables
    • Foreign Key relationships between Fact and Dimension tables
    • NOT NULL constraints for ID columns
    • Referential integrity between related tables
    • Structured schema aligned with Data Warehouse best practices

    Bus Matrix & Schema Design

  • Designed a Bus Matrix to document the relationships between Fact and Dimension tables
  • Organized the analytical structure for easier scalability and future reporting
  • Documented table schemas, columns, and data types using Data Warehouse schema templates

    • SQL Analysis & Business Exploration

    Developed analytical SQL queries combining Fact and Dimension tables to generate business insights and validate the Data Warehouse structure.

    Examples of analysis included:

    Supply & Inventory Analysis
  • Total number of listings
  • Number of unique hosts
  • Property distribution by type

    • Geographical Insights
  • Listings by neighborhood
  • Distribution of properties across locations
  • High-demand areas based on bookings and reviews

    • Pricing Analysis
  • Average price per neighborhood
  • High-value listings and premium locations
  • Property types with the highest average prices

    • Demand & Booking Trends
  • Booking activity by property type
  • Availability analysis
  • Listings with low or no engagement

    • Review & Engagement Analysis
  • Most reviewed room types
  • Review activity by location
  • Listings without reviews

    • Advanced SQL Practice
  • Multi-table joins between Fact and Dimension tables
  • Aggregations and grouping
  • Subqueries and filtering
  • Data exploration using business-focused SQL logic

    • The Outcome:

    This project resulted in a fully functional Airbnb Data Warehouse designed for analytics, learning, and scalable reporting.

    The final solution successfully:

  • Transformed raw CSV files into a structured analytical database
  • Applied Data Warehouse principles using Fact and Dimension modeling
  • Improved data quality through validation, constraints, and deduplication
  • Created reliable relationships between business entities
  • Enabled analytical querying across multiple business areas
  • Built a strong foundation for future dashboards and BI reporting

    • The project demonstrates practical experience in:

  • SQL development
  • Data cleaning and transformation
  • Data modeling and Warehousing concepts
  • Fact & Dimension architecture
  • Relational database design
  • Business-oriented data analysis

    • Overall, this project combines core Data Engineering fundamentals with analytical thinking to create a scalable and business-ready Data Warehouse solution.

    Tools & Technologies

    • SQL Server Management Studio (SSMS)
    • SQL
      • Data Cleaning
      • Data Transformation
      • Data Modeling
      • Data Warehousing
      • Query Development
    • CSV data sources
    • Relational Database Design
    • Fact & Dimension Modeling
    • Bus Matrix Design