# Data Modeling and Schema Design

> Chapter 3 of the Courseiva GOOGLE-PDE curriculum — https://courseiva.com/learn/google-pde/data-modeling-and-schema-design

**Official objective:** 1.3 — Design data models and schemas (relational, NoSQL, and BigQuery) to support analytical and operational workloads.

## Introduction

Exam objective 1.3 — design data models and schemas — is the difference between storing data in a messy pile of papers and filing it in a colour-coded, cross-referenced cabinet that you can search in seconds. For the Google Professional Data Engineer exam, mastering this topic tells the examiner you understand how to choose the right storage structure for the job, whether that is a strict relational table, a flexible NoSQL document, or a hybrid columnar system like BigQuery. The entire exam expects you to reason about trade-offs: speed vs. consistency, storage cost vs. query performance, and structured vs. unstructured data.

## The City Planning Department Analogy

A city planner is responsible for designing the layout of a new neighbourhood before a single brick is laid. They decide where roads go, where houses, shops, and parks are placed, and how water and electricity pipes connect everything. If they put all the houses on one tiny street without thinking about how many people will live there, the road will be jammed every morning. The same planner also has to design a different kind of map for the waste management team — one that tracks where bins are collected, how often, and what types of rubbish each bin contains. That second map might be a simple list of addresses and collection days, not a detailed blueprint of the neighbourhood.

Data modeling and schema design is exactly this: creating the blueprint (the model) and the detailed instructions (the schema) for how data is stored, connected, and accessed. The neighbourhood is your database. The roads are the relationships between data points. The planner is the data engineer who decides whether to use a rigid grid (relational model) or a flexible, organic layout (NoSQL model). If the planner gets the design wrong, the neighbourhood either runs out of space for new houses or the traffic becomes a nightmare. In data, a bad model means slow queries, wasted storage, or data that is impossible to connect meaningfully.

This analogy works because it maps precisely to how data flows: the planner must anticipate future growth (the next five years of data), different uses (analytical workloads versus operational transactions), and the trade-offs between a strict, predictable structure and a flexible, fast one. It is not a generic “router is like a traffic cop” — it is a specific design job with real consequences for every building (table) and pipe (connection) in the system.

## Core explanation

Data modeling is the process of deciding what data you need to store and how the pieces of that data relate to each other. Schema design is the actual implementation — the blueprint that tells the database system exactly what fields each record contains, what data types those fields hold (like text, number, or date), and any rules about what values are allowed.

Think of it like planning a library. A data model is the decision to categorise books by genre, author, and topic. The schema is the exact shelf layout: the Fiction section has a sign, the shelves are labelled A-Z by author surname, and each book has a bar code that links to a card in the catalogue. The card catalogue itself is another schema — it lists the book title, author, ISBN, and location. Without a model, the library is just a pile of books. Without a schema, the librarian cannot find anything.

There are three main families of data models you need to know for the PDE exam:

- Relational model: Data is organised into tables (like spreadsheets) with rows (records) and columns (fields). Tables are connected through keys — a primary key uniquely identifies each row in a table, and a foreign key in one table points to a primary key in another table. This model is rigid: you must define all columns and their data types upfront. It is excellent for transactional workloads — think bank transactions or airline bookings — where consistency and accuracy are critical. The SQL language is used to query relational databases.

- NoSQL model: This is a broad category that includes several sub-types. Document stores store data as JSON-like documents (like a file card with nested information). Key-value stores are the simplest: you have a unique key and the value can be anything — like a locker number and whatever you put inside. Wide-column stores (like Bigtable) organise data into rows but allow different columns per row, which is great for time-series data like temperature readings every second. Graph databases store nodes (entities) and edges (relationships), perfect for social networks or recommendation engines. NoSQL models are flexible — you can add new fields without changing the schema — but they often trade away strong consistency for speed and scale.

- BigQuery columnar model: BigQuery is a data warehouse — it stores data in columns, not rows. This matters because analytical queries often scan only a few columns across millions of rows. For example, if you want the total sales per region, BigQuery reads only the "sales_amount" and "region" columns, ignoring everything else. This makes it extremely fast for analytics but slower for single-row lookups (which is fine because that is not what it is designed for). The schema in BigQuery is still defined upfront, but it supports nested and repeated fields — a single row can contain a whole array of related data, like all the items in one customer order.

The critical skill for the exam is deciding which model to use for which job. For a banking system, you need the strict consistency of a relational model — you cannot let two customers withdraw the last $100 at the same time. For a social media feed that recommends posts based on who you follow, a graph database is better. For a real-time sensor dashboard that needs to write millions of data points per second, a wide-column NoSQL store like Bigtable is the right choice. For running complex analytical queries across terabytes of historical sales data, BigQuery is ideal.

Schema design also involves normalisation — the process of reducing data redundancy by splitting data into multiple related tables. For example, instead of storing the full customer address in every order row, you store a customer ID in the order table and keep the address in a separate customer table. This saves space and prevents errors when a customer moves. Denormalisation is the opposite: you intentionally duplicate data to speed up reads, like storing the customer name directly on the order row so you do not have to join two tables for every query. The trade-off is between write efficiency (normalised) and read performance (denormalised).

Finally, data types matter. A date is not the same as a string. If you store "2025-03-10" as text, the database cannot calculate the difference in days or filter by year. Choosing the right data type — INTEGER, FLOAT, STRING, TIMESTAMP, BOOLEAN — is part of schema design. The exam expects you to know that storing a date as a TIMESTAMP allows time-zone aware operations, while storing it as a STRING does not.

## Real-world context

A retail company called “ShopFast” wants to build a new recommendation system and a sales dashboard. The data engineer must model the data for both.

First, the engineer talks to the business team. The sales dashboard needs to answer: “What was the total revenue per region last quarter?” This is an analytical workload — it scans many rows but only a few columns. The engineer identifies BigQuery as the right choice because it is built for exactly this kind of query. The schema for BigQuery is designed with three tables: a ‘sales’ table (columns: sale_id, product_id, region, sale_amount, sale_date), a ‘products’ table (product_id, product_name, category), and a ‘regions’ table (region_id, region_name). The ‘sales’ table uses a TIMESTAMP for sale_date to allow time-based filters. The engineer chooses to denormalise slightly by including region_name directly in the sales table as well, because the dashboard will join these tables often and the data does not change frequently.

Second, the engineer tackles the recommendation system. This is an operational workload — it needs to read user preferences and product relationships in real time, under 50 milliseconds per request. For this, the engineer chooses a graph database (a NoSQL type). The schema here has nodes and edges: each user is a node, each product is a node, and an edge called “purchased” connects a user to a product with a timestamp property. Another edge called “viewed” connects users to products they have looked at. The schema is flexible: if the business later adds a “reviewed” edge, the engineer can add it without altering existing data. This graph model allows the recommendation algorithm to traverse relationships quickly — “users who bought this also bought that” — by walking the edges.

Third, the engineer designs the transaction database where the actual purchases happen. This must be ACID compliant — Atomic, Consistent, Isolated, Durable — meaning a purchase either fully completes or fully fails, and two customers cannot order the last item at the same time. This is a relational database (like Cloud SQL). The schema includes a ‘customers’ table, an ‘orders’ table, and an ‘inventory’ table. The engineer uses primary keys and foreign keys to enforce referential integrity — for example, the ‘order’ table cannot contain a customer_id that does not exist in the ‘customers’ table. The schema also includes constraints: the ‘quantity’ column in ‘inventory’ must be a non-negative integer, enforced by a CHECK constraint. This prevents the database from ever recording a negative stock level.

The engineer documents the schema for each system, including data types, indexes, and relationships. They also plan for scaling: BigQuery will handle growth in data volume automatically, but the relational database might need sharding (splitting data across multiple servers) if the transaction volume grows beyond a single server. The NoSQL graph database scales horizontally by adding more nodes. The key skill here was mapping the business requirements to the correct data model and schema — not just storing data, but storing it optimally for how it will be used.

## Exam focus

The PDE exam loves to test your ability to choose between data models for a given scenario. You will see scenario-based multiple-choice questions that describe a workload and ask which storage system or schema design is most appropriate. The traps are subtle: they will describe a workload that could work with two different models, but one has a critical disadvantage you must spot.

Here are the specific exam topics you must master:

- When to use relational vs. NoSQL: The exam tests your understanding of ACID compliance versus BASE (Basically Available, Soft state, Eventually consistent). If the scenario demands strong consistency (like a bank transfer), the answer is relational. If it demands high availability and can tolerate eventual consistency (like a social media newsfeed), the answer is NoSQL. A common trap is presenting a NoSQL option that lacks transactions and asking you to identify that it is unsuitable for financial data.

- BigQuery schema design: You must know that BigQuery supports nested and repeated fields (structures and arrays). The exam tests whether you can model a one-to-many relationship using a REPEATED field instead of a separate table. For example, an order with multiple line items: in BigQuery, you can store the line items as an array inside the order row. This avoids a JOIN and improves query performance. The trap is that beginners often normalise everything, not recognising that BigQuery is optimised for denormalised schemas.

- Data types and constraints: The exam tests knowledge of data types like TIMESTAMP, DATE, and STRING. They will give a query that filters on a date stored as a STRING and ask why it is slow or incorrect. The correct answer is that STRING does not support date arithmetic and cannot use date-partition pruning. They also test constraints like NOT NULL, UNIQUE, and CHECK, especially in the context of Cloud Spanner (Google’s globally distributed relational database).

- Partitioning and clustering: For BigQuery, the exam tests how to design schemas that leverage partitioning (splitting data by date into separate storage blocks) and clustering (sorting data within a partition by a column like customer_id). A common question: “You have a sales table with 10 TB of data. Most queries filter on sale_date and product_id. How should you design the schema?” The correct answer is to partition by sale_date and cluster by product_id. The trap is choosing partition by product_id (which creates too many small partitions) or not clustering at all.

- Schema evolution: How do you handle changes to a schema over time? The exam tests strategies like adding nullable columns (safe), setting default values, or using versioned schemas with a “version” column. They will present a scenario where a table already has 1 billion rows, and you need to add a new column — the correct approach is to add a NULLABLE column with no default, which is instantaneous and does not rewrite existing rows.

- Indexing in relational databases: The exam tests where to add indexes — on columns used in WHERE clauses and JOIN conditions. They will describe a slow query and ask what index to add. The trap is adding an index on a column that has very few distinct values (like a boolean column), which does not help and wastes storage.

- Data modeling for streaming data: How do you design a schema for data that arrives in real time, like from Cloud Pub/Sub? The exam tests using BigQuery’s streaming buffer and designing schemas with a timestamp column for ingestion time. They also test writing to a time-partitioned table so that old partitions can be expired (data lifecycle management).

- The exam also tests anti-patterns: storing an array of values in a single STRING column (violates atomicity), using a single table for entities that should be normalised (causes data duplication), and misusing NULL values (using NULL to mean “unknown” versus “not applicable”). You must be able to spot these in a schema diagram or description.

## Step by step

1. **Understand the business requirements** — Before writing a single line of schema, you must know what the system needs to do. Is it a transaction processing system (like an e-commerce checkout) that needs atomic updates and strong consistency? Or is it an analytical dashboard that runs complex aggregate queries across terabytes of data? The answer determines whether you choose a relational database, a NoSQL store, or BigQuery.
2. **Identify the entities and their relationships** — List the real-world objects you need to track — customers, orders, products, payments. Determine how they relate: one customer can have many orders (one-to-many), one product can be in many orders and one order can have many products (many-to-many). This step produces an Entity-Relationship (ER) diagram that guides the schema.
3. **Choose the data model family** — Based on the requirements and relationships, decide between relational, NoSQL (document, key-value, wide-column, or graph), or BigQuery. A banking system needs relational for ACID compliance. A social media recommendation engine needs a graph model. A real-time sensor feed needs wide-column (Bigtable). A historical sales dashboard needs BigQuery for columnar performance.
4. **Design the schema with types and constraints** — Define each table or collection with explicit column names, data types (STRING, INTEGER, FLOAT, TIMESTAMP, BOOLEAN), and constraints (NOT NULL, UNIQUE, CHECK). For BigQuery, decide on partitioning and clustering columns. For NoSQL, define the document structure with nested fields. For Firestore, choose between subcollections and embedded arrays. This is where the blueprint becomes executable code.
5. **Plan for schema evolution and scaling** — Design how the schema will change over time. Add new columns as NULLABLE to avoid rewriting existing rows. Use versioned schemas if the data structure changes dramatically. Plan for data growth: BigQuery partitioning handles it automatically, but relational databases may need sharding. Document the schema in a repository so future engineers understand the decisions made.
6. **Test and validate with realistic data** — Write sample queries to verify the schema supports the required access patterns. Measure query performance. For relational databases, check if indexes are being used. For BigQuery, look at the query execution plan to see if partition pruning is working. Iterate based on the results — schema design is not a one-time activity.

## Comparisons

### Relational Database (Cloud SQL) vs NoSQL Document Store (Firestore)

**Relational Database (Cloud SQL):**
- Enforces ACID transactions for strong consistency
- Uses fixed schema with primary and foreign keys
- Performance drops with JOINs on very large tables

**NoSQL Document Store (Firestore):**
- Trades ACID for eventual consistency and high availability
- Uses flexible, schema-on-read documents
- Reads are fast because related data is embedded in a single document

### Normalised Schema vs Denormalised Schema

**Normalised Schema:**
- Stores data in separate tables to minimise redundancy
- Requires JOINs to query related data
- Ideal for write-heavy transactional workloads

**Denormalised Schema:**
- Duplicates data across rows to reduce JOINs
- Read queries are faster because all data is in one table
- Ideal for read-heavy analytical workloads like BigQuery

### BigQuery Partitioning vs BigQuery Clustering

**BigQuery Partitioning:**
- Divides table into segments based on a column (usually date)
- Reduces query cost by scanning only relevant partitions
- Maximum of 4,000 partitions per table

**BigQuery Clustering:**
- Sorts data within each partition based on one or more columns
- Improves query performance for filtered queries on the cluster columns
- No limit on the number of clusters, but only up to 4 cluster columns

### Primary Key (Relational) vs Key (NoSQL - Firestore document ID)

**Primary Key (Relational):**
- Uniquely identifies each row in a table
- Enforced by the database — duplicate keys are rejected
- Often an auto-incrementing integer or UUID

**Key (NoSQL - Firestore document ID):**
- Is the document ID, set by the application or auto-generated
- Not enforced for uniqueness across collections (though IDs are unique within a collection)
- Typically a human-readable string or a UUID chosen for query performance

## Diagram

_Decision tree for choosing a data model and schema design based on workload type, with specific implementation steps for relational, BigQuery, and NoSQL databases._

```mermaid
flowchart TD
    A[Business Requirements] --> B{Workload Type}
    B -->|Transactional, strong consistency| C[Relational Database - Cloud SQL / Spanner]
    B -->|Analytical, large datasets| D[BigQuery - Columnar Warehouse]
    B -->|High throughput, flexible schema| E[NoSQL Database]
    E --> F[Document - Firestore]
    E --> G[Wide-column - Bigtable]
    E --> H[Graph - JanusGraph]
    C --> I[Define Tables, PKs, FKs, Indexes]
    D --> J[Define Partitioning, Clustering, Nested Fields]
    F --> K[Define Collections, Documents, Embedding Strategy]
    G --> L[Design Row Key for Scan Efficiency]
    H --> M[Define Nodes, Edges, Properties]
```

## Common misconceptions

- **Misconception:** NoSQL databases don't use schemas at all — you can just throw in any data with any structure. **Reality:** NoSQL databases have schemas, but they are flexible. Document stores like Firestore have an implicit schema — you define the fields your application expects, and adding a new field is simpler than in relational databases, but there is still a structure. You cannot store completely random data and query it meaningfully. (Beginners hear the term 'schema-less' and assume there are no rules. In reality, NoSQL means 'schema-on-read' — the application interprets the data, but the database still has collections, documents, and expected field types.)
- **Misconception:** BigQuery schemas should be fully normalised like a traditional relational database to save storage space. **Reality:** BigQuery is a columnar data warehouse optimised for analytics. Denormalised schemas with nested/repeated fields often perform better because they avoid JOINs. Storage is cheap in BigQuery; the performance cost of JOINs on billions of rows is high. (This misconception comes from years of relational database training where normalisation is a best practice. BigQuery breaks that rule — it rewards denormalisation for analytical queries.)
- **Misconception:** A primary key in BigQuery is mandatory and enforced by the system. **Reality:** BigQuery does not enforce primary keys or unique constraints. You can have duplicate rows. You must handle deduplication in your query logic or during data ingestion. Cloud Spanner and Cloud SQL enforce primary keys, but BigQuery does not. (The term 'table' in BigQuery sounds like a relational table, so learners assume all the same rules apply. BigQuery is fundamentally a different system — it is a data warehouse, not a transactional database.)
- **Misconception:** Indexes are the answer to every slow query in any database system. **Reality:** Indexes are critical in relational databases (Cloud SQL, Cloud Spanner) but are not used the same way in BigQuery or many NoSQL systems. In BigQuery, performance comes from partitioning, clustering, and columnar storage — indexes are not applicable. In Bigtable, the row key design is the index. (This comes from early database education that overemphasises indexing as the universal performance tool. Different systems have different performance mechanisms, and the exam tests this distinction.)
- **Misconception:** If you use Cloud Firestore (a NoSQL document store), you should model your data exactly like a relational database with separate collections for every entity. **Reality:** In Firestore, you often denormalise and embed related data within a single document to reduce the number of reads. For example, store the user's name directly in the order document rather than in a separate users collection and doing a join at read time. (People new to NoSQL instinctively apply the relational model they know. Firestore charges per document read, so minimising reads by embedding data is a core design principle — the opposite of normalisation.)

## Key takeaways

- Data modeling means deciding which data to store and how the pieces relate; schema design means defining the exact structure (columns, types, rules) for a specific database system.
- Relational databases (like Cloud SQL) are for transactional workloads requiring strong consistency; NoSQL (like Firestore or Bigtable) is for high-throughput, flexible-schema workloads where eventual consistency is acceptable.
- BigQuery is a columnar data warehouse optimised for analytical queries on large datasets; its performance depends on partitioning, clustering, and using nested/repeated fields to avoid expensive JOINs.
- Normalisation reduces data duplication and is best for write-heavy transactional systems; denormalisation speeds up reads and is often better for analytical systems like BigQuery.
- A primary key in relational databases uniquely identifies each row; BigQuery does not enforce primary keys, so you must handle duplicates in your data pipeline.
- The correct data model for a workload depends on the access patterns: read-heavy vs. write-heavy, strong consistency vs. availability, and structured vs. semi-structured data.

## FAQ

**What is the difference between a data model and a schema?**

A data model is the high-level plan of what entities exist and how they relate (like an architectural blueprint). A schema is the specific implementation of that plan in a particular database, including column names, data types, and constraints (like the detailed wiring diagram).

**What does ACID stand for and why does it matter?**

ACID stands for Atomicity, Consistency, Isolation, Durability. It is a set of properties that guarantee database transactions are processed reliably. It matters for workloads like banking or e-commerce where you cannot lose data or have partial updates. Relational databases typically support ACID; most NoSQL databases trade ACID for speed and scale.

**Is BigQuery a relational database?**

No, BigQuery is a columnar data warehouse, not a relational database. It uses SQL but does not enforce primary keys, foreign keys, or ACID transactions. It is optimised for read-heavy analytical queries on very large datasets, not for high-frequency transactional writes.

**When should I use a NoSQL database instead of a relational one?**

Use NoSQL when you need high write throughput, flexible schema (data that changes structure over time), the ability to scale horizontally across many servers, and can tolerate eventual consistency. Examples include real-time analytics, IoT sensor data, session management, and content management systems. Use relational when you need strong consistency, complex joins, and ACID transactions.

**What is partitioning in BigQuery and how does it help?**

Partitioning divides a table into segments based on a column (usually a date or timestamp). Queries that filter on that column only scan the relevant partitions, not the entire table. This dramatically reduces query cost and improves performance. For example, a table partitioned by day means a query for last week only reads 7 partitions instead of the whole table.

**Can I change a BigQuery schema after loading data?**

Yes, you can add new nullable columns, rename columns, and change the data type of a column in some cases (using SQL DDL). However, you cannot drop a column without recreating the table. Best practice is to design for evolution upfront by making new columns nullable and not setting default values.

## Check your understanding

1. **A financial application needs to process transactions where two customers cannot withdraw the same money simultaneously. Should you use a relational database or a NoSQL database?**

   Answer: A relational database, because it supports ACID transactions that enforce strong consistency, ensuring that concurrent withdrawals are handled safely.

2. **In BigQuery, what is the benefit of using a nested REPEATED field to store order line items directly in the order table, rather than a separate line_items table?**

   Answer: It avoids the need for a JOIN when querying orders with their line items, which reduces query time and cost, and is the recommended pattern for BigQuery's columnar architecture.

3. **A social media feed must display user posts within 100 milliseconds. The schema must allow new relationships (e.g., 'follows', 'likes') to be added without downtime. Which data model is best suited?**

   Answer: A graph database (a NoSQL type) because it stores nodes (users, posts) and edges (relationships) natively, and can add new edge types without altering existing data.

4. **You have a BigQuery table with 5 TB of sales data. Most queries filter on the 'sale_date' column. How should you design the table's partitioning?**

   Answer: Partition the table by 'sale_date' (using a TIMESTAMP or DATE column), so that queries on a specific date range only scan the relevant partitions instead of the entire table.

5. **What is the main difference between normalisation and denormalisation in schema design?**

   Answer: Normalisation splits data into separate tables to reduce redundancy (good for write-heavy systems), while denormalisation combines data into fewer tables to speed up reads (good for analytical systems).

---

Interactive version with quiz and diagrams: https://courseiva.com/learn/google-pde/data-modeling-and-schema-design