If you treat every database problem the same way, you will end up with a system that is either too slow for real-time updates or too expensive for answering big questions. This chapter draws a sharp line between Firestore, a NoSQL document store for live application data, and BigQuery, a serverless data warehouse for analytical queries. For the PCDE exam, understanding this distinction is non-negotiable: the exam will force you to choose the right tool for a given scenario, and the wrong choice loses marks.
Jump to a section
A simple way to picture Firestore and BigQuery: Document Store and Analytical Database Overview
A home renovation project in full swing. Your kitchen has been ripped out, the living room is full of dust sheets, and the plumber is shouting about the pipe layout from the basement. You have two completely different ways to keep track of everything.
For the day-to-day chaos, you grab a whiteboard marker and jot down on the wall: 'Plumber needs 15mm copper pipe by 2pm'. 'Electrician wants to run wires on Tuesday'. A neighbour stops by to borrow your drill; you scribble their name and a quick note next to it. This is your Firestore. It is a live document store. Every note is a single document, stored in a collection (the kitchen wall). You can read, write, or erase one note at a time, instantly, without any complex structure. It is perfect for tracking the current state of a hundred tiny, changing tasks.
Then, at the end of the week, you need real answers. How much copper pipe did we actually use across all the trades? Which task took longest? What is the total amount of plasterboard ordered? You cannot answer that from sticky notes. So you take all the scribbles from the wall, load them into a spreadsheet on your laptop, and run formulas on the whole dataset. You filter, aggregate, sum, and compare. This is BigQuery. It is a serverless data warehouse. It is designed to analyse massive collections of data at once, not to update a single record in real-time. It answers the 'what happened' questions, not the 'what is happening right now' questions.
The critical insight: you would never use the spreadsheet to tell the plumber where to leave the pipe. And you would never use the sticky note to calculate the total project cost. Each tool is built for a different job, and a successful project uses both.
The world of databases is divided by purpose, not just by vendor. Two of the most important categories are 'document stores' and 'data warehouses'. Google Cloud provides a flagship product for each: Firestore and BigQuery. Understanding why both exist, and when to use each, is central to the PCDE curriculum.
Let us start with Firestore. Firestore is a NoSQL document database. 'NoSQL' means 'Not Only SQL' (Structured Query Language). It is a database that does not require you to define a rigid table structure (a schema) in advance. Instead, you store data as documents. A document is very similar to a JSON object (JavaScript Object Notation) — a lightweight, text-based format that humans can read and machines can parse. Each document contains fields (like 'customerName', 'orderTotal', 'orderDate') and their values. Documents are organised into collections. For example, you might have a collection called Orders. Inside that collection, each individual order is a separate document. The beauty of Firestore is its speed and flexibility. You can write a new document or update an existing one in milliseconds. It is built for real-time applications. Think of a chat app, where a new message must appear instantly. Or a cooking app where a user's favourite recipe needs to be updated without reloading the page. This is the sweet spot of Firestore — live, operational data that changes frequently and needs immediate reads and writes.
Now, BigQuery. BigQuery is a serverless data warehouse. The term 'serverless' means you do not manage any underlying servers or infrastructure. Google handles all the hardware, scaling, and maintenance. A 'data warehouse' is a system designed specifically for storing and analysing very large volumes of historical data. Unlike Firestore, BigQuery is not built for real-time transactional updates. It is built for complex analytical queries. You might have billions of rows of data — say, five years of sales records from every store in a chain. BigQuery can scan all of this data in seconds and give you the answer to questions like: 'What was the total revenue for our top 10 products last quarter?' or 'Which stores had the highest customer churn rate in the last 12 months?' BigQuery uses SQL, the standard language for querying structured data. This makes it familiar to anyone who has used traditional databases.
The fundamental difference boils down to the type of work each tool does. Firestore is an Online Transaction Processing (OLTP) system. It handles individual, small transactions — one order, one user profile, one inventory update — very quickly. BigQuery is an Online Analytical Processing (OLAP) system. It handles large, complex queries that aggregate data across many records. A good analogy is a supermarket checkout versus the head office's annual sales report. The checkout (Firestore) needs to update stock for one item and take payment in seconds. The head office report (BigQuery) might query millions of till transactions to understand buying patterns.
Why does this matter for the PCDE exam? Because the exam will test your ability to match use cases to the correct database. The exam loves scenarios: 'A gaming company needs to store player profiles and update them in real-time.' You should immediately think Firestore. 'A bank needs to analyse years of mortgage data to find risk patterns.' You should think BigQuery. The exam also tests constraints. Firestore has limits on query complexity — you cannot do complex joins across large datasets. BigQuery has limits on latency — it is not built for millisecond response times on a single record.
There is a third concept that often appears alongside these two: the 'lift and shift' trap. Beginners sometimes assume they should use a single database for everything. The PCDE exam explicitly tests whether you understand that trying to use Firestore for analytics or BigQuery for real-time operations would lead to performance failures or massive cost overruns. The correct answer is almost always a hybrid approach: Firestore for the live application, and BigQuery for the historical analysis, with data being exported from Firestore into BigQuery at regular intervals.
Finally, let us clarify two more terms that beginners muddle. 'Document store' is a category of NoSQL databases. Firestore is Google Cloud's document store. Other examples include MongoDB. 'Data warehouse' is a category of database for analytics. BigQuery is Google Cloud's data warehouse. Others include Amazon Redshift, Snowflake, and Azure Synapse. The PCDE exam is Google-centric, so you will mostly need to compare Firestore and BigQuery, but you should also know they fit into a broader ecosystem.
Defining the Use Case: Real-time vs Analytical
The first step in solving any database problem is to determine whether your workload is transactional (OLTP) or analytical (OLAP). If you need to read or write a single record instantly (a user logging in, an item being added to cart), you need an OLTP system like Firestore. If you need to run a summary query over millions of records (total revenue last year), you need an OLAP system like BigQuery. This decision drives every subsequent step.
Choosing Firestore for the Operational Layer
Once you decide the data is operational, you design a Firestore collection structure. You group related documents into collections. For example, a collection called 'users' contains a document for each user. Each document has fields like 'name', 'email', 'lastLogin'. You choose document IDs carefully (often using the user's unique ID) to allow direct lookups. You also set up security rules to control who can read or write each document. This is where your application's live data lives.
Designing the Export Pipeline to BigQuery
Because operational data eventually needs analysis, you design a pipeline that exports Firestore documents into BigQuery tables. You can use the built-in Firestore export to BigQuery feature, use Cloud Functions to stream document changes on every write, or schedule a daily batch job with Dataflow. The choice depends on how fresh the analytics data needs to be. Every time a new order is placed in Firestore, the export pipeline ensures that same data eventually appears as a row in a BigQuery table.
Structuring the BigQuery Schema for Analytics
In BigQuery, you define a schema for each table. You decide which columns to include (flattening nested Firestore fields if needed), set data types (STRING, INTEGER, FLOAT, TIMESTAMP), and choose partitioning and clustering columns. For example, you might partition the `orders` table by `orderDate` so that queries filtering by date scan only the relevant partitions. This step is critical for performance and cost, because BigQuery charges by the amount of data scanned.
Running Analytical Queries and Building Dashboards
Now analysts and business users can query the BigQuery tables using standard SQL. They might write queries to find the top-selling products, calculate average order value, or segment customers by behaviour. The results feed into dashboards (using a tool like Looker Studio) or are exported to spreadsheets. Importantly, this step never touches Firestore. The live system is unaffected by these heavy reads, preserving performance for end-users.
Monitoring Performance and Costs
Finally, you monitor both systems. For Firestore, you watch the read/write rates and alert if they approach quotas or cost thresholds. For BigQuery, you monitor slot usage (compute resources) and data scanned per query. If a query scans too much data, you might need to optimise the schema or add filters. The PCDE exam expects you to know that you must balance performance and cost, especially when moving data between the two systems.
A medium-sized e-commerce company, 'GreenLeaf Goods', sells organic home products online. Their website is built on a modern stack and they use Google Cloud.
The first thing their development team sets up is Firestore. Every time a customer visits the site, the frontend application reads their profile from Firestore. When the customer adds a product to their cart, the cart state is written to a Firestore document in the carts collection. When they check out, the order details are written to an orders collection. This happens instantly. If the customer refreshes the page, the cart is still there. This is Firestore's job — live, operational data. Data is stored as documents with fields like customerID, itemName, quantity, price, and timestamp.
Now, the business side. The CEO wants to know: 'Which products are most popular with customers in London during winter?' This is an analytical question. To answer it, the data team needs to query every single order from the last three years, filter by location and season, and then aggregate by product. Running this query against Firestore would be a disaster. Firestore is not built for scanning millions of records in a single query; it is built for retrieving individual documents quickly. The query would be extremely slow and expensive. So the team uses BigQuery instead.
Here is what happens step by step in a real environment:
The team sets up an automated export from Firestore to BigQuery. Every hour, a script copies new documents from the Firestore orders collection into a BigQuery table. The table has a row for every order item, with columns that match the document fields.
When the CEO's question comes in, a data analyst writes a SQL query in BigQuery. The query looks something like: SELECT productName, SUM(quantity) as totalSold FROM orders WHERE customerCity = 'London' AND season = 'Winter' GROUP BY productName ORDER BY totalSold DESC.
BigQuery scans all the data in parallel across thousands of servers. It returns the results in a few seconds, even if there are billions of rows.
The analyst visualises the result in a dashboard tool. The CEO sees the answer in a clean chart.
The team also uses a third pattern: real-time analytics for live dashboards. For this, they use a Firestore listener that sends updates to a separate real-time analytics tool, but that is an advanced pattern.
The key action for the IT professional involves two core tasks:
Designing the data model: knowing which fields to store in Firestore documents, and how to structure collections so that the most common queries are fast. For example, storing customer orders with the customer ID as a document ID so you can directly fetch them.
Planning the data pipeline: setting up the regular export from Firestore to BigQuery. This often uses a service like Cloud Functions or Dataflow to move data automatically. The decision of how often to export (every minute, every hour, daily) is a business decision based on how fresh the analytics need to be.
A common trap the team avoids is trying to make BigQuery respond in real-time. They never use BigQuery to serve the live website. Also, they avoid storing full product descriptions inside Firestore if they are large and rarely accessed; those go in Cloud Storage, with only a URL stored in Firestore.
In summary, the IT professional's daily reality is about choosing the right tool for each data job, wiring them together correctly, and managing the operational cost of both systems.
The PCDE exam tests this topic in several predictable ways. Knowing these patterns will save you marks.
First, the exam presents scenario-based multiple choice questions. A typical question: 'A ride-sharing app needs to store driver location updates every 10 seconds and also produce a monthly report on total miles driven per city. Which combination of Google Cloud services is most appropriate?' The correct answer is almost always Firestore for the real-time location data and BigQuery for the monthly report, with an export mechanism between them. The trap answer is suggesting Cloud SQL or Spanner for the real-time data. The exam wants you to match the natural fit: real-time, frequent writes = Firestore (or Memorystore for even faster, but that is another topic). Analytical, large-scale aggregation = BigQuery.
Second, exam questions focus on constraints. They love to test whether you know that Firestore does not support complex analytical joins, and that BigQuery is not built for single-row millisecond lookups. A question might ask: 'You need to query a table of 10 billion rows to find the average purchase value. Which service should you use?' The answer is BigQuery. The trap is suggesting Firestore because a beginner might think 'a database is a database.'
Third, the exam tests cost and performance characteristics. Firestore charges based on the number of reads, writes, and deletes, plus storage. BigQuery charges based on the amount of data scanned per query and storage. The exam expects you to know that using BigQuery for frequent, small queries (e.g., looking up a single user's order) would be extremely expensive and slow due to the overhead of scanning partitions. Conversely, using Firestore for a full table scan (like the London winter products query) would be both slow and very expensive in terms of read operations.
Fourth, the exam tests knowledge of data structure. Firestore is unstructured or schema-less at the document level, but collections can enforce some structure. BigQuery requires a defined schema for tables (columns with data types). The exam might ask: 'You are building a feature where the product specification varies wildly between categories. Some products have weight, others have colour, others have warranty years. Which database should you use?' The answer is Firestore, because you can store different fields in each document without altering a rigid schema. BigQuery would force you to define all possible columns upfront or use a messy approach like JSON columns.
Fifth, exam questions test the integration between the two services. The most common integration is exporting Firestore data to BigQuery for analytics. The exam might ask about the tools used for this export, such as Cloud Functions, Dataflow, or the built-in Firestore export to BigQuery feature. They may also ask about latency: how real-time is the data in BigQuery? The answer is 'near real-time' if using a streaming export, but never as fast as Firestore itself.
Key topics to memorise:
Firestore is an OLTP (Online Transaction Processing) system.
BigQuery is an OLAP (Online Analytical Processing) system.
Firestore uses a document-collection model.
BigQuery uses a table-schema model.
Firestore supports real-time listeners.
BigQuery supports SQL querying of petabytes of data.
The exam avoids asking about specific pricing numbers in the PCDE (those change over time), but you must understand the relative cost model: Firestore pays per operation, BigQuery pays per byte scanned.
Firestore is a NoSQL document database designed for real-time, operational workloads with sub-second read and write times for individual documents.
BigQuery is a serverless data warehouse designed for analytical queries that scan billions of rows, returning aggregated results in seconds.
Use Firestore for live application data like user profiles, shopping carts, and chat messages; use BigQuery for historical analysis like sales trends and customer behaviour reports.
Firestore uses a collection-document model with no fixed schema, allowing each document to have different fields.
BigQuery uses a table-schema model with defined columns and data types, optimised for columnar storage and parallel processing.
A common PCDE exam pattern is to choose a hybrid solution: Firestore for real-time operational data, exported to BigQuery for historical analytics.
You cannot use Firestore for complex joins or aggregations across large datasets, and you cannot use BigQuery for single-millisecond transactional lookups.
The PCDE exam tests your ability to match use case characteristics — latency, query type, data volume — to the correct database service.
These come up on the exam all the time. Here's how to tell them apart.
Firestore (Document Store)
Designed for real-time, operational workloads (OLTP).
Stores data in documents organised into collections, with no fixed schema.
Optimised for fast individual record reads and writes (milliseconds).
BigQuery (Data Warehouse)
Designed for analytical workloads on historical data (OLAP).
Stores data in tables with a strictly defined schema of columns and data types.
Optimised for scanning billions of rows in a single query (seconds to minutes).
Firestore Query Language
Supports filtering, ordering, and simple aggregations (like count).
Cannot perform JOINs across collections or complex subqueries.
Queries are limited to a single collection or a composite index.
BigQuery SQL
Supports full standard SQL including JOINs, subqueries, and window functions.
Designed for complex analytical queries across multiple tables.
Queries can reference petabytes of data across any number of tables.
Firestore Cost Model
Charges per read, write, and delete operation, plus storage per GB.
Cost scales with the number of database operations, not data scanned.
Inefficient queries (e.g., fetching many documents) can be very expensive.
BigQuery Cost Model
Charges based on the amount of data scanned per query (in bytes), plus storage.
Cost scales with the volume of data processed by each query.
Inefficient queries (e.g., SELECT * without filters) dramatically increase cost.
Mistake
Firestore can handle analytical queries just as well as BigQuery, it is just slower.
Correct
Firestore is not designed for analytical queries at all. It cannot perform complex aggregations, joins, or window functions. Attempting to use it for analytics will result in extremely high costs and timeouts, not just slower performance.
Beginners think all databases are fundamentally the same, with speed being the only difference. They do not understand that the underlying architecture is completely different: Firestore uses a distributed key-value store, while BigQuery uses a columnar storage engine with a massively parallel processing (MPP) architecture.
Mistake
You cannot use SQL with Firestore at all.
Correct
Firestore has a query language that looks similar to SQL but is not true SQL. It supports filtering, ordering, and limited aggregation (like count), but it lacks joins, subqueries, and analytic functions. BigQuery uses standard SQL.
People hear 'NoSQL' and think 'no SQL at all'. In reality, many NoSQL databases, including Firestore, have their own query languages that are SQL-like but with significant limitations.
Mistake
BigQuery can replace Firestore entirely if you just set it up to respond quickly enough.
Correct
BigQuery is not built for real-time, single-record operations. Its minimum query response time is typically several hundred milliseconds to seconds, even for small queries, due to overhead in the query engine. Firestore can respond in single-digit milliseconds for a single document read.
A common cognitive bias is to want to simplify by using one tool for everything. Beginners see BigQuery's power and think they can tune it to act fast. They do not realise the architectural trade-offs are fundamental, not just configuration settings.
Mistake
Firestore is free forever if you keep data small.
Correct
Firestore has a free tier with limited daily quotas, but beyond that, you pay per read, write, delete, and storage. Costs can escalate quickly if you have a high-traffic application, especially with inefficient queries that trigger many reads.
Google's marketing of the free tier creates a false sense of permanence. Beginners underestimate how quickly small-scale applications grow, and do not anticipate the cost of each document read for every page load.
Mistake
BigQuery data is always real-time.
Correct
BigQuery supports streaming inserts that make data available in seconds, but this is still not true real-time (sub-second). Also, streaming data incurs higher costs. Most common use cases involve batched exports with hourly or daily latency.
The term 'real-time' is overused. Beginners conflate 'near real-time' with 'instantaneous'. BigQuery's streaming is impressive but comes with trade-offs that the exam tests.
Reveal each answer, then mark whether you got it right. Score 60%+ to unlock the next chapter.
No. BigQuery is not designed for sub-second writes and reads for individual records. A chat app needs Firestore (or a real-time database like Firebase Realtime Database) to handle instant message delivery.
Firestore has a generous free tier that includes a daily quota of reads, writes, deletions, and storage. However, once you exceed those quotas, you pay per operation and per GB stored. For a high-traffic application, costs can be significant.
A document is a single record in Firestore, stored as a JSON-like object with fields and values. A row is a single record in BigQuery, stored as a structured entry with predefined columns. Documents can have different fields in the same collection; rows must all have the same columns.
Firestore has its own query language that resembles SQL but is limited. It supports filters, sorting, and simple aggregations like count, but it does not support JOINs, subqueries, or analytical functions. For full SQL, you need BigQuery.
You can use the native 'Export Firestore to BigQuery' feature in the Google Cloud console, set up a Cloud Function that writes document changes to BigQuery, or use Dataflow for more complex streaming or batch pipelines.
Yes, the exam explicitly tests your ability to compare the two services and choose the correct one for a given scenario. You must understand their strengths, weaknesses, and typical integration patterns.
You've finished Firestore and BigQuery: Document Store and Analytical Database Overview. Continue through the PCDE study guide to build a complete picture of the exam.
Done with this chapter?