BigQuery Complete Guide: SQL, Datasets and Cost Optimization

Overview

This tutorial covers the core BigQuery concepts required for the Google Cloud Professional Data Engineer certification. You'll learn how to write efficient SQL queries, manage datasets effectively, and implement cost optimization strategies that directly impact production environments.

Prerequisites:

• Active Google Cloud Project with billing enabled
• Basic SQL knowledge (SELECT, WHERE, JOIN statements)
• gcloud CLI installed and configured
• Familiarity with Google Cloud Console

Why This Matters: BigQuery is a central component of the Data Engineer certification. Understanding query optimization and cost controls separates junior engineers from professionals—cloud costs are often the first metric leadership scrutinizes.

Getting Started

Before writing queries, authenticate your environment and set your default project.

gcloud auth login
gcloud config set project PROJECT_ID

Create your first dataset using the BigQuery API:

bq mk --dataset \
  --description "Tutorial dataset for learning" \
  --location=US \
  cloudninjas_tutorial

Verify the dataset was created:

bq ls -d --project_id=PROJECT_ID

Now load sample data. Create a simple table:

bq mk --table cloudninjas_tutorial.employees \
  employee_id:INTEGER,name:STRING,salary:FLOAT64,department:STRING,hire_date:DATE

BigQuery SQL: Query Optimization Fundamentals

Query optimization in BigQuery differs from traditional databases. BigQuery scans data column-by-column (columnar storage), so your query structure directly impacts both execution time and cost.

First Rule: Only Select Required Columns

-- BAD: Scans entire table
SELECT * FROM `project.dataset.large_table`;

-- GOOD: Only needed columns
SELECT employee_id, name, salary 
FROM `project.dataset.employees`
WHERE department = 'Engineering';

When you use SELECT *, BigQuery scans every column. With 100 columns, you're charged for scanning 100 columns even if you only need 5. This is critical for cost optimization.

Filtering Early with WHERE Clauses

-- Apply filters before joins
SELECT e.employee_id, e.name, d.dept_name
FROM `project.dataset.employees` e
JOIN `project.dataset.departments` d 
  ON e.department_id = d.id
WHERE e.hire_date >= '2023-01-01'
  AND d.location = 'Canada';

BigQuery's query optimizer is intelligent, but explicitly filtering reduces the working dataset size, improving performance on massive tables.

Partitioning and Clustering for Large Tables

-- Create a partitioned and clustered table
CREATE TABLE `project.dataset.transactions`
PARTITION BY DATE(transaction_date)
CLUSTER BY customer_id, transaction_type
AS
SELECT 
  transaction_id,
  customer_id,
  transaction_type,
  amount,
  transaction_date
FROM `project.dataset.raw_transactions`;

This table is now partitioned by date and clustered by customer and type. Queries filtering on these columns scan only relevant partitions, dramatically reducing costs on billion-row tables.

Advanced Dataset Management and Cost Control

Understanding Slots and On-Demand Pricing

BigQuery offers two pricing models: on-demand ($6.25/TB scanned in US) and annual slots (predefined capacity). For certification, understand when to recommend each:

• On-Demand: Sporadic workloads, variable query patterns, small teams
• Slots: Predictable, continuous workloads; cost-effective at scale (>150TB/month)

Implementing Dataset-Level Access Controls

-- Use dataset labels for cost tracking
bq update --set_iam_policy policy.json cloudninjas_tutorial

-- Example policy.json structure:
{
  "bindings": [
    {
      "role": "roles/bigquery.dataEditor",
      "members": ["group:data-engineers@company.com"]
    },
    {
      "role": "roles/bigquery.dataViewer",
      "members": ["group:analysts@company.com"]
    }
  ]
}

View Tables for Cost Attribution

-- Create a logical view without storing data
CREATE VIEW `project.dataset.active_employees` AS
SELECT employee_id, name, salary, department
FROM `project.dataset.employees`
WHERE active = TRUE
  AND termination_date IS NULL;

Views are zero-cost to store. Querying a view charges for the underlying table scan, enabling cost allocation by department or function.

Preventing Runaway Costs with Query Limits

-- Set maximum bytes scanned at project level
bq update --max_bytes_billed=1000000000 cloudninjas_tutorial

-- Or per-query in SQL
SELECT 
  employee_id, 
  name
FROM `project.dataset.employees`
LIMIT 10;
-- Use LIMIT to cap result rows, though this doesn't reduce scan cost

The max_bytes_billed parameter is your safety valve—queries exceeding this limit fail before incurring charges. Set this to match your monthly budget divided by expected query volume.

Real-World Example: E-Commerce Analytics

Let's build a cost-optimized analytics schema for an e-commerce company:

-- 1. Create partitioned, clustered orders table
CREATE TABLE `project.dataset.orders`
PARTITION BY DATE(order_date)
CLUSTER BY customer_id, region
AS
SELECT 
  order_id,
  customer_id,
  product_id,
  quantity,
  unit_price,
  ROUND(quantity * unit_price, 2) AS total,
  order_date,
  region,
  status
FROM `project.raw_data.orders_raw`;

-- 2. Create materialized view for daily sales dashboard
CREATE MATERIALIZED VIEW `project.dataset.daily_sales_summary`
PARTITION BY DATE(order_date)
CLUSTER BY region
AS
SELECT 
  DATE(order_date) AS order_date,
  region,
  COUNT(DISTINCT customer_id) AS unique_customers,
  SUM(total) AS daily_revenue,
  AVG(total) AS avg_order_value,
  COUNT(*) AS total_orders
FROM `project.dataset.orders`
WHERE status = 'completed'
GROUP BY order_date, region;

-- 3. Efficient regional analysis (scans only needed columns/partitions)
SELECT 
  region,
  SUM(daily_revenue) AS monthly_revenue,
  AVG(avg_order_value) AS avg_aov
FROM `project.dataset.daily_sales_summary`
WHERE order_date BETWEEN '2024-01-01' AND '2024-01-31'
GROUP BY region
ORDER BY monthly_revenue DESC;

This approach uses materialized views (refreshed nightly) for the dashboard, eliminating repeated scans of the 100GB raw orders table. Cost reduction: ~85% for daily reporting.

Exam Tips for Google Cloud Professional Data Engineer

Key Concepts You'll See:

• Partition Pruning: The exam tests whether you understand how partitioning eliminates full table scans. Know that DATE, TIMESTAMP, and INTEGER partitioning are common.
• Clustering vs. Partitioning: Partitioning divides data into separate storage; clustering sorts within partitions. Use both for tables >1TB.
• Slot Reservation Math: 100 slots = 100TB/month on-demand equivalent. Calculate when to recommend slots given annual spend projections.
• Nested and Repeated Fields: The exam may ask about STRUCT and ARRAY types. Know that deeply nested queries impact performance.

Common Exam Scenarios:

1. "Your data engineer team's BigQuery costs doubled. You're asked to optimize. What's your first step?" — Answer: Analyze query audit logs, identify SELECT * queries, implement column-level filtering and partitioning.
2. "A 50TB dataset must be queried by both analysts (read-only) and engineers (read-write). How do you structure IAM?" — Answer: Create datasets by function, use authorized views, and assign roles at dataset level.
3. "Your materialized view refreshes take 2 hours for a 5GB source table." — Answer: Implement incremental refreshes using MERGE statements instead of full refresh.

Quick Reference Commands:

-- Check table size and cost estimate
bq show -j query_job_id

-- Export results to GCS (cheaper than repeated queries)
bq extract --destination_format=PARQUET \
  `project.dataset.table` gs://bucket/path/file-*.parquet

-- Use EXPLAIN to see execution plan
EXPLAIN SELECT * FROM `project.dataset.table`;

Summary

Key Takeaways:

• BigQuery charges by data scanned, not rows returned. Always select specific columns.
• Partition large tables by date/region; cluster by high-cardinality filter columns.
• Use views and materialized views to reduce repeated scanning and improve query performance.
• Implement max_bytes_billed limits to prevent cost overruns.
• For the certification, master the trade-offs between on-demand and slot pricing based on workload patterns.

Next Steps:

1. Build a practice dataset in your Google Cloud Project using public datasets (bigquery-public-data)
2. Create a sample partition/cluster strategy for a billion-row table
3. Write a cost analysis report comparing three query approaches (unoptimized, indexed, materialized view)
4. Review the Google Cloud Professional Data Engineer Certification Guide for integrated study paths