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.
Jump to a section
A simple way to picture Data Modeling and Schema Design
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
These come up on the exam all the time. Here's how to tell them apart.
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
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
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)
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
Mistake
NoSQL databases don't use schemas at all — you can just throw in any data with any structure.
Correct
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.
Mistake
BigQuery schemas should be fully normalised like a traditional relational database to save storage space.
Correct
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.
Mistake
A primary key in BigQuery is mandatory and enforced by the system.
Correct
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.
Mistake
Indexes are the answer to every slow query in any database system.
Correct
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.
Mistake
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.
Correct
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.
Reveal each answer, then mark whether you got it right. Score 60%+ to unlock the next chapter.
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).
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.
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.
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.
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.
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.
You've finished Data Modeling and Schema Design. Continue through the PDE study guide to build a complete picture of the exam.
Done with this chapter?