Building a Data Warehouse in BigQuery

Notice the naming: raw_, dwh_ and dm_ prefixes make a dataset's role obvious. Datasets should also be named for business meaning, such as customer loans or website logs, not technical purposes such as layer 1.

Event or snapshot?

1. 01 A customer taps a bike out of a dock at 08:03.

   Event Snapshot

2. 02 A docking station has a name, a region and a capacity.

   Event Snapshot

3. 03 A sensor reports 21.4 °C at 12:00:05.

   Event Snapshot

Pick the write mode

1. 01 Each day new bike trips arrive. A trip never changes once it has happened.

   WRITE_APPEND WRITE_TRUNCATE WRITE_EMPTY

2. 02 A stations file arrives daily as a full copy. Stations can be added, changed or removed, and you only need the current picture.

   WRITE_APPEND WRITE_TRUNCATE WRITE_EMPTY

3. 03 A one-off load must never overwrite a table that already has data.

   WRITE_APPEND WRITE_TRUNCATE WRITE_EMPTY

1. 01 A small stations table, and a manager wants an always-fresh top-two-regions answer without storing anything extra.

   View Table

2. 02 1,000 dashboards each use a view that joins and aggregates five multi-petabyte tables.

   View Table

3. 03 You want zero extra storage and results that update the moment the source table does.

   View Table

Normalised vs denormalised: try it

A gender label stored as text on every person row is denormalised. Storing a small id and looking the label up in a reference table is normalised. Flip between them and see what each buys you.

Gender column stores

Rename "Woman" to "Female"

Reading the label

Risk

Storage is cheap in BigQuery and joins are far less scary than in the Hadoop era, so consistency and clarity usually win. But a model with no thought at all leaves you with duplicated data, conflicting numbers and users who no longer trust the warehouse.

Inmon or Kimball?

1. 01 Finance needs statements that cannot contain mistakes, from sources that rarely change, and accuracy matters more than build speed.

   Inmon Kimball

2. 02 New sources and columns appear every week and the business wants answers quickly, even if some inefficiency remains.

   Inmon Kimball

3. 03 You are building one enterprise-wide source of truth first, with every data mart obeying its rules.

   Inmon Kimball

The book's answer to "which is best for BigQuery" is both, depending on the use case. Whichever you pick, the whole organisation commits to it for a long time, so experienced modellers usually decide.

Facts and dimensions

A fact table holds measurements at a fixed granularity, such as trips per station per day. A dimension table holds the attributes that describe an entity, such as a station's name and region. In a star schema a dimension has no parent tables, so it is denormalised.

1. 01 Total trips and total trip seconds per station per day.

   Fact Dimension

2. 02 Each station’s name, region and capacity.

   Fact Dimension

3. 03 A customer’s name, segment and signup date.

   Fact Dimension

BigQuery also supports nested and repeated columns: store each region's stations inside the region row. You keep denormalised convenience and avoid repeating region fields, though SQL users new to nested data may find it awkward.

Part A: get comfortable

1. Enable the BigQuery and Cloud Storage APIs

   Open each service once in the console so its API is enabled for your project.

2. Run SELECT "hello world" in the BigQuery editor

   Find Explorer, the query editor and the job and query history panels.

   
   						SELECT "hello world";
   					

3. Create a dataset and load a CSV from your computer

   Use Create table, source Upload, and let BigQuery auto-detect the schema for a headed CSV. Then delete both when done.

4. Pin the public data project and check a table’s size first

   Add the project bigquery-public-data by name, open baseball.schedules, and read its size under Details before querying. Costs scale with bytes scanned.

   
   						SELECT * FROM `bigquery-public-data.baseball.schedules` LIMIT 1000;
   					

5. Create a bucket with a globally unique name

   A project-name prefix keeps it unique. Use the same location you will use for your datasets.

6. Upload the book’s sample data to the bucket

   Clone the book’s GitHub repository in Cloud Shell and copy the chapter 3 dataset into the bucket. The book uses gsutil; Google now recommends gcloud storage for the same job.

   
   						gcloud storage cp -r Data-Engineering-with-Google-Cloud-Platform/* gs://YOUR_BUCKET/from-git/
   					

Part B: build the bike-sharing warehouse

1. Scenario 1: create a small Cloud SQL (MySQL) instance

   This costs money by the hour. Use the smallest tier, set your own strong root password, and delete the instance as soon as you have exported the table.

2. Export the stations table to Cloud Storage as CSV

   Give the Cloud SQL service account permission to write objects to the bucket first (an IAM role; Chapter 9 explains why).

3. Load it into a raw dataset with an explicit schema

   The export has no header row, so do not auto-detect. Type the schema yourself.

   
   						station_id:STRING,name:STRING,region_id:STRING,capacity:INTEGER
   					

4. Create a data mart dataset and a view for the business question

   Top two regions by total station capacity, then save the result as CSV.

   
   						SELECT region_id, SUM(capacity) AS total_capacity
   FROM `PROJECT.raw_bikesharing.stations`
   WHERE region_id != ''
   GROUP BY region_id ORDER BY total_capacity DESC LIMIT 2;
   					

5. Scenario 2: create the datasets with the Python client

   Loop over a list of dataset names (raw, warehouse, marts) and create any that do not exist. Doing it in code is what makes it repeatable.

6. Load trips for day one, then day two, with WRITE_APPEND

   Then prove there are two dates and no duplicate trip IDs.

   
   						SELECT DISTINCT DATE(start_date) FROM `PROJECT.raw_bikesharing.trips`;
   SELECT trip_id, COUNT(*) c FROM `PROJECT.raw_bikesharing.trips` GROUP BY trip_id HAVING c > 1;
   					

7. Copy the regions table from the public dataset

   Write the result of a SELECT on the public table into a table in your raw dataset.

8. Reload stations with WRITE_TRUNCATE and check for duplicates

   Group by station_id and confirm nothing appears twice.

9. Build fact_trips_daily and dim_stations

   A daily fact table joined to a stations dimension. Run the fact build once per date.

10. Create the four operational views

    Daily trips, daily average duration, top five start stations by duration, and the three regions with the shortest total duration.

11. Clean up

    Delete the Cloud SQL instance and any datasets and buckets you no longer need.

    
    						gcloud sql instances delete mysql-instance-source
    					

For the final self-check, add a question the model cannot yet answer (for example, top three regions by female riders on the latest date) and decide which fact and dimension tables you need. There is no single correct model.

Bucket / object

A Cloud Storage container with a globally unique name, and the files inside it.

Dataset

A named group of BigQuery tables and views.

Colossus

Google’s distributed file system that holds BigQuery storage.

Staging layer

Files landed in Cloud Storage before loading.

Raw / warehouse / mart

The three dataset layers: as-loaded, modelled, and per-audience.

Event vs snapshot

Something that happened (append-only) vs something that exists (can change).

Write disposition

A load setting: append, truncate or write-if-empty.

Fact table

Measurements at a defined granularity.

Dimension table

Attributes describing an entity that supports facts.

Star schema

A fact table surrounded by denormalised dimensions.

Normalised / denormalised

Split into related tables to avoid repetition, or kept together for ease of use.