Storing the Data

Choosing the right storage service is one of the most heavily tested topics. The decision tree depends on data structure, access pattern, latency requirements, scale, and consistency needs.

Relational Databases

NoSQL Databases

# Bigtable: Design a row key for time-series IoT data
# BAD: timestamp#device_id (hotspotting - all writes to latest time)
# GOOD: device_id#reverse_timestamp (distributes writes across nodes)

# Example row key design:
# sensor_001#9999999999-1709827200  (reversed Unix timestamp)
# sensor_002#9999999999-1709827200
# sensor_001#9999999999-1709830800

# This ensures:
# 1. Writes distribute across tablets (different device_ids)
# 2. Reads for a device get latest data first (reversed time)
# 3. Range scans by device are efficient (prefix scan)

Object Storage (Cloud Storage)

# Lifecycle policy: Auto-transition and delete
gsutil lifecycle set lifecycle.json gs://my-bucket

# lifecycle.json:
{
  "rule": [
    {
      "action": {"type": "SetStorageClass", "storageClass": "NEARLINE"},
      "condition": {"age": 30, "matchesStorageClass": ["STANDARD"]}
    },
    {
      "action": {"type": "SetStorageClass", "storageClass": "COLDLINE"},
      "condition": {"age": 90, "matchesStorageClass": ["NEARLINE"]}
    },
    {
      "action": {"type": "Delete"},
      "condition": {"age": 365}
    }
  ]
}

Architecture: Dremel + Colossus + Borg + Jupiter

BigQuery's architecture separates compute and storage:

• Colossus — Distributed file system for persistent, columnar storage. Data stored in Capacitor format (Google's columnar format).
• Dremel — Execution engine that creates a tree of processing nodes. Root server distributes query to intermediate and leaf nodes.
• Jupiter — Google's petabit-scale network connecting compute and storage, enabling the separation.
• Borg — Cluster management system that allocates compute resources (slots) dynamically.

Partitioning and Clustering

-- Partitioned + clustered table
CREATE TABLE `project.dataset.events`
(
  event_id STRING,
  user_id STRING,
  event_type STRING,
  amount NUMERIC,
  region STRING,
  event_time TIMESTAMP
)
PARTITION BY DATE(event_time)
CLUSTER BY user_id, region
OPTIONS (
  partition_expiration_days = 365,
  require_partition_filter = true,  -- Force partition pruning
  description = "Production events table"
);

-- GOOD: Uses partition pruning + cluster pruning
SELECT user_id, SUM(amount)
FROM `project.dataset.events`
WHERE DATE(event_time) = '2025-03-01'
  AND region = 'US'
GROUP BY user_id;

-- BAD: Function on partition column prevents pruning
SELECT * FROM `project.dataset.events`
WHERE EXTRACT(YEAR FROM event_time) = 2025;  -- Full table scan!

Query Optimization Techniques

-- Nested and repeated fields (denormalization)
CREATE TABLE `project.dataset.orders_nested` AS
SELECT
  order_id,
  customer_id,
  order_date,
  STRUCT(
    shipping_address.street,
    shipping_address.city,
    shipping_address.state,
    shipping_address.zip
  ) AS shipping,
  ARRAY_AGG(STRUCT(
    item_id,
    product_name,
    quantity,
    unit_price
  )) AS line_items
FROM `project.dataset.orders` o
JOIN `project.dataset.order_items` oi USING (order_id)
JOIN `project.dataset.addresses` a ON o.shipping_address_id = a.address_id
GROUP BY order_id, customer_id, order_date,
  shipping_address.street, shipping_address.city,
  shipping_address.state, shipping_address.zip;

-- Query nested data efficiently (no JOINs needed)
SELECT
  order_id,
  shipping.city,
  (SELECT SUM(li.quantity * li.unit_price) FROM UNNEST(line_items) li) AS total
FROM `project.dataset.orders_nested`
WHERE order_date = '2025-03-01';

Slots and Pricing Models

Data Modeling in BigQuery

BigQuery supports multiple modeling approaches, but its columnar architecture favors wide, denormalized tables over traditional normalized schemas:

Materialized Views

Materialized views pre-compute query results and update automatically when base tables change. BigQuery transparently rewrites queries to use materialized views when beneficial.

-- Create a materialized view for daily aggregations
CREATE MATERIALIZED VIEW `project.dataset.daily_summary`
PARTITION BY event_date
CLUSTER BY region
OPTIONS (
  enable_refresh = true,
  refresh_interval_minutes = 30,
  max_staleness = INTERVAL "4:0:0" HOUR TO SECOND
)
AS
SELECT
  DATE(event_time) AS event_date,
  region,
  COUNT(*) AS event_count,
  SUM(amount) AS total_amount,
  AVG(amount) AS avg_amount,
  APPROX_COUNT_DISTINCT(user_id) AS unique_users
FROM `project.dataset.events`
GROUP BY 1, 2;

-- This query will automatically use the materialized view:
SELECT region, SUM(total_amount)
FROM `project.dataset.events`
WHERE DATE(event_time) = '2025-03-01'
GROUP BY region;

BigLake: Unified Storage Engine

BigLake extends BigQuery governance to data stored in Cloud Storage. Create BigLake tables that point to files in GCS and query them with BigQuery SQL, while applying the same row-level and column-level security as native BigQuery tables.

-- Create a BigLake table over Parquet files in Cloud Storage
CREATE EXTERNAL TABLE `project.dataset.events_lake`
WITH CONNECTION `us.my-biglake-connection`
OPTIONS (
  format = 'PARQUET',
  uris = ['gs://my-data-lake/events/*.parquet'],
  metadata_cache_mode = 'AUTOMATIC',    -- Cache metadata for performance
  max_staleness = INTERVAL 1 HOUR       -- Refresh metadata hourly
);

-- Query the BigLake table just like a native BQ table
SELECT DATE(event_time) AS dt, COUNT(*) AS events
FROM `project.dataset.events_lake`
WHERE region = 'US'
GROUP BY 1
ORDER BY 1 DESC
LIMIT 30;

Dataplex: Data Fabric

Dataplex organizes data across storage systems into a logical hierarchy:

# Create a Dataplex lake for the sales domain
gcloud dataplex lakes create sales-lake \
  --location=us-central1 \
  --display-name="Sales Domain Lake"

# Add a raw zone (accept any format)
gcloud dataplex zones create raw-zone \
  --lake=sales-lake \
  --location=us-central1 \
  --resource-location-type=SINGLE_REGION \
  --type=RAW \
  --display-name="Raw Ingestion Zone"

# Add a curated zone (structured data only)
gcloud dataplex zones create curated-zone \
  --lake=sales-lake \
  --location=us-central1 \
  --resource-location-type=SINGLE_REGION \
  --type=CURATED \
  --display-name="Curated Analytics Zone"

# Attach a Cloud Storage bucket as an asset
gcloud dataplex assets create raw-events \
  --lake=sales-lake \
  --zone=raw-zone \
  --location=us-central1 \
  --resource-type=STORAGE_BUCKET \
  --resource-name=projects/my-project/buckets/sales-raw-data

# Attach a BigQuery dataset as an asset
gcloud dataplex assets create curated-analytics \
  --lake=sales-lake \
  --zone=curated-zone \
  --location=us-central1 \
  --resource-type=BIGQUERY_DATASET \
  --resource-name=projects/my-project/datasets/sales_curated

File Format Comparison

Lakehouse Architecture on GCP

The modern lakehouse combines the low-cost storage of a data lake with the managed query engine and governance of a data warehouse. On GCP, this is implemented with:

• Cloud Storage as the storage layer (Parquet/Avro files)
• BigLake for unified governance and fine-grained security
• BigQuery as the compute/query engine (queries both native and external tables)
• Dataplex for metadata management, data quality, and cataloging
• Dataproc for Spark workloads that read/write to the same data

-- Lakehouse pattern: Unified query across native and lake data

-- Native BigQuery table (hot/recent data)
SELECT user_id, event_type, amount, event_time
FROM `project.dataset.events_native`
WHERE DATE(event_time) >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)

UNION ALL

-- BigLake table (cold/historical data in GCS)
SELECT user_id, event_type, amount, event_time
FROM `project.dataset.events_lake`
WHERE DATE(event_time) < DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY);

Quick Selection Cheat Sheet