From Dirty Data to Business Insights: Building the Tembo Hotel Suites Database

Introduction

As part of my data analytics journey, I worked on a practical SQL project based on a fictional hotel called Tembo Hotel Suites.

The goal was not simply to write SQL queries. I wanted to take a messy hotel booking dataset, clean it, transform it into a properly structured relational database, and finally use SQL to answer business questions.

This project helped me understand how SQL fits into a real-world data analytics workflow — from raw data all the way to business insights.

---

The Problem

The original dataset contained hotel booking information such as:

• Guest details
• Phone numbers
• Room information
• Booking dates
• Staff information
• Salaries
• Payment methods
• Booking status
• Services used
• Guest ratings
• Revenue information

However, the data was so messy.

Some of the problems included:

• Inconsistent capitalization
• Extra spaces
• Different phone number formats
• Missing values
• Inconsistent room type names
• Different date formats
• Inconsistent payment methods
• Inconsistent booking statuses
• Numbers stored as text
• Inconsistent formatting of monetary values

This made the dataset unsuitable for reliable analysis without cleaning it first.

The original Tembo Hotel dataset contained inconsistent formats, missing values, and other data-quality issues.

---

My Approach

I divided the project into five main stages:

1. Database and staging setup
2. Data cleaning
3. Database design
4. Loading clean data
5. Business analysis

This structure also helped me keep my SQL project organized and easier to understand.

---

1. Database and Staging Setup

I first created a staging area where I could import the original dirty dataset without modifying the raw data directly.

The main staging table was:

The columns included information such as:

• booking_id
• guest_name
• guest_phone
• guest_city
• guest_nationality
• room_no
• room_type
• room_rate_per_night
• check_in_date
• check_out_date
• nights_stayed
• staff_name
• staff_department
• staff_salary
• payment_method
• booking_status
• total_amount
• service_used
• service_price
• guest_rating

Keeping the raw data in a staging table allowed me to inspect and clean the data before moving it into the final database structure.

---

2. Data Cleaning

This was one of the most important parts of the project.

I inspected each column to identify inconsistencies and potential problems.

For example, guest names could contain inconsistent spacing or capitalization.

Room types also contained inconsistent values, such as abbreviated versions that needed to be standardized.

Phone numbers appeared in different formats, including local and international formats.

Dates were another major challenge because the dataset contained different formats, including values such as:

• 01-12-2024
• 05/05/2024
• 06-09-24
• 2023-06-12

Before performing analysis, these values needed to be standardized.

I also cleaned fields such as:

• Payment methods
• Booking statuses
• Salaries
• Service prices
• Total amounts
• Guest ratings

The purpose of this stage was to make the data consistent enough for reliable analysis.

---

3. Database Design

After cleaning the data, I moved from one large staging table to a more structured relational database.

I created separate schemas for different areas of the business:

• hotel
• finance
• staffs
• services
• staging

I then created tables such as:

Guest

Stores information about hotel guests.

Room

Stores information about rooms and room types.

Booking

Stores booking information and connects guests, rooms and staff.

Payment

Stores payment-related information.

Staff

Stores information about hotel employees.

Service

Stores additional services offered by the hotel.

This approach reduced unnecessary duplication and made the database easier to maintain and query.

Relational structure connecting guests, bookings, rooms, staff, payments, and services.

---

4. Loading the Clean Data

Once the database structure was ready, I inserted the cleaned information into the appropriate tables.

I also performed verification queries such as:

• SELECT *
• COUNT(*)
• Checking relationships
• Checking whether records were successfully inserted

This allowed me to confirm that the transformation from the staging table into the final database had worked correctly.

---

5. Business Analysis

After the database was structured, I used SQL to answer business questions.

Some of the analysis included:

Revenue by Month

I analyzed how hotel revenue changed across different months.

This can help management identify strong and weak periods.

Room Performance

I looked at room types and their performance, including average guest ratings and booking activity.

Guest Locations

I analyzed the cities where guests were coming from.

This could help the hotel understand its customer base and potentially target marketing campaigns.

Staff Performance

I investigated which staff members handled the most bookings.

This provides a starting point for understanding workload distribution.

Department Revenue

I also explored revenue associated with different areas of the hotel.

Cancellation Rate

Cancellation rates were analyzed across room types to identify whether certain room categories experienced more cancellations.

Month-over-Month Revenue

I used SQL window functions such as LAG() to compare revenue between months.

This was particularly useful because it moved the project beyond basic aggregation into more advanced SQL analysis.

Using SQL window functions to compare monthly hotel revenue.

What I Learned

This project taught me that SQL is much more than writing SELECT statements.

I learned how important data preparation is before analysis.

Some of the biggest lessons were:

• Dirty data can produce misleading insights.

If dates, numbers or categories are inconsistent, the final analysis may be inaccurate.

• Staging tables are useful.

Keeping the original data separate from the cleaned data makes the workflow safer and easier to troubleshoot.

• Database design matters.

Breaking a large dataset into logical tables makes the database more organized and reduces unnecessary duplication.

• SQL can answer real business questions.

Queries become much more useful when they are connected to actual decisions a business needs to make.

• Data analytics is a process.

The workflow is not simply:

Data → Dashboard

It is more like:

Raw Data → Cleaning → Transformation → Database Design → Analysis → Insights

Tools I Used

For this project, I used:

• PostgreSQL — database management
• DBeaver — SQL development and database management
• SQL— data cleaning, transformation and analysis
• GitHub — project versioning and portfolio documentation
• Power BI — visualization and business intelligence

Project Structure

I organized the SQL project into separate files:

-README.md
-tembo_hotel_dirty.csv

-01_database_and_staging_setup.sql
-02_data_cleaning.sql
-03_database_design.sql
-04_loading_clean_data.sql
-05_business_analysis.sql

This structure makes it easier to follow the project from the original data through to the final analysis.

Final Thoughts

The Tembo Hotel Suites project was a valuable part of my data analytics learning journey.

Instead of working with a perfectly clean dataset, I had to deal with the kinds of problems that can appear in real-world data.

The project helped me practice:

• Data cleaning
• SQL transformations
• Relational database design
• Primary and foreign keys
• Data validation
• Aggregations
• Window functions
• Business analysis
• GitHub project documentation

Most importantly, I learned that good analysis starts with good data preparation.

Power BI dashboard used to turn the analyzed hotel data into interactive business insights.

There is still a lot more I want to learn, but projects like this are helping me move from simply learning SQL syntax to using SQL to solve practical problems.

Project Repository

The complete SQL project, including the original dirty dataset and all SQL scripts, is available on my GitHub.

GitHub: https://github.com/FEDDY218/TEMBO_HOTEL-SUITES-DATABASE.git

---

My next goal is to continue building more projects that combine SQL, Python, Power BI and data analytics to solve real-world problems.

One project at a time.

Comments

[Raknaos]

Staging table first is the right call and the step most tutorials skip. The date column is where I'd add one defensive move: 06-09-24 is ambiguous between June 9 and September 6, and no amount of cleaning resolves it — only the source or its documentation does. When I've hit that, I've had to add an explicit assumption column and a count of affected rows so the business analysis stays caveated instead of silently wrong.

On the relational side, a candidate-key test before you split guest/booking/payment is cheap and catches the duplication you only notice later: check whether (guest_id, check_in_date) is unique in the staging table, and if it isn't, decide before the design step whether that's a data defect or a legitimate multi-booking day.