BigQuery for Analytics

By the end of this lesson you will be able to create a BigQuery dataset in India, build a partitioned table, answer business questions with SQL, and check how much data a query will scan before you run it. BigQuery is one of Google Cloud's most loved services, and SQL skills transfer directly.

What is BigQuery?

BigQuery is a serverless data warehouse: a database built for analysis, not for running an app. You load large amounts of data and ask questions in SQL, such as "total fees by city last quarter". There are no servers to size. Google spreads each query across many machines.

The difference from Cloud SQL is like the difference between a shop's billing counter and its accountant. The counter (Cloud SQL) handles one bill at a time, very fast. The accountant (BigQuery) reads the whole year's bills at the end of the month to find patterns.

BigQuery stores data by column, not by row. A query that reads only city and amount touches only those two columns. This makes analysis fast, and it affects cost, as you will see.

Key words:

• Dataset: a container for tables, created in one location, such as asia-south1.
• Table: rows and typed columns, written as project.dataset.table.
• GoogleSQL: BigQuery's SQL dialect, close to standard SQL. If you know the SQL tutorial, you already know most of it.

Step by step: build a small fee warehouse

Step 1. Create a dataset in Mumbai with the bq tool (installed with gcloud and in Cloud Shell). Data in this dataset stays in that location:

bq --location=asia-south1 mk --dataset jaipur-coaching-dev:coaching_analytics

Dataset 'jaipur-coaching-dev:coaching_analytics' successfully created.

Step 2. In the BigQuery console's query editor, create a table partitioned by month and clustered by city, then add ten payments:

CREATE TABLE coaching_analytics.fee_payments (
  student STRING,
  city    STRING,
  course  STRING,
  amount  INT64,
  paid_on DATE
)
PARTITION BY DATE_TRUNC(paid_on, MONTH)
CLUSTER BY city;

INSERT INTO coaching_analytics.fee_payments (student, city, course, amount, paid_on)
VALUES
  ('Aarav',  'Rohtak',  'JEE',  4500, DATE '2026-08-05'),
  ('Priya',  'Jaipur',  'NEET', 5000, DATE '2026-08-07'),
  ('Kabir',  'Jaipur',  'JEE',  4500, DATE '2026-08-10'),
  ('Meera',  'Kochi',   'NEET', 5000, DATE '2026-09-02'),
  ('Aarav',  'Rohtak',  'JEE',  4500, DATE '2026-09-05'),
  ('Rohan',  'Lucknow', 'JEE',  4500, DATE '2026-09-12'),
  ('Ananya', 'Pune',    'CUET', 3000, DATE '2026-09-15'),
  ('Priya',  'Jaipur',  'NEET', 5000, DATE '2026-10-07'),
  ('Farhan', 'Lucknow', 'CUET', 3000, DATE '2026-10-09'),
  ('Isha',   'Pune',    'NEET', 5000, DATE '2026-10-20');

Partitioning splits the table into monthly pieces, so a query for October reads only October. Clustering sorts data inside each partition by city, so filters on city read less. Real warehouses load data in bulk from Cloud Storage files (bq load) or streams, not row by row.

Step 3. Ask a first question: total fees by city, highest first. Run it from Cloud Shell with CSV output:

bq query --use_legacy_sql=false --format=csv '
SELECT city, COUNT(*) AS payments, SUM(amount) AS total
FROM coaching_analytics.fee_payments
GROUP BY city
ORDER BY total DESC, city'

city,payments,total
Jaipur,3,14500
Rohtak,2,9000
Pune,2,8000
Lucknow,2,7500
Kochi,1,5000

bq may first print a "Waiting on bqjob..." progress line. The table above is the result. --use_legacy_sql=false selects GoogleSQL; always include it.

Dates and window functions

From here on, results are shown in the CSV form that bq query --format=csv prints; the console shows the same values in a grid. BigQuery has rich date functions. This query groups by month, shows a readable label, and adds a running total with a window function, which looks at other rows without collapsing them:

SELECT
  FORMAT_DATE('%b %Y', DATE_TRUNC(paid_on, MONTH)) AS month,
  COUNT(*) AS payments,
  SUM(amount) AS total,
  SUM(SUM(amount)) OVER (ORDER BY DATE_TRUNC(paid_on, MONTH)) AS running_total
FROM coaching_analytics.fee_payments
GROUP BY DATE_TRUNC(paid_on, MONTH)
ORDER BY DATE_TRUNC(paid_on, MONTH);

month,payments,total,running_total
Aug 2026,3,14000,14000
Sep 2026,4,17000,31000
Oct 2026,3,13000,44000

Now find the top-paying student in each course. QUALIFY filters on a window function result, a handy BigQuery feature that saves a subquery:

SELECT course, student, SUM(amount) AS paid
FROM coaching_analytics.fee_payments
GROUP BY course, student
QUALIFY RANK() OVER (PARTITION BY course ORDER BY SUM(amount) DESC) = 1
ORDER BY course, student;

course,student,paid
CUET,Ananya,3000
CUET,Farhan,3000
JEE,Aarav,9000
NEET,Priya,10000

Ananya and Farhan tie in CUET, so RANK() gives both rank 1. Use ROW_NUMBER() if you want exactly one row per course.

How BigQuery charges, and how to stay safe

BigQuery separates storage (data kept) from compute (queries run). For compute there are two pricing models:

• On-demand: you pay by the amount of data each query scans.
• Capacity: you reserve processing power, measured in slots, through BigQuery editions (Standard, Enterprise, Enterprise Plus).

There is a monthly free allowance for queries and storage, and a BigQuery sandbox that works without a billing account (with limits, such as tables that expire). Check the official BigQuery pricing page for current details.

Because on-demand cost follows bytes scanned, check before you run. A dry run validates the query and reports the bytes it would process, for free:

bq query --use_legacy_sql=false --dry_run \
  'SELECT city, amount FROM coaching_analytics.fee_payments'

Query successfully validated. Assuming the tables are not modified, running this query will process 157 bytes of data.

The console shows the same estimate at the top right of the editor before you click Run.

More you will use later

• Public datasets in the project bigquery-public-data are great for practice.
• Scheduled queries refresh summary tables every night.
• Views save a query under a name; materialised views also store results for speed.
• Looker Studio and Connected Sheets turn query results into dashboards and spreadsheets.
• BigQuery ML trains simple machine learning models with CREATE MODEL in SQL.

Common mistakes

• SELECT * on huge tables. You pay for every column scanned, even with LIMIT 10.
• Forgetting the location. A dataset in US stores data in the United States. Choose asia-south1 or asia-south2 for Indian data.
• Using BigQuery as an app database. It is built for analysis, not thousands of tiny updates per second.
• Leaving out --use_legacy_sql=false in old scripts. Legacy SQL has different syntax.

Key points

• BigQuery is a serverless, column-based data warehouse queried with GoogleSQL.
• Datasets have a fixed location; choose Indian regions for data that must stay in India.
• Partitioning and clustering reduce the data each query reads.
• Window functions and QUALIFY answer ranking and running-total questions neatly.
• On-demand queries are charged by bytes scanned; use dry runs, column selection and quotas.

SELECT
  course,
  COUNT(DISTINCT student) AS students,
  SUM(amount) AS total,
  ROUND(AVG(amount), 2) AS avg_payment
FROM coaching_analytics.fee_payments
GROUP BY course
ORDER BY total DESC;

course,students,total,avg_payment
NEET,3,20000,5000.0
JEE,3,18000,4500.0
CUET,2,6000,3000.0