Skip to content
Tutorials Center
My learning

Topics

Lesson 41 of 45 ยท Projects

Project: Log and Sales Analytics in BigQuery

Load a CSV into BigQuery, create a partitioned and clustered table, write analytics queries, control cost and connect a dashboard, with runnable SQL.

BigQuery is Google Cloud's serverless data warehouse: you load data, write SQL, and pay for the data your queries read (on-demand) or for reserved capacity. In this project you build a small analytics warehouse for sales data: upload a CSV, load it into a table, rebuild it as a partitioned and clustered table, answer business questions with SQL, keep the cost under control and connect a dashboard.

The BigQuery commands need a project, so they are read-only. The queries themselves are standard SQL, and the same ideas run in the SQL editor below on a tiny SQLite database, so you can practice the queries here. SQLite is not BigQuery: there is no partitioning, no bytes scanned, and the date functions differ, and the lesson points out where.

The warehouse workflow

Load CSV from a bucket into a table.
Shape Partition and cluster it.
Query Answer questions in SQL.
Show A dashboard on top.

Cost control runs through every step.

1 / 2

Step 0: Set up and sample data

Make a dataset (a folder of tables in one location) and a bucket, and write down the location: tables and the bucket you load from should live in the same location. See BigQuery basics for datasets and pricing.

Terminal
export PROJECT_ID=my-project-id
export LOCATION=US

gcloud config set project $PROJECT_ID
gcloud services enable bigquery.googleapis.com

bq --location=$LOCATION mk --dataset $PROJECT_ID:shop
gcloud storage buckets create gs://my-sales-data-12345 --location=$LOCATION

The sample file sales.csv has a header row and one line per order line:

TEXT
order_id,order_ts,country,product,quantity,unit_price
1001,2026-03-01 09:15:00,US,keyboard,1,49.90
1002,2026-03-01 11:02:00,DE,mouse,2,19.50
1003,2026-03-02 08:40:00,US,monitor,1,189.00
Terminal
gcloud storage cp sales.csv gs://my-sales-data-12345/sales.csv

Step 1: Load the CSV

Terminal
bq load \
  --source_format=CSV \
  --skip_leading_rows=1 \
  --schema=order_id:INT64,order_ts:TIMESTAMP,country:STRING,product:STRING,quantity:INT64,unit_price:NUMERIC \
  shop.sales_raw \
  gs://my-sales-data-12345/sales.csv

Loading from Cloud Storage is free of query charges (you pay for storage). Always give an explicit schema for production loads; automatic detection can guess wrong when the first rows are unusual. Bad rows stop the load unless you allow some with --max_bad_records.

Step 2: A partitioned and clustered table

A partitioned table splits the data by a column (here the day of the order) so a query that filters on dates reads only the matching partitions. Clustering sorts data within partitions by columns you filter on often, so BigQuery can skip blocks. Both reduce the bytes scanned and therefore the on-demand cost (see partitioning, clustering and cost control).

SQL
CREATE TABLE shop.sales
PARTITION BY DATE(order_ts)
CLUSTER BY country, product
AS
SELECT * FROM shop.sales_raw;

For a table that grows every day, require a partition filter so nobody can accidentally scan all of history:

SQL
ALTER TABLE shop.sales
SET OPTIONS (require_partition_filter = TRUE);

Partition expiration is another useful dial for logs: partition_expiration_days = 90 deletes old partitions automatically.

Step 3: Queries that answer questions

Here are the questions a sales team asks, in BigQuery standard SQL. Notice that every query selects named columns and filters on the partition column; never use SELECT * on a big table.

SQL
-- Revenue per day for March
SELECT DATE(order_ts) AS day, SUM(quantity * unit_price) AS revenue
FROM shop.sales
WHERE DATE(order_ts) BETWEEN '2026-03-01' AND '2026-03-31'
GROUP BY day
ORDER BY day;

-- Top products per country
SELECT country, product, SUM(quantity) AS units
FROM shop.sales
WHERE DATE(order_ts) >= '2026-03-01'
GROUP BY country, product
QUALIFY ROW_NUMBER() OVER (PARTITION BY country ORDER BY SUM(quantity) DESC) <= 3;

QUALIFY filters on window functions and is a BigQuery convenience that many other databases lack. For log analytics the same pattern works on exported Cloud Logging data, which a log sink can write into BigQuery automatically; you then group by severity, service and hour.

Step 4: Cost control

Before running a big query, do a dry run: it validates the SQL and reports how many bytes it would read, without running or charging anything.

Terminal
bq query --use_legacy_sql=false --dry_run \
  'SELECT country, SUM(quantity) FROM shop.sales WHERE DATE(order_ts) = "2026-03-01" GROUP BY country'

Other safeguards: set --maximum_bytes_billed on queries so an expensive one fails instead of billing, set a project-level quota on bytes per day, keep dashboards pointing at small summary tables rather than raw data, and review the cost management lesson for budgets and exports. Prices change, so read the current pricing page.

Step 5: A summary table and a dashboard

Dashboards re-run their queries whenever someone opens them, so give them a compact, pre-aggregated table. A scheduled query can refresh it every night:

SQL
CREATE OR REPLACE TABLE shop.daily_revenue AS
SELECT DATE(order_ts) AS day, country, SUM(quantity * unit_price) AS revenue
FROM shop.sales
WHERE DATE(order_ts) >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
GROUP BY day, country;

In Looker Studio, choose the BigQuery connector, select shop.daily_revenue, and add a time-series chart (day, revenue) and a table by country. Share the report with people rather than giving them the table; their viewer access is handled by the report's data credentials, and viewers do not need BigQuery roles when the report uses the owner's credentials.

Give anything that queries BigQuery from code a service account with roles/bigquery.dataViewer on the dataset and roles/bigquery.jobUser on the project, not Editor.

Cleanup

Terminal
bq rm -r -f -d $PROJECT_ID:shop
gcloud storage rm --recursive gs://my-sales-data-12345

Storage in BigQuery is cheap but not free, and an unattended scheduled query keeps running (and costing) until you delete it.

Practice the queries (SQLite stand-in)

This editor creates the same shape of data and runs the revenue and top-products queries. Differences from BigQuery: there is no PARTITION BY, so the table is plain; dates are text and we use date(); and QUALIFY does not exist in SQLite, so the top-products query uses a subquery with ROW_NUMBER(). The real engine and cost model are not simulated.

SQL Stand-in: sales analytics with SQL

Press Run (or Ctrl + Enter) to see the result here.

The first result shows three days of revenue, and the second shows the best-selling product per country. Try adding a WHERE country = 'DE' filter, or change the rank limit to 2.

A dashboard summary table (SQLite stand-in)

This second editor builds the summary table from Step 5 and queries it the way a dashboard chart would. It is the same idea in a smaller engine.

SQL Stand-in: a pre-aggregated table for a dashboard

Press Run (or Ctrl + Enter) to see the result here.

In BigQuery the dashboard would scan the small daily_revenue table instead of the raw sales, which is the habit that keeps repeated views cheap.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does a partitioned table let a query do?

    Filtering on the partition column prunes partitions, reducing bytes scanned and on-demand cost.
  2. 2. What does a dry run do?

    Use it to estimate cost before running a query on a large table.
  3. 3. Why should dashboards read a summary table?

    Each dashboard view triggers queries. Small summary tables mean far fewer bytes scanned.
  4. 4. Why give a load an explicit schema in production?

    Explicit schemas make loads predictable and reproducible.

Summary

  • Load files from Cloud Storage with bq load, then rebuild them as a partitioned, clustered table.
  • Filter on the partition column, select only needed columns, and dry-run expensive queries.
  • Use require_partition_filter, maximum_bytes_billed and partition expiration as guard rails.
  • Point dashboards at small summary tables refreshed by scheduled queries, and delete scheduled queries and datasets when you finish.

Next: Project: A Three-Tier App with Terraform