Prepare and test your skills

Prepare and test your skills

Value realization is the stage of the data value chain where processed data becomes business value through analysis, visualization, and prediction. Raw records have already been staged, cleaned, and stored as trusted tables, and analysis turns those tables into answers that guide decisions. In Google Cloud, this work centers on BigQuery (opens in a new tab) for large-scale analysis, Looker (opens in a new tab) for business intelligence, and machine learning services for prediction.
The path from processed data to consumers is an ordered flow that protects consistency while allowing change. Data flows from transactional sources through staging into long-lived core tables, then through a business-logic layer to consumers who rely on stable schemas and metrics.
A five-step left-to-right flow from data sources through staging, core tables, and BigQuery views to consumers, showing views as the stable access layer.
The schemas and business logic in the access layer act like service agreements for downstream users. Any change to schema, logic, or completeness can affect dashboards, notebooks, and operational systems, so the warehouse team integrates new data while keeping that layer stable.
Business intelligence is the practice of using governed, up-to-date data for repeatable analysis that supports informed decisions. BigQuery analytics maximizes analysis on large datasets by running analytic queries at scale on a fully managed, highly scalable platform. Results then reach decision makers through visualization and exploration tools that tell a clear data story.
Consumers connect to the access layer and read the same trusted logic in different ways:
Looker is the business intelligence platform for this stage because it lets teams access, analyze, and act on an up-to-date, trusted version of the data. Because every consumer reads through the same views, a dashboard, a notebook, and an operational system all see consistent definitions, which makes decisions faster and more reliable.
Machine learning creates value by moving from explaining what happened to predicting what is likely to happen next. BigQuery ML (BQML) builds and trains machine learning models directly inside BigQuery using SQL queries, so analysts can create predictive models without moving data to a separate system. Vertex AI (opens in a new tab) is a fully managed, unified AI development platform for building and using more advanced and generative AI, while Dataflow can deploy and manage complete machine learning pipelines for batch and streaming data.
AI assistance is also built into the analysis tools themselves. Gemini in BigQuery helps users work with data in BigQuery, and Gemini in Looker helps users work with data in Looker. Together, these options let an organization start with SQL-based prediction where the data already lives and expand to a full AI platform when the use case needs greater scale or customization.
| Service | Primary unit | Choose when |
|---|---|---|
| BigQuery analytics | Analytic query on large datasets | The need is fast, repeatable analysis of processed warehouse data |
| Looker | Governed exploration and dashboard | The need is shared business intelligence on a trusted version of data |
| BigQuery ML | Model trained with SQL | The need is prediction directly where the data lives without a separate ML system |
Exam tip: Choose BigQuery ML when models are built directly using SQL queries; choose Vertex AI when the need is a fully managed platform to build and use broader AI.
Data types and storage architectures describe what kind of data an organization has and where that data should live for analysis. Structured, semi-structured, and unstructured data differ in how much predefined organization they have, and data lakes and data warehouses provide different homes for those formats. Metadata connects the two by describing what the data means and where it is stored.
Structured data is organized into tables with defined schemas, partitions, and column statistics. Apache Hive (opens in a new tab) is an open source data warehouse system built on Apache Hadoop that uses HiveQL, a SQL-like query language, to analyze large structured datasets. The Apache Hive metastore (HMS) holds metadata about Hive tables, such as their schema and location. Semi-structured data has some organization, such as tags or keys, but does not follow a rigid table schema. Unstructured data has no predefined model. Data lakes can store file-based data that may be semi-structured or unstructured, and a metastore describes that data with metadata so it can be queried.
A data warehouse is a central repository for structured data used for reporting, dashboards, and analysis. Data marts are subsets of the data warehouse focused on a specific business unit or business focus. Extract, transform, load (ETL) pipelines copy data from source systems to the data warehouse, then clean the data, apply business rules, check data integrity, and create aggregates or disaggregates. Extract, load, transform (ELT) loads data into the warehouse first, then uses SQL scripts to transform it; this can consume warehouse resources needed for concurrent querying, so ELT batches often run at night or off-peak. BigQuery (opens in a new tab) is a cloud data warehouse that is massively parallel and can scale resources for both ELT and concurrent querying. Change data capture (CDC) tracks data changes from source systems over time and works with ELT by storing the original record before downstream changes.
A data lake stores data files and uses a metastore to manage metadata about those files. Dataproc Metastore (opens in a new tab) is a fully managed Apache Hive metastore (HMS) on Google Cloud that manages technical metadata such as schemas, partitions, and column statistics. Dataproc Metastore is highly available, auto-healing, and serverless, and it provides interoperability between data processing engines such as Apache Hive, Apache Spark, and Presto. Tables in Hive applications can be managed internal tables or unmanaged external tables. The Hive warehouse directory is the default location for managed table data. An artifacts bucket is a Cloud Storage bucket created automatically with every metastore service, and it stores service artifacts such as exported metadata and managed table data. Endpoints provide clients access to Hive Metastore metadata through network endpoints using Apache Thrift or gRPC protocols. Metadata Federation lets clients access metadata stored in multiple Dataproc Metastore instances, and Auxiliary versions let multiple Hive client versions connect to the same service. Private Service Connect provides a private connection to Dataproc Metastore metadata across VPC networks, and VPC Service Controls creates perimeters to protect resources and data.
Data lakes and data warehouses differ in the data they store, how metadata is managed, and the engines used to process data. A lake manages files and assigns meaning through a metastore, while a warehouse manages tables with schemas for direct analysis.
| Dimension | Data lake | Data warehouse |
|---|---|---|
| Primary data | Underlying data files | Tables with schemas |
| Metadata | Managed by Dataproc Metastore (HMS) | Table definitions in the warehouse |
| Processing engines | Apache Hive, Apache Spark, Presto, Managed Service for Apache Spark | SQL, BigQuery |
| Main use | Manage data lake metadata, assign meaning to files, provide interoperability | Reporting, dashboards, analysis, data marts |
A metastore is a service that stores technical metadata about tables, such as schemas, partitions, and column statistics. Dataproc Metastore is a fully managed Apache Hive metastore (HMS) that manages data lake metadata and provides interoperability between processing engines. The Lakehouse runtime catalog (opens in a new tab) is another way to manage table metadata while reading and writing underlying data files directly in Hive. The Lakehouse runtime catalog supports catalog, database, table, and partition operations, and table and partition privileges through Identity and Access Management (IAM). The Lakehouse runtime catalog does not support user-defined functions, bucketing or skewing columns, table and partition column stats, key constraints, Kerberos delegation tokens, column privileges, roles, workload manager resource plans, or transactions and compactions. Hive Metastore supports all of these features.
Dataproc Metastore 2 and Dataproc Metastore 1 are the two versions of the service. Dataproc Metastore 2 uses a scaling factor to determine resources and can scale up or down by modifying the factor. Dataproc Metastore 2 offers horizontal scalability and zonal high availability (HA) without configuration, plus regional HA and disaster recovery (DR). Dataproc Metastore 1 uses service tiers to provide a predictable, predetermined amount of resources and provides zonal HA by default. In the Google Cloud console, Dataproc Metastore 2 shows Edition Enterprise - Single Region, while Dataproc Metastore 1 shows Tier: DEVELOPER or Tier: ENTERPRISE.
| Feature | Dataproc Metastore 2 | Dataproc Metastore 1 |
|---|---|---|
| Resource model | Scaling factor | Service tiers |
| Scalability | Horizontal scalability | Predictable, predetermined resources |
| High availability | Zonal HA without configuration; regional HA and DR | Zonal HA by default |
| Console value | Edition Enterprise - Single Region | Tier: DEVELOPER or Tier: ENTERPRISE |
Exam tip: ETL transforms data before loading it into the warehouse, while ELT loads data first and then transforms it with SQL.
Data ingestion and transformation processes move raw data from source systems into a cloud data warehouse and prepare it for analysis. Ingestion brings the data in either as a continuous stream or as a scheduled batch, and transformation cleans and reshapes it so business users can query dashboards and reports. In Google Cloud, BigQuery (opens in a new tab) serves as the central warehouse where ingested data lands and where analysis happens.
Batch and streaming data ingestion are two methods for bringing data into BigQuery that differ in timing and freshness requirements. Streaming ingestion writes data continuously through interfaces such as the Storage Write API, and the data is then used for ad hoc querying and near-real-time dashboards that detect trends and patterns through visualization. Because freshness requirements are very high in streaming, latency of the ingestion process is critical, and reliability means users can run ad hoc queries with consistent and predictable latency while dashboards reflect the latest available information. Batch ingestion collects and processes data on a schedule, such as the nightly financial-services process that joins data in BigQuery with other sources to generate daily regulatory PDF reports by a fixed deadline. As long as the batch completes by its deadline it is considered fast enough, and reliability means both ingestion and report production finish correctly and in a consistent timeframe without error.
Extract, Transform, and Load (ETL) is a workflow that extracts data from a source system, transforms it inside the pipeline, and then loads the prepared result into the data warehouse. A typical ETL workflow moves data through three steps in order:
Two parallel process flows comparing ETL where transform happens in the pipeline before loading, versus ELT where data is loaded first and transformed with SQL inside BigQuery.
The transform step applies several kinds of changes to prepare data for analysis:
It is common to have multiple data pipelines where the first pipeline copies data from the source system to the data warehouse and subsequent pipelines apply business logic for use in various data marts. A data mart is a subset of the data warehouse focused on a specific business unit or business focus. When multiple pipelines exist, an orchestration DAG manages them as sub-DAGs, so the larger objective of preparing data for a business unit's dashboards or reports is completed in the correct order.
Extract, Load, and Transform (ELT) is an alternative to ETL that first extracts data from the source system and loads it into the data warehouse, then performs transformations inside the warehouse using SQL scripts. This approach lets teams express transformations in SQL, but the transformation work can consume warehouse resources needed for concurrent querying, so ELT batches often run at night or off-peak when demand is lower. When ELT is adopted, the extract and load work is commonly separated into one DAG and the transformations into their own DAGs, where data is loaded once and then transformed multiple times to create different downstream tables for reporting. Cloud warehouses such as BigQuery are massively parallel data processing technologies, and in BigQuery additional resources can be purchased to support both increasing ELT demand and concurrent querying.
| Workflow | Order of operations | Where transformation happens |
|---|---|---|
| ETL | Extract, then transform, then load | Inside the pipeline before data enters the warehouse |
| ELT | Extract, then load, then transform | Inside the data warehouse using SQL after data is loaded |
Extract and Load (EL) and change data capture (CDC) are ingestion patterns that focus on getting data into the warehouse efficiently. EL can be used alone or followed by transformations, in which case it becomes ELT, and automated services such as BigQuery Data Transfer Service can perform EL without building a custom ingestion pipeline. CDC is a software design pattern that tracks data changes over time from various source systems, which fits data warehousing because the warehouse collates and tracks those changes. CDC works well with ELT because the original record is stored before any downstream changes are made. To implement it, database logs are processed with CDC software such as Datastream or open source Debezium, records flow through Dataflow (opens in a new tab) into BigQuery, and then a SQL query determines the latest version before further transformations are applied.
Exam tip: ETL transforms data in the pipeline before loading it, while ELT loads raw data first and then transforms it inside the warehouse with SQL.
Extract, Transform, and Load (ETL) transforms data inside the pipeline before loading it into the data warehouse, while Extract, Load, and Transform (ELT) loads data first and then transforms it inside the warehouse using SQL scripts. ETL cleans and reshapes data before it enters the warehouse, whereas ELT transformation work can consume warehouse resources needed for concurrent querying so ELT batches often run at night or off-peak.
Use BigQuery ML (BQML) when the need is prediction directly where the data lives without a separate machine learning system, because it builds and trains models directly inside BigQuery using SQL queries. Use Vertex AI when the need is a fully managed, unified platform to build and use broader, more advanced and generative AI, including use cases that need greater scale or customization.
A data lake stores underlying data files whose meaning is managed by metadata in Dataproc Metastore, while a data warehouse is a central repository for structured tables with schemas used for reporting, dashboards, and analysis. A lake provides interoperability across processing engines such as Apache Hive, Apache Spark, and Presto, whereas a warehouse manages tables for direct analysis and supports subsets called data marts focused on a specific business unit or business focus.
An enterprise is modernizing its data value chain on Google Cloud to transform raw information into actionable business insights. The organization has the following architectural requirements:
Which architectural approach best satisfies these requirements?
Last updated