CareerByteCode
ByteBlogby CareerByteCode

Looker Studio Dashboard on BigQuery Data

What you build: six months of shop data in BigQuery and a clean dashboard view, with two real dashboard bugs caught (a fan-out join and UTC dates), every chart number checked, and a one-click Looker S

Looker Studio Dashboard on BigQuery Data

✍️ Translate this blog into Hindi, Tamil or Telugu

Looker Studio is Google's free dashboard tool, and BigQuery is where the data usually lives. Connecting them takes a minute. Getting correct numbers on the dashboard is the real skill: the most common dashboard bugs are not in the charts, they are in the data underneath. In this lab you load six months of shop data into BigQuery, build the view a dashboard should use, and catch two classic bugs that make dashboards lie: a join that silently counts some sales twice, and dates shifted by the time zone. Then you work out the exact numbers every chart will show, check the cost, and connect Looker Studio with one link.

Who it is for: analysts, students and anyone asked to "make a dashboard from BigQuery". Time: about 1 hour. Cost: free. BigQuery's free tier covers this lab many times over (the data is 124 KB), and Looker Studio is free.

All BigQuery steps were run for real by the CareerByteCode Team on a fresh Google Cloud project, and every result is the real one we got. Honest note: the Looker Studio screens (Part C) need a one-time "Authorize" click in your own Google account, which we did not do in our automated lab account, so those clicks are not tested by us. They are short and standard, and the numbers you should see are the tested ones from Step 5.

⭐ Recommended by CareerByteCode · BytePlay courseNVIDIA GPU AI Factory: Architecture, Engineering & Operationsโ‚น40,000Start learning →

Part A: a dashboard in plain English

Dashboard in one picture
Raw tables, a clean view that the dashboard reads, charts that group the view, and viewers who see the result
  • Data source: the connection from Looker Studio to one BigQuery table or view. Every chart on a page uses a data source.
  • View: a saved query in BigQuery that looks like a table. A dashboard should read a clean view, not raw tables, so the cleaning rules live in one place and every chart agrees.
  • Dimension: what you group by (day, city, category). Metric: what you add up or count (revenue, orders).
  • Grain: what one row means. In our view, one row = one day + city + category + status. Getting the grain wrong is how numbers get counted twice.
  • Fan-out: when a join matches one row to several rows, its numbers are repeated. Real-life example: you invite 3 friends and one of them appears twice in your phone book, so you buy one meal too many.

Before you start

  1. A Google Cloud account (the free trial works) and a project.
  2. Open Cloud Shell (the >_ icon at the top right of the console). It has bq and Python. Set your project:
export PROJECT_ID="your-project-id"
gcloud config set project $PROJECT_ID
gcloud services enable bigquery.googleapis.com
  1. Make a folder ~/lab190 with this small script, which makes 6 months of orders for a made-up online shop (and, like many real exports, lists 3 customers twice):
# make_sales.py: 6 months of orders for a small online shop (orders.csv) and its customers (customers.csv).
# order_ts is in UTC, like most app databases. customers.csv has 3 customers listed twice (a CRM export bug).
import csv, random, datetime as dt
random.seed(5)
cities = ["Chennai", "Bengaluru", "Pune", "Kochi", "Hyderabad"]
cats = {"Books": (199, 899), "Electronics": (999, 9999), "Home": (299, 2999), "Fashion": (399, 3499)}
custs = [(i, f"Customer {i}", random.choice(cities)) for i in range(1, 301)]
with open("customers.csv", "w", newline="") as f:
    w = csv.writer(f); w.writerow(["customer_id", "name", "city"])
    for c in custs: w.writerow(c)
    for c in custs[:3]: w.writerow(c)          # the duplicates
start = dt.datetime(2026, 5, 1)
with open("orders.csv", "w", newline="") as f:
    w = csv.writer(f); w.writerow(["order_id", "customer_id", "order_ts", "category", "amount", "status"])
    for i in range(1, 2401):
        ts = start + dt.timedelta(minutes=random.randint(0, 183 * 24 * 60))
        cat = random.choice(list(cats)); lo, hi = cats[cat]
        w.writerow([i, random.randint(1, 300), ts.strftime("%Y-%m-%d %H:%M:%S"), cat, random.randint(lo, hi),
                    random.choice(["delivered"] * 8 + ["returned", "cancelled"])])
print("orders.csv: 2400 orders, customers.csv: 303 rows (300 customers, 3 listed twice)")

Part B: the data (tested)

Step 1: load the shop data into BigQuery

Why: bq mk makes a dataset (a folder for tables), bq load loads each CSV with a schema (name:TYPE). Order times are TIMESTAMP values in UTC, as in most app databases.

# Load 6 months of shop data into BigQuery: a dataset, then two tables from CSV files
python3 make_sales.py
bq --location=US mk --dataset --description "Shop data for a Looker Studio dashboard" shop_bi 2>&1 | tail -1
bq load --quiet --skip_leading_rows=1 shop_bi.orders orders.csv order_id:INT64,customer_id:INT64,order_ts:TIMESTAMP,category:STRING,amount:INT64,status:STRING
bq load --quiet --skip_leading_rows=1 shop_bi.customers customers.csv customer_id:INT64,name:STRING,city:STRING
bq query --quiet --use_legacy_sql=false --format=pretty "SELECT (SELECT COUNT(*) FROM shop_bi.orders) AS orders, (SELECT COUNT(*) FROM shop_bi.customers) AS customer_rows, (SELECT SUM(amount) FROM shop_bi.orders WHERE status = 'delivered') AS delivered_revenue"
orders.csv: 2400 orders, customers.csv: 303 rows (300 customers, 3 listed twice)
Dataset 'your-project-id:shop_bi' successfully created.
Upload complete.
Upload complete.
+--------+---------------+-------------------+
| orders | customer_rows | delivered_revenue |
+--------+---------------+-------------------+
|   2400 |           303 |           4590467 |
+--------+---------------+-------------------+

2,400 orders, 303 customer rows, and 4,590,467 of delivered revenue. Remember that number: it is the truth every dashboard total must match.

Break it: the obvious view counts too much

The first view most people write joins orders to customers to get each order's city. It works, the charts look fine, and nobody notices the total.

# Break it: the "obvious" dashboard view joins orders to customers to get the city. Check its total against the orders table
bq query --quiet --use_legacy_sql=false "CREATE OR REPLACE VIEW shop_bi.sales_naive AS SELECT DATE(o.order_ts) AS day, c.city, o.category, o.amount, o.status FROM shop_bi.orders o JOIN shop_bi.customers c USING (customer_id)" && echo "view sales_naive created"
bq query --quiet --use_legacy_sql=false --format=pretty "SELECT (SELECT SUM(amount) FROM shop_bi.sales_naive WHERE status = 'delivered') AS dashboard_total, (SELECT SUM(amount) FROM shop_bi.orders WHERE status = 'delivered') AS true_total"
bq query --quiet --use_legacy_sql=false --format=pretty "SELECT customer_id, COUNT(*) AS rows_in_customers FROM shop_bi.customers GROUP BY customer_id HAVING COUNT(*) > 1"
Created your-project-id.shop_bi.sales_naive

view sales_naive created
+-----------------+------------+
| dashboard_total | true_total |
+-----------------+------------+
|         4625360 |    4590467 |
+-----------------+------------+
+-------------+-------------------+
| customer_id | rows_in_customers |
+-------------+-------------------+
|           1 |                 2 |
|           2 |                 2 |
|           3 |                 2 |
+-------------+-------------------+

The dashboard total is 4,625,360, the true total is 4,590,467. That is 34,893 of revenue that never happened. The cause: customers 1, 2 and 3 are listed twice, so every one of their orders matches two customer rows and is counted twice (fan-out). No error, no warning: just a wrong number on the CEO's screen.

Fix it: the dashboard view

Why: two rules make this view safe:

  1. One row per customer before the join: QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id) = 1 keeps one copy of each customer.
  2. The business's time zone: DATE(order_ts, 'Asia/Kolkata') gives the day in India time, not in UTC.

It also pre-groups the data to one row per day + city + category + status, which keeps the dashboard fast and cheap.

# Fix it: one row per customer before the join, and the day in INDIA time (the data is UTC). This is the dashboard's data source
bq query --quiet --use_legacy_sql=false "CREATE OR REPLACE VIEW shop_bi.daily_sales AS
WITH customers AS (SELECT * FROM shop_bi.customers WHERE TRUE QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id) = 1)
SELECT DATE(o.order_ts, 'Asia/Kolkata') AS day, c.city, o.category, o.status, COUNT(*) AS orders, SUM(o.amount) AS revenue
FROM shop_bi.orders o JOIN customers c USING (customer_id) GROUP BY day, city, category, status" && echo "view daily_sales created"
bq query --quiet --use_legacy_sql=false --format=pretty "SELECT SUM(revenue) AS dashboard_total, (SELECT SUM(amount) FROM shop_bi.orders WHERE status = 'delivered') AS true_total FROM shop_bi.daily_sales WHERE status = 'delivered'"
Created your-project-id.shop_bi.daily_sales

view daily_sales created
+-----------------+------------+
| dashboard_total | true_total |
+-----------------+------------+
|         4590467 |    4590467 |
+-----------------+------------+
Fan-out bug and fix
The naive view shows 4,625,360; three customers are listed twice; the fixed view matches the true 4,590,467

The dashboard total now matches the orders table exactly. Always check a dashboard's main total against the source table once. It takes one query.

Step 2: why the time zone matters

Why: an order at 01:00 on 2 May in Chennai is 19:30 on 1 May in UTC. A view that uses DATE(order_ts) puts it on the wrong day.

# Why the time zone matters: orders placed between midnight and 05:30 in India have yesterday's date in UTC
bq query --quiet --use_legacy_sql=false --format=pretty "SELECT DATE(order_ts) AS utc_day, DATE(order_ts, 'Asia/Kolkata') AS india_day, COUNT(*) AS orders FROM shop_bi.orders WHERE DATE(order_ts) != DATE(order_ts, 'Asia/Kolkata') GROUP BY 1, 2 ORDER BY 1 LIMIT 3"
bq query --quiet --use_legacy_sql=false --format=pretty "SELECT COUNTIF(DATE(order_ts) != DATE(order_ts, 'Asia/Kolkata')) AS orders_on_a_different_day, COUNT(*) AS all_orders FROM shop_bi.orders"
+------------+------------+--------+
|  utc_day   | india_day  | orders |
+------------+------------+--------+
| 2026-05-01 | 2026-05-02 |      2 |
| 2026-05-02 | 2026-05-03 |      2 |
| 2026-05-03 | 2026-05-04 |      1 |
+------------+------------+--------+
+---------------------------+------------+
| orders_on_a_different_day | all_orders |
+---------------------------+------------+
|                       565 |       2400 |
+---------------------------+------------+

565 of 2,400 orders (24 percent) fall on a different day in UTC than in India. Daily charts, "today's sales" scorecards and month ends would all be off. That is why the view uses the business's own time zone.

Step 3: the numbers behind each chart

Why: before you build anything in Looker Studio, know what each chart must show. These are the same GROUP BY queries Looker Studio will run on the view; if a chart shows something else, the chart's settings are wrong, not the data.

# The numbers each dashboard chart will show (the same SQL Looker Studio runs for you)
echo "Scorecard: delivered revenue and orders"; bq query --quiet --use_legacy_sql=false --format=pretty "SELECT SUM(revenue) AS revenue, SUM(orders) AS orders FROM shop_bi.daily_sales WHERE status = 'delivered'"
echo "Time series: revenue per month"; bq query --quiet --use_legacy_sql=false --format=pretty "SELECT FORMAT_DATE('%Y-%m', day) AS month, SUM(revenue) AS revenue FROM shop_bi.daily_sales WHERE status = 'delivered' GROUP BY month ORDER BY month"
echo "Bar chart: revenue per city"; bq query --quiet --use_legacy_sql=false --format=pretty "SELECT city, SUM(revenue) AS revenue FROM shop_bi.daily_sales WHERE status = 'delivered' GROUP BY city ORDER BY revenue DESC"
echo "Table: return rate per category"; bq query --quiet --use_legacy_sql=false --format=pretty "SELECT category, SUM(orders) AS orders, ROUND(100 * SUM(IF(status = 'returned', orders, 0)) / SUM(orders), 1) AS return_pct FROM shop_bi.daily_sales GROUP BY category ORDER BY return_pct DESC"
Scorecard: delivered revenue and orders
+---------+--------+
| revenue | orders |
+---------+--------+
| 4590467 |   1932 |
+---------+--------+
Time series: revenue per month
+---------+---------+
|  month  | revenue |
+---------+---------+
| 2026-05 |  806218 |
| 2026-06 |  635979 |
| 2026-07 |  809442 |
| 2026-08 |  768743 |
| 2026-09 |  721892 |
| 2026-10 |  848193 |
+---------+---------+
Bar chart: revenue per city
+-----------+---------+
...
+-----------+---------+
Table: return rate per category
+-------------+--------+------------+
|  category   | orders | return_pct |
+-------------+--------+------------+
| Electronics |    605 |       10.7 |
| Home        |    629 |       10.3 |
| Books       |    589 |        9.3 |
| Fashion     |    577 |        7.6 |
+-------------+--------+------------+
Chart numbers
The scorecard, revenue per month, revenue per city and return rate per category, from the dashboard view
Dashboard preview
A preview drawn from these tested numbers: the scorecard, time series, table and bar chart your Looker Studio page should show

Step 4: keep the dashboard cheap

Why: every chart on every page view runs a query. A dry run shows how many bytes one chart reads, without running it.

# Keep the dashboard cheap: how many bytes does one chart's query scan? (a dry run costs nothing)
bq query --use_legacy_sql=false --dry_run "SELECT city, SUM(revenue) FROM shop_bi.daily_sales WHERE status = 'delivered' GROUP BY city" 2>&1 | grep -o "process [0-9]* bytes"
bq show --format=prettyjson shop_bi.orders | grep -E '"numBytes"|"numRows"'
process 69617 bytes
  "numBytes": "123926",
  "numRows": "2400",
🧪⭐ Recommended by CareerByteCode · ByteLabs project bundleCloud and DevOps Realtime Starterโ‚น499Build the projects →

One chart reads 69,617 bytes (70 KB). Even thousands of page views a month stay deep inside the free terabyte. On big tables: pre-aggregate in a view or table (as we did), partition by date, and turn on Looker Studio's data cache (Resource > Manage added data sources > Edit > Data freshness).

Why: the Looker Studio Linking API builds a new report connected to your view straight from a URL. Replace your-project-id with your project id and open it in the browser where you are signed in to Google:

# A one-click link that opens Looker Studio with this view already connected (the Looker Studio Linking API)
echo "https://lookerstudio.google.com/reporting/create?ds.ds0.connector=bigQuery&ds.ds0.type=TABLE&ds.ds0.projectId=$PROJECT_ID&ds.ds0.datasetId=shop_bi&ds.ds0.tableId=daily_sales&r.reportName=Shop%20sales" | sed "s/$PROJECT_ID/your-project-id/"
https://lookerstudio.google.com/reporting/create?ds.ds0.connector=bigQuery&ds.ds0.type=TABLE&ds.ds0.projectId=your-project-id&ds.ds0.datasetId=shop_bi&ds.ds0.tableId=daily_sales&r.reportName=Shop%20sales

Part C: build the dashboard in Looker Studio (not tested by us)

These are the standard Looker Studio steps. We have not run them in this lab (see the honest note at the top); check every number against Step 3.

  1. Open https://lookerstudio.google.com and sign in with the Google account that can read your project's BigQuery data. The first time, Looker Studio asks you to Authorize it to use your account.
  2. Open the link from Step 5, or click Create > Report, choose the BigQuery connector, then My projects > your project > shop_bi > daily_sales > Add.
  3. Scorecard: Insert > Scorecard, metric revenue (Sum). Add a filter status equals delivered. It must show 4,590,467.
  4. Time series: Insert > Time series, dimension day (set its type to Year Month), metric revenue, the same filter. Six points from 806,218 (May) to 848,193 (October).
  5. Bar chart: dimension city, metric revenue, filter delivered, sorted descending: Pune first at 1,102,801.
  6. Table: dimension category, metrics orders and a calculated field return % = SUM(CASE WHEN status = "returned" THEN orders ELSE 0 END) / SUM(orders); Electronics is highest at 10.7 percent.
  7. Add a Date range control and a Drop-down list on city so viewers can filter.
  8. Share: click Share and add people. Under Resource > Manage added data sources > Edit, the data credentials decide whose BigQuery access is used: Owner's credentials (viewers need no BigQuery access) or Viewer's credentials (each viewer needs BigQuery read access).

POC: prove it works

  • 6 months of orders loaded into BigQuery; the true delivered revenue is 4,590,467.
  • A naive join view reported 4,625,360 because 3 customers were listed twice (fan-out); the fixed view reports exactly 4,590,467.
  • 565 of 2,400 orders fall on a different day in UTC than in India; the view uses India time.
  • The exact numbers for the scorecard, time series, bar chart and table are known before the dashboard is built.
  • One chart's query reads about 70 KB.

Quiz task: check what you learned

  1. Your dashboard total is higher than the source table. First suspect? (Answer: a join that fans out: a duplicate key on the other side of the join.)
  2. How do you make "orders per day" use India time? *(Answer: DATE(order_ts, 'Asia/Kolkata') in the view.)*
  3. Why should charts read a view instead of the raw tables? (Answer: cleaning rules live in one place, every chart agrees, and pre-grouped data is cheaper.)
  4. A viewer sees "data set configuration error". What is likely wrong? (Answer: the data source uses Viewer's credentials and that viewer has no BigQuery access.)
  5. How do you check one chart's cost? *(Answer: run its query with --dry_run.)*

Clean up (delete everything)

Why: delete the dataset (tables and views). If you built the Looker Studio report, delete it from Looker Studio's home page too (it would show errors once the data is gone).

# Clean up: delete the dataset (tables and views)
bq rm -r -f -d shop_bi && echo "dataset shop_bi deleted"
rm -f orders.csv customers.csv
echo "datasets left: $(bq ls --format=json 2>/dev/null | python3 -c 'import json,sys; print(len(json.load(sys.stdin) or []))' 2>/dev/null || echo 0)"
dataset shop_bi deleted
datasets left: 0
Clean up
Dry run cost, the one-click link, and the dataset deleted

FAQ

Is Looker Studio really free? Yes, Looker Studio is free. The BigQuery queries it runs are billed to the project as normal (inside the free tier for small data). Looker Studio Pro is a paid version with team features.

Looker Studio or Looker? Looker Studio (this lab) is the free drag-and-drop tool. Looker is a separate, paid enterprise BI platform with a modelling language (LookML).

How fresh is the data? Looker Studio caches BigQuery results (12 hours by default for BigQuery). Set Data freshness on the data source to every 15 minutes or every hour if you need fresher numbers.

Can I use a custom SQL query instead of a view? Yes (choose Custom query in the BigQuery connector), but a view is easier to review, reuse and fix in one place.

Why not just use a spreadsheet? For a few hundred rows, fine. For millions of rows, many viewers and numbers that must match the source exactly, BigQuery plus a dashboard scales and stays consistent.

How do I stop one viewer from seeing other cities? Use BigQuery row-level security on the view's tables, and Viewer's credentials in Looker Studio, so each person only reads the rows they are allowed to.

Troubleshooting

You seeWhyFix
Totals higher than the sourceFan-out from duplicate join keysDeduplicate before joining; check the main total once
Daily numbers off by part of a dayDates taken in UTCDATE(ts, 'your/Time_Zone') in the view
Access Denied: ... User does not have bigquery.jobs.createThe account has no BigQuery job permissionGrant BigQuery Job User and Data Viewer
Charts show old numbersLooker Studio's cacheData freshness setting, or Refresh data in the report
Not found: Table ... was not found in location USThe view and tables are in different locationsKeep everything in one dataset location
A chart's total differs from Step 3A missing filter (status) or wrong aggregationCompare the chart's dimension, metric and filters with the SQL

What next

Prepared by the CareerByteCode Team from our tested lab, with AI help for the write-up.

Read next →SQL Interview Questions Practised on BigQuery⏱ 19 min

⭐ Keep learning with CareerByteCode

Hand-picked to go deeper on this topic
News, guides and updates from the CareerByteCode team
🏅 Expert writer

The CareerByteCode team writes about what is new on careerbytecode.in: courses, labs, exams, events and the community, which is free to join for influencers and content creators in tech.

Get CareerByteCode Team's next blog by email

One email a week when they publish. Add the weekly Best of ByteBlog too. No spam, unsubscribe with one click.

We email you once to confirm. By subscribing you agree to the privacy policy.

💬 Comments (0)

Top · Newest

Sign in to comment

Related blogs

⭐ Recommended by CareerByteCode · BytePlay courseNVIDIA GPU AI Factory: Architecture, Engineering & Operationsโ‚น40,000Start learning →