Prepare and test your skills

Prepare and test your skills

A data lake is a scalable central platform that ingests and stores raw data from many systems for later processing. The Cloud Storage (opens in a new tab) service is commonly used as the storage layer for a data lake on Google Cloud. A data lake is different from a database, which runs day-to-day application transactions, and from a data warehouse, which analyzes organized data for reporting.
Data lakes on Cloud Storage store data as objects in buckets with a flat hierarchy. Each object has a globally unique ID and can carry system-assigned or user-defined metadata for organization and access. Applications and pipelines reach those objects through REST APIs or client libraries from inside or outside Google Cloud. Cloud Storage is low-cost, highly durable, and highly scalable, with optional redundancy across regions.
Storage classes match how often data is accessed. Standard is for frequent access including data lakes, Nearline retains data for at least 30 days, Coldline for at least 90 days, Archive for at least 365 days, and Autoclass moves objects automatically based on access. Ingestion is flexible and can use console uploads, gcloud CLI, APIs, or client libraries for structured or unstructured data. Larger moves can use online transfer with Storage Transfer Service or offline transfer with Transfer Appliance, and processed analytical data is often saved in columnar formats such as Parquet or ORC for efficient storage and faster reads.
A database is an operational system that stores structured records for applications, while a data lake stores raw files and objects for later processing. Databases support transactions, strong consistency, and low-latency reads and writes for current data. For example, Firestore in Datastore mode (Datastore) is a NoSQL document database with atomic transactions, strong consistency, encryption at rest, and automatic scaling, but it is not relational and is not effective for analytic data.
Other database choices fit different operational needs. Cloud SQL supports relational online transaction processing (OLTP) with full SQL, Bigtable fits cases where ACID transactions are not required or data is not highly structured, and BigQuery (opens in a new tab) supports online analytical processing (OLAP). A data lake does not replace these databases and is not designed to run application transactions. It holds data from multiple systems before or alongside application databases until pipelines transform it.
A data warehouse is an enterprise system for analysis and reporting, usually on data that comes from an operational database. BigQuery is the commonly used data warehouse on Google Cloud. BigQuery decouples storage and compute, stores data in compressed columnar format, organizes tables into datasets, and uses schemas, labels, and partitioning to manage and query data. It allocates query resources as slots and is fully managed, replicating data across zones or regions depending on dataset location.
A data lake is storage-centric and keeps raw, semistructured, and unstructured data in Cloud Storage and applies structure later. This approach is called schema-on-read because the schema is applied when data is read or processed rather than enforced at write time. A data warehouse uses defined table schemas and is optimized for analytical queries, dashboards, and reporting over integrated data.
Data lakes and data warehouses often work together through a processing pipeline that extracts, transforms, and loads data. Extract, transform, and load (ETL) transforms data before loading, while extract, load, and transform (ELT) loads data first and transforms it later. In a typical batch flow from lake to warehouse:
A left-to-right process flow showing ingestion into Cloud Storage, orchestration by Airflow, processing with Dataflow or Spark into Parquet, and loading into BigQuery for analysis.
Pub/Sub, Dataflow, and Managed Service for Apache Spark are common pipeline components for streaming and batch work. BigQuery can also load data directly from Cloud Storage, through Dataflow, or by real-time streaming.
The three paradigms split by the kind of work they support and the shape of data they hold:
| Paradigm | Primary purpose | Typical Google Cloud service | Data and schema |
|---|---|---|---|
| Data lake | Ingest and store data from many systems at scale | Cloud Storage | Raw, semistructured, and unstructured objects; schema-on-read |
| Database | Run applications and transactions | Cloud SQL, Datastore, Bigtable | Structured records; relational or NoSQL; transactions and consistency |
| Data warehouse | Analyze and report on integrated data | BigQuery | Structured tables and datasets; defined schemas; columnar storage |
Exam tip: A data lake stores raw data for later processing, a database runs application transactions, and a data warehouse analyzes modeled data.
Data lakes also support machine learning because they can hold large, diverse datasets before transformation. Cloud Storage can use Rapid Cache to improve read performance and reduce transfer costs for read-heavy workloads such as ML training, inference, and analytics. A lakehouse pattern combines Google Cloud and open source services to provide a single source of truth for analytics and AI and reduce duplication.
A database is a system for storing and managing operational data that must stay organized, linked, and ready for fast transactions. In Google Cloud, databases support structured, transactional workloads such as recording orders, updating patient records, or processing business transactions. Databases focus on precise reads and writes of current data rather than large-scale historical analysis.
What databases are for is keeping structured operational data correct and quickly accessible for applications and users. A relational database links related facts across tables without storing the same data multiple times. This design supports transaction processing and feeds business intelligence, data analytics, and machine learning services that depend on reliable day-to-day records. Because no single database fits every project, the right choice depends on the specific use case and query pattern.
A SQL database, also known as a relational database, stores highly structured data in tables of rows and columns. Each row represents one item, such as one patient, and each column holds one data point, such as name, insurance information, or contact details. A relational database management system (RDBMS) maintains links across multiple tables so relationships between data points can be found quickly. It can also create indexes on particular columns for faster lookups.
Structured Query Language (SQL) is the standard language used by RDBMS products including PostgreSQL, MySQL, SQL Server, and Oracle Database. SQL uses statement-format commands, with different variants by system. The core statement types divide work by role:
Statements that make structural or administrative changes are usually reserved for developers and administrators, while end-user applications perform read and write operations. SQL Server uses a row-based table structure that connects related elements from different tables without duplicating storage. The combination of predefined schema, linked tables, and indexed queries favors consistent records and targeted queries over flexible exploration.
Common relational database choices differ mainly in ownership, history, and enterprise focus:
| Database | Ownership | Documented characteristic |
|---|---|---|
| PostgreSQL | Open source | Object-relational design for enterprise-level performance, reliability, and robust features |
| MySQL | Open source, sponsored by Oracle | Supports SQL queries; administered by graphical interface or command line; deployable manually or through a cloud provider |
| SQL Server | Owned by Microsoft | Row-based tables that connect related data without duplication; used for transaction processing, business intelligence, analytics, and machine learning |
Exam tip: PostgreSQL is open source while SQL Server is owned by Microsoft; choose based on project requirements because no single database fits every workload.
Cloud SQL (opens in a new tab) is the Google Cloud managed service for MySQL, PostgreSQL, and SQL Server. For Microsoft SQL Server, Cloud SQL for SQL Server is the recommended path unless configuration requirements are not supported or operating-system access is needed. In that exception case, a SQL Server failover cluster can run on Compute Engine (opens in a new tab) with continuous-availability storage from Google Cloud NetApp Volumes. Enterprises increasingly choose fully managed services like Cloud SQL to reduce maintenance work.
Self-managed databases on Compute Engine virtual machines require the team to handle updates, monitoring and availability, backups, and recovery from failures. Managed options remove much of that effort, while virtual machines provide more configuration flexibility and operating-system control. Spanner is another Google Cloud database option that supports applications running in a global topology with Compute Engine. Firestore data can also be loaded into BigQuery, which shows how operational database data flows into later analysis.
A data warehouse is an enterprise system used for analysis and reporting on data that usually comes from an operational database. BigQuery (opens in a new tab) is Google Cloud's commonly used data warehouse, optimized to run analytic queries on terabytes in seconds and petabytes in minutes. Unlike systems built for single-row transactions, a warehouse scans, filters, and aggregates large volumes of historic structured data quickly and cost-effectively.
How data warehouses organize large volumes of structured data starts with a hierarchy that separates storage from analysis. Datasets are the highest-level abstraction to organize access to tables, while schemas and labels provide further organization within those datasets. Tables can use partitioning to improve query performance and cost and to manage information lifecycle, so queries scan only relevant partitions. Storage resources are allocated as data is consumed and deallocated as data is removed or tables are dropped, so no pre-provisioned capacity is required.
BigQuery supports several analytic workflows on this organized data:
These workflows all flow from stored tables to the query engine to results, so the same warehouse data can serve exploration, reporting, and machine learning without moving copies between systems.
Columnar storage is the physical foundation that makes warehouse queries fast on large tables. BigQuery decouples data storage and compute and stores data in Colossus, where data is compressed and stored in a columnar format called Capacitor. Because Capacitor stores each column separately, BigQuery operates directly on compressed data without decompressing it and reads only the columns a query needs. BigQuery also gathers column statistics while loading data and includes diagnostic query plan and timing information to explain performance.
Query execution scales separately from storage through the Dremel execution engine. Dremel uses a multilevel tree architecture with in-memory execution, automatic storage optimization within Capacitor, and autoscaling resource management for fast petabyte-scale queries. Each query uses slots, which are units of computation that include CPU and RAM, and resources are allocated by query type and complexity. Separation of execution also lets different projects, users, and groups query datasets in different projects without affecting each other by separating slot quotas and billing.
Exam tip: BigQuery reads only needed columns from compressed Capacitor storage, while a row-based database must read whole rows even for a narrow analytic query.
Schema design in BigQuery controls how much data is scanned and how many joins are needed. Traditional models include star schema, where a central fact table collects metrics such as order amount with keys to surrounding dimension tables, and snowflake schema, where those dimension tables are further normalized. BigQuery supports both models, but its native representation uses nested and repeated fields for a more natural representation of related data. Changing a schema to use nested and repeated fields reduces joins and aligns the schema with the internal Dremel and Capacitor representation.
Two design techniques skip unnecessary data to improve performance and cost. With clustering, BigQuery better estimates query cost, queries eliminate scans of unnecessary data, and aggregates calculate quicker because blocks colocate records with similar values. Designers should examine queries for columns frequently used for filtering and cluster tables on those columns. Partitioning provides a similar benefit by dividing tables, often by date, so queries scan only matching partitions instead of the full table.
Warehouse workloads are analytical, historic, and bulk-oriented, while transactional systems are optimized for fast row lookups and strong consistency. Data warehouses contain historic information, most older data is often static, and many warehouses are designed to be append-only. Warehouses are typically updated with extract, transform, and load (ETL) or extract, load, and transform (ELT) patterns, where controlled update paths reproduce recent events from other sources. Queries are therefore large scans and aggregations for trends, reports, and models rather than updates to single records.
| System | Storage format | How capacity and speed are achieved |
|---|---|---|
| BigQuery data warehouse | Compressed columnar format in Colossus using Capacitor | Storage and compute scale separately with Dremel, slots, and autoscaling; no pre-provisioning |
| Oracle (opens in a new tab) transactional database | Row format using Oracle block format organized in segments | Requires pre-provisioned storage with careful sizing; uses indexes for fast row lookups and joins, an optimizer with statistics for plans, and strong consistency for backup and recovery |
The dependency is one-way: operational databases feed the warehouse through pipelines, and the warehouse then serves analysis. Processing pipelines using Pub/Sub, Dataflow, and Managed Service for Apache Spark take data from operational systems, transform it, and load it into BigQuery. Once loaded, storage duplication is encouraged if it makes queries faster, because storage is scalable and much cheaper than compute in the warehouse model.
A data lake ingests and stores raw data from many systems for later processing, a database runs day-to-day application transactions, and a data warehouse analyzes organized data for reporting. On Google Cloud, Cloud Storage is commonly used as the storage layer for a data lake, Cloud SQL, Datastore, and Bigtable run operational databases, and BigQuery is the commonly used data warehouse.
Use a Cloud Storage data lake when you need to keep raw, semistructured, and unstructured objects at scale and apply structure later, which is called schema-on-read because the schema is applied when data is read or processed. Use a BigQuery data warehouse when you need to analyze integrated structured data with defined table schemas, compressed columnar storage, and optimization for analytical queries, dashboards, and reporting.
Data moves through a processing pipeline that uses extract, transform, and load (ETL), which transforms data before loading, or extract, load, and transform (ELT), which loads data first and transforms it later. In a typical batch flow, source systems send raw objects into Cloud Storage, Managed Service for Apache Airflow coordinates pipeline tasks, Dataflow, Cloud Data Fusion, or Managed Service for Apache Spark transforms the raw data into clean structured form, and the transformed data is saved in Parquet or ORC and loaded into BigQuery for analysis.
A retail organization runs monthly revenue aggregations and trend analyses directly on its legacy transactional database. As data volumes grow, the analytics team notices two major issues:
The organization decides to offload its historical structured data to BigQuery for analytical processing.
How does BigQuery organize and process data to resolve these performance bottlenecks?
Last updated