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

✍️ 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

- 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
- A Google Cloud account (the free trial works) and a project.
- Open Cloud Shell (the
>_icon at the top right of the console). It hasbqand Python. Set your project:
export PROJECT_ID="your-project-id"
gcloud config set project $PROJECT_ID
gcloud services enable bigquery.googleapis.com- Make a folder
~/lab190with 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:
- One row per customer before the join:
QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id) = 1keeps one copy of each customer. - 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 |
+-----------------+------------+
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 |
+-------------+--------+------------+

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).
Step 5: a one-click Looker Studio link
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%20salesPart 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.
- 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.
- Open the link from Step 5, or click Create > Report, choose the BigQuery connector, then My projects > your project > shop_bi > daily_sales > Add.
- Scorecard: Insert > Scorecard, metric
revenue(Sum). Add a filter status equals delivered. It must show 4,590,467. - Time series: Insert > Time series, dimension
day(set its type to Year Month), metricrevenue, the same filter. Six points from 806,218 (May) to 848,193 (October). - Bar chart: dimension
city, metricrevenue, filter delivered, sorted descending: Pune first at 1,102,801. - Table: dimension
category, metricsordersand a calculated fieldreturn %=SUM(CASE WHEN status = "returned" THEN orders ELSE 0 END) / SUM(orders); Electronics is highest at 10.7 percent. - Add a Date range control and a Drop-down list on
cityso viewers can filter. - 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
- 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.)
- How do you make "orders per day" use India time? *(Answer:
DATE(order_ts, 'Asia/Kolkata')in the view.)* - 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.)
- 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.)
- 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
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 see | Why | Fix |
|---|---|---|
| Totals higher than the source | Fan-out from duplicate join keys | Deduplicate before joining; check the main total once |
| Daily numbers off by part of a day | Dates taken in UTC | DATE(ts, 'your/Time_Zone') in the view |
Access Denied: ... User does not have bigquery.jobs.create | The account has no BigQuery job permission | Grant BigQuery Job User and Data Viewer |
| Charts show old numbers | Looker Studio's cache | Data freshness setting, or Refresh data in the report |
Not found: Table ... was not found in location US | The view and tables are in different locations | Keep everything in one dataset location |
| A chart's total differs from Step 3 | A missing filter (status) or wrong aggregation | Compare the chart's dimension, metric and filters with the SQL |
What next
- SQL for every chart: BigQuery for Beginners: Query Billions of Rows for Free.
- Dashboard-ready data from logs: Logs to BigQuery: Analyse a Month of Logs with SQL.
- Read and cut your cloud bill with the same skills: FinOps for Beginners: Read and Cut Your Cloud Bill.
- More hands-on posts: ByteBlog. Practice in ByteLabs and see the courses.
Prepared by the CareerByteCode Team from our tested lab, with AI help for the write-up.
⭐ Keep learning with CareerByteCode
Hand-picked to go deeper on this topicGet 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.
Related blogs
CareerByteCode TeamSQL Interview Questions Practised on BigQuery
What you build: 3 small BigQuery tables and 25 classic SQL interview questions answered with real queries and results, plus the NOT IN NULL trap, two classic errors and the cost of SELECT *.
#beginners
CareerByteCode TeamBigQuery for Beginners: Query Billions of Rows for Free
What you build: SQL on a public dataset with millions of rows, then your own sales table with a view and a daily partition, in your own Google Cloud account. Every step tested.
#beginners
CareerByteCode TeamCloud SQL for PostgreSQL: Create, Connect and Back Up
What you build: a Cloud SQL for PostgreSQL 17 instance with private IP only, a database and user, real data, an on-demand backup and a real restore, plus the Auth Proxy. Every step tested.
#beginners
CareerByteCode TeamCloud Storage Deep Dive: Classes, Lifecycle and Signed URLs
What you build: a private bucket with storage classes, a lifecycle policy, object versions and expiring signed URLs, in your own Google Cloud account. Every step tested.
💬 Comments (0)
Top · NewestSign in to comment