# Managing Data Lakes and Warehouses

> Chapter 8 of the Courseiva GOOGLE-PDE curriculum — https://courseiva.com/learn/google-pde/managing-data-lakes-and-warehouses

**Official objective:** 2.4 — Design and implement data lakes (Cloud Storage) and data warehouses (BigQuery) including partitioning, clustering, and data lifecycle.

## Introduction

Domain 2 of the PDE exam focuses on designing and building data processing systems, and right at the heart of this is knowing where to put your data. The problem is simple: raw data is cheap but chaotic, while organised data is expensive but fast. This chapter explains how to master both approaches using Google Cloud's two flagship storage solutions, which is critical for passing the exam's questions on cost optimisation and query performance.

## The Personal Library and Spare Room Analogy

Have you ever had to organise a mountain of stuff you've collected over the years, from childhood photos to old tax forms, while also needing quick access to your most important documents for a big meeting?

Imagine you start by shoving everything into a spare room in your house. That's your data lake. You dump all your stuff in there without organising it first. Old receipts, digital photos, holiday videos, scanned letters, random notes from your phone. The room is cheap storage, and you can always find something if you're willing to search through the piles. But if you need to find last year's tax return fast, you're in trouble. The room is a mess.

Now, think of a proper filing cabinet in your home office. That's your data warehouse. You only put the most important documents in there, and you've carefully organised them with labelled folders and a colour-coded index system. You know that all tax documents are in the 'Tax' folder, sorted by year. You can grab your 2023 tax return in seconds. The filing cabinet is more expensive to maintain per document, but finding information is incredibly fast and reliable. The key is knowing what to keep in the spare room (raw, unprocessed data for future experiments) and what to move into the filing cabinet (cleaned, structured data for immediate business reports).

## Core explanation

To pass the PDE exam, you need to understand two fundamental storage concepts: data lakes and data warehouses. They solve different problems, and Google Cloud provides specific services for each.

A data lake is a centralised repository that stores all your data, in any format, at any scale. Think of it as a giant digital dump. You can store raw, unprocessed data without having to structure it first. This is incredibly useful because data scientists often need to explore data without knowing exactly what questions they'll ask later. The core service for this in Google Cloud is Cloud Storage. Cloud Storage is a scalable, durable, and cost-effective object storage service. 'Object storage' means each piece of data (like a CSV file, a video, or a database backup) is stored as an object with a unique ID and metadata. It's not a traditional file system like on your laptop. In Cloud Storage, you create 'buckets' (like folders) to hold your objects. It is perfect for storing raw data logs, sensor data, backups, and multimedia files because it is cheap per gigabyte.

In contrast, a data warehouse is a system designed for analysing structured, processed data. It is optimised for reading data quickly to answer business questions. The data must be 'structured', meaning it has a defined schema (like a table with rows and columns). The core service for this in Google Cloud is BigQuery. BigQuery is a serverless, highly scalable, and cost-efficient data warehouse. 'Serverless' means you don't need to manage any servers or infrastructure; Google handles all the hardware, and you just pay for the queries you run and the data you store. With BigQuery, you can run SQL queries across massive datasets in seconds. It is designed for business intelligence (BI), reporting, and running analytical queries on data that has been cleaned and transformed.

Now, let's talk about two powerful features you must master for the exam: partitioning and clustering. These are techniques to make your queries faster and cheaper.

Partitioning means dividing a large table into smaller, more manageable pieces called partitions. Each partition contains a subset of rows based on a specific column, usually a date or timestamp. For example, if you have a sales table, you can partition it by date. A query for sales on '2024-01-15' will only scan the partition for that date, not the entire table. This drastically reduces the amount of data BigQuery has to process, making the query faster and cheaper. On the exam, you will be asked when to use partitioning: typically, when your tables are very large and you frequently filter queries on a specific column.

Clustering is another optimisation technique. It sorts the data within each partition based on the values in one or more columns. Imagine a dictionary where all the words are alphabetically sorted. Finding a word is fast because you know where to look. Clustering works the same way. If you cluster a sales table by 'customer_id' within each date partition, a query looking for a specific customer's orders will find them faster because the data is physically sorted. Clustering does not reduce the amount of data scanned as much as partitioning, but it can massively improve query performance. A key exam tip: clustering is best for columns that are frequently used in filter clauses and have high cardinality (many unique values), like 'customer_id' or 'product_id'.

Finally, you need to understand data lifecycle management. This is the practice of moving data through different storage classes or deleting it automatically as it ages. In Cloud Storage, there are different 'storage classes' that trade cost for access speed. 'Standard' storage is for frequently accessed data (highest cost). 'Nearline' is for data accessed once a month. 'Coldline' is for data accessed once a quarter. 'Archive' is for data accessed less than once a year (lowest cost). You can set 'lifecycle rules' on your Cloud Storage buckets to automatically move objects from Standard to Nearline after 30 days, then to Coldline after 90 days, and finally delete them after a year. This saves significant storage costs without manual effort.

In the exam, you will need to choose between Cloud Storage (data lake) and BigQuery (data warehouse) based on the use case. You will also need to design efficient table schemas using partitioning and clustering, and apply lifecycle rules to optimise storage costs. These concepts are not just nice-to-know; they are the core of designing scalable and cost-effective data systems on Google Cloud.

## Real-world context

Let's imagine you work for a large online retailer called 'ShopStream'. The company streams clickstream data (every click a user makes on the website) and transaction data (purchase details). Your job is to build a data system on Google Cloud that supports both data scientists exploring raw behaviour and a dashboard showing real-time sales.

Step one is setting up the data lake. You configure a Cloud Storage bucket called 'shopstream-raw-data'. You set up a lifecycle rule on this bucket to automatically move files older than 30 days to Nearline storage, and files older than 180 days to Archive storage. This ensures the bucket doesn't become too expensive. The raw clickstream log files (in JSON format) are dumped here every 5 minutes by a streaming pipeline.

Step two is processing the data for the warehouse. You run a Cloud Dataflow job (a data processing service) that reads the raw clickstream data, cleans it, removes null values, and enriches it with user profile data. This job writes the cleaned, structured data into BigQuery. In BigQuery, you create a table called 'sales_summary'. To make queries fast and cheap, you partition this table by 'transaction_date' and cluster it by 'product_id'.

Step three involves the data science team. They want to explore old clickstream data to find unusual user behaviour patterns. They directly query the JSON files in the Cloud Storage bucket using BigQuery's federated query feature. This lets them look at raw data in the data lake without loading it into the warehouse. Because the data is partitioned by date in the bucket structure itself (using folder names like '2024/01/15'), their queries only scan relevant files.

Finally, the analytics team creates a real-time dashboard in Looker (Google's business intelligence tool) that queries the partitioned and clustered 'sales_summary' table. When a manager filters the dashboard to show sales for 'product_id 1234' in 'January 2024', BigQuery's query engine uses the partition and cluster information to scan only the specific partition and the sorted blocks within it. The query completes in seconds, not minutes, and costs a tiny fraction of what a full table scan would cost.

As a data engineer, your daily tasks include:
- Monitoring Cloud Storage bucket sizes and adjusting lifecycle rules to control costs.
- Tuning BigQuery clustering columns for new tables based on query patterns.
- Writing SQL scripts to create partitioned and clustered tables.
- Auditing query performance to identify tables that need partitioning added.
- Setting up data retention policies to automatically delete outdated records.

## Exam focus

The PDE exam tests your ability to design data lakes and warehouses with a focus on practicality and cost. You will not be asked to write code, but you will be given scenario-based multiple-choice questions where you must choose the most efficient and cost-effective design.

Here are the exact concepts the exam loves to test regarding partitioning and clustering:
- When to use partitioning versus clustering. The trap: they often ask you to improve query performance for a table that is already partitioned. The correct answer is often to add clustering on a high-cardinality filter column.
- The cost implications of partitioning. The trap: they might suggest partitioning on a column with very low cardinality (like a boolean 'is_active' column), which creates very few partitions and doesn't save much. You must choose partitioning on a column with high cardinality that is frequently used in filters (like a date column).
- The limit of partitioning in BigQuery. BigQuery allows a maximum of 4,000 partitions per table. The trap: a question might ask you to partition by a field that would create more than 4,000 partitions. The correct answer is to use an alternative strategy like partitioning by year or month instead of day.

For data lifecycle, the exam expects you to know the storage classes in order of cost and access frequency:
- Standard: frequent access
- Nearline: once a month
- Coldline: once a quarter
- Archive: less than once a year
The trap: they might ask you to move a dataset accessed 'once every 90 days' to Coldline or Archive. The correct answer is Nearline, because Nearline is for data accessed less than once a month (30 days), and Coldline is for less than once a quarter (90 days). A 'once every 90 days' pattern fits Nearline's definition perfectly, not Coldline.

For data lake vs warehouse scenarios, the trap is often about data types:
- If the question mentions 'preparing data for machine learning' or 'experimenting with schema-on-read', the answer is Cloud Storage (data lake).
- If the question mentions 'business intelligence reports' or 'running SQL for executive dashboards', the answer is BigQuery (data warehouse).
- They love to combine them: the correct architecture is often to store raw data in Cloud Storage and then use a service like Dataflow or Dataproc to transform and load it into BigQuery.

Key definitions to memorise for the exam:
- Partitioning: divides a table into segments based on a column. Reduces data scanned. Best for date columns.
- Clustering: sorts data within partitions. Improves query performance on filter and sort columns. Best for columns with high cardinality.
- Serverless: no infrastructure to manage. BigQuery is serverless.
- Object storage: stores data as objects (not files in a folder hierarchy). Cloud Storage is object storage.
- Lifecycle rules: automated policies to move data between storage classes or delete it.
- Data lake: stores raw data in its native format.
- Data warehouse: stores structured, processed data optimised for analysis.

## Step by step

1. **1. Identify the Data Source and Purpose** — First, determine whether the incoming data is raw and unstructured (logs, videos, sensor data) needing a data lake, or cleaned and structured (transaction records, CRM data) needing a data warehouse. This decision drives the rest of the architecture.
2. **2. Create a Cloud Storage Bucket for the Data Lake** — Set up a Cloud Storage bucket with appropriate name and location. Define lifecycle rules to manage costs: for example, move objects to Nearline after 30 days and to Archive after 180 days. This ensures the data lake remains cost-effective as data ages.
3. **3. Set Up a Data Processing Pipeline** — Use a service like Dataflow or Dataproc to read raw data from the Cloud Storage bucket, clean it, transform it, and load it into BigQuery. This step converts chaotic raw data into structured tables suitable for analysis.
4. **4. Design the BigQuery Table with Partitioning and Clustering** — Create the destination BigQuery table. Choose a date column for partitioning (e.g., transaction_date). Then select 1-4 high-cardinality columns for clustering (e.g., customer_id, product_id). This design will drastically speed up common queries and reduce costs.
5. **5. Verify and Tune Performance** — Run sample queries against the new table. Use the BigQuery execution details to see how much data each query scanned. If a query scans more data than expected, adjust the partitioning or clustering columns. Set up scheduled queries or dashboards to monitor performance over time.
6. **6. Implement a Testing and Rollback Strategy** — Before applying changes to production tables, create a copy of the table and test your partitioning and clustering design on a smaller dataset. This prevents costly mistakes and allows you to compare query performance before and after optimisation.

## Comparisons

### Data Lake (Cloud Storage) vs Data Warehouse (BigQuery)

**Data Lake (Cloud Storage):**
- Stores raw, unprocessed data in any format (JSON, CSV, video, etc.)
- Optimised for cheap, scalable storage with infrequent access patterns
- Best for exploratory data science and schema-on-read scenarios

**Data Warehouse (BigQuery):**
- Stores structured, processed data in tables with a defined schema
- Optimised for fast SQL queries and business intelligence reporting
- Best for dashboarding, analytics, and frequent read operations

### Partitioning vs Clustering

**Partitioning:**
- Physically divides a table into separate segments based on a column
- Reduces the amount of data scanned by filtering out entire partitions
- Best on columns with high cardinality that are frequently used in WHERE clauses, typically dates

**Clustering:**
- Physically sorts data within partitions based on column values
- Improves query performance by grouping similar data together
- Best on high-cardinality columns used for filters or sort operations, like customer_id

### Standard Storage Class vs Archive Storage Class

**Standard Storage Class:**
- Highest cost per gigabyte of storage
- No minimum storage duration and no retrieval fee
- Best for data accessed frequently (multiple times per month)

**Archive Storage Class:**
- Lowest cost per gigabyte of storage
- Minimum storage duration of 365 days and has retrieval costs
- Best for data accessed less than once a year, like legal archives

### On-Premise Data Warehouse vs BigQuery (Cloud Data Warehouse)

**On-Premise Data Warehouse:**
- Requires purchasing and maintaining physical servers and hardware
- Scaling requires manual procurement and can take weeks
- Cost is capital expenditure (hardware purchase) plus operational costs

**BigQuery (Cloud Data Warehouse):**
- Serverless; no hardware to manage, Google handles everything
- Scales automatically and instantly based on query load
- Cost is operational expenditure based on storage and queries consumed

## Diagram

_This flowchart shows how raw data flows into a Cloud Storage data lake, is processed, then loaded into a BigQuery data warehouse with partitioning and clustering applied for performance, while lifecycle rules manage storage costs._

```mermaid
flowchart TD
    A[Raw Data Sources] --> B[Cloud Storage Bucket (Data Lake)]
    B --> C{Data Processing Pipeline (Dataflow/Dataproc)}
    C --> D[BigQuery Table (Data Warehouse)]
    D --> E[Partitioned by Date]
    D --> F[Clustered by Customer_ID]
    E --> G[Faster Queries]
    F --> G
    B --> H[Lifecycle Rules]
    H --> I[Move to Nearline/Coldline/Archive]
    H --> J[Delete old data]
```

## Common misconceptions

- **Misconception:** BigQuery is just a database like MySQL, so I should store all my transactional data in it. **Reality:** BigQuery is an analytical data warehouse designed for complex queries on large datasets, not for high-frequency, small transactional operations (OLTP). Use Cloud SQL or Spanner for transactional workloads. (Beginners hear 'database' and assume all databases are the same. They don't understand the difference between OLTP (online transaction processing, like booking a ticket) and OLAP (online analytical processing, like analysing yearly sales).)
- **Misconception:** If I cluster my table, I don't need to partition it because clustering is just a better version of partitioning. **Reality:** Partitioning and clustering serve different purposes and are often used together. Partitioning physically separates data to reduce scan size, while clustering sorts within those partitions to improve data locality. They are complementary, not interchangeable. (The names sound similar, and both optimise queries. It's easy to think one replaces the other, but the exam expects you to know when to use each and when to combine them.)
- **Misconception:** Cloud Storage buckets are like hard drives, so I can mount them to a virtual machine and use them as a regular file system. **Reality:** Cloud Storage is object storage, not a block storage file system. You cannot mount it like a drive. You interact with it via API calls or using tools like `gsutil`. For a file system that can be mounted, you would use Persistent Disk or Filestore. (This misconception arises because users are familiar with local storage on their laptops. The idea of 'storage in the cloud' instinctively feels like a remote hard drive, but the technical implementation is fundamentally different.)
- **Misconception:** Lifecycle rules are only for cost saving, so I should set them to delete data as soon as possible. **Reality:** Lifecycle rules exist for both cost optimisation and compliance. Deleting data too aggressively can violate legal data retention requirements. You must balance cost with regulatory obligations. (Beginners focus only on the cost angle because that is the most obvious benefit. They overlook the critical aspect of data governance, which is heavily tested in the PDE exam.)
- **Misconception:** You can only partition BigQuery tables on a date or timestamp column. **Reality:** While partitioning on a date/timestamp column is by far the most common and recommended, you can also partition on an integer column using range partitioning, or by ingestion time using pseudo columns like `_PARTITIONTIME`. (The exam materials emphasise date partitioning heavily, so learners assume it is the only option. Knowing the alternatives is a differentiating factor for difficult exam questions.)
- **Misconception:** You can have up to 4,000 clusters per table. **Reality:** The limit of 4,000 applies to partitions, not clusters. You can cluster on up to 4 columns, but there is no hard limit on the number of clusters (the sorting blocks) within a partition. (This is a classic exam trap where candidates confuse the numerical limits of partitioning with clustering. The number '4,000' is specifically for partitions.)

## Key takeaways

- Data lakes (Cloud Storage) store raw data in any format, while data warehouses (BigQuery) store structured, processed data optimised for SQL queries.
- Partitioning divides a BigQuery table into smaller segments based on a column (usually a date), reducing the amount of data scanned and lowering query costs.
- Clustering sorts data within partitions based on column values, improving query performance for filters and sorts on those columns.
- Use lifecycle rules on Cloud Storage buckets to automatically move data to cheaper storage classes (Nearline, Coldline, Archive) or delete it based on age.
- BigQuery is serverless, meaning you pay only for storage and queries processed, with no servers to manage or patch.
- Always partition large BigQuery tables on a column with high cardinality that is frequently used in WHERE clauses, and cluster on high-cardinality filter columns like customer_id or product_id.
- For the exam, choose a data lake when the use case involves exploratory analysis or schema-on-read, and choose a data warehouse when the use case requires fast, structured reporting and business intelligence.
- The maximum number of partitions in a BigQuery table is 4,000 — if you need more, you must partition by a coarser grain (e.g., month instead of day).

## FAQ

**Is BigQuery faster than Cloud Storage for reading data?**

Not necessarily. BigQuery is incredibly fast at running SQL queries on structured data, but Cloud Storage is faster for raw file access if you just need to download a file. They serve different purposes: BigQuery is for analysis, Cloud Storage is for object storage.

**Can I change the partitioning or clustering of a BigQuery table after it is created?**

No, you cannot change the partitioning or clustering columns on an existing table. You must create a new table with the desired schema and copy the data into it using a query like `CREATE TABLE ... AS SELECT * FROM old_table`.

**Does clustering cost extra money in BigQuery?**

Clustering itself does not incur additional storage costs. However, it can actually reduce costs because less data is scanned during queries. The sorting happens automatically at no extra charge when you insert data.

**What is the difference between Nearline, Coldline, and Archive storage in Cloud Storage?**

They are different storage classes based on access frequency. Nearline is for data accessed less than once a month, Coldline is for data accessed less than once a quarter, and Archive is for data accessed less than once a year. Archive is the cheapest, Standard is the most expensive.

**Should I always partition my BigQuery tables?**

No, only partition tables that are larger than 1 GB and are frequently queried with filters on a time-based column. Small tables do not benefit from partitioning, and it adds management overhead without performance gains.

**Can I query data directly from Cloud Storage using SQL?**

Yes, BigQuery supports federated queries. You can query data stored in Cloud Storage (like CSV or JSON files) directly using SQL without loading the data into BigQuery first. This is great for exploring raw data.

## Check your understanding

1. **What is the main advantage of partitioning a BigQuery table by a date column?**

   Answer: It allows queries that filter on that date column to scan only the relevant partitions, reducing the amount of data processed and lowering query costs.

2. **A BigQuery table is partitioned by date and clustered by customer_id. A query filters on customer_id without a date filter. Does the clustering help?**

   Answer: Yes, even without a date filter, BigQuery will scan all partitions, but clustering will still help because it sorts the data within each partition, so the query engine can find the relevant rows faster.

3. **Your team needs to store raw sensor data for a data science project that explores unknown patterns. Should you use Cloud Storage or BigQuery?**

   Answer: Use Cloud Storage (a data lake). Raw, unstructured sensor data is best stored in its native format for exploratory analysis, where the schema is not yet defined.

4. **You have a Cloud Storage bucket with files that are accessed once every 45 days. Which storage class is most cost-effective?**

   Answer: Nearline storage, because it is designed for data accessed less than once a month (30 days), and your access pattern fits within that window.

5. **What is the maximum number of partitions you can have in a single BigQuery table?**

   Answer: 4,000 partitions.

---

Interactive version with quiz and diagrams: https://courseiva.com/learn/google-pde/managing-data-lakes-and-warehouses