Data Analytics Pipeline on Google Cloud

Migrate, transform, and visualize financial data using BigQuery and Spark.

Introduction

30 Second Summary

Every time you tap your card or transfer money, a data pipeline behind the scenes stores, transforms, and monitors that transaction. Building these pipelines is one of the most in-demand skills in cloud engineering.

In this project, you will build a complete data analytics pipeline on Google Cloud for financial transaction data. Starting with raw CSV files, you will migrate data into BigQuery, optimize it for fast queries, process it with PySpark, and visualize the results in an interactive dashboard.

What You'll Build

You will use BigQuery, Cloud Storage, Serverless Spark, and Looker Studio to build an end-to-end pipeline that ingests, transforms, analyzes, and visualizes financial transaction data.

By the end of this project, you'll have:

  • BigQuery datasets with raw and optimized financial transaction tables using partitioning and clustering.
  • SQL analytics queries that uncover transaction patterns using aggregations, window functions, and CTEs.
  • A PySpark notebook on Serverless Spark and a shareable Looker Studio dashboard with interactive charts and filters.
  • A secret mission to build a fraud detection view that flags statistically anomalous transactions.

Are there any prerequisites?

You need a Google account, a web browser, and a credit or debit card for GCP free trial verification (you will not be charged). All tools in this project are browser-based, so no local installations are required.

Set Up Your GCP Account and Project

Every data analytics pipeline needs cloud infrastructure before you can start working with data. In this step, you will set up your Google Cloud environment from scratch.

You will create a free trial account, configure a dedicated project with the right APIs enabled, and prepare a Cloud Storage bucket to hold your raw data files.

In this step, get ready to:
  • Create your GCP free trial account with $300 in credits.
  • Set up a project and enable the required APIs.
  • Create a Cloud Storage bucket for storing raw data files.
Create your GCP free trial account
  • Open cloud.google.com/free in your browser.
  • Click Get started for free.
  • Sign in with your Google account.
  • Fill out the required account information, including your country and a credit or debit card for verification.
  • Accept the terms and complete the signup process.

What is the GCP free trial?

The free trial gives you $300 in credits valid for 90 days. Your credit card is used for identity verification only.

You will not be charged unless you manually upgrade to a paid account after the trial ends.

Pro tip: already have an account?

If you already have a Google Cloud account, skip the free trial signup and jump straight to the next substep. You can create a new project inside your existing account.

Create a project and enable APIs

Google Cloud organizes resources inside projects. You need a dedicated project for this pipeline so all your datasets, buckets, and Spark jobs live in one place.

  • In the Google Cloud Console, click the project dropdown near the top-left of the page.
  • Click New Project.
  • Enter data-analytics-pipeline in the Project name field.
  • Click Create.
  • Click the project dropdown again and select data-analytics-pipeline to switch to your new project.

Cloud Shell is a browser-based terminal built into the Google Cloud Console. It comes pre-installed with the SDKs and tools you need for this project, so there is nothing to install locally.

  • Click the Activate Cloud Shell button (the terminal icon) in the top-right toolbar of the console.
  • Wait for Cloud Shell to finish loading. You will see a command prompt when it is ready.
  • Enable the required APIs by running these commands:
gcloud services enable bigquery.googleapis.com
gcloud services enable storage.googleapis.com
gcloud services enable dataproc.googleapis.com

What do these APIs do?

  • bigquery.googleapis.com enables BigQuery, the serverless data warehouse where you will store and query your financial transactions.
  • storage.googleapis.com enables Cloud Storage, where you will upload raw CSV files before loading them into BigQuery.
  • dataproc.googleapis.com enables Dataproc, which powers Serverless Spark. You will use it later to process data with PySpark transformations.

Each command prints a confirmation message when the API is enabled successfully.

Seeing a permission error?

  • Make sure you selected the data-analytics-pipeline project from the project dropdown at the top of the console. Running commands against the wrong project is a common mistake.
  • If Cloud Shell is unresponsive, click the three-dot menu in the Cloud Shell toolbar and select Restart.
  • Help me troubleshoot a permission error when enabling GCP APIs.
Create a Cloud Storage bucket

Your pipeline needs a place to store raw data files before loading them into BigQuery. A Cloud Storage bucket serves as this staging area.

  • In Cloud Shell, create a storage bucket by running these commands:
export PROJECT_ID=$(gcloud config get-value project)
gcloud storage buckets create gs://financial-data-${PROJECT_ID} --location=US

What do these commands do?

  • The first command stores your project ID in a variable called PROJECT_ID. This lets you reuse it in other commands throughout the project.
  • The second command creates a Cloud Storage bucket named financial-data- followed by your project ID. Bucket names must be globally unique across all of Google Cloud, so appending your project ID ensures a unique name.
  • Verify your bucket was created by running:
gcloud storage ls

You should see your bucket listed in the output, something like gs://financial-data-your-project-id/.

Bucket creation failed?

  • If you see AccessDeniedException, confirm that billing is enabled on your project. The free trial should handle this automatically, but you can check under Billing in the Navigation menu.
  • If you see HTTPError 409: Conflict, the bucket name is already taken. This is rare when using your project ID. Try re-running the export command first to make sure PROJECT_ID is set correctly.
  • Help me fix a Cloud Storage bucket creation error.

Your cloud environment is ready. Next up, you will generate a synthetic financial dataset and migrate it into BigQuery.

Migrate Financial Data into BigQuery

Your GCP project is ready with BigQuery, Cloud Storage, and Serverless Spark APIs enabled. Now it is time to get data into BigQuery so you can start analyzing it.

You will follow the ELT (Extract, Load, Transform) pattern. First, you generate a synthetic financial transaction dataset. Then you stage it in Cloud Storage and load it into BigQuery, where you can transform and analyze it later.

In this step, get ready to:
  • Generate a synthetic financial transaction dataset with 5,000 records.
  • Upload the CSV file to your Cloud Storage bucket.
  • Load the data into a BigQuery table using SQL.
Generate a synthetic financial transaction dataset

Instead of using a public dataset, you will generate your own synthetic data. This gives you full control over the schema and lets you practice the data preparation step that many real-world pipelines require.

  • In Cloud Shell, create a new Python file by opening it in the nano text editor:
nano generate_transactions.py
  • Paste the following script into the editor:
import csv
import random
from datetime import datetime, timedelta

# Set seed for reproducible results
random.seed(42)

# Define merchant categories and their associated stores
merchants = {
    'GROCERIES': ['Whole Foods', 'Trader Joes', 'Safeway', 'Kroger'],
    'DINING': ['Starbucks', 'Chipotle', 'McDonalds', 'Olive Garden'],
    'TRANSPORT': ['Uber', 'Lyft', 'Shell Gas', 'BP Gas'],
    'UTILITIES': ['Electric Co', 'Water Works', 'Internet Corp', 'Phone Plus'],
    'SHOPPING': ['Amazon', 'Target', 'Walmart', 'Best Buy'],
    'TRANSFER': ['Internal Transfer', 'Wire Transfer', 'ACH Transfer', 'Bank Transfer']
}

# Map each category to a payment method
transaction_types = {
    'GROCERIES': 'CARD',
    'DINING': 'CARD',
    'TRANSPORT': 'CARD',
    'UTILITIES': 'WIRE',
    'SHOPPING': 'CARD',
    'TRANSFER': 'WIRE'
}

# Create 50 unique account IDs and a 2-year date range
accounts = [f'ACCT-{str(i).zfill(4)}' for i in range(1, 51)]
start_date = datetime(2023, 1, 1)
end_date = datetime(2024, 12, 31)

# Generate 5,000 synthetic transactions
rows = []
for i in range(1, 5001):
    category = random.choice(list(merchants.keys()))
    merchant = random.choice(merchants[category])
    txn_type = transaction_types[category]
    days_offset = random.randint(0, (end_date - start_date).days)
    txn_date = start_date + timedelta(days=days_offset)

    # Set realistic amounts based on category
    if category == 'TRANSFER':
        amount = round(random.uniform(500, 10000), 2)
    elif category == 'UTILITIES':
        amount = round(random.uniform(50, 300), 2)
    else:
        amount = round(random.uniform(5, 500), 2)

    # Randomly assign debit or credit direction
    direction = 'DEBIT' if category != 'TRANSFER' or random.random() > 0.5 else 'CREDIT'

    rows.append({
        'transaction_id': f'TXN-{str(i).zfill(6)}',
        'account_id': random.choice(accounts),
        'transaction_date': txn_date.strftime('%Y-%m-%d'),
        'transaction_type': txn_type,
        'amount': amount,
        'merchant_name': merchant,
        'category': category,
        'direction': direction
    })

# Write all transactions to a CSV file
with open('transactions.csv', 'w', newline='') as f:
    writer = csv.DictWriter(f, fieldnames=[
        'transaction_id', 'account_id', 'transaction_date',
        'transaction_type', 'amount', 'merchant_name',
        'category', 'direction'
    ])
    writer.writeheader()
    writer.writerows(rows)

print(f'Generated {len(rows)} transactions in transactions.csv')

What does this script do?

  • It creates 50 fake bank accounts and defines six spending categories (Groceries, Dining, Transport, Utilities, Shopping, Transfer) with realistic merchants for each.
  • It generates 5,000 transactions spread across two years (2023-2024), with amounts that vary by category. Transfers are $500-$10,000 while dining might be $5-$500.
  • It writes everything to a CSV file with eight columns that you will later load into BigQuery.
  • Save the file by pressing **Ctrl+O**, then press **Enter** to confirm the filename.
  • Exit nano by pressing **Ctrl+X**.
  • Run the script to generate your dataset:
python3 generate_transactions.py

You should see Generated 5000 transactions in transactions.csv in the output.

  • Preview the first few lines of the generated file:
head -5 transactions.csv

You should see the CSV header row followed by four transaction records with values like TXN-000001, account IDs, dates, and dollar amounts.

Script not running?

  • If you see a syntax error, make sure you copied the entire script. A missing closing brace or parenthesis on the last few lines is the most common cause.
  • If nano did not save the file, try the command again. Press **Ctrl+O**, then **Enter**, then **Ctrl+X**.
  • Help me troubleshoot my generate_transactions.py script in Cloud Shell.
Upload the CSV to Cloud Storage

Before BigQuery can load the data, the file needs to be in Cloud Storage. Cloud Storage acts as a staging area where BigQuery reads source files from.

  • Upload the CSV file to your bucket by running this command:
gcloud storage cp transactions.csv gs://financial-data-${PROJECT_ID}/raw/transactions.csv
  • Verify the upload succeeded by listing the bucket contents:
gcloud storage ls gs://financial-data-${PROJECT_ID}/raw/

You should see transactions.csv listed in the output.

File not showing up?

  • If you see an error about the bucket not existing, your PROJECT_ID environment variable may have expired. Re-export it by running:
export PROJECT_ID=$(gcloud config get-value project)
  • Then try the upload command again.
  • Help me fix my Cloud Storage upload.
Load data into BigQuery

Now that your CSV is staged in Cloud Storage, you can load it into BigQuery using the LOAD DATA SQL statement. This is the SQL-native approach to data ingestion, which means you can do everything from inside the BigQuery query editor.

  • In the Google Cloud Console, click the Navigation menu (three horizontal lines in the top-left corner).
  • Select BigQuery under the Analytics section.
  • Click SQL query to open a new query editor.
  • Paste the following SQL into the query editor:
-- Create a dataset to organize your tables
CREATE SCHEMA IF NOT EXISTS financial_data
OPTIONS (location = 'US');

-- Load the CSV from Cloud Storage into a new BigQuery table
LOAD DATA INTO financial_data.transactions_raw (
  transaction_id STRING,
  account_id STRING,
  transaction_date DATE,
  transaction_type STRING,
  amount FLOAT64,
  merchant_name STRING,
  category STRING,
  direction STRING
)
FROM FILES (
  format = 'CSV',
  uris = ['gs://financial-data-[[PROJECT_ID="your-project-id"]]/raw/transactions.csv'],
  skip_leading_rows = 1
);

What does this SQL do?

  • CREATE SCHEMA creates a dataset called financial_data. A dataset in BigQuery is a container that holds your tables and views.
  • LOAD DATA INTO creates the transactions_raw table with an explicit schema (eight typed columns) and fills it with data from the CSV file in Cloud Storage.
  • skip_leading_rows = 1 tells BigQuery to skip the header row in the CSV so it does not try to load column names as data.
  • Click Run and wait for the job to complete. This takes a few seconds.
  • In the Explorer panel on the left, expand your project name.
  • Expand the financial_data dataset.
  • Click on transactions_raw.
  • Click the Preview tab to see your loaded data.

You should see rows of transaction data with columns like transaction_id, account_id, amount, and category. All 5,000 records are now in BigQuery and ready to query.

Query failed with an error?

  • If you see Not found: URI, double-check that you replaced the project ID placeholder in the uris line with your actual project ID. You can find your project ID in the project dropdown at the top of the console.
  • If you see Access Denied, make sure you selected the correct project (data-analytics-pipeline) from the project dropdown at the top of the page.
  • If you see a schema mismatch error, make sure your CSV has exactly eight columns matching the column names in the SQL statement.
  • Help me fix my BigQuery LOAD DATA statement.

Why upload to Cloud Storage first?

You might wonder why you did not load the CSV directly from Cloud Shell into BigQuery. The LOAD DATA statement reads from Cloud Storage, not from your local filesystem. This is the standard pattern for BigQuery data ingestion. In production, source systems export data to Cloud Storage, and BigQuery loads it from there.

Your raw data is now sitting in BigQuery, ready for analysis. Next up, you will design an optimized schema using partitioning and clustering to make your queries faster and cheaper.

Design an Optimized Schema

Your financial transaction data is now sitting in BigQuery, ready for analysis. But there is a problem with the raw table you loaded in the previous step.

Every query against transactions_raw scans every single row, even when you only need data from one month or one transaction type. In this step, you will create an optimized version of the table using partitioning and clustering, then measure the performance difference.

In this step, get ready to:
  • Create a partitioned and clustered version of your transactions table.
  • Compare query performance between the raw and optimized tables.
  • Examine query execution details to understand how BigQuery prunes data.
Create a partitioned and clustered table

BigQuery stores data in columnar format across many storage blocks. Without any optimization hints, a query must scan all of those blocks to find matching rows.

What are partitioning and clustering?

  • Partitioning splits a table into segments based on a column's value. When you partition by date, BigQuery groups all rows from the same day into the same partition. A query filtering on a date range only scans the partitions that match.
  • Clustering sorts data within each partition by the columns you choose. When you filter on a clustered column, BigQuery skips storage blocks that do not contain matching values. You can cluster by up to 4 columns, and the column order determines sort priority.

Together, partitioning and clustering let BigQuery read only the data it needs instead of scanning everything.

  • In the BigQuery query editor (the same editor you used in Step 2), create an optimized copy of your transactions table by running this query:
-- Create an optimized copy with partitioning and clustering
CREATE OR REPLACE TABLE financial_data.transactions_optimized
PARTITION BY transaction_date
CLUSTER BY transaction_type, category
AS
SELECT * FROM financial_data.transactions_raw;

What does this query do?

  • CREATE OR REPLACE TABLE creates a new table (or replaces it if it already exists) called transactions_optimized.
  • PARTITION BY transaction_date tells BigQuery to organize the data into daily partitions based on the transaction date.
  • CLUSTER BY transaction_type, category sorts data within each partition by transaction type first, then by category. Queries that filter on these columns scan fewer storage blocks.
  • The AS SELECT * clause copies all rows from the raw table into this new optimized structure.
  • Click Run and wait for the job to complete.
  • In the Explorer panel on the left, expand the financial_data dataset. You should see transactions_optimized listed alongside transactions_raw.

Don't see the new table?

  • Make sure the query completed successfully. Check the Job information tab in the results panel for any errors.
  • If you see a Not found: Table financial_data.transactions_raw error, go back to Step 2 and make sure you completed the data load.
  • Try clicking the refresh icon next to your project name in the Explorer panel.

Why isn't my optimized table showing up in BigQuery?

Compare query performance between raw and optimized tables

You now have two copies of the same data. One is raw, and the other is partitioned and clustered. To see the difference, you will run the same query on both tables and compare how much data BigQuery scans.

  • Run this aggregation query on the raw table:
-- Aggregate card transactions by category for Q1 2024
SELECT
  category,
  COUNT(*) AS transaction_count,
  SUM(amount) AS total_amount
FROM financial_data.transactions_raw
WHERE transaction_date BETWEEN '2024-01-01' AND '2024-03-31'
  AND transaction_type = 'CARD'
GROUP BY category
ORDER BY total_amount DESC;

What does this query do?

This query filters for card transactions in the first quarter of 2024, groups them by category, and calculates the count and total amount for each category. The WHERE clause targets exactly the columns that partitioning (date) and clustering (transaction type) are designed to optimize.

  • Click Run.
  • Look at the query results bar at the bottom of the results panel. Note the Bytes processed value. Write it down or remember it for comparison.
  • Now run the exact same query on the optimized table:
-- Same query on the optimized table for comparison
SELECT
  category,
  COUNT(*) AS transaction_count,
  SUM(amount) AS total_amount
FROM financial_data.transactions_optimized
WHERE transaction_date BETWEEN '2024-01-01' AND '2024-03-31'
  AND transaction_type = 'CARD'
GROUP BY category
ORDER BY total_amount DESC;

Why does the optimized table scan less data?

  • Partition pruning kicks in because the WHERE clause filters on transaction_date. BigQuery skips all partitions outside the January to March 2024 range entirely.
  • Block pruning kicks in because the query also filters on transaction_type = 'CARD'. Since the table is clustered by transaction_type, BigQuery skips storage blocks that do not contain CARD rows.

Fewer bytes scanned means lower cost and faster results. BigQuery charges $6.25 per TiB scanned on its on-demand pricing model, so this optimization directly saves money at scale.

  • Click Run and compare the Bytes processed value with the raw table query. The optimized table should show a lower number.

Both queries showing the same bytes processed?

With a small dataset of 5,000 rows, the difference may be minimal or even identical. BigQuery has a minimum processing threshold, and very small tables can fit within a single storage block. The performance difference becomes dramatic with millions of rows. The important thing is understanding the mechanism.

If you see an error instead of results, make sure the table name in your query matches exactly: financial_data.transactions_optimized.

Why is my BigQuery partitioned table not showing a performance improvement?

Examine query execution details

Beyond bytes processed, BigQuery provides detailed execution metrics that show exactly how the query engine processed your data.

  • In the query results panel, click the Execution details tab. This shows a breakdown of how BigQuery executed the most recent query.
  • Note the Input rows and Slot time consumed values for the optimized table query.
  • Go back and re-run the raw table query (the one targeting financial_data.transactions_raw).
  • Click the Execution details tab again and compare the Input rows and Slot time consumed with the optimized query.

What should I look for in the execution details?

  • Input rows tells you how many rows BigQuery actually read. The optimized query should read fewer rows because partitions outside your date range were skipped.
  • Slot time consumed measures the total compute work across all workers. Lower slot time means BigQuery needed less compute power to produce the same results.

This is the type of analysis a Customer Engineer performs when advising clients on query optimization. You diagnose how data is being scanned and recommend schema changes that reduce cost and latency.

Your schema is now optimized for the types of queries you will run throughout this project. Next up, you will write SQL analytics queries that uncover transaction patterns, monthly trends, and account activity.

Analyze Transaction Patterns with SQL

In the previous step, you built an optimized table with partitioning and clustering. You also proved that it scans less data than the raw table.

Now it is time to put that optimized table to work. In this step, you will write SQL queries in BigQuery that reveal spending trends, rank accounts, and produce a reusable summary that your dashboard will connect to later.

In this step, get ready to:
  • Write aggregation queries that surface monthly trends and top accounts.
  • Use window functions and CTEs to calculate running totals and spending ranks.
  • Create a reusable summary view for downstream visualization.
Write aggregation queries

Aggregation queries group rows together and produce summary statistics like counts, sums, and averages. These are the bread and butter of data analytics.

  • Analyze monthly debit spending trends by running this query in the BigQuery query editor:
-- Monthly debit transaction trends
SELECT
  FORMAT_DATE('%Y-%m', transaction_date) AS month,
  COUNT(*) AS transaction_count,
  ROUND(SUM(amount), 2) AS total_amount,
  ROUND(AVG(amount), 2) AS avg_amount
FROM financial_data.transactions_optimized
WHERE direction = 'DEBIT'
GROUP BY month
ORDER BY month;

What does this query do?

  • FORMAT_DATE('%Y-%m', transaction_date) converts each date into a year-month string like 2023-01 so you can group transactions by month.
  • The WHERE direction = 'DEBIT' filter focuses the analysis on money going out, which is more interesting for spending trend analysis.
  • GROUP BY month collapses all transactions in the same month into a single summary row with counts, sums, and averages.
  • Click **Run** in the BigQuery toolbar.
  • You should see rows for each month from 2023-01 through 2024-12, with transaction counts and total amounts per month.

Seeing "Not found: Table" error?

  • Make sure you are querying financial_data.transactions_optimized (the optimized table you created in Step 3), not a different table name.
  • Check that your project is selected in the project dropdown at the top of the console. BigQuery looks for tables in the active project.
  • Why is my BigQuery query returning a table not found error?
  • Find the top 10 accounts by total spending by running this query:
-- Top 10 accounts ranked by total debit spending
SELECT
  account_id,
  COUNT(*) AS transaction_count,
  ROUND(SUM(amount), 2) AS total_spent,
  ROUND(AVG(amount), 2) AS avg_transaction
FROM financial_data.transactions_optimized
WHERE direction = 'DEBIT'
GROUP BY account_id
ORDER BY total_spent DESC
LIMIT 10;

What does this query do?

  • GROUP BY account_id summarizes each account's total activity into one row.
  • ORDER BY total_spent DESC sorts accounts from highest spending to lowest, and LIMIT 10 keeps only the top ten.
  • Click **Run**.
  • You should see exactly 10 rows showing the highest-spending accounts, sorted by total_spent in descending order.

Seeing 0 rows or unexpected results?

  • Double-check that direction = 'DEBIT' uses single quotes around DEBIT. SQL string literals require single quotes.
  • Make sure the value DEBIT is uppercase. The data was generated with uppercase direction values, so 'debit' would return no matches.
  • Why is my BigQuery aggregation query returning no rows?
Use window functions and CTEs

Aggregation queries are powerful, but they collapse rows into summaries. Window functions let you calculate values like running totals and rankings across rows without collapsing them.

A CTE (Common Table Expression) is a temporary named result set defined with a WITH clause. It breaks complex queries into readable, logical steps.

  • Calculate running totals and rank accounts by monthly spending by running this query:
-- CTE: calculate each account's monthly spending
WITH monthly_account_spending AS (
  SELECT
    account_id,
    FORMAT_DATE('%Y-%m', transaction_date) AS month,
    SUM(amount) AS monthly_total
  FROM financial_data.transactions_optimized
  WHERE direction = 'DEBIT'
  GROUP BY account_id, month
)
-- Main query: add running totals and spending ranks
SELECT
  account_id,
  month,
  monthly_total,
  SUM(monthly_total) OVER (
    PARTITION BY account_id
    ORDER BY month
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total,
  RANK() OVER (
    PARTITION BY month
    ORDER BY monthly_total DESC
  ) AS spending_rank
FROM monthly_account_spending
ORDER BY month, spending_rank
LIMIT 50;

What does this query do?

  • The CTE monthly_account_spending first calculates how much each account spent per month. This becomes a temporary table that the main query reads from.
  • SUM(...) OVER (PARTITION BY account_id ORDER BY month) creates a running total. For each row, it sums the current month's spending plus all previous months for that account.
  • RANK() OVER (PARTITION BY month ORDER BY monthly_total DESC) assigns a rank within each month, so rank 1 is the highest spender for that month.
  • Click **Run**.
  • You should see up to 50 rows. Each row includes an account's monthly spending, a cumulative running_total, and a spending_rank for that month.

Getting a syntax error on the WITH clause?

  • Make sure there is no semicolon between the closing parenthesis of the CTE ) and the SELECT keyword. The CTE and the main query are one statement.
  • Check that you did not accidentally leave the previous query in the editor above this one. Clear the editor before pasting.
  • Help me debug my CTE and window function query in BigQuery.

Why do CTEs and window functions matter for a Customer Engineer?

Clients often ask for running totals, rankings, and period-over-period comparisons. These queries showcase BigQuery's ability to handle complex analytics that would be difficult in spreadsheets. Knowing how to write them is exactly the kind of skill a Customer Engineer uses during proof-of-concept demos.

Create a reusable summary view

A view in BigQuery is a saved query that behaves like a table. Downstream tools like Looker Studio can connect to a view just like they connect to a table, but the view always runs fresh against the latest data.

  • Create a daily summary view by running this query:
-- Reusable daily summary for downstream tools like Looker Studio
CREATE OR REPLACE VIEW financial_data.daily_summary AS
SELECT
  transaction_date,
  transaction_type,
  category,
  direction,
  COUNT(*) AS transaction_count,
  ROUND(SUM(amount), 2) AS total_amount,
  ROUND(AVG(amount), 2) AS avg_amount,
  ROUND(MIN(amount), 2) AS min_amount,
  ROUND(MAX(amount), 2) AS max_amount
FROM financial_data.transactions_optimized
GROUP BY transaction_date, transaction_type, category, direction;

What does this view do?

  • The view groups every combination of date, transaction type, category, and direction into a single summary row with counts, totals, averages, minimums, and maximums.
  • Because it is a view (not a table), it does not store duplicate data. It re-runs the query each time something reads from it, so the results are always up to date.
  • You will connect Looker Studio to this view in Step 7 to build your dashboard.
  • Click **Run**.
  • You should see a success message confirming the view was created.
  • In the **Explorer** panel on the left, expand financial_data and confirm that daily_summary appears under **Views**.

Don't see daily_summary in the Explorer?

  • Click the refresh icon (circular arrow) next to your project name in the Explorer panel. New views sometimes take a moment to appear.
  • If BigQuery returned an error, check that you are referencing financial_data.transactions_optimized in the FROM clause. The dataset and table name must match exactly.
  • Why is my BigQuery view not showing up in the Explorer panel?

Your SQL analytics are complete and your summary view is ready for Looker Studio. Next, you will compare batch and streaming ingestion to learn how data arrives in BigQuery in real time.

Compare Batch and Streaming Ingestion

Your SQL analytics from the previous step revealed spending patterns, monthly trends, and account rankings across your transaction data. Now you know how to query data in BigQuery. But how does data get into BigQuery in the first place?

There are two main ingestion patterns: batch loading (uploading data in bulk from files) and streaming (inserting rows one at a time so they are immediately queryable). In this step, you will try both approaches and understand when to recommend each one.

In this step, get ready to:
  • Generate a second batch of transactions and load them into BigQuery from a CSV file.
  • Stream live rows into BigQuery using Python so they are immediately queryable.
  • Compare the two ingestion patterns and learn when to use each.
Generate and batch-load a second data file

You already loaded one CSV into BigQuery in Step 2. That was batch ingestion. To see the pattern again with fresh data, you will generate a second CSV covering Q1 2025 and load it into your existing optimized table.

Pro tip: Cloud Shell sessions can restart

If your Cloud Shell session restarted since Step 1, the PROJECT_ID environment variable may be gone. Re-export it by running:

export PROJECT_ID=$(gcloud config get-value project)
  • Create a new Python script by running this command in Cloud Shell:
nano generate_batch2.py
  • Paste the following script into the editor:
import csv
import random
from datetime import datetime, timedelta

# Use a different seed from the first script for unique data
random.seed(99)

# Same merchant categories as the original dataset
merchants = {
    'GROCERIES': ['Whole Foods', 'Trader Joes', 'Safeway', 'Kroger'],
    'DINING': ['Starbucks', 'Chipotle', 'McDonalds', 'Olive Garden'],
    'TRANSPORT': ['Uber', 'Lyft', 'Shell Gas', 'BP Gas'],
    'UTILITIES': ['Electric Co', 'Water Works', 'Internet Corp', 'Phone Plus'],
    'SHOPPING': ['Amazon', 'Target', 'Walmart', 'Best Buy'],
    'TRANSFER': ['Internal Transfer', 'Wire Transfer', 'ACH Transfer', 'Bank Transfer']
}

# Map each category to a payment method
transaction_types = {
    'GROCERIES': 'CARD',
    'DINING': 'CARD',
    'TRANSPORT': 'CARD',
    'UTILITIES': 'WIRE',
    'SHOPPING': 'CARD',
    'TRANSFER': 'WIRE'
}

# 50 accounts, covering Q1 2025 (new date range for batch 2)
accounts = [f'ACCT-{str(i).zfill(4)}' for i in range(1, 51)]
start_date = datetime(2025, 1, 1)
end_date = datetime(2025, 3, 31)

# Generate 500 transactions with BATCH2- prefix
rows = []
for i in range(1, 501):
    category = random.choice(list(merchants.keys()))
    merchant = random.choice(merchants[category])
    txn_type = transaction_types[category]
    days_offset = random.randint(0, (end_date - start_date).days)
    txn_date = start_date + timedelta(days=days_offset)

    # Set amount ranges based on category
    if category == 'TRANSFER':
        amount = round(random.uniform(500, 10000), 2)
    elif category == 'UTILITIES':
        amount = round(random.uniform(50, 300), 2)
    else:
        amount = round(random.uniform(5, 500), 2)

    direction = 'DEBIT' if category != 'TRANSFER' or random.random() > 0.5 else 'CREDIT'

    rows.append({
        'transaction_id': f'BATCH2-{str(i).zfill(6)}',
        'account_id': random.choice(accounts),
        'transaction_date': txn_date.strftime('%Y-%m-%d'),
        'transaction_type': txn_type,
        'amount': amount,
        'merchant_name': merchant,
        'category': category,
        'direction': direction
    })

# Write all rows to CSV
with open('transactions_batch2.csv', 'w', newline='') as f:
    writer = csv.DictWriter(f, fieldnames=[
        'transaction_id', 'account_id', 'transaction_date',
        'transaction_type', 'amount', 'merchant_name',
        'category', 'direction'
    ])
    writer.writeheader()
    writer.writerows(rows)

print(f'Generated {len(rows)} batch 2 transactions in transactions_batch2.csv')

What does this script do?

This script is similar to the one you used in Step 2, with a few key differences:

  • It generates 500 transactions instead of 5,000.
  • The date range covers Q1 2025 (January to March) instead of 2023-2024.
  • Transaction IDs use the BATCH2- prefix so you can tell them apart from the original data.
  • Press Ctrl+O and then Enter to save the file.
  • Press Ctrl+X to exit nano.

Awesome, I've got everything!

Great. Double check you have saved generate_batch2.py before moving on.

I'd like to double check the full code

import csv
import random
from datetime import datetime, timedelta

random.seed(99)

merchants = {
    'GROCERIES': ['Whole Foods', 'Trader Joes', 'Safeway', 'Kroger'],
    'DINING': ['Starbucks', 'Chipotle', 'McDonalds', 'Olive Garden'],
    'TRANSPORT': ['Uber', 'Lyft', 'Shell Gas', 'BP Gas'],
    'UTILITIES': ['Electric Co', 'Water Works', 'Internet Corp', 'Phone Plus'],
    'SHOPPING': ['Amazon', 'Target', 'Walmart', 'Best Buy'],
    'TRANSFER': ['Internal Transfer', 'Wire Transfer', 'ACH Transfer', 'Bank Transfer']
}

transaction_types = {
    'GROCERIES': 'CARD',
    'DINING': 'CARD',
    'TRANSPORT': 'CARD',
    'UTILITIES': 'WIRE',
    'SHOPPING': 'CARD',
    'TRANSFER': 'WIRE'
}

accounts = [f'ACCT-{str(i).zfill(4)}' for i in range(1, 51)]
start_date = datetime(2025, 1, 1)
end_date = datetime(2025, 3, 31)

rows = []
for i in range(1, 501):
    category = random.choice(list(merchants.keys()))
    merchant = random.choice(merchants[category])
    txn_type = transaction_types[category]
    days_offset = random.randint(0, (end_date - start_date).days)
    txn_date = start_date + timedelta(days=days_offset)

    if category == 'TRANSFER':
        amount = round(random.uniform(500, 10000), 2)
    elif category == 'UTILITIES':
        amount = round(random.uniform(50, 300), 2)
    else:
        amount = round(random.uniform(5, 500), 2)

    direction = 'DEBIT' if category != 'TRANSFER' or random.random() > 0.5 else 'CREDIT'

    rows.append({
        'transaction_id': f'BATCH2-{str(i).zfill(6)}',
        'account_id': random.choice(accounts),
        'transaction_date': txn_date.strftime('%Y-%m-%d'),
        'transaction_type': txn_type,
        'amount': amount,
        'merchant_name': merchant,
        'category': category,
        'direction': direction
    })

with open('transactions_batch2.csv', 'w', newline='') as f:
    writer = csv.DictWriter(f, fieldnames=[
        'transaction_id', 'account_id', 'transaction_date',
        'transaction_type', 'amount', 'merchant_name',
        'category', 'direction'
    ])
    writer.writeheader()
    writer.writerows(rows)

print(f'Generated {len(rows)} batch 2 transactions in transactions_batch2.csv')
  • Generate the batch data and upload it to Cloud Storage by running these commands:
python3 generate_batch2.py
gcloud storage cp transactions_batch2.csv gs://financial-data-${PROJECT_ID}/raw/transactions_batch2.csv
  • You should see Generated 500 batch 2 transactions in transactions_batch2.csv followed by a file copy confirmation.

Seeing a "not found" or permission error?

  • If the gcloud storage cp command fails, make sure your PROJECT_ID variable is set. Run echo $PROJECT_ID to check. If it is empty, re-run the export command from the tip callout above.
  • Make sure generate_batch2.py was saved correctly. Re-open it with nano generate_batch2.py to verify the contents.

Why is my Cloud Storage upload failing?

Now load the new CSV into your optimized BigQuery table. This is the batch ingestion pattern: upload a file, then run a load job that processes it in bulk.

  • Open BigQuery Studio in the Google Cloud Console (Navigation menu > BigQuery).
  • Click SQL query to open a new query editor.
  • Paste the following SQL and run it to load the batch data:
LOAD DATA INTO financial_data.transactions_optimized (
  transaction_id STRING,
  account_id STRING,
  transaction_date DATE,
  transaction_type STRING,
  amount FLOAT64,
  merchant_name STRING,
  category STRING,
  direction STRING
)
FROM FILES (
  format = 'CSV',
  uris = ['gs://financial-data-[[PROJECT_ID="your-project-id"]]/raw/transactions_batch2.csv'],
  skip_leading_rows = 1
);

What does this SQL do?

  • LOAD DATA INTO tells BigQuery to append rows to your existing transactions_optimized table.
  • FROM FILES points to the CSV you just uploaded in Cloud Storage.
  • skip_leading_rows = 1 skips the CSV header row so it is not treated as data.
  • The data is only available after the load job finishes. This is the defining characteristic of batch ingestion.
  • Click Run and wait for the job to complete.
  • In the Explorer panel, click on financial_data > transactions_optimized and click Preview.
  • You should see rows with the BATCH2- prefix mixed in alongside the original TXN- rows.

LOAD DATA query failed?

  • Double check that you replaced your-project-id in the uris line with your actual project ID. You can find it in the project dropdown at the top of the console.
  • Make sure the file was uploaded successfully by going back to Cloud Shell and running gcloud storage ls gs://financial-data-${PROJECT_ID}/raw/ to confirm transactions_batch2.csv is listed.

Why is my LOAD DATA query failing in BigQuery?

Stream data using the Python BigQuery client

Batch loading waits until the entire job finishes before data becomes queryable. Streaming takes the opposite approach. Each row is available for queries the moment it arrives.

  • In Cloud Shell, create a new Python script by running this command:
nano stream_transactions.py
  • Paste the following script into the editor:
from google.cloud import bigquery
import random
from datetime import datetime

# Fixed seed for reproducible output
random.seed(123)

# Initialize the BigQuery client (auto-detects your project)
client = bigquery.Client()

# Build the fully qualified table ID
project_id = client.project
table_id = f'{project_id}.financial_data.transactions_optimized'

# Define categories and merchants for streamed rows
new_rows = []
categories = ['DINING', 'SHOPPING', 'TRANSPORT', 'GROCERIES']
merchants = {
    'DINING': 'Live Stream Coffee',
    'SHOPPING': 'Stream Mart',
    'TRANSPORT': 'Stream Rides',
    'GROCERIES': 'Stream Fresh'
}

# Generate 10 transactions with a STREAM- prefix
for i in range(10):
    cat = random.choice(categories)
    new_rows.append({
        'transaction_id': f'STREAM-{str(i + 1).zfill(6)}',
        'account_id': f'ACCT-{str(random.randint(1, 50)).zfill(4)}',
        'transaction_date': datetime.now().strftime('%Y-%m-%d'),
        'transaction_type': 'CARD',
        'amount': round(random.uniform(10, 200), 2),
        'merchant_name': merchants[cat],
        'category': cat,
        'direction': 'DEBIT'
    })

# Stream the rows into BigQuery using the legacy streaming API
errors = client.insert_rows_json(table_id, new_rows)

# Check for errors and print results
if errors:
    print(f'Errors occurred: {errors}')
else:
    print(f'Successfully streamed {len(new_rows)} rows!')
    print('Data is immediately available for queries.')
    print(f'Try: SELECT * FROM `{table_id}` WHERE transaction_id LIKE "STREAM-%"')

What does this script do?

  • It uses the google-cloud-bigquery Python library, which is pre-installed in Cloud Shell.
  • client.project automatically detects your project ID, so you do not need to hardcode it.
  • insert_rows_json() streams each row directly into the BigQuery table. The data is immediately available for queries without waiting for a load job to finish.
  • All 10 rows use the STREAM- prefix so you can easily find them in queries.
  • Press Ctrl+O and then Enter to save the file.
  • Press Ctrl+X to exit nano.

Awesome, I've got everything!

Great. Double check you have saved stream_transactions.py before moving on.

I'd like to double check the full code

from google.cloud import bigquery
import random
from datetime import datetime

random.seed(123)
client = bigquery.Client()

project_id = client.project
table_id = f'{project_id}.financial_data.transactions_optimized'

new_rows = []
categories = ['DINING', 'SHOPPING', 'TRANSPORT', 'GROCERIES']
merchants = {
    'DINING': 'Live Stream Coffee',
    'SHOPPING': 'Stream Mart',
    'TRANSPORT': 'Stream Rides',
    'GROCERIES': 'Stream Fresh'
}

for i in range(10):
    cat = random.choice(categories)
    new_rows.append({
        'transaction_id': f'STREAM-{str(i + 1).zfill(6)}',
        'account_id': f'ACCT-{str(random.randint(1, 50)).zfill(4)}',
        'transaction_date': datetime.now().strftime('%Y-%m-%d'),
        'transaction_type': 'CARD',
        'amount': round(random.uniform(10, 200), 2),
        'merchant_name': merchants[cat],
        'category': cat,
        'direction': 'DEBIT'
    })

errors = client.insert_rows_json(table_id, new_rows)

if errors:
    print(f'Errors occurred: {errors}')
else:
    print(f'Successfully streamed {len(new_rows)} rows!')
    print('Data is immediately available for queries.')
    print(f'Try: SELECT * FROM `{table_id}` WHERE transaction_id LIKE "STREAM-%"')
  • Run the streaming script by running this command:
python3 stream_transactions.py

You should see Successfully streamed 10 rows! followed by a message confirming the data is immediately available.

Seeing errors from the streaming script?

  • If you see a NotFound error, make sure the transactions_optimized table exists in BigQuery. You created it in Step 3.
  • If you see a permission error, make sure your Cloud Shell is connected to the correct project. Run gcloud config get-value project to verify.

Why is my BigQuery streaming script failing?

The rows you just streamed are already queryable. Let's confirm that right now in BigQuery.

  • Go to the BigQuery query editor and run this SQL to find your streamed rows:
SELECT * FROM financial_data.transactions_optimized
WHERE transaction_id LIKE 'STREAM-%'
ORDER BY transaction_id;
  • Click Run.
  • You should see 10 rows with transaction IDs from STREAM-000001 through STREAM-000010, all with today's date.

Why are these rows already available?

When you used batch loading, the data only became available after the load job finished. With streaming, each row is queryable the moment it arrives. This is what makes streaming ideal for real-time dashboards and live monitoring.

Understand when to use each approach

You have now seen both ingestion patterns in action. The batch load appended 500 rows from a CSV after the job completed. The streaming script inserted 10 rows that were immediately queryable.

Batch vs streaming: when to use each

Batch loading is best when you have a file or dataset to load in bulk. Data becomes available after the load job completes. It is free for standard loads and ideal for initial migrations, nightly imports, and historical data backfills.

Streaming is best when data arrives continuously and must be queryable immediately. BigQuery's Storage Write API is the recommended production streaming API. It is ideal for real-time dashboards, event processing, and live transaction monitoring.

Most real-world pipelines use both patterns together. Batch handles historical loads and scheduled imports. Streaming handles the live data that arrives between batch runs.

As a Customer Engineer, you would advise clients on which pattern fits their use case. A data warehouse that refreshes overnight only needs batch. A fraud detection system monitoring live payments needs streaming. Many clients start with batch and add streaming as their real-time needs grow.

You have seen both ways to get data into BigQuery and know when each one fits. Next up, you will process your transaction data with PySpark on Serverless Spark.

Process Data with PySpark on Serverless Spark

In the previous step, you loaded data using both batch and streaming patterns. Your BigQuery table now contains financial transactions from multiple sources.

Some transformations are easier to express with PySpark than with SQL. In this step, you will use Serverless Spark in BigQuery Studio to read your transaction data, transform it, and write results back to a new BigQuery table.

In this step, get ready to:
  • Open a Spark notebook in BigQuery Studio.
  • Read and transform BigQuery data using PySpark DataFrames.
  • Write PySpark results back to a new BigQuery table.
Open a Spark notebook in BigQuery Studio

Serverless Spark runs PySpark code directly inside BigQuery Studio. There is no cluster to create or manage.

  • In BigQuery Studio, click the dropdown arrow next to SQL query.
  • Select Notebook, then Spark template.
  • Wait for the runtime to connect. This takes 1-2 minutes.
  • If prompted, click Connect to connect to the default runtime.

Why Serverless Spark instead of Dataproc clusters?

Traditional Apache Spark on Google Cloud requires creating and managing Dataproc clusters. Serverless Spark eliminates that overhead entirely.

The runtime spins up in 1-2 minutes and shuts down automatically when you are done. This lets you focus on writing PySpark code instead of managing infrastructure.

Once the runtime shows as connected, you will see an empty notebook cell ready for PySpark code.

Runtime not connecting?

  • Make sure you enabled the Dataproc API in Step 1 by running gcloud services enable dataproc.googleapis.com in Cloud Shell.
  • If the notebook is stuck on "Connecting," try refreshing the browser tab and reopening the notebook.
  • Help me troubleshoot my Serverless Spark runtime not connecting in BigQuery Studio.
Read BigQuery data and run a PySpark transformation

With the Spark runtime connected, you can now read your transactions table into a PySpark DataFrame and run transformations on it.

  • In the first notebook cell, paste this code to create a Spark session and read your transactions table:
# Create a Spark session using the pre-configured runtime
from pyspark.sql import SparkSession
from pyspark.sql import functions as F

spark = SparkSession.builder.getOrCreate()

# Read the transactions table from BigQuery
df = spark.read.format("bigquery") \
    .option("table", "[[PROJECT_ID=\"your-project-id\"]].financial_data.transactions_optimized") \
    .load()

# Preview the schema and first 5 rows
df.printSchema()
df.show(5)

What does this code do?

  • SparkSession.builder.getOrCreate() creates or reuses a Spark session. The Serverless Spark runtime pre-configures this for you, so no extra setup is needed.
  • spark.read.format("bigquery") uses the Spark BigQuery connector to read your table directly into a PySpark DataFrame.
  • printSchema() displays the column names and data types. show(5) prints the first 5 rows as a formatted table.
  • Run the cell by clicking the play button or pressing Shift+Enter.
  • You should see a schema printout listing all 8 columns and their types, followed by a table of 5 transaction rows.

Cell not running or showing an error?

  • Check that your project ID in the .option("table", ...) line matches the project ID shown in the Google Cloud Console header.
  • Make sure the runtime is still connected. If it disconnected, click Connect again and re-run the cell.
  • Help me debug my Spark cell that is failing to read BigQuery data.
  • Click + Code to add a new cell below the first one.
  • Paste this PySpark transformation to calculate monthly spending by category:
# Filter to debit transactions and extract the year-month
monthly_spending = df.filter(df.direction == "DEBIT") \
    .withColumn("month", F.date_format("transaction_date", "yyyy-MM")) \
    .groupBy("month", "category") \
    .agg(
        F.count("*").alias("transaction_count"),
        F.sum("amount").alias("total_amount"),
        F.round(F.avg("amount"), 2).alias("avg_amount")
    ) \
    .orderBy("month", "category")

# Display the first 20 rows of the result
monthly_spending.show(20)

What does this transformation do?

  • df.filter(df.direction == "DEBIT") keeps only debit transactions, since you are analyzing spending.
  • withColumn("month", ...) creates a new column by extracting the year-month from each transaction date.
  • groupBy and agg calculate the count, sum, and average amount for each month-category combination.
  • orderBy sorts the results by month and then by category for easy reading.
  • Run the cell by clicking the play button or pressing Shift+Enter.
  • You should see a table with rows showing each month-category pair, along with transaction_count, total_amount, and avg_amount columns.

Seeing a NameError for df?

  • The df variable is created in the first cell. Make sure you ran the first cell successfully before running this one.
  • If the runtime disconnected and reconnected between cells, re-run the first cell to recreate the df variable.
  • Help me fix a NameError in my PySpark notebook.
Write PySpark results back to BigQuery

The final piece is writing your Spark-processed data back to BigQuery as a new table. This closes the loop: BigQuery to Spark and back to BigQuery.

  • Click + Code to add another new cell.
  • Paste this code to save the Spark results as a new BigQuery table:
# Write the monthly spending DataFrame to a new BigQuery table
monthly_spending.write.format("bigquery") \
    .option("table", "[[PROJECT_ID=\"your-project-id\"]].financial_data.spark_monthly_summary") \
    .option("writeMethod", "direct") \
    .mode("overwrite") \
    .save()

print("Results written to financial_data.spark_monthly_summary!")

What does this code do?

  • .write.format("bigquery") uses the Spark BigQuery connector to write the DataFrame back to BigQuery.
  • writeMethod set to direct uses the BigQuery Storage Write API for efficient writes.
  • .mode("overwrite") replaces the table if it already exists, so you can safely re-run the cell.
  • Run the cell. You should see the message Results written to financial_data.spark_monthly_summary! in the output.
  • In the BigQuery Explorer panel on the left, click the refresh icon next to your project name.
  • Expand financial_data. You should see the new spark_monthly_summary table listed.
  • Click on spark_monthly_summary and then click Preview to verify the data.

Table not appearing in the Explorer panel?

  • Click the refresh icon next to your project name in the Explorer panel to reload the table list.
  • Check that your project ID in the .option("table", ...) line is correct. It should match the project ID in the Cloud Console header.
  • If you see a permissions error, make sure you are using the same project that you created in Step 1.
  • Help me fix a failed PySpark write to BigQuery.

Awesome, I've got everything!

Great. Double check you see the spark_monthly_summary table in your BigQuery Explorer panel under financial_data.

I'd like to double check all three cells

Here are all three notebook cells for reference. Compare yours against these:

# === Cell 1: Create Spark session and read data ===
from pyspark.sql import SparkSession
from pyspark.sql import functions as F

spark = SparkSession.builder.getOrCreate()

df = spark.read.format("bigquery") \
    .option("table", "YOUR_PROJECT_ID.financial_data.transactions_optimized") \
    .load()

df.printSchema()
df.show(5)
# === Cell 2: PySpark transformation ===
monthly_spending = df.filter(df.direction == "DEBIT") \
    .withColumn("month", F.date_format("transaction_date", "yyyy-MM")) \
    .groupBy("month", "category") \
    .agg(
        F.count("*").alias("transaction_count"),
        F.sum("amount").alias("total_amount"),
        F.round(F.avg("amount"), 2).alias("avg_amount")
    ) \
    .orderBy("month", "category")

monthly_spending.show(20)
# === Cell 3: Write results back to BigQuery ===
monthly_spending.write.format("bigquery") \
    .option("table", "YOUR_PROJECT_ID.financial_data.spark_monthly_summary") \
    .option("writeMethod", "direct") \
    .mode("overwrite") \
    .save()

print("Results written to financial_data.spark_monthly_summary!")

Your Spark-processed data is now sitting in BigQuery as a new table. Next up, you will build an interactive dashboard in Looker Studio to bring all your analysis to life.

Build a Dashboard with Looker Studio

Your PySpark transformation is complete. Your Spark results are stored as a new table in BigQuery, ready to be queried alongside the rest of your financial data.

Tables and views are powerful, but stakeholders need visuals they can explore. In this step, you will connect your data to Looker Studio, Google Cloud's free visualization tool, and build an interactive dashboard with charts and filters.

In this step, get ready to:
  • Connect your BigQuery data to Looker Studio.
  • Build interactive charts for transaction trends.
  • Add filters and save your dashboard.
Connect BigQuery to Looker Studio

The daily_summary view you created in Step 4 aggregates transaction data by date, type, and category. This is the data source your dashboard will use.

  • Confirm the daily summary view returns data by running this query in the BigQuery query editor:
-- Verify the daily summary view returns data
SELECT * FROM financial_data.daily_summary
ORDER BY transaction_date DESC
LIMIT 10;
  • Click **Run** and confirm you see 10 rows with columns like transaction_date, transaction_type, category, and aggregated amounts.

Seeing "Not found: Table" error?

This means the daily_summary view does not exist yet. Go back to Step 4 and run the CREATE OR REPLACE VIEW query to create it.

Also check that your project is set to data-analytics-pipeline in the project dropdown at the top of the console.

Why is my daily_summary view query failing?

Now you will send these query results directly to Looker Studio to start building your dashboard.

  • In the Query results section below your query, click Open in.
  • Select Looker Studio.
  • On the Welcome page, click Get Started.
  • Authorize Looker Studio to access your BigQuery data when prompted.

The report editor opens with your data displayed as a default table on the canvas.

What is Looker Studio?

Looker Studio is a free data visualization tool from Google Cloud. It connects directly to BigQuery tables and views, pulling live data every time someone opens the dashboard.

The Open in shortcut you just used creates a new report pre-loaded with your query results as a data source. Any charts you build will query BigQuery directly.

Looker Studio not opening?

Make sure pop-ups are allowed for lookerstudio.google.com in your browser. Looker Studio opens in a new tab, so pop-up blockers can prevent it from loading.

If you see an authorization error, make sure you are signed in to the same Google account you used for your GCP project.

Why can't I open my BigQuery results in Looker Studio?

Build interactive charts

The default table shows raw rows, which is not very useful for spotting trends. You will replace it with three charts that visualize transaction patterns over time, by category, and by type.

  • Click on the default table on the canvas to select it.
  • Press Delete on your keyboard to remove it.

Your first chart will show how transaction amounts change over time.

  • Click Add a chart in the toolbar.
  • Select Time series chart.
  • Click on the canvas to place the chart.
  • In the Data panel on the right, set Dimension to transaction_date.
  • Set Metric to total_amount.

You should see a line chart showing total transaction amounts plotted over time. Next, add a bar chart to compare spending across categories.

  • Click Add a chart in the toolbar.
  • Select Bar chart.
  • Click on the canvas to place it below the time series chart.
  • Set Dimension to category.
  • Set Metric to total_amount.

Finally, add a pie chart to show the breakdown of transaction types.

  • Click Add a chart in the toolbar.
  • Select Pie chart.
  • Click on the canvas to place it next to the bar chart.
  • Set Dimension to transaction_type.
  • Set Metric to transaction_count.

Your canvas now has three charts: a time series showing trends over time, a bar chart comparing categories, and a pie chart breaking down transaction types.

Why these three chart types?

Each chart answers a different question. The time series shows when spending happens. The bar chart shows where money goes across categories. The pie chart shows the proportion of each transaction type (CARD vs WIRE).

A good dashboard tells a story by combining these perspectives. This is the kind of deliverable a Customer Engineer builds to show clients what their data can reveal.

Charts showing "No data"?

Click on the chart that shows no data, then check the Data panel on the right. Make sure the Dimension and Metric fields match the column names from your data source exactly.

If you see a "Configuration incomplete" warning, drag the correct field from the Available Fields list into the Dimension or Metric box.

Why are my Looker Studio charts not displaying data from BigQuery?

Add filters and save your dashboard

Static charts show a fixed view of the data. Filter controls let viewers slice the data themselves without editing the report or writing SQL.

  • Click Add a control in the toolbar.
  • Select Drop-down list.
  • In the Data panel, set the Control field to direction.

This dropdown lets viewers filter the entire dashboard by DEBIT or CREDIT transactions. Now add a second filter for categories.

  • Click Add a control in the toolbar again.
  • Select Drop-down list.
  • Set the Control field to category.

Time to see your dashboard in action.

  • Click View in the top-right to exit edit mode.
  • Use the direction dropdown to select DEBIT.
  • Watch all three charts update to show only debit transactions.
  • Try selecting a specific value from the category dropdown to see the charts narrow down further.

How do filter controls work?

When a viewer selects a value from a dropdown control, Looker Studio applies that filter to every chart on the page that shares the same data source. The charts re-query BigQuery with the filter applied and display updated results.

This is what makes dashboards interactive. Stakeholders can answer their own questions without asking you to write new SQL queries.

  • Click the report title at the top of the page (it says Untitled Report).
  • Rename it to Financial Transaction Analytics.

Your dashboard is saved automatically in Looker Studio. You now have a stakeholder-ready report backed by live BigQuery data.

Secret mission

Build a Fraud Detection Anomaly View

Your pipeline can already analyze transactions, but can it catch fraud? Build a statistical anomaly detection view that flags suspicious transactions using z-scores. Then connect it to your Looker Studio dashboard so stakeholders can see flagged anomalies at a glance.

Before you go

Clean Up Your Resources

Decide whether to keep your resources running, pause them to come back later, or delete them entirely. All resources in this project were created under the GCP free trial ($300 in credits for 90 days). BigQuery also has a permanent free tier that covers 1 TiB of queries and 10 GiB of storage per month, even after your free trial ends.

Cost warning

After your free trial expires, Cloud Storage and Serverless Spark usage will no longer be covered by free credits. BigQuery's free tier will keep covering light query and storage usage at no cost. If you do not plan to continue using the project, delete it or at least remove the Cloud Storage bucket to avoid unexpected charges.

Resources you used:

  • GCP project (data-analytics-pipeline).
  • Cloud Storage bucket (financial-data-YOUR_PROJECT_ID).
  • BigQuery dataset (financial_data) with tables and views (transactions_raw, transactions_optimized, daily_summary, spark_monthly_summary).
  • Looker Studio dashboard ("Financial Transaction Analytics").
  • Python scripts in Cloud Shell (generate_transactions.py, generate_batch2.py, stream_transactions.py).

Keep everything running

No action needed. Choose this if you are still actively building or want to explore the data further.

  • Your BigQuery tables, views, and Looker Studio dashboard will persist.
  • BigQuery's free tier (1 TiB queries/month, 10 GiB storage/month) covers light ongoing usage at no cost.
  • Cloud Storage will continue using your free trial credits while they last.
  • Looker Studio is free and does not incur any charges.

Pause - I'll come back to this later

Delete the Cloud Storage bucket to avoid storage costs after your free trial expires. Your BigQuery tables and Looker Studio dashboard will remain available.

  • Open Cloud Shell in the Google Cloud Console.
  • Run the following command to delete the Cloud Storage bucket and all its contents:
gcloud storage rm --recursive gs://financial-data-${PROJECT_ID}
  • Your BigQuery data is already loaded into tables, so removing the CSV files from Cloud Storage will not affect your queries or dashboard.

Delete - I don't want to use this again

Remove all project resources at once by deleting the entire GCP project. This stops any potential future charges.

  • In the Google Cloud Console, open the navigation menu.
  • Go to IAM & Admin, then Manage Resources.
  • Select your data-analytics-pipeline project.
  • Click Delete and follow the confirmation prompts.
  • This removes the Cloud Storage bucket, BigQuery dataset, all tables, and the Serverless Spark runtime.
  • Your Looker Studio dashboard will remain in your account but will no longer be able to load data from the deleted BigQuery tables. You can delete the report from the Looker Studio home page.

Mission accomplished

You Did It!

You just built a complete data analytics pipeline on Google Cloud from scratch. Starting with raw CSV files, you migrated, optimized, transformed, and visualized financial transaction data using the core GCP data stack.

Here's what you learned:

  • Built a BigQuery data warehouse with partitioned and clustered tables, migrated financial data using the ELT pattern, and measured the performance improvement of an optimized schema.
  • Implemented both batch and streaming ingestion patterns, wrote SQL analytics with window functions and CTEs, and processed data with PySpark on Serverless Spark.
  • Created an interactive Looker Studio dashboard with dynamic charts and filters backed by BigQuery views.
  • Built a fraud detection anomaly view that flags statistically suspicious transactions and connected it to your dashboard.

Ready to quiz yourself?