Google BigQuery Tutorial: Serverless Data Warehousing (2026)
Google BigQuery is a serverless, highly scalable data warehouse that handles petabyte-scale analytics with SQL. After migrating on-prem Hadoop analytics to BigQuery for several organizations, I appreciate how it eliminates cluster management overhead while delivering interactive query performance that rivals dedicated hardware.
This tutorial covers BigQuery's architecture, table design, query optimization, streaming ingestion, machine learning integration, and cost management for building analytical solutions on Google Cloud.
BigQuery Architecture and Storage
BigQuery uses a separation of storage and compute architecture. Data is stored in a managed columnar format (Capacitor) across Google's distributed storage layer. Compute is handled by Dremel, a distributed query engine that executes SQL in parallel across thousands of nodes. This separation lets you store petabytes cheaply while spinning up compute only when querying.
Storage is organized into datasets containing tables. Each table has a schema, partitioning strategy, and clustering configuration. BigQuery automatically manages data placement, replication, and durability across Google's infrastructure.
-- BigQuery SQL
-- Create a dataset
CREATE SCHEMA IF NOT EXISTS analytics
OPTIONS(location = 'US', default_table_expiration_days = NULL);
-- Create a partitioned table
CREATE TABLE analytics.events (
event_id STRING,
user_id STRING,
event_type STRING,
page STRING,
amount FLOAT64,
event_timestamp TIMESTAMP
)
PARTITION BY DATE(event_timestamp)
CLUSTER BY event_type, user_id
OPTIONS(
description = 'User events table',
require_partition_filter = true
);
-- Query with partition filter (required)
SELECT event_type, count(*) AS cnt
FROM analytics.events
WHERE DATE(event_timestamp) = '2026-01-15'
AND event_type = 'purchase'
GROUP BY event_type;Table Design and Partitioning
Partitioning divides table data into segments based on a column (typically date/timestamp). Clustering sorts data within partitions by specified columns. Together, partitioning and clustering dramatically reduce the data scanned by queries, lowering cost and improving performance.
Use date/timestamp partitioning for time-series data. Use integer-range partitioning for numeric IDs. Cluster by columns commonly used in WHERE and GROUP BY clauses. BigQuery automatically manages partition lifecycle, including automatic deletion of old partitions based on partition expiration settings.
-- Partition by date
CREATE TABLE analytics.logs (
log_id STRING,
level STRING,
message STRING,
timestamp TIMESTAMP
)
PARTITION BY DATE(timestamp)
CLUSTER BY level
OPTIONS(partition_expiration_days = 90);
-- Partition by integer range
CREATE TABLE analytics.user_events (
user_id INT64,
event_type STRING,
event_time TIMESTAMP
)
PARTITION BY RANGE_BUCKET(user_id, GENERATE_ARRAY(0, 1000000, 10000))
CLUSTER BY event_type;
-- Schema update options
ALTER TABLE analytics.events
ADD COLUMN page_url STRING,
ADD COLUMN session_id STRING;
-- Decorator for partition-specific queries
SELECT count(*) FROM analytics.events
WHERE _PARTITIONDATE = '2026-01-15';Query Optimization and Performance
BigQuery charges by data scanned, so optimization directly reduces cost. Use partition filters to scan only relevant data. Use approximate aggregation functions (APPROX_COUNT_DISTINCT) for cardinality estimates on large datasets. Materialized views pre-compute expensive aggregations.
Query best practices: avoid SELECT *, filter early in subqueries, use approximate functions for exploration, and leverage materialized views for repeated queries. BigQuery's Dremel engine parallelizes execution, but data layout optimization reduces the data each node processes.
-- Use approximate functions
SELECT
APPROX_COUNT_DISTINCT(user_id) AS approx_unique_users,
APPROX_QUANTILES(amount, 100)[OFFSET(50)] AS median_amount
FROM analytics.events
WHERE DATE(event_timestamp) = '2026-01-15';
-- Materialized view for pre-computed aggregates
CREATE MATERIALIZED VIEW analytics.daily_summary
AS
SELECT
DATE(event_timestamp) AS event_date,
event_type,
COUNT(*) AS event_count,
SUM(amount) AS total_amount,
APPROX_COUNT_DISTINCT(user_id) AS unique_users
FROM analytics.events
GROUP BY 1, 2;
-- Query materialized view (auto-delegates to materialized view)
SELECT * FROM analytics.daily_summary
WHERE event_date = '2026-01-15';
-- Execution plan analysis
EXPLAIN
SELECT user_id, count(*) FROM analytics.events
WHERE DATE(event_timestamp) = '2026-01-15'
GROUP BY user_id HAVING count(*) > 10;Streaming Ingestion and CDC
BigQuery supports real-time streaming via the Storage Write API, streaming insert API, and Pub/Sub integration. The Storage Write API provides exactly-once delivery and is recommended for production streaming. Streaming inserts provide at-least-once delivery with lower latency.
Change Data Capture (CDC) from databases is supported via Datastream. Datastream reads database logs (PostgreSQL WAL, MySQL binlog) and streams changes to BigQuery in real-time, enabling near-real-time data warehouse updates.
# Streaming with Storage Write API (Python)
from google.cloud import bigquery_storage_v1
from google.cloud.bigquery_storage_v1 import types
import pandas as pd
client = bigquery_storage_v1.BigQueryWriteClient()
parent = "projects/my-project/datasets/analytics/tables/events"
rows = [
{"event_id": "e1", "user_id": "u1", "event_type": "click", "event_timestamp": "2026-01-15T10:00:00Z"}
]
df = pd.DataFrame(rows)
write_client = bigquery_storage_v1.BigQueryWriteClient()
write_client.append_rows(
parent=parent,
writer_stream="default",
rows=df.to_dict('records')
)
# Pub/Sub to BigQuery
# Create a subscription that writes to BigQuery directly
gcloud pubsub subscriptions create events-sub \
--topic=events \
--bigquery-table=analytics.events
# Datastream for CDC from PostgreSQL
gcloud datastream streams create my-stream \
--source=postgres-source \
--destination=bq-destination \
--postgresql-source-config=... \
--bigquery-destination-config=...BigQuery ML and AI Integration
BigQuery ML lets you build and deploy machine learning models using SQL. Train models directly on BigQuery data without moving data to external ML platforms. Supported model types include linear regression, logistic regression, k-means, and TensorFlow-based deep learning.
BigQuery also integrates with Vertex AI for advanced ML workflows. Export BigQuery data to Vertex AI for custom model training, or import Vertex AI models for batch prediction within BigQuery SQL.
-- Linear regression model
CREATE OR REPLACE MODEL analytics.sales_model
OPTIONS(
model_type='linear_reg',
input_label_cols=['amount'],
data_split_method='AUTO_SPLIT'
) AS
SELECT region, category, quantity, price, amount
FROM analytics.sales
WHERE DATE(order_date) < '2026-01-01';
-- Predict
SELECT * FROM ML.PREDICT(
MODEL analytics.sales_model,
(SELECT 'us-east' AS region, 'electronics' AS category, 5 AS quantity, 99.99 AS price)
);
-- K-means clustering
CREATE OR REPLACE MODEL analytics.user_segments
OPTIONS(model_type='kmeans', num_clusters=5) AS
SELECT user_id, total_purchases, avg_order_value, days_since_last_order
FROM analytics.user_metrics;
-- Batch prediction
SELECT * FROM ML.PREDICT(
MODEL analytics.user_segments,
(SELECT user_id, total_purchases, avg_order_value, days_since_last_order FROM analytics.user_metrics)
);Cost Management and Security
BigQuery pricing: $5/TB for on-demand queries, or flat-rate slots for predictable costs. Cost control: set query quotas, use partition filters, approximate functions, and materialized views. Monitor billing via Cloud Billing reports and BigQuery job statistics.
Security: BigQuery supports column-level security, row-level security, and data masking. Use IAM roles for access control. Export audit logs to Cloud Logging for compliance. BigQuery encryption uses Google-managed or customer-managed keys (CMEK).
-- Cost control: query with partition filter
SELECT count(*) FROM analytics.events
WHERE DATE(event_timestamp) = '2026-01-15' -- scans only one partition
AND event_type = 'purchase';
-- Set query limit per user
-- GCP Console → BigQuery → Settings → Max bytes billed
-- Column-level security
CREATE OR REPLACE FUNCTION analytics.mask_email(email STRING)
RETURNS STRING AS (
REGEXP_REPLACE(email, r'(.{2}).*(@.*)', r'\1***\2')
);
SELECT
user_id,
analytics.mask_email(email) AS masked_email
FROM analytics.users;
-- Row-level security
CREATE ROW ACCESS POLICY analytics.user_filter
ON analytics.events
GRANT TO ('user:analyst@example.com')
FILTER USING (user_id = SESSION_USER());
-- Audit logging
-- BigQuery audit logs are exported to Cloud Logging
-- Query: resource.type="bigquery_dataset" AND protoPayload.methodName="google.bigquery.v2.JobService.Insert"Frequently Asked Questions
What is the difference between BigQuery and Snowflake?
Both are serverless data warehouses. BigQuery is GCP-native with tight integration with Google Cloud services. Snowflake is cloud-agnostic with multi-cloud support. BigQuery charges per TB scanned; Snowflake charges per compute credit. Choose based on your cloud ecosystem.
Can BigQuery handle real-time analytics?
BigQuery supports streaming ingestion with Storage Write API for near-real-time analytics (seconds latency). For true real-time sub-second latency, use BigQuery BI Engine for dashboard queries or combine with Pub/Sub for event streaming.
What is the maximum table size BigQuery supports?
BigQuery supports tables up to petabytes. There is no practical limit for most organizations. Storage scales automatically. Partitioning is recommended for tables larger than 1 GB for optimal query performance.
How does BigQuery handle concurrent queries?
BigQuery manages concurrency automatically with on-demand pricing. For high-concurrency workloads, use flat-rate pricing with reserved slots. BI Engine provides sub-second response for dashboard queries with high concurrency.
Originally published on Ayodhyyya. Last updated June 1, 2026.