GSP1050
Overview
Spanner is Google's fully managed, horizontally scalable relational database service. Customers in financial services, gaming, retail, and many other industries trust it to run their most demanding workloads, where consistency and availability at scale are critical.
In this lab, you review schema-related features of Spanner and apply those to a Banking Operations database. You also review the methods and rules by which Spanner creates query plans.
What you'll do
In this lab, you learn how to modify schema-related attributes of a Spanner instance:
- Load data into tables
- Use prebuilt Python client library code to load data
- Query data with client libraries
- Update the database schema
- Add a secondary index
- Examine query plans
Setup and requirements
Before you click the Start Lab button
Read these instructions. Labs are timed and you cannot pause them. The timer, which starts when you click Start Lab, shows how long Google Cloud resources are made available to you.
This hands-on lab lets you do the lab activities in a real cloud environment, not in a simulation or demo environment. It does so by giving you new, temporary credentials you use to sign in and access Google Cloud for the duration of the lab.
To complete this lab, you need:
- Access to a standard internet browser (Chrome browser recommended).
Note: Use an Incognito (recommended) or private browser window to run this lab. This prevents conflicts between your personal account and the student account, which may cause extra charges incurred to your personal account.
- Time to complete the lab—remember, once you start, you cannot pause a lab.
Note: Use only the student account for this lab. If you use a different Google Cloud account, you may incur charges to that account.
How to start your lab and sign in to the Google Cloud console
-
Click the Start Lab button. If you need to pay for the lab, a dialog opens for you to select your payment method.
On the right is the Lab setup and access panel with the following:
- The Open Google Cloud console button
- The temporary credentials (username and password) that you must use for this lab
- Other information, if needed, to step through this lab
Note that the lab timer is located near the top of the page, showing the remaining time.
-
Click Open Google Cloud console (or right-click and select Open Link in Incognito Window if you are running the Chrome browser).
The lab spins up resources, and then opens another tab that shows the Sign in page.
Tip: Arrange the tabs in separate windows, side-by-side.
Note: If you see the Choose an account dialog, click Use Another Account.
-
If necessary, copy the Username below and paste it into the Sign in dialog.
{{{user_0.username | "Username"}}}
You can also find the Username in the Lab setup and access panel.
-
Click Next.
-
Copy the Password below and paste it into the Welcome dialog.
{{{user_0.password | "Password"}}}
You can also find the Password in the Lab setup and access panel.
-
Click Next.
Important: You must use the credentials the lab provides you. Do not use your Google Cloud account credentials.
Note: Using your own Google Cloud account for this lab may incur extra charges.
-
Click through the subsequent pages:
- Accept the terms and conditions.
- Do not add recovery options or two-factor authentication (because this is a temporary account).
- Do not sign up for free trials.
After a few moments, the Google Cloud console opens in this tab.
Note: To access Google Cloud products and services, click the Navigation menu or type the service or product name in the Search field.
Activate Cloud Shell
Cloud Shell is a virtual machine that is loaded with development tools. It offers a persistent 5GB home directory and runs on the Google Cloud. Cloud Shell provides command-line access to your Google Cloud resources.
-
Click Activate Cloud Shell
at the top of the Google Cloud console.
-
Click through the following windows:
- Continue through the Cloud Shell information window.
- Authorize Cloud Shell to use your credentials to make Google Cloud API calls.
When you are connected, you are already authenticated, and the project is set to your Project_ID, . The output contains a line that declares the Project_ID for this session:
Your Cloud Platform project in this session is set to {{{project_0.project_id | "PROJECT_ID"}}}
gcloud is the command-line tool for Google Cloud. It comes pre-installed on Cloud Shell and supports tab-completion.
- (Optional) You can list the active account name with this command:
gcloud auth list
- Click Authorize.
Output:
ACTIVE: *
ACCOUNT: {{{user_0.username | "ACCOUNT"}}}
To set the active account, run:
$ gcloud config set account `ACCOUNT`
- (Optional) You can list the project ID with this command:
gcloud config list project
Output:
[core]
project = {{{project_0.project_id | "PROJECT_ID"}}}
Note: For full documentation of gcloud, in Google Cloud, refer to the gcloud CLI overview guide.
Spanner instance
To help you move quickly through this lab, the lab environment automatically creates a Spanner instance, database, and tables for you.
Here are the resource details for your reference:
| Item |
Name |
Details |
| Spanner instance |
banking-ops-instance |
Project-level instance |
| Spanner database |
banking-ops-db |
Instance-specific database |
| Table |
Portfolio |
Contains top-level bank offerings |
| Table |
Category |
Contains second-tier bank offering groupings |
| Table |
Product |
Contains specific line-item bank offerings |
| Table |
Campaigns |
Contains details on marketing initiatives |
Task 1. Load data into tables
The banking-ops-db database starts with empty tables. Complete the following steps to load data into three of the tables (Portfolio, Category, and Product).
-
In the Google Cloud console, on the Navigation menu (
), click View all products > Databases > Spanner.
-
Click banking-ops-instance to explore its databases.
-
Click banking-ops-db and scroll down to Tables to view the four existing tables.
-
In the navigation pane, click Spanner Studio, and then click New SQL editor tab (+) to open a new SQL editor.
-
In the SQL editor, paste the following INSERT statement to load the Portfolio table, and then click Run (Spanner runs each row insertion in succession):
INSERT INTO Portfolio (PortfolioId, Name, ShortName, PortfolioInfo)
VALUES
(1, 'Banking', 'Bnkg', 'All Banking Business'),
(2, 'Asset Growth', 'AsstGrwth', 'All Asset Focused Products'),
(3, 'Insurance', 'Ins', 'All Insurance Focused Products');
The Results pane displays the status of the statement. A check mark indicates that the statement completed successfully, and the Portfolio table now has three rows.
-
Click Clear in the SQL editor toolbar.
-
Paste the following INSERT statement to load the Category table, and then click Run:
INSERT INTO Category (CategoryId, PortfolioId, CategoryName)
VALUES
(1, 1, 'Cash'),
(2, 2, 'Investments - Short Return'),
(3, 2, 'Annuities'),
(4, 3, 'Life Insurance');
The Results pane displays the status of the INSERT operation. A check mark indicates that the statement completed successfully, and the Category table now has four rows.
-
Click Clear in the SQL editor toolbar.
-
Paste the following INSERT statement to load the Product table, and then click Run:
INSERT INTO Product (ProductId, CategoryId, PortfolioId, ProductName, ProductAssetCode, ProductClass)
VALUES
(1, 1, 1, 'Checking Account', 'ChkAcct', 'Banking LOB'),
(2, 2, 2, 'Mutual Fund Consumer Goods', 'MFundCG', 'Investment LOB'),
(3, 3, 2, 'Annuity Early Retirement', 'AnnuFixed', 'Investment LOB'),
(4, 4, 3, 'Term Life Insurance', 'TermLife', 'Insurance LOB'),
(5, 1, 1, 'Savings Account', 'SavAcct', 'Banking LOB'),
(6, 1, 1, 'Personal Loan', 'PersLn', 'Banking LOB'),
(7, 1, 1, 'Auto Loan', 'AutLn', 'Banking LOB'),
(8, 4, 3, 'Permanent Life Insurance', 'PermLife', 'Insurance LOB'),
(9, 2, 2, 'US Savings Bonds', 'USSavBond', 'Investment LOB');
The Results pane displays the status of the INSERT operation. A check mark indicates that the statement completed successfully, and the Product table now has nine rows.
Click Check my progress to verify the objective.
Load Data into Portfolio, Category, and Product Tables
Task 2. Use prebuilt Python client library code to load data
In the next several steps, you use the Spanner client library for Python.
- In Cloud Shell, run the following commands to create and switch to a new directory for the required files:
mkdir python-helper
cd python-helper
- Download the environment requirements file and the helper script for this lab:
wget https://storage.googleapis.com/cloud-training/OCBL373/requirements.txt
wget https://storage.googleapis.com/cloud-training/OCBL373/snippets.py
- Install the dependencies for the Spanner client:
pip install -r requirements.txt
pip install setuptools
- The
snippets.py file contains multiple Spanner DDL, DML, and DCL functions that you use as helper code during this lab. Run snippets.py with the insert_data argument to populate the Campaigns table:
python snippets.py banking-ops-instance --database-id banking-ops-db insert_data
Click Check my progress to verify the objective.
Load Data into Campaigns Table
Task 3. Query data with client libraries
You can use the query_data() function in snippets.py to query your database. In this task, you run it to confirm that the script loaded data into the Campaigns table. You do not need to modify the following reference code:
def query_data(instance_id, database_id):
"""Queries sample data from the database using SQL."""
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)
with database.snapshot() as snapshot:
results = snapshot.execute_sql(
"""
SELECT
CampaignId,
PortfolioId,
CampaignStartDate,
CampaignEndDate,
CampaignName,
CampaignBudget
FROM Campaigns
"""
)
for row in results:
print(
"CampaignId: {}, PortfolioId: {}, CampaignStartDate: {}, "
"CampaignEndDate: {}, CampaignName: {}, CampaignBudget: {}".format(*row)
)
- Run
snippets.py with the query_data argument to query the Campaigns table:
python snippets.py banking-ops-instance --database-id banking-ops-db query_data
The output resembles the following:
CampaignId: 1, PortfolioId: 1, CampaignStartDate: 2026-07-23, CampaignEndDate: 2026-07-23, CampaignName: New Account Reward, CampaignBudget: 15000
CampaignId: 2, PortfolioId: 2, CampaignStartDate: 2026-07-23, CampaignEndDate: 2026-07-23, CampaignName: Intro to Investments, CampaignBudget: 5000
CampaignId: 3, PortfolioId: 2, CampaignStartDate: 2026-07-23, CampaignEndDate: 2026-07-23, CampaignName: Youth Checking Accounts, CampaignBudget: 25000
CampaignId: 4, PortfolioId: 3, CampaignStartDate: 2026-07-23, CampaignEndDate: 2026-07-23, CampaignName: Protect Your Family, CampaignBudget: 10000
Task 4. Update the database schema
As a database administrator (DBA), you need to add a new column named MarketingBudget to the Category table. Adding a new column to an existing table requires an update to your database schema. Spanner supports schema updates to a database while the database continues to serve traffic. Schema updates do not require taking the database offline and do not lock entire tables or columns; you can continue reading and writing data to the database during the schema update.
Add a column using Python
The update_ddl() method of the Database class modifies the schema.
Use the add_column() function in snippets.py, which calls this method. You do not need to modify the following reference code:
def add_column(instance_id, database_id):
"""Adds a new column to the Category table."""
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)
operation = database.update_ddl(
[
"ALTER TABLE Category ADD COLUMN MarketingBudget INT64"
]
)
print("Waiting for operation to complete...")
operation.result(OPERATION_TIMEOUT_SECONDS)
print("Added the MarketingBudget column.")
- Run
snippets.py with the add_column argument:
python snippets.py banking-ops-instance --database-id banking-ops-db add_column
Click Check my progress to verify the objective.
Add column to Category table
Other options to add a column to an existing table
Issue a DDL command with the gcloud CLI
Note: This option is shown as an alternate example. Do not run this command.
The following command performs the same schema update that you ran with Python:
gcloud spanner databases ddl update banking-ops-db --instance=banking-ops-instance --ddl='ALTER TABLE Category ADD COLUMN MarketingBudget INT64;'
Issue a DDL command in the Google Cloud console
Note: This option is shown as an alternate example. Do not perform this action.
- Click the table name in the database listing.
- Click Write DDL.
- Paste the DDL statement in the DDL Templates field.
- Click Submit.

Write data to the new column
The following code writes data to the new column. It sets MarketingBudget to 100000 for the row with a CategoryId of 1 and a PortfolioId of 1, and sets it to 500000 for the row with a CategoryId of 3 and a PortfolioId of 2. You do not need to modify the following reference code:
def update_data(instance_id, database_id):
"""Updates sample data in the database.
This updates the `MarketingBudget` column, which must be created before
running this sample. You can add the column by running the `add_column`
sample or by running this DDL statement against your database.
"""
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)
with database.batch() as batch:
batch.update(
table="Category",
columns=("CategoryId", "PortfolioId", "MarketingBudget"),
values=[
(1, 1, 100000),
(3, 2, 500000),
],
)
print("Updated data.")
- Run
snippets.py with the update_data argument:
python snippets.py banking-ops-instance --database-id banking-ops-db update_data
- Query the table again to view the update by running
snippets.py with the query_data_with_new_column argument:
python snippets.py banking-ops-instance --database-id banking-ops-db query_data_with_new_column
The output is as follows:
CategoryId: 1, PortfolioId: 1, MarketingBudget: 100000
CategoryId: 2, PortfolioId: 2, MarketingBudget: None
CategoryId: 3, PortfolioId: 2, MarketingBudget: 500000
CategoryId: 4, PortfolioId: 3, MarketingBudget: None
Task 5. Add a secondary index
Suppose you want to fetch all rows from the Category table that have CategoryName values in a certain range. You could read all values from the CategoryName column using a SQL statement or a read call, and then discard the rows that don't meet the criteria, but doing this full table scan is expensive, especially for tables with many rows. Instead, you can speed up the retrieval of rows when searching by non-primary key columns by creating a secondary index on the table.
Adding a secondary index to an existing table requires a schema update. Like other schema updates, Spanner supports adding an index while the database continues to serve traffic. Spanner populates the index with data (also known as a backfill) automatically in the background. Backfills might take several minutes to complete, but you don't have to take the database offline or avoid writing to certain tables or columns during this process.
Add a secondary index using the Python client library
Use the add_index() function to create a secondary index. You do not need to modify the following reference code:
def add_index(instance_id, database_id):
"""Adds a secondary index to the Category table."""
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)
operation = database.update_ddl(
[
"CREATE INDEX CategoryByCategoryName "
"ON Category(CategoryName)"
]
)
print("Waiting for operation to complete...")
operation.result(OPERATION_TIMEOUT_SECONDS)
print("Added the CategoryByCategoryName index.")
- Run
snippets.py with the add_index argument:
python snippets.py banking-ops-instance --database-id banking-ops-db add_index
Click Check my progress to verify the objective.
Add secondary index to Category table
Read using the index
To read data using the index, call the read() method with the index specified. You do not need to modify the following reference code:
def read_data_with_index(instance_id, database_id):
"""Reads sample data from the database using an index."""
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)
with database.snapshot() as snapshot:
keyset = spanner.KeySet(all_=True)
results = snapshot.read(
table="Category",
columns=("CategoryId", "CategoryName"),
keyset=keyset,
index="CategoryByCategoryName",
)
for row in results:
print("CategoryId: {}, CategoryName: {}".format(*row))
- Run
snippets.py with the read_data_with_index argument:
python snippets.py banking-ops-instance --database-id banking-ops-db read_data_with_index
The output is as follows:
CategoryId: 3, CategoryName: Annuities
CategoryId: 1, CategoryName: Cash
CategoryId: 2, CategoryName: Investments - Short Return
CategoryId: 4, CategoryName: Life Insurance
Add an index with a STORING clause
Notice that the preceding read example did not read the MarketingBudget column. Spanner's read interface does not support joining an index with a data table to look up values that the index does not store.
To bypass this restriction, create an alternate definition of the CategoryByCategoryName index that stores a copy of MarketingBudget in the index.
Use the update_ddl() method of the Database class to add an index with a STORING clause. You do not need to modify the following reference code:
def add_storing_index(instance_id, database_id):
"""Adds a storing index to the example database."""
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)
operation = database.update_ddl(
[
"CREATE INDEX CategoryByCategoryName2 ON Category(CategoryName)"
"STORING (MarketingBudget)"
]
)
print("Waiting for operation to complete...")
operation.result(OPERATION_TIMEOUT_SECONDS)
print("Added the CategoryByCategoryName2 index.")
- Run
snippets.py with the add_storing_index argument:
python snippets.py banking-ops-instance --database-id banking-ops-db add_storing_index
Now you can run a read operation that fetches the CategoryId, CategoryName, and MarketingBudget columns using the CategoryByCategoryName2 index. You do not need to modify the following reference code:
def read_data_with_storing_index(instance_id, database_id):
"""Reads sample data using an index with a storing clause."""
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)
with database.snapshot() as snapshot:
keyset = spanner.KeySet(all_=True)
results = snapshot.read(
table="Category",
columns=("CategoryId", "CategoryName", "MarketingBudget"),
keyset=keyset,
index="CategoryByCategoryName2",
)
for row in results:
print(
"CategoryId: {}, CategoryName: {}, MarketingBudget: {}".format(
*row
)
)
- Run
snippets.py with the read_data_with_storing_index argument:
python snippets.py banking-ops-instance --database-id banking-ops-db read_data_with_storing_index
The output is as follows:
CategoryNameId: 3, CategoryName: Annuities, MarketingBudget: 500000
CategoryNameId: 1, CategoryName: Cash, MarketingBudget: 100000
CategoryNameId: 2, CategoryName: Investments - Short Return, MarketingBudget: None
CategoryNameId: 4, CategoryName: Life Insurance, MarketingBudget: None
Task 6. Examine query plans
In this section, you explore Spanner query plans.
- Return to the Google Cloud console, where Spanner Studio is open. Clear any existing SQL in the editor, paste the following query, and then click Run:
SELECT Name, ShortName, CategoryName
FROM Portfolio
INNER JOIN Category
ON Portfolio.PortfolioId = Category.PortfolioId;
The result resembles the following:

Life of a query
Spanner first compiles a SQL query into an execution plan and sends it to an initial root server for execution. Spanner chooses the root server to minimize the number of hops required to reach the queried data. The root server then performs the following actions:
- Initiates remote execution of subplans (if necessary)
- Waits for results from the remote executions
- Handles any remaining local execution steps such as aggregating results
- Returns results for the query
Remote servers that receive a subplan act as the "root" server for their subplan, following the same model as the top-most root server. The result is a tree of remote executions. Conceptually, query execution flows from top to bottom, and the servers return query results from bottom to top. The following diagram shows this pattern:

Aggregate query
Now examine the query plan for an aggregate query.
- In Spanner Studio, clear the existing SQL from the editor, paste the following query, and then click Run:
SELECT
pr.ProductId,
COUNT(*) AS ProductCount
FROM Product AS pr
WHERE pr.ProductId < 100
GROUP BY pr.ProductId;
- After the query completes, click the Explanation tab to examine the query plan.
Spanner sends the execution plan to a root server that coordinates the query execution and performs the remote distribution of subplans.
This execution plan starts with a serialization step that orders all returned values. Next, the plan runs an initial hash aggregate operator to calculate preliminary results, and then runs a distributed union that distributes subplans to remote servers whose splits satisfy ProductId < 100. The distributed union sends results to a final hash aggregate operator. The aggregate operator performs the COUNT aggregation by ProductId and returns results to a serialize result operator. Finally, the plan runs a scan to order the returned results.
The query plan resembles the following:

Tip: To view more details for each step in the query plan, click any operator in the graph to open its details pane.

Co-located join queries
Spanner physically stores interleaved tables with the rows of their related tables co-located. A join between interleaved tables is known as a co-located join. Co-located joins can offer performance benefits over joins that require indexes or back joins.
- In Spanner Studio, clear the existing SQL from the editor, paste the following query, and then click Run:
SELECT
c.CategoryName,
pr.ProductName
FROM Category AS c, Product AS pr
WHERE c.PortfolioId = pr.PortfolioId
AND c.CategoryId = pr.CategoryId;
- After the query completes, click the Explanation tab to examine the query plan.
This execution plan starts with a distributed union, which distributes subplans to remote servers that have splits of the Category table. Because Product is an interleaved table of Category, each remote server can execute the entire subplan locally without requiring a join across different servers.
The subplans contain a cross apply. Each cross apply performs a table scan on the Category table to retrieve PortfolioId, CategoryId, and CategoryName. The cross apply then maps output from the table scan to output from an index scan on the CategoryByCategoryName index, subject to a filter of the PortfolioId in the index matching the PortfolioId from the table scan output. Each cross apply sends its results to a serialize result operator that serializes the CategoryName and ProductName data and returns results to the local distributed unions. The distributed union aggregates results from the local distributed unions and returns them as the query result.

Congratulations!
You now have a solid understanding of the schema-related features of Spanner and the methods by which Spanner creates query plans.
Google Cloud training and certification
...helps you make the most of Google Cloud technologies. Our classes include technical skills and best practices to help you get up to speed quickly and continue your learning journey. We offer fundamental to advanced level training, with on-demand, live, and virtual options to suit your busy schedule. Certifications help you validate and prove your skill and expertise in Google Cloud technologies.
Manual Last Updated November 12, 2026
Lab Last Tested July 23, 2026
Copyright 2026 Google LLC. All rights reserved. Google and the Google logo are trademarks of Google LLC. All other company and product names may be trademarks of the respective companies with which they are associated.