Study guide
10.2 Cloud Spanner Schema Design: Primary Keys, Interleaved Tables, and Query Tuning
Google Cloud Certified Professional Data Engineer Exam
Track your progress as you read
Finished sections get a check mark.
1 Chapter 1: Security, IAM, and Data Protection
1.1 Cloud IAM, Service Accounts, and Resource Hierarchy in GCP Data Systems 1.2 Data Encryption, Key Management (CMEK), and Cloud KMS 1.3 Sensitive Data Protection: Cloud DLP, De-identification, and Masking
2 Chapter 2: Reliability and Compliance Architecture
2.1 High Availability, Regional vs Multi-Regional Deployments, and Disaster Recovery 2.2 Data Residency, Sovereignty, and Regulatory Compliance (HIPAA, GDPR) 2.3 Modern Data Architecture Patterns: Data Mesh, Lakehouse, and Modern Data Stack
3 Chapter 3: Cross-Cloud and Federated Governance
3.1 BigLake Architecture: Unified Multicloud Storage and Fine-Grained Access Control 3.2 BigQuery Omni: Multicloud Analytics Across AWS S3, Azure ADLS, and Google Cloud 3.3 Analytics Hub and Federated Computational Governance
4 Chapter 4: Data Ingestion and Pipeline Planning
4.1 Data Pipeline Planning: Sources, Sinks, and Network Architecture (VPC, PSC, Interconnect) 4.2 Batch vs Streaming Processing Trade-Offs and Architecture Selection 4.3 Change Data Capture (CDC) with Datastream for Continuous Database Ingestion
5 Chapter 5: Streaming Ingestion with Cloud Pub/Sub
5.1 Cloud Pub/Sub Core Architecture: Topics, Subscriptions, and Message Lifecycle 5.2 Pub/Sub Message Ordering, Exactly-Once Delivery, and Deduplication 5.3 Pub/Sub BigQuery/Cloud Storage Direct Subscriptions and Dead-Letter Topics
6 Chapter 6: Pipeline Processing with Apache Beam and Cloud Dataflow
6.1 Apache Beam Programming Model: Pipelines, PCollections, and Core PTransforms 6.2 Cloud Dataflow Architecture: Autoscaling, Worker Allocation, and Dataflow Prime 6.3 Windowing Strategies in Streaming Dataflow: Fixed, Sliding, and Session Windows
7 Chapter 7: Advanced Dataflow: Robustness and Exactly-Once Semantics
7.1 Watermarks, Triggers, Accumulating vs Discarding Panes, and Late Data Handling 7.2 Side Inputs, Broadcast Joins, and Stateful Stream Processing 7.3 Pipeline Robustness: Dead-Letter Queues, Error Handling, and Pipeline Lifecycle Management
8 Chapter 8: Managed Data Processing: Cloud Dataproc and Cloud Data Fusion
8.1 Cloud Dataproc: Architecture, Ephemeral vs Persistent Clusters, and Spark Tuning 8.2 Dataproc Serverless and Autoscaling Policies for Batch Processing 8.3 Cloud Data Fusion: Visual ETL/ELT Pipelines, CDAP Plugins, and Data Lineage
9 Chapter 9: Storage Selection and Cloud Storage Architecture
9.1 GCP Storage Selection Decision Matrix: Relational, NoSQL, Object, and Analytical Stores 9.2 Cloud Storage Architecture: Storage Classes, Lifecycle Rules, and Object Versioning 9.3 Cloud Storage Security: Bucket Lock (WORM), Access Control, and Data Migration Tools
10 Chapter 10: Relational Cloud Databases: Cloud Spanner and Cloud SQL
10.1 Cloud Spanner Architecture: TrueTime, External Consistency, and Global Scalability 10.2 Cloud Spanner Schema Design: Primary Keys, Interleaved Tables, and Query Tuning 10.3 Cloud SQL: High Availability, Read Replicas, Maintenance, and Point-in-Time Recovery
11 Chapter 11: Scalable NoSQL Databases: Cloud Bigtable and Firestore
11.1 Cloud Bigtable Architecture: Storage Model, Tablet Splitting, and Cluster Management 11.2 Cloud Bigtable Row Key Design: Lexicographical Ordering, Salt Keys, and Anti-Patterns 11.3 Cloud Firestore: Document Model, Real-Time Sync, and ACID Collections
12 Chapter 12: Enterprise Data Warehousing with BigQuery
12.1 BigQuery Architecture: Capacitor Columnar Format, Jupiter Network, and Borg Slots 12.2 BigQuery Partitioning: Ingestion-Time, Date/Timestamp, and Integer Range Strategies 12.3 BigQuery Clustering: Cardinality, Multi-Column Ordering, and Performance Optimization 12.4 Data Warehouse Modeling: Normalization, Star Schemas, and Slowly Changing Dimensions
13 Chapter 13: Advanced Analytical Queries and Transformations in BigQuery
13.1 Advanced SQL in BigQuery: Window Analytic Functions, ARRAY/STRUCT, and PIVOT/UNPIVOT 13.2 Modern SQL Orchestration with Dataform: DAG Modeling, Testing, and Documentation 13.3 BI Engine In-Memory Acceleration and Enterprise Looker/LookML Semantic Modeling
14 Chapter 14: Machine Learning and Feature Engineering with BigQuery ML
14.1 BigQuery ML Architecture: In-Database Training, Model Topologies, and SQL Analytics 14.2 Feature Engineering: ML.TRANSFORM, Preprocessing Pipelines, and Vertex AI Feature Store 14.3 MLOps, Model Governance, Continuous Monitoring, and Deployment Lifecycles
15 Chapter 15: Operations, Orchestration, and Data Governance
15.1 Workflow Orchestration with Cloud Composer: Managed Airflow, DAG Design, and Dependencies 15.2 Unified Data Governance with Dataplex: Lakes, Zones, Data Quality, and Lineage 15.3 Pipeline Observability, FinOps, and Cost Optimization Across GCP Data Services
10.2 Cloud Spanner Schema Design: Primary Keys, Interleaved Tables, and Query Tuning
Key Takeaways Explain This Topic AI
Monotonically increasing or sequential primary keys (e.g., AUTO_INCREMENT, sequences, sequential timestamps) route all consecutive writes to a single split and Paxos leader, creating severe write hotspots capped at ~1,000-2,000 writes/second.
Hotspots are eliminated by engineering high-entropy primary keys using randomly distributed UUIDv4 values, hash prefixes (e.g., FARM_FINGERPRINT modulo N), or bit-reversed sequential integer sequences.
Table interleaving physically co-locates child table rows alongside parent table rows within the same split on Colossus, transforming distributed multi-node joins into ultra-fast local single-split seeks and enabling atomic parent-child cascade deletions.
Secondary indexes function as separate distributed tables; adding a STORING clause includes frequently queried non-key columns directly in the index split, completely eliminating costly network back-joins to the base table.
NULL-filtered indexes (using WHERE column IS NOT NULL) conserve storage and reduce write amplification by excluding empty or sparse rows from secondary index maintenance.
Last updated: September 2026
10.2 Cloud Spanner Schema Design: Primary Keys, Interleaved Tables, and Query Tuning
Exam Focus: The Professional Data Engineer exam rigorously tests your ability to design performant, horizontally scalable relational schemas in Cloud Spanner. You will be presented with failure scenarios where Spanner exhibits high write latency, CPU spikes on a single node, or slow join queries. To pass, you must know how to eliminate write hotspots using high-entropy primary key strategies (hash prefixing, UUIDv4, bit-reversal), co-locate relational hierarchies using interleaved tables, eliminate base table back-joins using the STORING clause on secondary indexes, and analyze query execution plans for distributed cross-applies.
In a single-instance relational database like Cloud SQL or Oracle, schema design focuses primarily on normal forms (3NF), B-tree index coverage, and foreign key integrity. In Cloud Spanner, schema design directly governs data placement across a distributed cluster . Because Spanner automatically partitions tables into lexicographically sorted key ranges called splits , an improperly designed primary key can funnel millions of concurrent writes into a single server, neutralizing Spanner's horizontal scaling capabilities.
1. Split Boundaries and the Monotonic Primary Key Hotspot Anti-Pattern
To understand why primary key selection is critical in Spanner, consider how data is physically partitioned: +───────────────────────────────────────────────────────────────────────────────────+
- | LEXICOGRAPHICALLY SORTED SPLIT PARTITIONS |
- +───────────────────────────────────────────────────────────────────────────────────+
- | Split 1: Keys [0000 - 3333] │ Split 2: Keys [3334 - 6666] │ Split 3: Keys [6667 - 9999] |
- | Managed by Node A (Leader) │ Managed by Node B (Leader) │ Managed by Node C (Leader) |
- +──────────────────────────────┴──────────────────────────────┴─────────────────────+
Spanner maintains all rows in sorted order by their primary key. Rows residing within a given key range are grouped into a split , and each split is assigned to a specific compute node's Paxos group.
The Anti-Pattern: Monotonically Increasing Keys
In traditional relational systems, primary keys often use auto-incrementing integers ( SERIAL , AUTO_INCREMENT , or database sequences) or sequential timestamps (e.g., event_timestamp ).
When a table in Spanner uses a monotonically increasing key:
Every newly inserted row has a primary key strictly greater than the previous row.
Because keys are ordered lexicographically, 100% of all incoming insert operations target the exact end of the key space .
The end of the key space resides entirely within a single split managed by a single Paxos Leader compute node .
Regardless of whether the Spanner cluster is provisioned with 3 nodes, 30 nodes, or 300 nodes, only one single node processes all incoming writes!
The single node saturates its CPU and disk I/O, hitting a hard throughput ceiling of approximately 1,000 to 2,000 writes per second . All other provisioned compute nodes sit completely idle. [Client Inserts] ───► Key: 2026-09-15 10:00:01 ───┐
- [Client Inserts] ───► Key: 2026-09-15 10:00:02 ───┼──► [Split 3 / Node C] (100% CPU HOTSPOT)
- [Client Inserts] ───► Key: 2026-09-15 10:00:03 ───┘
- [Split 1 / Node A] (0% CPU - Idle)
- [Split 2 / Node B] (0% CPU - Idle)
- 2. High-Entropy Primary Key Strategies
To achieve linear write scalability where throughput scales proportionally with the number of provisioned nodes, primary keys must distribute inserts uniformly across the entire key space and across all available splits.
Strategy A: Universally Unique Identifiers (UUIDv4)
Mechanics: Generate randomly distributed 128-bit UUID Version 4 values (e.g., a5c89e21-4f12-4e89-918d-6c1e92d8f012 ). In Google Standard SQL for Spanner, use the GENERATE_UUID() function.
Advantages: Completely random distribution across the entire hexadecimal space; zero coordination required between client application threads.
Trade-off: 16 bytes (or 36-character string representation) consumes slightly more storage than a 64-bit integer, and random inserts cause index fragmentation in cache buffers.
Strategy B: Hash Prefixing (Sharding Buckets)
Mechanics: When the application domain requires a natural sequential identifier or timestamp, prepend a deterministic hash prefix to the key. In Spanner SQL, compute a hash bucket using FARM_FINGERPRINT : CREATE TABLE SensorReadings (
- ShardId INT64,
- DeviceId STRING(64),
- ReadingTimestamp TIMESTAMP,
- Temperature FLOAT64
- ) PRIMARY KEY (ShardId, DeviceId, ReadingTimestamp);
The client or a generated column computes ShardId = MOD(ABS(FARM_FINGERPRINT(DeviceId)), 10) . This spreads incoming writes evenly across 10 discrete key ranges (shards 0 through 9), distributing writes across 10 different splits and compute nodes.
Strategy C: Bit-Reversed Sequences
Mechanics: If the application requires sequential numeric IDs, reverse the binary bits of the sequence number before storing it as the primary key. Sequential numbers like 1, 2, 3 have consecutive binary representations, but their bit-reversed counterparts alternate between opposite ends of the 64-bit integer spectrum:
- Integer 1 ( 000...0001 ) -> Bit-reversed: 100...0000 (large negative/high number)
- Integer 2 ( 000...0010 ) -> Bit-reversed: 010...0000 (mid-range number)
- Integer 3 ( 000...0011 ) -> Bit-reversed: 110...0000
Advantages: Eliminates hotspotting while preserving sequential uniqueness without relying on random UUID generation. In Google Standard SQL, Spanner provides the BIT_REVERSE() function and auto-generating bit-reversed sequences ( CREATE SEQUENCE ... OPTIONS (sequence_kind = 'bit_reversed_positive') ).
Strategy D: Swapping Key Column Ordering
When designing composite primary keys, never place a sequential timestamp as the leading column. Instead, place a high-cardinality, uniformly distributed identifier first:
- Hotspot Anti-Pattern: PRIMARY KEY (EventTimestamp, CustomerId)
- Scalable Pattern: PRIMARY KEY (CustomerId, EventTimestamp)
- 3. Interleaved Tables: Co-Locating Relational Hierarchies
In distributed databases, joining two large tables that reside on different physical servers requires a distributed cross-join or distributed cross-apply , transferring millions of rows across the datacenter network and severely degrading query performance. Cloud Spanner solves this with Interleaved Tables .
The Interleaving Concept
Interleaving creates a strict parent-child relationship at the physical storage level. Spanner physically co-locates child rows directly alongside their corresponding parent row within the exact same Colossus split! +───────────────────────────────────────────────────────────────────────────────────+
- | PHYSICAL INTERLEAVED STORAGE ON COLOSSUS |
- +───────────────────────────────────────────────────────────────────────────────────+
- | Split 1 (Node A): |
- | CustomerId: 100 (Parent Row) |
- | ├── OrderId: 100-1 (Child Row) |
- | │ ├── LineItemId: 100-1-A (Grandchild Row) |
- | │ └── LineItemId: 100-1-B (Grandchild Row) |
- | └── OrderId: 100-2 (Child Row) |
- | CustomerId: 101 (Parent Row) |
- | └── OrderId: 101-1 (Child Row) |
- +───────────────────────────────────────────────────────────────────────────────────+
- DDL Implementation
To interleave a child table into a parent table, the child table's primary key must start with the parent table's complete primary key, followed by the INTERLEAVE IN PARENT clause: -- Parent Table
- CREATE TABLE Customers (
- CustomerId STRING(36),
- CustomerName STRING(100),
- Email STRING(100)
- ) PRIMARY KEY (CustomerId);
- - Interleaved Child Table
- CREATE TABLE Orders (
- CustomerId STRING(36),
- OrderId STRING(36),
- OrderDate TIMESTAMP,
- TotalAmount NUMERIC
- ) PRIMARY KEY (CustomerId, OrderId),
- INTERLEAVE IN PARENT Customers ON DELETE CASCADE;
- - Interleaved Grandchild Table
- CREATE TABLE OrderLineItems (
- CustomerId STRING(36),
- OrderId STRING(36),
- LineItemId INT64,
- Sku STRING(32),
- Quantity INT64,
- UnitPrice NUMERIC
- ) PRIMARY KEY (CustomerId, OrderId, LineItemId),
- INTERLEAVE IN PARENT Orders ON DELETE CASCADE;
- Architectural Benefits of Interleaving
Zero-Network Joins: Queries that join parent and child records ( SELECT * FROM Customers c JOIN Orders o ON c.CustomerId = o.CustomerId WHERE c.CustomerId = 'C1' ) execute as a local single-split scan . The compute node reads the contiguous data from local memory or Colossus without initiating cross-node network RPCs.
Atomic Hierarchical ACID Transactions: Mutations affecting a customer, their orders, and their line items all execute against the same Paxos group, avoiding expensive distributed Two-Phase Commit (2PC) coordination.
Cascading Deletions: Specifying ON DELETE CASCADE automatically and atomically prunes all associated child and grandchild records when a parent record is deleted.
When NOT to Interleave Tables
Independent Access Patterns: If the child table is frequently queried across all customers without filtering by CustomerId (e.g., SELECT * FROM Orders WHERE OrderDate > '2026-09-01' ), interleaving forces Spanner to perform a full distributed table scan across every customer split.
Size Constraints: All interleaved data for a single root parent row should not grow boundlessly. While a split can hold up to 4 GB, massive single-parent hierarchies that grow uncontrollably can cause split management inefficiencies.
Multi-Parent Relationships: A table can only be interleaved in one parent. Many-to-many relationships cannot be modeled with simple interleaving.
4. Secondary Indexes: Global Sharding, the STORING Clause, and NULL-Filtered Indexes
Secondary indexes in Spanner are not local B-trees stored inside the base table; every secondary index is an entirely separate distributed table partitioned into its own independent splits.
- The Back-Join Overhead
- Suppose you create a simple index on Orders.OrderDate : CREATE INDEX OrdersByOrderDate ON Orders(OrderDate);
- If you execute the following query: SELECT OrderId, OrderDate, TotalAmount, Status
- FROM Orders
- WHERE OrderDate >= '2026-09-01';
Spanner scans the OrdersByOrderDate index splits to find matching OrderDate entries.
The index contains only OrderDate and the base table's primary keys ( CustomerId , OrderId ).
To retrieve TotalAmount and Status , Spanner must execute a back-join : it performs network RPCs back to the base Orders table splits distributed across other nodes to fetch the non-indexed columns.
If the query matches 50,000 orders, Spanner initiates thousands of cross-node network lookups, causing severe query latency.
The Solution: The STORING Clause
The STORING clause copies specified non-key columns directly into the secondary index table: CREATE INDEX OrdersByOrderDate ON Orders(OrderDate)
STORING (TotalAmount, Status);
Impact: The index now physically contains OrderDate , CustomerId , OrderId , TotalAmount , and Status . The query is satisfied entirely by scanning the index split ( covering index ). Zero network back-joins are performed.
Trade-off: Increases storage consumption and incurs write amplification, as every update to TotalAmount or Status requires writing to both the base table and the index table.
NULL-Filtered Indexes
Many operational workflows process records based on a transient status, such as unprocessed tasks or unfulfilled orders. In such tables, 99% of historical rows have a NULL or completed timestamp. CREATE INDEX UnprocessedOrdersIndex ON Orders(ScheduledShipDate)
WHERE ScheduledShipDate IS NOT NULL;
Impact: Rows where ScheduledShipDate IS NULL are completely excluded from the index. This reduces index storage from gigabytes to megabytes and eliminates write overhead for all records where the column is unset.
5. Query Execution Plans and Performance Tuning
When optimizing Spanner queries, data engineers inspect query execution plans via the Google Cloud Console, gcloud spanner databases execute-sql --query-mode=PLAN , or the EXPLAIN statement. +───────────────────────────────────────────────────────────────────────────────────+
- | DISTRIBUTED QUERY EXECUTION OPERATORS |
- +───────────────────────────────────────────────────────────────────────────────────+
- | Distributed Cross Apply: |
- | - Outer input evaluated on coordinator node |
- | - Batch RPCs sent to distributed splits for inner evaluation |
- | - High latency if inner input requires frequent cross-network back-joins |
- | |
- | Distributed Union: |
- | - Coordinator sends parallel sub-queries to all participating splits |
- | - Merges sorted streams from remote nodes; highly efficient for scans |
- +───────────────────────────────────────────────────────────────────────────────────+
- Key Query Tuning Best Practices
Force Index Hints: If Spanner's cost-based query optimizer selects a full table scan over an index due to stale statistics, force the index explicitly: SELECT OrderId, TotalAmount
- FROM Orders @{FORCE_INDEX=OrdersByOrderDate}
- WHERE OrderDate >= '2026-09-01';
Parameterize All Queries: Never concatenate string literals into dynamic SQL statements. Parameterized queries ( WHERE CustomerId = @cid ) enable Spanner to cache execution plans in memory across calls, preventing costly plan compilation overhead.
Use Read-Only Transactions for Multi-Statement Reads: Wrap multi-query read workflows in an explicit read-only transaction ( readOnly: true ). Read-only transactions do not acquire shared locks, never abort due to lock contention, and do not trigger commit-wait delays.
6. Primary Key Strategy Trade-Off Matrix
Strategy Hotspot Resistance Monotonic Ordering Key Size (Bytes) Recommended Use Case
UUIDv4 ( GENERATE_UUID() ) Maximum (Uniform random) No 16 bytes (binary) / 36 chars High-volume OLTP user IDs, payments, orders
Bit-Reversed Sequence Maximum (Uniform spread) Pseudo-random 8 bytes ( INT64 ) Replacing legacy auto-increment sequences
Hash Prefixing ( FARM_FINGERPRINT ) High (Configurable N buckets) Preserved within bucket 8 bytes prefix + natural key Sharding sequential IoT/telemetry events
- Sequential Timestamp / Serial ZERO (Severe Hotspot) Yes 8 bytes Anti-pattern; strictly prohibited
- 7. Real-World Exam Scenarios and Architectural Anti-Patterns
- Scenario 1: E-Commerce Order Flash Sale Hotspot
Requirement: A retail platform launches a flash sale expecting 50,000 order insertions per second. The initial schema uses PRIMARY KEY (CreatedAt, OrderId) . During preliminary load testing, write latency spikes to over 5 seconds and write throughput caps at 1,800 QPS with a single Spanner node pinned at 100% CPU.
The Anti-Pattern: Using CreatedAt as the leading primary key column. All orders for the current second hit the same split and the same Paxos leader node.
The Certified Architecture: Redesign the primary key to use a randomly generated UUIDv4 OrderId as the leading column ( PRIMARY KEY (OrderId) ), or prepend a hash shard ID: PRIMARY KEY (ShardId, CreatedAt, OrderId) . This distributes write transactions across all provisioned nodes, scaling throughput linearly to 50,000+ QPS.
Scenario 2: Slow Order History Join Queries
Requirement: An online banking portal displays recent transactions when a user logs in. The query joins Accounts and Transactions on AccountId . Under peak login load, query latency degrades to 800ms due to distributed cross-apply joins between separate splits.
The Anti-Pattern: Leaving Accounts and Transactions as independent top-level tables with foreign keys.
The Certified Architecture: Interleave Transactions into Accounts : CREATE TABLE Transactions (...) PRIMARY KEY (AccountId, TransactionId), INTERLEAVE IN PARENT Accounts ON DELETE CASCADE; . This guarantees that an account and all its transactions reside on the exact same physical split, converting distributed cross-network joins into sub-millisecond local storage lookups.
8. Common Exam Pitfalls and Gotchas
Pitfall 1: Using Foreign Keys Instead of Interleaving for Hierarchical 1:N Data: While Spanner supports standard foreign keys, foreign keys only enforce referential integrity; they do not co-locate data physically. Joins across foreign keys still execute as distributed network joins. Interleaving physically co-locates data on the same split.
Pitfall 2: Neglecting the STORING Clause on High-Frequency Lookups: Creating a secondary index without STORING forces Spanner to execute a base table back-join for every projected column not in the index. On large result sets, this causes severe network RPC latency.
Pitfall 3: Indexing Columns with High NULL Ratios Without a Filter: Indexing an optional timestamp column across 100 million rows where 98% of values are NULL wastes index storage and creates write overhead. Always use a NULL-filtered index ( WHERE Column IS NOT NULL ).
Pitfall 4: Misunderstanding Interleaved Table Deletions: Attempting to delete a parent row that contains interleaved child rows without specifying ON DELETE CASCADE in the DDL will cause the delete statement to fail with an integrity error.