Hotel Analytics ELT Pipeline on GCP

Build an end-to-end ELT pipeline processing 75k hotel listings on GCP.

Introduction

30 Second Summary

Every time you search for a hotel on Booking.com, a data pipeline processes millions of listings to surface the prices, rankings, and recommendations you see on screen. Data engineers build and maintain these pipelines at companies like Agoda, turning raw information into business-critical analytics.

In this project, you will build an ELT pipeline that extracts 75,000 real hotel listings, stages them in Google Cloud Storage, loads them into BigQuery, and transforms them into analytics-ready tables using advanced SQL.

What You'll Build

A complete cloud ELT pipeline on Google Cloud Platform that processes real Booking.com hotel data through extraction, cleaning, staging, loading, and SQL transformation stages.

By the end of this project, you'll have:

  • A Python extraction script that downloads, validates, and cleans 75,000 real hotel listings from a public dataset.
  • A Google Cloud Storage staging layer and a BigQuery data warehouse with raw and transformed analytics tables built using window functions and CTEs.
  • A public GitHub repository with professional documentation showcasing your pipeline engineering skills.
  • Secret Mission: Add a data quality validation layer that programmatically checks your pipeline output.

Are there any prerequisites?

You should be comfortable writing basic Python scripts and SQL queries. You also need a GitHub account and a Google Cloud Platform account with the $300 free trial credit available.

Set Up Your GCP Environment and Python Project

Before building the pipeline, you need the tools that connect your local machine to Google Cloud Platform.

In this step, you will install the Google Cloud CLI, create your Python project, and provision the two cloud resources your pipeline needs: a storage bucket for staging data and a BigQuery dataset for your data warehouse.

In this step, get ready to:
  • Install and configure the Google Cloud CLI.
  • Create the project directory and Python virtual environment.
  • Create the GCS staging bucket and BigQuery dataset.
Install the Google Cloud CLI on macOS

The Google Cloud CLI gives you command-line access to every GCP service. You will use it throughout this project to upload files, run queries, and manage resources.

  • Check if the Google Cloud CLI is already installed by running this command in your terminal:
gcloud version

✔️ I see a version number

The Google Cloud CLI is already installed. Skip ahead to the gcloud init step below.

ⓧ Command not found

You need to install the Google Cloud CLI. Follow the steps below.

  • Download the macOS ARM64 archive by running this command:
curl -O https://dl.google.com/dl/cloudsdk/channels/rapid/downloads/google-cloud-cli-darwin-arm.tar.gz
  • Extract the archive by running:
tar -xf google-cloud-cli-darwin-arm.tar.gz
  • Run the install script by running:
./google-cloud-sdk/install.sh

What does the install script do?

The install script adds the gcloud command to your shell PATH so you can run it from any terminal window. It will ask a few yes/no questions about updating your shell profile.

Accept the defaults by pressing Enter at each prompt.

  • Close and reopen your terminal so the PATH changes take effect.
  • Verify the installation by running:
gcloud version

You should see a version number printed in the output.

Still seeing command not found?

Make sure you closed and reopened your terminal after running the install script. The PATH update only applies to new terminal sessions.

If it still does not work, try running source ~/.zshrc to reload your shell configuration manually.

Help me troubleshoot my gcloud CLI installation on macOS.

  • Initialize the CLI and authenticate with your GCP account by running:
gcloud init

What happens during gcloud init?

This command opens your browser and asks you to sign in with your Google account. After signing in, it asks you to select an existing GCP project.

Select the project linked to your $300 free trial credit. If prompted about a default compute region, you can press Enter to skip it.

  • Verify that your project is set correctly by running:
gcloud config list project

You should see your project ID printed under [core].

Wrong project showing?

If the wrong project is listed, switch to the correct one by running gcloud config set project YOUR_PROJECT_ID, replacing the ID with your own.

Help me configure my gcloud project.

Create the Python project structure and virtual environment

Every pipeline project needs an isolated Python environment so its dependencies do not conflict with other projects on your machine.

  • Move to your Desktop by running this command:
cd ~/Desktop
  • Create your project folder and move into it by running these commands:
mkdir hotel-analytics-pipeline
cd hotel-analytics-pipeline
  • Confirm the folder exists by running:
ls ~/Desktop

You should see hotel-analytics-pipeline in the output.

  • Create a Python virtual environment by running:
python3 -m venv venv
  • Activate the virtual environment by running:
source venv/bin/activate

You should see (venv) appear at the start of your terminal prompt. This confirms the virtual environment is active.

What is a virtual environment?

A virtual environment is an isolated copy of Python for a single project. Packages you install inside it stay separate from your system Python, so you can use different versions of the same library across projects.

  • Install the four packages your pipeline needs by running:
pip install google-cloud-bigquery==3.42.1 pandas==2.2.3 pyarrow==17.0.0 db-dtypes==1.3.1

What are these packages?

  • google-cloud-bigquery is Google's official Python SDK for running queries and loading data into BigQuery.
  • pandas is the data manipulation library you will use to clean and transform the raw CSV.
  • pyarrow enables fast data serialization between pandas and BigQuery.
  • db-dtypes adds BigQuery-specific data types (like DATE and TIME) to pandas DataFrames.

After installation completes, you should see a Successfully installed message listing all four packages.

Seeing a permission error?

Make sure your virtual environment is active. You should see (venv) in your terminal prompt. If it is missing, run source venv/bin/activate again before retrying the install.

Help me fix my pip install error.

Create the GCS bucket and BigQuery dataset

Your pipeline needs two cloud resources: a Cloud Storage bucket to stage raw CSV files, and a BigQuery dataset to hold your tables. Both are created with single CLI commands.

  • Create the staging bucket in the US multi-region by running:
gsutil mb -l US gs://[[BUCKET_NAME="your-unique-bucket-name-hotel-pipeline"]]

Why does the bucket name need to be unique?

GCS bucket names are globally unique across all of Google Cloud. No two users can have the same bucket name. Include your name or a random string to avoid conflicts.

Bucket names can only contain lowercase letters, numbers, dashes, and underscores. They must be between 3 and 63 characters.

You should see a message like Creating gs://your-bucket-name/... confirming the bucket was created.

Seeing a 409 error?

The error 409 Bucket already exists means someone else has already claimed that name. Try a different bucket name with a more unique prefix.

Help me fix my bucket creation error.

  • Create the BigQuery dataset by running:
bq --location=US mk -d hotel_analytics

What does this command do?

  • bq mk -d creates a new BigQuery dataset.
  • --location=US stores the dataset in the US multi-region (matching your bucket location). This cannot be changed later.
  • hotel_analytics is the dataset name you will reference in all your SQL queries and Python scripts.

You should see a confirmation message that the dataset was created.

  • Verify both resources exist by running:
bq ls

You should see hotel_analytics listed as a dataset in the output.

Dataset not showing up?

Make sure your gcloud project is set to the correct project ID. Run gcloud config list project to check, and use gcloud config set project YOUR_PROJECT_ID to fix it.

Help me troubleshoot my BigQuery dataset creation.

Your cloud environment is fully configured. Next up, you will write the extraction script that downloads and cleans 75,000 real hotel listings.

Extract and Clean the Hotel Data

Your GCP environment is ready. Your Cloud Storage bucket and BigQuery dataset are standing by, waiting for data to flow through them.

In this step, you'll write a Python script that pulls 75,000 real hotel listings from a public dataset on Hugging Face. Raw data is messy. Columns have mixed types, some records are missing prices entirely, and not every field is relevant to your analysis. Your extraction script handles all of this.

In this step, get ready to:
  • Write an extraction script that downloads 75,000 hotel listings from Hugging Face.
  • Add cleaning logic that converts types, drops invalid rows, and logs quality statistics.
  • Run the pipeline and verify the cleaned output CSV.
Write the extraction script

The first half of your script handles downloading the raw CSV from Hugging Face and selecting only the columns your pipeline needs.

  • Create a new file called extract.py in your project folder by running:
touch extract.py
  • Open extract.py in Cursor and paste the following code:
import pandas as pd
import os

# URL pointing to the BrightData Booking.com hotel listings dataset on Hugging Face
DATASET_URL = "https://huggingface.co/datasets/BrightData/Booking.com-Listings/resolve/main/booking_listings.csv"
OUTPUT_DIR = "data"
OUTPUT_FILE = "hotel_listings_clean.csv"


def extract_and_clean():
    print("Downloading hotel listings dataset from Hugging Face...")
    df = pd.read_csv(DATASET_URL)
    print(f"Downloaded {len(df)} records with {len(df.columns)} columns")

    # Keep only the columns relevant to hotel analytics
    columns_to_keep = [
        "id",
        "title",
        "city",
        "location",
        "full_location",
        "star_rating",
        "review_score",
        "review_count",
        "final_price",
        "original_price",
        "currency",
        "free_cancellation_until",
        "hotel_rank",
        "nb_bedrooms",
        "nb_all_beds",
        "listing_country",
        "check_in",
        "check_out",
        "adults",
        "rooms",
    ]

    df_clean = df[columns_to_keep].copy()

What does this code do?

pd.read_csv(DATASET_URL) downloads the full 75,000-row CSV directly from Hugging Face into a pandas DataFrame. No special authentication is needed because this is a public dataset.

The columns_to_keep list narrows the original 36 columns down to 20 fields relevant to hotel pricing and review analysis. Fewer columns means less noise for your downstream transformations.

You won't see a change yet. The next chunk completes the function so you can run it.

Add data cleaning and validation logic

Raw scraped data often stores numbers as strings. Fields like star_rating and final_price need explicit type conversion before any math works on them. You also need to drop rows where the price is missing entirely, since those records are useless for pricing analysis.

  • In extract.py, add the following code directly below the df_clean = df[columns_to_keep].copy() line (still inside the extract_and_clean() function):
    # Convert columns that should be numeric but may contain string values
    df_clean["star_rating"] = pd.to_numeric(df_clean["star_rating"], errors="coerce")
    df_clean["review_score"] = pd.to_numeric(df_clean["review_score"], errors="coerce")
    df_clean["review_count"] = pd.to_numeric(df_clean["review_count"], errors="coerce")
    df_clean["final_price"] = pd.to_numeric(df_clean["final_price"], errors="coerce")
    df_clean["original_price"] = pd.to_numeric(
        df_clean["original_price"], errors="coerce"
    )
    df_clean["hotel_rank"] = pd.to_numeric(df_clean["hotel_rank"], errors="coerce")
    df_clean["nb_bedrooms"] = pd.to_numeric(df_clean["nb_bedrooms"], errors="coerce")
    df_clean["nb_all_beds"] = pd.to_numeric(df_clean["nb_all_beds"], errors="coerce")
    df_clean["adults"] = pd.to_numeric(df_clean["adults"], errors="coerce")
    df_clean["rooms"] = pd.to_numeric(df_clean["rooms"], errors="coerce")

    # Drop rows where price is missing (critical for analysis)
    initial_count = len(df_clean)
    df_clean = df_clean.dropna(subset=["final_price"])
    dropped = initial_count - len(df_clean)
    print(f"Dropped {dropped} rows with missing price")

    # Save the cleaned data to a CSV file
    os.makedirs(OUTPUT_DIR, exist_ok=True)
    output_path = os.path.join(OUTPUT_DIR, OUTPUT_FILE)
    df_clean.to_csv(output_path, index=False)
    print(f"Saved {len(df_clean)} cleaned records to {output_path}")

    # Print a summary of data quality for key fields
    print("\nExtraction Summary:")
    print(f"  Columns: {len(df_clean.columns)}")
    print(f"  Rows: {len(df_clean)}")
    print(f"  Null rates:")
    for col in ["star_rating", "review_score", "original_price"]:
        null_pct = round(df_clean[col].isna().mean() * 100, 1)
        print(f"    {col}: {null_pct}%")

    return output_path


if __name__ == "__main__":
    extract_and_clean()

What does this code do?

pd.to_numeric(..., errors="coerce") attempts to convert each value to a number. Values that cannot be parsed (like empty strings or text) become NaN instead of crashing the script.

dropna(subset=["final_price"]) removes any row where the price is null. A hotel listing without a price is useless for pricing analysis.

The extraction summary at the end logs null rates for three commonly-incomplete fields. This gives you a quick data quality snapshot every time the script runs.

  • Save extract.py.

✔️ Awesome, I've got everything!

Great. Double check you've saved your file before running it in the next substep.

ⓧ I'd like to double check the full code

Here is the complete extract.py file for comparison:

import pandas as pd
import os

DATASET_URL = "https://huggingface.co/datasets/BrightData/Booking.com-Listings/resolve/main/booking_listings.csv"
OUTPUT_DIR = "data"
OUTPUT_FILE = "hotel_listings_clean.csv"


def extract_and_clean():
    print("Downloading hotel listings dataset from Hugging Face...")
    df = pd.read_csv(DATASET_URL)
    print(f"Downloaded {len(df)} records with {len(df.columns)} columns")

    columns_to_keep = [
        "id",
        "title",
        "city",
        "location",
        "full_location",
        "star_rating",
        "review_score",
        "review_count",
        "final_price",
        "original_price",
        "currency",
        "free_cancellation_until",
        "hotel_rank",
        "nb_bedrooms",
        "nb_all_beds",
        "listing_country",
        "check_in",
        "check_out",
        "adults",
        "rooms",
    ]

    df_clean = df[columns_to_keep].copy()

    df_clean["star_rating"] = pd.to_numeric(df_clean["star_rating"], errors="coerce")
    df_clean["review_score"] = pd.to_numeric(df_clean["review_score"], errors="coerce")
    df_clean["review_count"] = pd.to_numeric(df_clean["review_count"], errors="coerce")
    df_clean["final_price"] = pd.to_numeric(df_clean["final_price"], errors="coerce")
    df_clean["original_price"] = pd.to_numeric(
        df_clean["original_price"], errors="coerce"
    )
    df_clean["hotel_rank"] = pd.to_numeric(df_clean["hotel_rank"], errors="coerce")
    df_clean["nb_bedrooms"] = pd.to_numeric(df_clean["nb_bedrooms"], errors="coerce")
    df_clean["nb_all_beds"] = pd.to_numeric(df_clean["nb_all_beds"], errors="coerce")
    df_clean["adults"] = pd.to_numeric(df_clean["adults"], errors="coerce")
    df_clean["rooms"] = pd.to_numeric(df_clean["rooms"], errors="coerce")

    initial_count = len(df_clean)
    df_clean = df_clean.dropna(subset=["final_price"])
    dropped = initial_count - len(df_clean)
    print(f"Dropped {dropped} rows with missing price")

    os.makedirs(OUTPUT_DIR, exist_ok=True)
    output_path = os.path.join(OUTPUT_DIR, OUTPUT_FILE)
    df_clean.to_csv(output_path, index=False)
    print(f"Saved {len(df_clean)} cleaned records to {output_path}")

    print("\nExtraction Summary:")
    print(f"  Columns: {len(df_clean.columns)}")
    print(f"  Rows: {len(df_clean)}")
    print(f"  Null rates:")
    for col in ["star_rating", "review_score", "original_price"]:
        null_pct = round(df_clean[col].isna().mean() * 100, 1)
        print(f"    {col}: {null_pct}%")

    return output_path


if __name__ == "__main__":
    extract_and_clean()
Run the extraction script and verify the output

Your script is complete. Time to run it and confirm the cleaned CSV appears with the expected record count.

  • Run the extraction script by executing this command in your terminal:
python extract.py

What should I see?

The script downloads a 62 MB CSV from Hugging Face, so expect a 30-60 second wait depending on your internet speed. You should see output similar to:

Downloading hotel listings dataset from Hugging Face...
Downloaded 75000 records with 36 columns
Dropped X rows with missing price
Saved XXXXX cleaned records to data/hotel_listings_clean.csv

Extraction Summary:
  Columns: 20
  Rows: XXXXX
  Null rates:
    star_rating: X.X%
    review_score: X.X%
    original_price: X.X%

The exact numbers depend on the current state of the dataset, but you should see around 75,000 downloaded records and a slightly lower cleaned count after dropping rows with missing prices.

  • Verify the output file was created by running:
ls data/
  • You should see hotel_listings_clean.csv in the output.

Script hanging or failing to download?

The CSV is about 62 MB. If your connection is slow, the download may take a few minutes. Let it run.

If you see a ConnectionError or timeout, check your internet connection and try again. The Hugging Face URL must be reachable from your machine.

If you see ModuleNotFoundError: No module named 'pandas', make sure your virtual environment is activated. Run source venv/bin/activate and try again.

help me debug my extract.py script

Why use a public dataset instead of a live API?

Travel APIs like the Booking.com Demand API are partner-only, and alternatives have strict rate limits (50 free requests for some providers). The BrightData dataset on Hugging Face gives you 75,000 real Booking.com records for free, with all the messy real-world quality issues (nulls, mixed types, missing values) that production pipelines deal with daily.

Your extraction pipeline is working. Next, you'll load this cleaned CSV into Cloud Storage and BigQuery to make it queryable at scale.

Load Data into Cloud Storage and BigQuery

Your extraction script produced a clean CSV with tens of thousands of hotel records. The next job is to get that data into the cloud where BigQuery can query it at scale.

In this step, you will upload the cleaned CSV to your Cloud Storage bucket (the staging layer) and then load it into a BigQuery table. This is the "L" in ELT.

In this step, get ready to:
  • Write a Python load script that stages data in GCS and loads it into BigQuery.
  • Run the script and verify the data landed correctly in the BigQuery console.
Write the load script

The load script handles two jobs: uploading the local CSV to your GCS bucket using gsutil cp, and then telling BigQuery to ingest the file from that GCS location.

  • In Cursor, create a new file called load.py in your project folder (at the same level as extract.py).

Start by adding the imports and configuration constants at the top of the file.

  • Add the following imports and config to load.py:
import subprocess
import sys
from google.cloud import bigquery

PROJECT_ID = "your-gcp-project-id"
DATASET_ID = "hotel_analytics"
TABLE_ID = "raw_listings"
BUCKET_NAME = "your-bucket-name-hotel-pipeline"
LOCAL_FILE = "data/hotel_listings_clean.csv"
GCS_URI = f"gs://{BUCKET_NAME}/hotel_listings_clean.csv"

What does this code do?

The imports pull in subprocess (to call gsutil from the command line), sys (for exit codes), and the bigquery client from the Google Cloud SDK.

The constants define where data flows: from LOCAL_FILE on your machine, to GCS_URI in the cloud, and finally into the BigQuery table at PROJECT_ID.DATASET_ID.TABLE_ID.

  • Replace your-gcp-project-id with your actual GCP project ID (the one you selected during gcloud init).
  • Replace your-bucket-name-hotel-pipeline with the bucket name you created earlier.

Find your project ID quickly

Run gcloud config get-value project in your terminal to see the project ID currently set in your CLI.

Next, add the function that uploads your local CSV to Cloud Storage.

  • Add the upload_to_gcs function below the constants in load.py:

def upload_to_gcs():
    print(f"Uploading {LOCAL_FILE} to {GCS_URI}...")
    result = subprocess.run(
        ["gsutil", "cp", LOCAL_FILE, GCS_URI],
        capture_output=True,
        text=True,
    )
    if result.returncode != 0:
        print(f"Upload failed: {result.stderr}")
        sys.exit(1)
    print("Upload complete")

What does this code do?

This function shells out to gsutil cp to copy the cleaned CSV from your local machine to the GCS bucket. Using subprocess.run lets you call gsutil directly without needing an extra Python package for GCS uploads.

If the upload fails (non-zero return code), the script prints the error and exits immediately. No point loading into BigQuery if the file never made it to the staging layer.

Now add the function that ensures the BigQuery dataset is ready to receive data.

  • Add the create_dataset function below upload_to_gcs in load.py:

def create_dataset():
    client = bigquery.Client(project=PROJECT_ID)
    dataset_ref = bigquery.Dataset(f"{PROJECT_ID}.{DATASET_ID}")
    dataset_ref.location = "US"
    client.create_dataset(dataset_ref, exists_ok=True)
    print(f"Dataset {DATASET_ID} ready")

What does this code do?

This creates the hotel_analytics dataset in BigQuery if it does not already exist. The exists_ok=True flag makes the call idempotent. You already created this dataset with bq mk in Step 1, so this is a safety net that lets you rerun the script without errors.

Finally, add the function that loads data from GCS into BigQuery, plus the main execution block.

  • Add the load_to_bigquery function and the __main__ block at the bottom of load.py:

def load_to_bigquery():
    client = bigquery.Client(project=PROJECT_ID)
    table_ref = f"{PROJECT_ID}.{DATASET_ID}.{TABLE_ID}"

    job_config = bigquery.LoadJobConfig(
        source_format=bigquery.SourceFormat.CSV,
        skip_leading_rows=1,
        autodetect=True,
        write_disposition=bigquery.WriteDisposition.WRITE_TRUNCATE,
    )

    print(f"Loading data from {GCS_URI} into {table_ref}...")
    load_job = client.load_table_from_uri(GCS_URI, table_ref, job_config=job_config)
    load_job.result()

    table = client.get_table(table_ref)
    print(f"Loaded {table.num_rows} rows into {table_ref}")


if __name__ == "__main__":
    upload_to_gcs()
    create_dataset()
    load_to_bigquery()

What does this code do?

The LoadJobConfig tells BigQuery how to interpret the file: it is a CSV, the first row is a header (skip_leading_rows=1), and BigQuery should infer the schema automatically (autodetect=True).

The WRITE_TRUNCATE disposition means "overwrite the table if it already exists." This lets you rerun the pipeline without duplicating data.

The __main__ block runs all three steps in sequence: upload to GCS, ensure the dataset exists, then load into BigQuery.

  • Save load.py.

✔️ Awesome, I've got everything!

Great. Double check you have saved load.py before running it in the next substep.

ⓧ I'd like to double check the full code

Here is the complete load.py file for comparison:

import subprocess
import sys
from google.cloud import bigquery

PROJECT_ID = "your-gcp-project-id"
DATASET_ID = "hotel_analytics"
TABLE_ID = "raw_listings"
BUCKET_NAME = "your-bucket-name-hotel-pipeline"
LOCAL_FILE = "data/hotel_listings_clean.csv"
GCS_URI = f"gs://{BUCKET_NAME}/hotel_listings_clean.csv"


def upload_to_gcs():
    print(f"Uploading {LOCAL_FILE} to {GCS_URI}...")
    result = subprocess.run(
        ["gsutil", "cp", LOCAL_FILE, GCS_URI],
        capture_output=True,
        text=True,
    )
    if result.returncode != 0:
        print(f"Upload failed: {result.stderr}")
        sys.exit(1)
    print("Upload complete")


def create_dataset():
    client = bigquery.Client(project=PROJECT_ID)
    dataset_ref = bigquery.Dataset(f"{PROJECT_ID}.{DATASET_ID}")
    dataset_ref.location = "US"
    client.create_dataset(dataset_ref, exists_ok=True)
    print(f"Dataset {DATASET_ID} ready")


def load_to_bigquery():
    client = bigquery.Client(project=PROJECT_ID)
    table_ref = f"{PROJECT_ID}.{DATASET_ID}.{TABLE_ID}"

    job_config = bigquery.LoadJobConfig(
        source_format=bigquery.SourceFormat.CSV,
        skip_leading_rows=1,
        autodetect=True,
        write_disposition=bigquery.WriteDisposition.WRITE_TRUNCATE,
    )

    print(f"Loading data from {GCS_URI} into {table_ref}...")
    load_job = client.load_table_from_uri(GCS_URI, table_ref, job_config=job_config)
    load_job.result()

    table = client.get_table(table_ref)
    print(f"Loaded {table.num_rows} rows into {table_ref}")


if __name__ == "__main__":
    upload_to_gcs()
    create_dataset()
    load_to_bigquery()
Run the load script and verify in BigQuery

With the script saved and your project ID and bucket name updated, it is time to stage the data in the cloud and load it into BigQuery.

  • Run the load script by executing this command in your terminal:
python load.py
  • Wait for the script to finish. You should see output similar to:
Uploading data/hotel_listings_clean.csv to gs://your-bucket-name-hotel-pipeline/hotel_listings_clean.csv...
Upload complete
Dataset hotel_analytics ready
Loading data from gs://your-bucket-name-hotel-pipeline/hotel_listings_clean.csv into your-gcp-project-id.hotel_analytics.raw_listings...
Loaded 74925 rows into your-gcp-project-id.hotel_analytics.raw_listings

What should I see?

The exact row count depends on the dataset at the time of download. Expect a number around 74,000 to 75,000. The key confirmation is that the final line shows rows loaded without errors.

Upload failed or permission denied?

If you see a permissions error, confirm your gcloud CLI is authenticated by running gcloud auth list. Make sure the active account has Storage Admin and BigQuery Data Editor roles on your project.

If the script cannot find gsutil, restart your terminal so the PATH changes from the Google Cloud CLI install take effect.

help me debug why my load.py script is failing to upload to GCS or load into BigQuery

Now verify the data landed in BigQuery by querying it directly in the console.

  • Open the BigQuery console at console.cloud.google.com/bigquery in your browser.
  • In the left pane, click Explorer.
  • Expand your project, then expand the hotel_analytics dataset.
  • Click the raw_listings table.
  • Click the Preview tab to see sample rows.

You should see hotel listing data with columns like id, title, city, final_price, and review_score.

  • Run a quick count query to confirm the row total. Click SQL query in the BigQuery console, paste this query, and click Run:
SELECT COUNT(*) as total_rows FROM `hotel_analytics.raw_listings`

The result should match the row count printed by your script.

Don't see the raw_listings table?

Click the refresh icon next to your project name in the Explorer pane. BigQuery sometimes caches the navigation tree and new tables take a moment to appear.

If the table still does not appear, double-check that PROJECT_ID in load.py exactly matches the project shown in the console header bar.

help me figure out why my raw_listings table is not showing in BigQuery after running load.py

Your data is now staged in Cloud Storage and loaded into BigQuery as a raw table. Next up, you will write SQL transformations that turn this raw data into analytics-ready tables using window functions and CTEs.

Transform Data with SQL in BigQuery

Your raw hotel data is sitting in BigQuery, but raw data alone does not answer business questions. The "T" in ELT happens now. You will write SQL that transforms 75,000 individual listings into aggregated, analytics-ready tables.

In this step, you will write two SQL transformations that use window functions and CTEs to produce city-level market summaries and listing-level pricing analysis. Then you will build a Python runner script to execute those transformations against BigQuery.

In this step, get ready to:
  • Write SQL transformations that create two analytics tables using window functions and CTEs.
  • Build a Python runner script that executes SQL against BigQuery.
  • Run the transformations and verify both analytics tables contain results.
Write the SQL transformation queries

BigQuery lets you run SQL transformations directly inside the warehouse. Instead of pulling data out, transforming it in Python, and pushing it back, you write SQL that creates new tables from existing ones.

Why transform inside BigQuery?

Google Cloud recommends the ELT approach for BigQuery customers. Transforming inside the warehouse fully utilizes BigQuery's distributed compute engine.

This is the same pattern companies like Agoda use. They run SparkSQL for core transformations in their warehouse rather than moving data out for processing.

  • Create a new file called transform.sql in your project folder.
  • Add the first transformation that builds a market summary table:
CREATE OR REPLACE TABLE `hotel_analytics.market_summary` AS
WITH city_stats AS (
    SELECT
        listing_country,
        city,
        COUNT(*) AS total_listings,
        ROUND(AVG(final_price), 2) AS avg_price,
        ROUND(AVG(review_score), 2) AS avg_review_score,
        ROUND(AVG(star_rating), 1) AS avg_star_rating,
        MIN(final_price) AS min_price,
        MAX(final_price) AS max_price,
        COUNTIF(original_price > final_price) AS discounted_count
    FROM `hotel_analytics.raw_listings`
    WHERE final_price > 0
    GROUP BY listing_country, city
    HAVING COUNT(*) >= 5
)
SELECT
    listing_country,
    city,
    total_listings,
    avg_price,
    avg_review_score,
    avg_star_rating,
    min_price,
    max_price,
    discounted_count,
    RANK() OVER (
        PARTITION BY listing_country ORDER BY total_listings DESC
    ) AS city_rank_by_listings,
    ROUND(
        avg_price - AVG(avg_price) OVER (PARTITION BY listing_country), 2
    ) AS price_diff_from_country_avg
FROM city_stats
ORDER BY listing_country, city_rank_by_listings;

What does this SQL do?

This query uses a CTE (Common Table Expression) called city_stats to first aggregate hotel metrics by city. It calculates average price, review score, star rating, and counts how many listings have discounts.

The outer SELECT then applies window functions on top of those city-level aggregations. RANK() OVER (PARTITION BY listing_country) ranks each city by listing count within its country. AVG() OVER (PARTITION BY listing_country) calculates the country-wide average price so each city can show how it compares.

You won't see a change yet. The next chunk completes the SQL file with the second transformation.

  • Below the first statement in transform.sql, add the pricing analysis transformation:

CREATE OR REPLACE TABLE `hotel_analytics.pricing_analysis` AS
SELECT
    id,
    title,
    city,
    listing_country,
    star_rating,
    final_price,
    original_price,
    ROUND(
        (original_price - final_price) / NULLIF(original_price, 0) * 100, 1
    ) AS discount_pct,
    review_score,
    review_count,
    ROUND(
        PERCENT_RANK() OVER (
            PARTITION BY listing_country ORDER BY final_price
        ), 4
    ) AS price_percentile_in_country,
    RANK() OVER (
        PARTITION BY city ORDER BY review_score DESC
    ) AS review_rank_in_city,
    ROUND(
        AVG(final_price) OVER (PARTITION BY city), 2
    ) AS city_avg_price,
    ROUND(
        final_price - AVG(final_price) OVER (PARTITION BY city), 2
    ) AS price_vs_city_avg
FROM `hotel_analytics.raw_listings`
WHERE final_price > 0 AND review_score IS NOT NULL

What does this transformation calculate?

This query works at the individual listing level. It calculates each hotel's price positioning within its country and city.

  • PERCENT_RANK() shows where a hotel's price falls on a 0-to-1 scale within its country. A value of 0.75 means the hotel is more expensive than 75% of listings in that country.
  • RANK() orders hotels by review score within each city, so you can find the top-rated hotel in any city.
  • AVG() OVER (PARTITION BY city) calculates each city's average price and compares every listing against it.
  • Save transform.sql.
  • Confirm the file exists by running:
ls transform.sql
  • You should see transform.sql listed in the output.

File not showing up?

Make sure you saved the file in your project folder (the same directory where extract.py and load.py live). Check your terminal is in the correct directory with pwd.

Help me find where my transform.sql file was saved.

✔️ Awesome, I've got everything!

Great. Double check you have saved the file before moving on.

ⓧ I'd like to double check the full code

CREATE OR REPLACE TABLE `hotel_analytics.market_summary` AS
WITH city_stats AS (
    SELECT
        listing_country,
        city,
        COUNT(*) AS total_listings,
        ROUND(AVG(final_price), 2) AS avg_price,
        ROUND(AVG(review_score), 2) AS avg_review_score,
        ROUND(AVG(star_rating), 1) AS avg_star_rating,
        MIN(final_price) AS min_price,
        MAX(final_price) AS max_price,
        COUNTIF(original_price > final_price) AS discounted_count
    FROM `hotel_analytics.raw_listings`
    WHERE final_price > 0
    GROUP BY listing_country, city
    HAVING COUNT(*) >= 5
)
SELECT
    listing_country,
    city,
    total_listings,
    avg_price,
    avg_review_score,
    avg_star_rating,
    min_price,
    max_price,
    discounted_count,
    RANK() OVER (
        PARTITION BY listing_country ORDER BY total_listings DESC
    ) AS city_rank_by_listings,
    ROUND(
        avg_price - AVG(avg_price) OVER (PARTITION BY listing_country), 2
    ) AS price_diff_from_country_avg
FROM city_stats
ORDER BY listing_country, city_rank_by_listings;

CREATE OR REPLACE TABLE `hotel_analytics.pricing_analysis` AS
SELECT
    id,
    title,
    city,
    listing_country,
    star_rating,
    final_price,
    original_price,
    ROUND(
        (original_price - final_price) / NULLIF(original_price, 0) * 100, 1
    ) AS discount_pct,
    review_score,
    review_count,
    ROUND(
        PERCENT_RANK() OVER (
            PARTITION BY listing_country ORDER BY final_price
        ), 4
    ) AS price_percentile_in_country,
    RANK() OVER (
        PARTITION BY city ORDER BY review_score DESC
    ) AS review_rank_in_city,
    ROUND(
        AVG(final_price) OVER (PARTITION BY city), 2
    ) AS city_avg_price,
    ROUND(
        final_price - AVG(final_price) OVER (PARTITION BY city), 2
    ) AS price_vs_city_avg
FROM `hotel_analytics.raw_listings`
WHERE final_price > 0 AND review_score IS NOT NULL
Write the transformation runner script

SQL files do not execute themselves. You need a Python script that reads the SQL, splits it into individual statements, and sends each one to BigQuery for execution.

  • Create a new file called run_transforms.py and add the following code:
from google.cloud import bigquery

PROJECT_ID = "your-gcp-project-id"
DATASET_ID = "hotel_analytics"


def run_transforms():
    client = bigquery.Client(project=PROJECT_ID)

    with open("transform.sql", "r") as f:
        sql_content = f.read()

    statements = [s.strip() for s in sql_content.split(";") if s.strip()]

    for i, statement in enumerate(statements, 1):
        print(f"Running transformation {i}/{len(statements)}...")
        query_job = client.query(statement)
        query_job.result()
        print(f"  Completed successfully")

    print("\nVerifying results:")
    for table_name in ["market_summary", "pricing_analysis"]:
        table = client.get_table(f"{PROJECT_ID}.{DATASET_ID}.{table_name}")
        print(f"  {table_name}: {table.num_rows} rows")

    print("\nSample query - Top 5 most expensive cities:")
    sample_query = f"""
        SELECT city, listing_country, avg_price, total_listings
        FROM `{DATASET_ID}.market_summary`
        ORDER BY avg_price DESC
        LIMIT 5
    """
    results = client.query(sample_query).result()
    for row in results:
        print(f"  {row.city}, {row.listing_country}: ${row.avg_price} ({row.total_listings} listings)")


if __name__ == "__main__":
    run_transforms()

What does this script do?

  • It reads the entire transform.sql file and splits it on semicolons to get individual SQL statements.
  • It loops through each statement and submits it to BigQuery as a query job, waiting for completion before moving to the next.
  • After all transformations finish, it verifies by checking row counts in both new tables.
  • It runs a sample query against market_summary to show you real results immediately.
  • Replace your-gcp-project-id with your actual GCP project ID (the same one you used in load.py).
  • Save run_transforms.py.

Not sure what your project ID is?

Run gcloud config get-value project in your terminal to see your currently active project ID. It should match what you set during gcloud init.

Help me find my GCP project ID.

✔️ Awesome, I've got everything!

Great. Make sure you have replaced the project ID placeholder before moving on.

ⓧ I'd like to double check the full code

from google.cloud import bigquery

PROJECT_ID = "your-gcp-project-id"
DATASET_ID = "hotel_analytics"


def run_transforms():
    client = bigquery.Client(project=PROJECT_ID)

    with open("transform.sql", "r") as f:
        sql_content = f.read()

    statements = [s.strip() for s in sql_content.split(";") if s.strip()]

    for i, statement in enumerate(statements, 1):
        print(f"Running transformation {i}/{len(statements)}...")
        query_job = client.query(statement)
        query_job.result()
        print(f"  Completed successfully")

    print("\nVerifying results:")
    for table_name in ["market_summary", "pricing_analysis"]:
        table = client.get_table(f"{PROJECT_ID}.{DATASET_ID}.{table_name}")
        print(f"  {table_name}: {table.num_rows} rows")

    print("\nSample query - Top 5 most expensive cities:")
    sample_query = f"""
        SELECT city, listing_country, avg_price, total_listings
        FROM `{DATASET_ID}.market_summary`
        ORDER BY avg_price DESC
        LIMIT 5
    """
    results = client.query(sample_query).result()
    for row in results:
        print(f"  {row.city}, {row.listing_country}: ${row.avg_price} ({row.total_listings} listings)")


if __name__ == "__main__":
    run_transforms()
Run the transformations and verify results

Both files are ready. Time to execute the transformations and confirm that BigQuery created your analytics tables.

  • Run the transformation script by executing this command:
python run_transforms.py

What should I see?

The script prints progress as it executes each SQL statement. You should see output similar to:

  • Running transformation 1/2... followed by Completed successfully.
  • Running transformation 2/2... followed by Completed successfully.
  • Row counts for both market_summary and pricing_analysis tables (both should have rows).
  • A sample of the top 5 most expensive cities with their average prices.
  • Confirm both tables show a non-zero row count in the Verifying results section of the output.
  • Confirm the sample query shows 5 cities with their average prices and listing counts.

Seeing a SQL syntax error?

The most common issue is a missing or extra comma in the SELECT clause. Open transform.sql and check that every column in the SELECT list (except the last one) has a trailing comma.

Also check that backticks surround your dataset and table references (e.g., `hotel_analytics.raw_listings`). Standard quotes will not work for BigQuery table references.

Help me debug my BigQuery SQL transformation error.

Seeing a 'Not found: Table' error?

This means the raw_listings table does not exist in your hotel_analytics dataset. Go back to the previous step and re-run python load.py to load the data into BigQuery first.

Also confirm that your PROJECT_ID in run_transforms.py matches the project where you loaded your data.

Your ELT pipeline is complete. Raw data flows from Hugging Face through Cloud Storage into BigQuery, and SQL transformations turn it into analytics-ready tables. Next up, you will publish this entire pipeline to GitHub as a portfolio piece.

Publish to GitHub

Your ELT pipeline is fully operational. Extraction, staging, loading, and transformation all work against real hotel data in BigQuery.

The final step is packaging this project for GitHub as a portfolio piece. A clean repository with a professional README signals engineering discipline to interviewers reviewing your work.

In this step, get ready to:
  • Create .gitignore and requirements.txt for a clean, reproducible project.
  • Write a README.md documenting the pipeline architecture and SQL transformations.
  • Initialize a Git repository, commit your code, and push to a public GitHub repo.
Create the .gitignore file

Some files in your project directory should never be committed to version control. Raw data files are large and regenerable. The virtual environment is machine-specific. Keeping them out of the repo keeps it lean and professional.

  • In Cursor, create a new file called .gitignore in the root of your project folder (the same level as extract.py).
  • Paste the following content into .gitignore:
data/
venv/
__pycache__/
*.pyc
.env

What does this file do?

  • data/ excludes the raw CSV files. Anyone cloning the repo regenerates them by running extract.py.
  • venv/ excludes the virtual environment. It's machine-specific and rebuilt from requirements.txt.
  • __pycache__/ and *.pyc exclude compiled Python bytecode files.
  • .env excludes any environment variable files containing secrets.
  • Save .gitignore.
Create the requirements.txt file

A pinned requirements file ensures anyone who clones the repo installs the exact same package versions you used. This prevents subtle version-mismatch bugs.

  • Create a new file called requirements.txt in the root of your project folder.
  • Paste the following pinned dependencies:
google-cloud-bigquery==3.42.1
pandas==2.2.3
pyarrow==17.0.0
db-dtypes==1.3.1

Why pin exact versions?

The == syntax locks each package to a specific release. When someone runs pip install -r requirements.txt six months from now, they get the exact same versions you tested against. This is standard practice in production data pipelines.

  • Save requirements.txt.
Write the README.md

The README is the first thing an interviewer sees when they open your repository. It needs to communicate the pipeline's architecture, the tools you used, and how to reproduce the results.

  • Create a new file called README.md in the root of your project folder.
  • Paste the following documentation:
# Hotel Analytics ELT Pipeline

An end-to-end ELT (Extract, Load, Transform) pipeline that processes 75,000 real hotel listings from Booking.com through Google Cloud Platform, producing analytics-ready tables in BigQuery.

## Architecture

```
Source (Hugging Face CSV)
        |
        v
[Extract + Clean] -- Python/pandas
        |
        v
[Stage] -- Google Cloud Storage
        |
        v
[Load] -- BigQuery (raw table)
        |
        v
[Transform] -- BigQuery SQL (analytics tables)
        |
        v
[Validate] -- Python quality checks
```

## Tech Stack

- **Language**: Python 3.10+
- **Cloud**: Google Cloud Platform (Cloud Storage, BigQuery)
- **Libraries**: google-cloud-bigquery, pandas, pyarrow
- **Data**: 75,000 Booking.com hotel listings (BrightData dataset)

## Pipeline Scripts

| Script | Purpose |
|--------|---------|
| `extract.py` | Downloads raw data, selects columns, cleans types, removes invalid rows |
| `load.py` | Uploads to GCS, creates BigQuery dataset, loads via `load_table_from_uri` |
| `transform.sql` | SQL transformations with CTEs, window functions (RANK, PERCENT_RANK, AVG OVER) |
| `run_transforms.py` | Executes SQL transformations against BigQuery |
| `validate.py` | Runs 5 data quality checks with PASS/FAIL reporting |

## SQL Transformations

The pipeline produces two analytics tables:

**market_summary** - City-level aggregations with:
- Average price, review score, star rating per city
- RANK() for city popularity within each country
- Price difference from country average using AVG() OVER

**pricing_analysis** - Listing-level metrics with:
- PERCENT_RANK() for price positioning within country
- RANK() for review ranking within city
- Discount percentage calculations
- City average comparisons using window functions

## How to Run

1. Install Google Cloud CLI and authenticate:
   ```bash
   gcloud init
   ```

2. Install dependencies:
   ```bash
   python -m venv venv
   source venv/bin/activate
   pip install -r requirements.txt
   ```

3. Update `PROJECT_ID` and `BUCKET_NAME` in `load.py` and `run_transforms.py`

4. Run the pipeline:
   ```bash
   python extract.py
   python load.py
   python run_transforms.py
   python validate.py
   ```

## Data Quality Checks

The `validate.py` script verifies:
- Row count exceeds threshold (50,000+)
- No negative prices in raw data
- Review scores within valid range (0-10)
- Critical field null rates below 5%
- Transformed tables contain output rows

Exit code 1 on any failure (production-style behavior).

What makes this README effective?

The architecture diagram gives reviewers an instant visual of the data flow. The scripts table shows what each file does without requiring them to open the code.

The SQL Transformations section explicitly names the window functions (RANK, PERCENT_RANK, AVG OVER). These are the exact functions tested in data engineering interviews.

  • Save README.md.

✔️ Awesome, I've got everything!

Great. Double check that you have saved all three new files: .gitignore, requirements.txt, and README.md.

ⓧ I'd like to double check the full code

Here is each file in full for comparison.

data/
venv/
__pycache__/
*.pyc
.env
google-cloud-bigquery==3.42.1
pandas==2.2.3
pyarrow==17.0.0
db-dtypes==1.3.1
# Hotel Analytics ELT Pipeline

An end-to-end ELT (Extract, Load, Transform) pipeline that processes 75,000 real hotel listings from Booking.com through Google Cloud Platform, producing analytics-ready tables in BigQuery.

## Architecture

```
Source (Hugging Face CSV)
        |
        v
[Extract + Clean] -- Python/pandas
        |
        v
[Stage] -- Google Cloud Storage
        |
        v
[Load] -- BigQuery (raw table)
        |
        v
[Transform] -- BigQuery SQL (analytics tables)
        |
        v
[Validate] -- Python quality checks
```

## Tech Stack

- **Language**: Python 3.10+
- **Cloud**: Google Cloud Platform (Cloud Storage, BigQuery)
- **Libraries**: google-cloud-bigquery, pandas, pyarrow
- **Data**: 75,000 Booking.com hotel listings (BrightData dataset)

## Pipeline Scripts

| Script | Purpose |
|--------|---------|
| `extract.py` | Downloads raw data, selects columns, cleans types, removes invalid rows |
| `load.py` | Uploads to GCS, creates BigQuery dataset, loads via `load_table_from_uri` |
| `transform.sql` | SQL transformations with CTEs, window functions (RANK, PERCENT_RANK, AVG OVER) |
| `run_transforms.py` | Executes SQL transformations against BigQuery |
| `validate.py` | Runs 5 data quality checks with PASS/FAIL reporting |

## SQL Transformations

The pipeline produces two analytics tables:

**market_summary** - City-level aggregations with:
- Average price, review score, star rating per city
- RANK() for city popularity within each country
- Price difference from country average using AVG() OVER

**pricing_analysis** - Listing-level metrics with:
- PERCENT_RANK() for price positioning within country
- RANK() for review ranking within city
- Discount percentage calculations
- City average comparisons using window functions

## How to Run

1. Install Google Cloud CLI and authenticate:
   ```bash
   gcloud init
   ```

2. Install dependencies:
   ```bash
   python -m venv venv
   source venv/bin/activate
   pip install -r requirements.txt
   ```

3. Update `PROJECT_ID` and `BUCKET_NAME` in `load.py` and `run_transforms.py`

4. Run the pipeline:
   ```bash
   python extract.py
   python load.py
   python run_transforms.py
   python validate.py
   ```

## Data Quality Checks

The `validate.py` script verifies:
- Row count exceeds threshold (50,000+)
- No negative prices in raw data
- Review scores within valid range (0-10)
- Critical field null rates below 5%
- Transformed tables contain output rows

Exit code 1 on any failure (production-style behavior).
Initialize git and commit your code

With all project files ready, you can initialize a Git repository and make your first commit. Git tracks every change you make, and the initial commit captures your complete pipeline in one snapshot.

  • Switch back to your terminal (with the virtual environment still active).
  • Initialize a git repository and commit all your files by running these commands:
git init
git add .
git commit -m "Initial commit: hotel analytics ELT pipeline"

What do these commands do?

  • git init creates a new Git repository in your current folder.
  • git add . stages all files (except those listed in .gitignore) for the commit.
  • git commit -m "..." saves the staged files as your first commit with a descriptive message.
  • You should see output listing the files being committed. Confirm that data/ and venv/ are NOT in the list.

Seeing data/ or venv/ in the commit?

If those directories appear in the committed files, your .gitignore may not be saved in the correct location. It must be in the same folder as extract.py (the project root), not inside a subfolder.

Help me fix files that were committed but should be in .gitignore.

Create a GitHub repository and push your code

Now you need a remote repository on GitHub to host your code publicly. You will create an empty repo on GitHub, then connect your local repository to it.

  • Open github.com in your browser.
  • In the upper-right corner, click the + icon, then click New repository.
  • Enter hotel-analytics-elt-pipeline as the repository name.
  • Set the visibility to Public.
  • Do NOT check any of the initialization options (no README, no .gitignore, no license). You already have these files locally.
  • Click Create repository.

Why skip the initialization options?

GitHub's docs recommend not initializing with README, license, or .gitignore when you are pushing an existing local repository. Adding them creates a conflict between your local history and GitHub's initial commit.

GitHub shows a Quick Setup page with your repository URL. Copy the HTTPS URL displayed at the top of the page.

  • Back in your terminal, connect your local repo to GitHub and push by running these commands:
git branch -M main
git remote add origin [[REPO_URL="https://github.com/YOUR_USERNAME/hotel-analytics-elt-pipeline.git"]]
git push -u origin main

What do these commands do?

  • git branch -M main renames your default branch to main (GitHub's default branch name).
  • git remote add origin connects your local repo to the GitHub remote.
  • git push -u origin main uploads all your commits to GitHub and sets main as the default upstream branch.
  • Refresh your GitHub repository page in the browser.
  • You should see all your files listed and the README rendered as a formatted page with the architecture diagram, tech stack, and pipeline script table.

Push rejected or authentication error?

If you see a permission or authentication error, GitHub requires either a personal access token or SSH key for pushing. Go to Settings > Developer settings > Personal access tokens on GitHub to generate a token with repo scope. Use the token as your password when prompted.

Help me authenticate with GitHub from the terminal.

Secret mission

Build a Data Quality Validation Layer

Your pipeline extracts, loads, and transforms data. But how do you know the output is correct? Production pipelines at companies like Agoda use automated validation layers that catch data issues before they reach dashboards and reports. In this mission, you'll build one.

Clean Up Your Resources

Clean Up Your Resources

Decide whether to keep your resources running, pause them to come back later, or delete them entirely. This project runs within the GCP free tier, so there are no ongoing costs to worry about.

Resources you used:

  • Google Cloud Storage bucket (contains your staged CSV file).
  • BigQuery dataset hotel_analytics with 3 tables (raw_listings, market_summary, pricing_analysis).
  • Local project directory with Python scripts, data files, and virtual environment.
  • GitHub repository (free, public).

Keep everything running

No action needed. Choose this if you plan to extend the pipeline or revisit the SQL transformations.

  • Your BigQuery dataset stays within the 10 GiB free storage tier indefinitely.
  • Your Cloud Storage bucket holds a single CSV file, costing fractions of a penny per month.
  • The Google Cloud CLI stays installed on your machine for future GCP projects.
  • Your GitHub repository remains public and portfolio-ready.

Pause - I'll come back to this later

Free up local disk space while keeping your cloud resources intact. Your BigQuery tables and GCS bucket are essentially free to keep.

  • Deactivate your virtual environment by running:
  • Close the project in Cursor.
  • Your cloud resources (BigQuery dataset, GCS bucket) cost nothing within the free tier, so leave them running.
deactivate

When you come back, reactivate the environment with source venv/bin/activate from your project directory.

Delete - I don't want to use this again

Remove all project resources and start fresh. Follow each step below.

  • Delete the BigQuery dataset and all its tables by running:
bq rm -r -d hotel_analytics

When prompted, type y and press Enter to confirm.

  • Delete the Cloud Storage bucket and its contents by running:
gsutil rm -r gs://[[BUCKET_NAME="your-bucket-name-hotel-pipeline"]]
  • Delete the local project directory by running:
cd ~/Desktop
rm -rf hotel-analytics-pipeline
  • Delete the GitHub repository by navigating to your repository page on GitHub.
  • Click Settings at the top of the repository.
  • Scroll to the bottom to the Danger Zone section.
  • Click Delete this repository and follow the confirmation prompts.

What about the Google Cloud CLI?

The Google Cloud CLI is a system-wide tool you may want for future GCP projects. If you want to remove it completely, follow the official uninstall guide at cloud.google.com/sdk/docs/uninstall-cloud-sdk.

The short version: locate your installation directory with gcloud info --format='value(installation.sdk_root)', then delete that directory and the config directory at ~/.config/gcloud.

Nice Work!

Nice Work!

Nice work! You just built a complete ELT pipeline that processes 75,000 real hotel listings through Google Cloud Platform and published it to GitHub as a portfolio piece.

You've learned how to:

  • Build a Python ELT pipeline that extracts real hotel data, stages it in Cloud Storage, and loads it into BigQuery using the Python SDK.
  • Write SQL transformations using CTEs and window functions (PERCENT_RANK, RANK, AVG OVER) to produce analytics-ready tables from raw data.
  • Publish a portfolio-ready repository to GitHub with professional documentation of the pipeline architecture and tech stack.
  • Secret Mission: Built a data quality validation layer with programmatic checks that mirrors production pipeline practices.

Ready to quiz yourself?