The case
Sales nobody can add up. The monthly report freezes the store.
Isaac wants to know how much the store sold by region over the last three years. The question sounds simple, but the request runs against the database that serves the customers. When someone runs the query, checkout slows down, and the accountant gets a spreadsheet nobody checks.
Meanwhile, the marketing team wants to know which pages of the site people visit before buying. Those clicks are not stored anywhere.
This post builds on Cloud Databases and Cloud Storage: block, file and object. There, the question was where to keep the data. Here it changes: what to do with the data once it's kept.
The problem
Two kinds of work in the same database
Serving the store and analyzing the store are different jobs:
- Serving is writing and reading a few rows, many times a second, without error. An order can't be left half done.
- Analyzing is reading millions of rows to answer one question, once or a few times a day.
The solution
Three data formats
Before choosing the service, you need to know the shape of the data:
Structured
Rows and columns with a fixed format: orders, items, stock.
At the store: each order always has a date, a customer, an amount and a status.
Semi-structured
Key and value, or documents (such as JSON), where each record can have different fields.
At the store: each customer's cart, with items and options that change from product to product.
Unstructured
Files with no table inside: images, PDFs, audio.
At the store: the product photos and the invoices in PDF.
The format guides the choice: structured data goes to a relational database, semi-structured data to a document database, and unstructured data to Cloud Storage.
Don't mix them up
Transactional × analytical
Transactional (OLTP)
“How does an order get into the store?”
- Example
- A customer checks out the cart: the purchase, the stock and the payment are written in milliseconds.
- Focus
- Many small operations at the same time, without losing any. Services: Cloud SQL and Spanner.
Analytical (OLAP)
“How much did we sell by region over the last three years?”
- Example
- A query reads millions of rows, but only three columns: region, date and amount.
- Focus
- Few queries, but huge ones, over a lot of data. Service: BigQuery.
The difference shows in how the data is stored. A transactional database keeps each row together, which helps to write and read a whole order at once. An analytical one keeps each column together, so it can read only the column the question needs and compress repeated data well, such as region names.
Underneath
From click to report
The site's clicks don't go straight to the report. They follow a path:
- Collection: the site publishes each click to Pub/Sub. The publisher doesn't wait for anyone to process it: if processing is slow, the site stays up and the messages wait in the queue.
- Transformation: Dataflow reads the messages, discards what doesn't matter (a search bot, for example) and aggregates them per minute.
- Storage for analysis: the result goes to BigQuery, where marketing's questions are asked.
This path is streaming: the data is processed as it arrives. The nightly export of the orders, on the other hand, is batch: a block of data is processed at once, and the same Dataflow serves both cases.
For time series at a very high volume, read by key, Bigtable is another option. For questions that change every day, BigQuery is the choice.
The complete solution
The lake and governance
Not all data needs to be organized before it's stored. A data lake is a repository of raw data in varied formats, kept in Cloud Storage because it is cheap and durable. The data comes in as it is and is processed later, when someone needs it.
The risk is turning the lake into a dump with no owner. Governance answers simple questions: who is responsible for each dataset, who can read it, and whether the data is correct. On Google Cloud, Dataplex Universal Catalog organizes the lake and the warehouse in one place, applying the same access rules and a catalog to find the data.
In practice
Where each piece of the store's data lives
| Data | Where it lives | Why |
|---|---|---|
| Store orders | Cloud SQL | Transactions with consistency, in milliseconds |
| Each customer's cart | Firestore | The fields change from product to product |
| Product photos and PDF invoices | Cloud Storage | Large files, cheap and durable |
| Site clicks | Pub/Sub, Dataflow and BigQuery | Near-real-time events, filtered and aggregated |
| Sales by region | BigQuery | Queries over lots of data, without touching the store |
Key terms
Five terms to remember
Tap a card to see the definition.
Quick test
Three questions, exam style
01
A report needs to add up five years of sales, by region, reading millions of rows. Which service fits best?
02
Where should the product photos and the PDF invoices be stored?
03
Site clicks should reach the report almost in real time, without the site waiting for processing. Which combination does this?
Each question asks for a different place. An order needs a fast transaction, a report needs bulk reading, and a click needs its own path to the dashboard. Keeping these three apart is what stops the report from freezing the store.