Home
Projects

Building a BigQuery Data Warehouse with Star Schema and BigQuery Partitioning for Cinema Admission Analytics

Data Warehouse · BigQuery · Star Schema · ETL/ELT

Building a BigQuery Data Warehouse for Cinema Admission Analytics

As part of my Data Analyst Internship, I worked on designing and implementing a centralized BigQuery data warehouse for cinema admission analytics. This project involved migrating historical admission data from Google Drive and Google Sheets, designing a Star Schema architecture, building staging, dimension, and fact tables, and improving query efficiency through partitioned BigQuery tables.

Data Architecture

Cinema Admission Data Warehouse Architecture

Data warehouse flow from Google Drive and Google Sheets sources into BigQuery staging, dimension, fact, and partitioned master tables.

Project Overview

The project was developed to support long-term cinema admission analytics by moving historical live admission data from scattered Google Drive folders and Google Sheets files into a centralized BigQuery data warehouse. The main goal was to create a cleaner, more scalable, and analysis-ready data structure that could support dashboarding, monitoring, and advanced analytics.

Before the data warehouse was built, the admission data was stored across many files, with each movie title having its own folder and spreadsheet. This structure was difficult to maintain as the data volume grew and was not ideal for advanced analysis or dashboard performance. To solve this, I designed a Star Schema model and implemented the database structure in BigQuery using staging tables, dimension tables, fact tables, and a partitioned master table.

Problem Statement

The original data storage structure created several challenges for analytics and dashboard development:

  • Scattered data sources: Admission data was stored in many Google Drive folders and Google Sheets files, making it difficult to manage and query consistently.
  • Slow migration process: An initial migration test using a transactional database approach was not efficient for large-scale analytical workloads.
  • Network latency and execution limits: Row-by-row insertion from Apps Script created performance bottlenecks and was vulnerable to Google Apps Script execution time limits.
  • OLTP vs OLAP mismatch: A transactional database was less suitable for large aggregation queries and dashboard analytics compared to BigQuery.
  • Growing data volume: As more movies and showtimes were added, a more scalable data structure was needed to support long-term analytics.

Solution

The solution was to move the architecture fully into BigQuery and redesign the data model using a Star Schema approach. Instead of inserting data row by row into a transactional database, the migration process was redesigned to use bulk loading into BigQuery staging tables, followed by transformation and distribution into dimension and fact tables.

Key Solution Components

  • Designed an Entity Relationship Diagram using a Star Schema approach.
  • Created staging tables to store raw migrated admission and city reference data.
  • Created dimension tables for movie and cinema attributes.
  • Created fact tables for screening performance and seat-level information.
  • Used BigQuery DDL statements to initialize physical database tables.
  • Implemented bulk data ingestion using NDJSON and BigQuery Jobs API.
  • Used MERGE operations to distribute cleaned data from staging tables into the warehouse model.
  • Created a partitioned admission master table to improve query efficiency and reduce unnecessary data scanning.

Data Warehouse Design

The data warehouse was designed using a Star Schema structure, separating descriptive attributes into dimension tables and measurable screening performance into fact tables. This structure made the data easier to query, reduce redundancy, and prepare for dashboarding and advanced analysis.

Table Type Purpose
Staging_Admission Staging Stores raw admission data migrated from Google Drive and Google Sheets.
Staging_City Staging Stores raw city reference data used for mapping cinema locations.
Dim_Movie Dimension Stores unique movie identifiers and standardized movie title attributes.
Dim_Cinema Dimension Stores cinema information, city, exhibitor, and timezone attributes.
Fact_Screening Fact Stores screening-level metrics such as total seats, filled seats, showtime, and last update.
Fact_Seat Fact Stores seat-level details such as seat number and ticket price.

Data Model

Star Schema Design

Star Schema ERD for Cinema Admission Data Warehouse

Star Schema design separating movie and cinema attributes into dimension tables and admission performance metrics into fact tables.

Technical Implementation

1. Designing the Star Schema

The first step was to design the data warehouse structure using an ERD and Star Schema approach. The model separated business entities such as movies and cinemas into dimension tables, while quantitative admission performance metrics were stored in fact tables.

2. Initializing Physical Tables in BigQuery

After the schema was designed, BigQuery DDL statements were used to create the staging, dimension, and fact tables. This ensured that each table had a clear structure and precise data types before the migration process started. Here is one of the queries used. 

CREATE TABLE `project.dataset.Fact_Screening` (
  screening_id STRING,
  movie_id STRING,
  cinema_id STRING,
  Cinema_Name STRING,
  show_date DATETIME,
  Show_Time TIME,
  no_show STRING,
  Total_Seats INT64,
  Filled_Seats INT64,
  last_update DATETIME
);

3. Migrating Data from Google Drive and Google Sheets

The source data was stored across many Google Drive folders and Google Sheets files. A Google Apps Script-based migration process was created to read these files, extract the admission data, and prepare the data for loading into BigQuery.

Instead of inserting rows one by one, the migration process used a bulk loading approach by converting the extracted data into Newline Delimited JSON. This allowed BigQuery to load large volumes of data more efficiently through the BigQuery Jobs API. Below is a short script snippet from Apps Script used in the migration process.

Google Apps Script · Resumable BigQuery Migration 
// ============================================================
// Resumable Historical Migration from Google Drive / Sheets
// to BigQuery Data Warehouse
// Sensitive identifiers are masked for portfolio publication.
// ============================================================

function runFullHistoricalMigration() {
  var props = PropertiesService.getUserProperties();
  var pendingFolders = props.getProperty('PENDING_FOLDERS');

  if (pendingFolders && JSON.parse(pendingFolders).length > 0) {
    Logger.log("Previous ingestion queue is incomplete. Resuming remaining tasks.");
    executeResumableIngestion();
  } else {
    Logger.log("Starting a new full historical migration.");
    resetSyncState();
    executeResumableIngestion();
  }
}

function resetSyncState() {
  var props = PropertiesService.getUserProperties();
  props.deleteProperty('PENDING_FOLDERS');
  removeExistingTriggers();
  Logger.log("Execution state and properties have been successfully cleared.");
}

function executeResumableIngestion() {
  removeExistingTriggers();

  var projectId = 'masked-gcp-project';
  var datasetId = 'masked_dataset';
  var folderId = 'masked_drive_folder_id';

  var props = PropertiesService.getUserProperties();
  var pendingFolders = props.getProperty('PENDING_FOLDERS');
  var folderArray = [];
  var timeIsUp = false;

  if (!pendingFolders) {
    Logger.log("Starting first run. Truncating staging tables.");

    runSyncQuery(projectId, "TRUNCATE TABLE `" + projectId + "." + datasetId + ".Staging_Admission`");
    runSyncQuery(projectId, "TRUNCATE TABLE `" + projectId + "." + datasetId + ".Staging_City`");

    var rootFolder = DriveApp.getFolderById(folderId);
    var subFolders = rootFolder.getFolders();

    while (subFolders.hasNext()) {
      var folder = subFolders.next();

      if (!folder.getName().startsWith("~")) {
        folderArray.push(folder.getId());
      }
    }

    Logger.log("Initial queue created. Total folders: " + folderArray.length);
  } else {
    folderArray = JSON.parse(pendingFolders);
    Logger.log("Resuming execution. Remaining folders: " + folderArray.length);
  }

  if (folderArray.length === 0) {
    Logger.log("All folders processed. Starting BigQuery MERGE operations.");
    executeBigQueryMerge(projectId, datasetId);
    props.deleteProperty('PENDING_FOLDERS');
    removeExistingTriggers();
    return;
  }

  // Google Apps Script has execution time limits.
  // This logic stops the process before timeout and saves the remaining queue.
  var startTime = new Date().getTime();
  var maxTime = 4.8 * 60 * 1000;

  while (folderArray.length > 0) {
    if (new Date().getTime() - startTime > maxTime) {
      timeIsUp = true;
      break;
    }

    var currentFolderId = folderArray.shift();
    var subFolder = DriveApp.getFolderById(currentFolderId);
    var files = subFolder.getFilesByType(MimeType.GOOGLE_SHEETS);

    Logger.log("Processing movie folder: " + subFolder.getName());

    var admissionNdjson = "";
    var cityNdjson = "";

    while (files.hasNext()) {
      var file = files.next();

      if (!file.getName().toLowerCase().includes("admission")) {
        continue;
      }

      var ss = SpreadsheetApp.openById(file.getId());

      // Extract admission data from the first sheet.
      var sheetAdm = ss.getSheets()[0];
      var dataAdm = sheetAdm.getDataRange().getDisplayValues();

      for (var i = 1; i < dataAdm.length; i++) {
        var row = dataAdm[i];

        if (!row[0] || row[0] === "") {
          continue;
        }

        admissionNdjson += JSON.stringify({
          movie_title: row[0],
          Cinema_Name: row[1],
          Exhibitor: row[2],
          CITY: row[3],
          Show: formatTimeForBigQuery(row[4]),
          Total_Seats: parseInt(row[5]) || 0,
          Filled_Seats: parseInt(row[6]) || 0,
          OR: parseFloat(row[7]) || 0.0,
          show_date: formatDateTimeForBigQuery(row[8]),
          last_update: formatDateTimeForBigQuery(row[9]),
          no_show: row[10]
        }) + "\n";
      }

      // Extract city reference data if available.
      var sheetCity = ss.getSheetByName("city_data");

      if (sheetCity) {
        var dataCity = sheetCity.getDataRange().getDisplayValues();

        for (var j = 1; j < dataCity.length; j++) {
          var rowC = dataCity[j];

          if (!rowC[0] || rowC[0] === "") {
            continue;
          }

          cityNdjson += JSON.stringify({
            cinema_name: rowC[0],
            daerah: rowC[1],
            daerah_specific: rowC[2]
          }) + "\n";
        }
      }
    }

    // Bulk load extracted data into BigQuery staging tables.
    if (admissionNdjson.trim().length > 0) {
      loadNdjsonToBigQuery(projectId, datasetId, 'Staging_Admission', admissionNdjson, 'WRITE_APPEND');
    }

    if (cityNdjson.trim().length > 0) {
      loadNdjsonToBigQuery(projectId, datasetId, 'Staging_City', cityNdjson, 'WRITE_APPEND');
    }

    Logger.log("Folder completed. Remaining folders: " + folderArray.length);
  }

  if (timeIsUp || folderArray.length > 0) {
    props.setProperty('PENDING_FOLDERS', JSON.stringify(folderArray));

    ScriptApp
      .newTrigger('executeResumableIngestion')
      .timeBased()
      .after(60 * 1000)
      .create();

    Logger.log("Execution time threshold reached. Next execution trigger created.");
  } else {
    props.deleteProperty('PENDING_FOLDERS');
    executeBigQueryMerge(projectId, datasetId);
    removeExistingTriggers();
  }
}

function loadNdjsonToBigQuery(projectId, dataset, table, ndjson, writeMode) {
  var blob = Utilities.newBlob(ndjson, "application/octet-stream");

  var job = {
    configuration: {
      load: {
        destinationTable: {
          projectId: projectId,
          datasetId: dataset,
          tableId: table
        },
        sourceFormat: 'NEWLINE_DELIMITED_JSON',
        writeDisposition: writeMode
      }
    }
  };

  BigQuery.Jobs.insert(job, projectId, blob);
  Logger.log("NDJSON data loaded into table: " + table);
}

function executeBigQueryMerge(projectId, datasetId) {
  var datasetRef = projectId + "." + datasetId + ".";

  // Deduplicate staging admission data by keeping the latest record.
  var sqlDedupStagingAdmission = `
    CREATE OR REPLACE TABLE \`${datasetRef}Staging_Admission\` AS
    SELECT * EXCEPT(rn)
    FROM (
      SELECT *,
        ROW_NUMBER() OVER (
          PARTITION BY movie_title, Cinema_Name, Exhibitor, show_date, Show, no_show
          ORDER BY last_update DESC
        ) AS rn
      FROM \`${datasetRef}Staging_Admission\`
    )
    WHERE rn = 1;
  `;

  // Deduplicate city reference data.
  var sqlDedupStagingCity = `
    CREATE OR REPLACE TABLE \`${datasetRef}Staging_City\` AS
    SELECT DISTINCT *
    FROM \`${datasetRef}Staging_City\`;
  `;

  // Create a clean staging view for standardized movie titles.
  var sqlCreateCleanView = `
    CREATE OR REPLACE VIEW \`${datasetRef}vw_Staging_Admission_Clean\` AS
    SELECT
      COALESCE(map.standardized_movie_title, UPPER(TRIM(stg.movie_title))) AS movie_title,
      stg.Cinema_Name,
      stg.Exhibitor,
      stg.CITY,
      stg.Show,
      stg.Total_Seats,
      stg.Filled_Seats,
      stg.\`OR\`,
      stg.show_date,
      stg.last_update,
      stg.no_show
    FROM \`${datasetRef}Staging_Admission\` stg
    LEFT JOIN \`${datasetRef}Master_Movie_Title_Mapping\` map
      ON UPPER(TRIM(stg.movie_title)) = UPPER(TRIM(map.raw_movie_title));
  `;

  // Insert new movie records into the movie dimension table.
  var sqlMergeDimMovie = `
    MERGE \`${datasetRef}Dim_Movie\` T
    USING (
      SELECT DISTINCT movie_title
      FROM \`${datasetRef}vw_Staging_Admission_Clean\`
      WHERE movie_title IS NOT NULL
    ) S
    ON T.movie_title = S.movie_title
    WHEN NOT MATCHED THEN
      INSERT (movie_id, movie_title)
      VALUES (CONCAT('MOV-', SUBSTR(GENERATE_UUID(), 1, 8)), S.movie_title);
  `;

  // Insert or update cinema records into the cinema dimension table.
  var sqlMergeDimCinema = `
    MERGE \`${datasetRef}Dim_Cinema\` T
    USING (
      SELECT
        Cinema_Name,
        MAX(NULLIF(TRIM(CITY), '')) AS CITY,
        MAX(NULLIF(TRIM(Exhibitor), '')) AS Exhibitor
      FROM \`${datasetRef}vw_Staging_Admission_Clean\`
      WHERE Cinema_Name IS NOT NULL
        AND TRIM(Cinema_Name) != ''
      GROUP BY Cinema_Name
    ) S
    ON T.Cinema_Name = S.Cinema_Name
    WHEN MATCHED THEN
      UPDATE SET
        CITY = COALESCE(S.CITY, T.CITY),
        Exhibitor = COALESCE(S.Exhibitor, T.Exhibitor)
    WHEN NOT MATCHED THEN
      INSERT (cinema_id, Cinema_Name, CITY, Exhibitor)
      VALUES (
        CONCAT('CIN-', SUBSTR(GENERATE_UUID(), 1, 8)),
        S.Cinema_Name,
        S.CITY,
        S.Exhibitor
      );
  `;

  // Insert or update screening-level facts.
  var sqlMergeFactScreening = `
    MERGE \`${datasetRef}Fact_Screening\` T
    USING (
      SELECT
        m.movie_id,
        m.movie_title,
        c.cinema_id,
        s.Cinema_Name,
        s.Exhibitor,
        s.CITY,
        CAST(s.show_date AS DATETIME) AS show_date,
        s.Show AS Show_Time,
        s.no_show,
        s.Total_Seats,
        s.Filled_Seats,
        CAST(s.last_update AS DATETIME) AS last_update
      FROM \`${datasetRef}vw_Staging_Admission_Clean\` s
      JOIN \`${datasetRef}Dim_Movie\` m
        ON UPPER(TRIM(s.movie_title)) = UPPER(TRIM(m.movie_title))
      JOIN \`${datasetRef}Dim_Cinema\` c
        ON UPPER(TRIM(s.Cinema_Name)) = UPPER(TRIM(c.Cinema_Name))
      WHERE s.movie_title IS NOT NULL
        AND s.Cinema_Name IS NOT NULL
    ) S
    ON T.movie_id = S.movie_id
      AND T.cinema_id = S.cinema_id
      AND T.show_date = S.show_date
      AND T.Show_Time = S.Show_Time
      AND T.no_show = S.no_show
    WHEN MATCHED THEN
      UPDATE SET
        Total_Seats = S.Total_Seats,
        Filled_Seats = S.Filled_Seats,
        last_update = S.last_update,
        movie_title = S.movie_title,
        CITY = S.CITY,
        Exhibitor = S.Exhibitor
    WHEN NOT MATCHED THEN
      INSERT (
        screening_id,
        movie_id,
        movie_title,
        cinema_id,
        Cinema_Name,
        Exhibitor,
        CITY,
        show_date,
        Show_Time,
        no_show,
        Total_Seats,
        Filled_Seats,
        last_update
      )
      VALUES (
        CONCAT('SCR-', SUBSTR(GENERATE_UUID(), 1, 8)),
        S.movie_id,
        S.movie_title,
        S.cinema_id,
        S.Cinema_Name,
        S.Exhibitor,
        S.CITY,
        S.show_date,
        S.Show_Time,
        S.no_show,
        S.Total_Seats,
        S.Filled_Seats,
        S.last_update
      );
  `;

  runSyncQuery(projectId, sqlDedupStagingAdmission);
  runSyncQuery(projectId, sqlDedupStagingCity);
  runSyncQuery(projectId, sqlCreateCleanView);
  runSyncQuery(projectId, sqlMergeDimMovie);
  runSyncQuery(projectId, sqlMergeDimCinema);
  runSyncQuery(projectId, sqlMergeFactScreening);

  Logger.log("All BigQuery warehouse merge operations completed successfully.");
}

function runSyncQuery(projectId, sql) {
  var job = {
    configuration: {
      query: {
        query: sql,
        useLegacySql: false
      }
    }
  };

  var response = BigQuery.Jobs.insert(job, projectId);
  var jobId = response.jobReference.jobId;
  var location = response.jobReference.location;

  while (true) {
    Utilities.sleep(3000);

    var checkJob = BigQuery.Jobs.get(projectId, jobId, {
      location: location
    });

    if (checkJob.status.state === 'DONE') {
      if (checkJob.status.errorResult) {
        throw new Error(checkJob.status.errorResult.message);
      }

      break;
    }
  }
}

function removeExistingTriggers() {
  var triggers = ScriptApp.getProjectTriggers();

  for (var i = 0; i < triggers.length; i++) {
    if (triggers[i].getHandlerFunction() === 'executeResumableIngestion') {
      ScriptApp.deleteTrigger(triggers[i]);
    }
  }
}

function formatTimeForBigQuery(timeStr) {
  if (!timeStr) return null;

  var parts = timeStr.toString().split(":");
  var hr = parts[0] ? parts[0].padStart(2, '0') : "00";
  var min = parts[1] ? parts[1].padStart(2, '0') : "00";
  var sec = parts[2] ? parts[2].padStart(2, '0') : "00";

  return hr + ":" + min + ":" + sec;
}

function formatDateTimeForBigQuery(dtStr) {
  if (!dtStr || dtStr.toString().toLowerCase().includes('nan')) {
    return null;
  }

  var parts = dtStr.toString().split(" ");

  if (parts.length !== 2) {
    return dtStr;
  }

  var dateParts = parts[0].split("/");

  if (dateParts.length === 3) {
    var part0 = dateParts[0];
    var part1 = dateParts[1];
    var year = dateParts[2];
    var month;
    var day;

    if (parseInt(part0, 10) > 12) {
      day = part0;
      month = part1;
    } else if (parseInt(part1, 10) > 12) {
      month = part0;
      day = part1;
    } else {
      day = part0;
      month = part1;
    }

    return year + "-" +
      month.padStart(2, '0') + "-" +
      day.padStart(2, '0') + " " +
      formatTimeForBigQuery(parts[1]);
  }

  return dtStr;
}

Masked Google Apps Script implementation for a resumable migration engine that extracts historical admission data from Google Drive and Google Sheets, bulk-loads it into BigQuery staging tables using NDJSON, and performs warehouse MERGE operations into dimension and fact tables.

4. Using Staging Tables and MERGE Operations

Raw data was first loaded into staging tables. From there, BigQuery MERGE operations were used to clean, deduplicate, and distribute the data into the final dimension and fact tables. This approach followed an ELT pattern where raw data was loaded first, then transformed inside the data warehouse.

5. Creating a Partitioned Admission Master Table

To improve query efficiency and prepare the data for long-term dashboard usage, a partitioned table named Admission_Master_Partitioned was created. The table was partitioned by month using the show_date column, allowing BigQuery to scan only the relevant date partitions during analysis.

Beyond query optimization, this partitioned table was also designed as the future main storage layer for incoming scraping results. Previously, the scraping workflow still relied on spreadsheets as the primary container for storing scraped admission data. With this partitioned BigQuery table, the long-term direction is to store scraping outputs directly in BigQuery, reducing dependency on spreadsheets and making the pipeline more scalable, stable, and efficient. 

CREATE TABLE `project.dataset.Admission_Master_Partitioned`
(
  movie_title STRING,
  Cinema_Name STRING,
  Exhibitor STRING,
  CITY STRING,
  Show TIME,
  Total_Seats INT64,
  Filled_Seats INT64,
  `OR` FLOAT64,
  show_date DATETIME,
  last_update DATETIME,
  no_show STRING
)
PARTITION BY DATETIME_TRUNC(show_date, MONTH)
OPTIONS (
  require_partition_filter = TRUE
);

The option require_partition_filter = TRUE was used to prevent queries from scanning the entire table without a date filter. This helped control BigQuery usage and made queries more efficient as the data volume continued to grow.

Output & Impact

The final output of this project was a centralized and scalable BigQuery data warehouse for cinema admission analytics. The warehouse provided a cleaner source of truth for analysis and reduced dependency on scattered Google Drive and Google Sheets files.

  • Designed a Star Schema architecture for cinema admission analytics.
  • Created staging, dimension, and fact tables in BigQuery.
  • Migrated historical admission data from Google Drive and Google Sheets into BigQuery.
  • Implemented bulk loading using NDJSON to reduce migration time and network latency.
  • Used MERGE operations to clean, deduplicate, and distribute data into the warehouse model.
  • Created a partitioned admission master table to improve query efficiency.
  • Reduced dependency on external spreadsheet-based data sources.
  • Prepared a scalable foundation for dashboarding, monitoring, and advanced analytics.
  • Designed the partitioned admission table as the future main container for directly storing scraping outputs, reducing dependency on spreadsheets as the primary storage layer.

Tools & Technologies

BigQuery SQL Google Apps Script Google Drive Google Sheets Data Warehouse Star Schema ETL/ELT Partitioning

Key Learnings

This project strengthened my understanding of how data modeling and warehouse architecture affect analytics scalability. A spreadsheet-based storage structure may work for early-stage workflows, but as data volume grows, a centralized data warehouse becomes essential for maintaining performance, consistency, and analytical flexibility.

The project also helped me understand the practical difference between OLTP and OLAP systems. For analytical workloads involving aggregation, dashboarding, and large-scale historical data, BigQuery provided a more suitable environment than a transactional database approach.

Another key learning was the importance of partitioning and query control in BigQuery. By partitioning the admission master table and requiring partition filters, the data warehouse became more efficient and better prepared for future growth.

Post a Comment