Study guide

1.1 ETL, ELT, and ETLT Transformation Paradigms

Google Cloud Associate Data Practitioner Exam

Track your progress as you read

Finished sections get a check mark.

1 Introduction & Exam Overview

About the Associate Data Practitioner Exam

2 Chapter 1: Data Processing Paradigms & Ingestion Strategies

1.1 ETL, ELT, and ETLT Transformation Paradigms 1.2 Data Transfer and Migration Tools 1.3 Data Quality, Cleaning, and Validation

3 Chapter 2: Data Formats & Storage Solution Selection

2.1 File Formats & Serialization Characteristics 2.2 Cloud Storage Buckets, Location Types & Storage Classes 2.3 Selecting Google Cloud Databases and Storage Services

4 Chapter 3: Loading Data & Modern Ingestion Architectures

3.1 Batch Loading Data into BigQuery 3.2 Streaming Ingestion & Deduplication Patterns 3.3 BigQuery External Tables, BigLake & Federated Queries 3.4 Schema Definition & Evolution for Loaded Data

5 Chapter 4: BigQuery SQL Analytics & Query Foundations

4.1 BigQuery SQL Syntax & Analytical Functions 4.2 Complex Data Types: Arrays, Structs, and JSON 4.3 Answering Business Questions with BigQuery: Worked Analyses

6 Chapter 5: BigQuery Performance Optimization & Cost Governance

5.1 Table Partitioning Strategies 5.2 Table Clustering & Data Skipping 5.3 Query Optimization, Execution Plans, and Cost Controls

7 Chapter 6: Interactive Analysis, Notebooks & Visual Presentation

6.1 Exploratory Analysis with Jupyter & Colab Enterprise 6.2 Looker Studio (Data Studio) Reporting & Dashboard Design 6.3 Enterprise BI with Looker & LookML Foundations 6.4 Building, Modifying & Sharing Looker Dashboards

8 Chapter 7: Machine Learning with BigQuery ML & Vertex AI

7.1 Planning an ML Project: BigQuery ML, AutoML, or Pretrained Models? 7.2 Creating, Training & Evaluating Models with BigQuery ML 7.3 Running Inference and Organizing Models in Model Registry 7.4 Using Pretrained Gemini Models from BigQuery with Remote Connections

9 Chapter 8: Pipeline Engines & Transformation Services

8.1 Transformation Engine Selection Matrix 8.2 Apache Beam & Cloud Dataflow Core Concepts 8.3 Stream Processing: Windowing, Watermarks, and Triggers

10 Chapter 9: Workflow Orchestration, Automation & Monitoring

9.1 SQL Pipelines with Dataform & Scheduled Queries 9.2 Workflow Orchestration with Cloud Composer & Workflows 9.3 Event-Driven Ingestion: Pub/Sub to BigQuery & Eventarc Triggers 9.4 Monitoring Pipelines: Dataflow Job UI, Cloud Logging & Cloud Monitoring

11 Chapter 10: Access Control, Governance & Data Sharing

10.1 IAM Principles & Role Hierarchies for Data Services 10.2 Cloud Storage Security & Access Controls 10.3 Fine-Grained Security: Column-Level & Row-Level Controls 10.4 Secure Data Sharing with Analytics Hub (BigQuery Sharing)

12 Chapter 11: Lifecycle Management, Reliability & Compliance

11.1 Object Lifecycle Management & Storage Tiering 11.2 High Availability & Disaster Recovery Architectures 11.3 Data Retention, Time Travel, and Point-in-Time Recovery 11.4 Encryption Key Management & Privacy Compliance

1.1 ETL, ELT, and ETLT Transformation Paradigms

Key Takeaways Explain This Topic AI

Traditional ETL transforms data on dedicated middle-tier compute clusters before loading, creating scalability bottlenecks and discarding raw historical fidelity.

Cloud-native ELT loads raw data directly into BigQuery, leveraging decoupled Colossus storage and Dremel compute to perform scalable, in-warehouse SQL transformations with Dataform.

The hybrid ETLT model sanitizes or tokenizes sensitive PII in transit using Cloud Dataflow or Cloud DLP before landing, reserving heavy dimensional modeling for in-warehouse SQL.

Schema-on-write strictly validates incoming data at the ingestion gate, while schema-on-read utilizes semi-structured formats like BigQuery JSON to interpret attributes dynamically at query time.

Last updated: October 2026

ETL, ELT, and ETLT Transformation Paradigms

Core Focus: Modern cloud analytics architectures have fundamentally shifted from traditional compute-bound ETL to scalable, in-warehouse ELT and security-conscious ETLT hybrid designs. Understanding how Google Cloud separates storage from compute to empower BigQuery transformations is central to the Google Cloud Associate Data Practitioner examination.

Data engineering architectures are defined by where, when, and how data transformation occurs. In classical on-premises data management, data pipelines followed a rigid sequential path: data was extracted from operational sources, transformed inside an intermediary compute cluster, and finally loaded into a target data warehouse. Today, cloud scalability and distributed columnar storage have inverted this paradigm, giving rise to ELT and hybrid ETLT models.

Architectural Evolution: From ETL to ELT

To understand modern Google Cloud data architectures, we must examine the architectural constraints that governed traditional data warehousing.

Traditional ETL (Extract, Transform, Load)

In legacy on-premises environments (such as appliances from Teradata, Oracle, or IBM Netezza), storage and compute were tightly coupled and extraordinarily expensive. Running intensive transformations—such as multi-table joins, regex parsing, aggregations, and windowing—directly on the warehouse CPU would starve transactional or analytical queries of critical computing cycles.

Consequently, data architects introduced dedicated ETL processing servers (such as Informatica PowerCenter, IBM InfoSphere DataStage, or custom script servers). The workflow proceeded as follows:

Extract: Data was pulled from operational transactional databases (OLTP), flat files, or legacy mainframes.

Transform: The dedicated middle-tier ETL server applied business rules, joined disparate datasets, performed type conversions, aggregated metrics, and normalized the schema.

Load: Only the final, fully structured, aggregated, and curated data was written into physical data warehouse tables. [Operational Sources] ---> (Extract) ---> [ETL Compute Engine] ---> (Transform) ---> (Load) ---> [Data Warehouse]

Drawbacks of Traditional ETL:

Compute Bottlenecks: The middle-tier ETL cluster created an artificial bottleneck. As source data volume grew from gigabytes to terabytes, ETL clusters required costly vertical or horizontal scaling.

Loss of Raw Fidelity: Because only pre-aggregated, transformed data entered the warehouse, exploratory data analysis or retrospective model changes required re-extracting history from source systems—if raw backups even existed.

Pipeline Rigidity: Any change in reporting requirements necessitated rewriting and re-deploying the upstream ETL code.

Cloud-Native ELT (Extract, Load, Transform)

Cloud data warehouses—led by Google BigQuery—eliminated the physical hardware constraints of legacy appliances. By decoupling storage from compute, cloud platforms made storage virtually limitless and inexpensive, while compute became dynamically scalable on demand.

Under the ELT model, the order of operations changes fundamentally:

Extract: Data is extracted from source applications, APIs, event streams, or databases.

Load: Raw data is immediately loaded into Cloud Storage or directly into BigQuery staging tables without upfront schema alteration or heavy pre-processing.

Transform: Business analysts and data engineers use declarative BigQuery SQL or Dataform to transform, join, deduplicate, and model the data directly inside the warehouse engine. [Operational Sources] ---> (Extract) ---> (Load) ---> [BigQuery Raw / Staging] ---> (Transform: SQL / Dataform) ---> [Curated Data Marts]

Advantages of Cloud-Native ELT:

Maximized Ingestion Throughput: Ingestion pipelines are lightweight and fast because they only focus on moving bytes, not computing transformations.

Data Lakehouse Preservation: Raw data is permanently preserved in BigQuery or Cloud Storage, allowing future models, machine learning algorithms, and audits to re-process historical records without re-querying operational source systems.

Declarative Development: Transformations are written in standard ANSI SQL, broadening access beyond specialized Java/Scala pipeline developers to SQL analysts and analytics engineers.

Separation of Storage and Compute in Google Cloud

The technological foundation that makes ELT viable in Google Cloud is the architectural separation of storage and compute.

In Google BigQuery:

Storage Layer (Colossus): BigQuery stores data in Colossus , Google's globally distributed, highly durable cluster file system. Data is stored in a proprietary, highly optimized columnar format called Capacitor . Storage is billed independently at low commodity rates per gigabyte per month (with automatic transitions to long-term storage pricing for partitions untouched for 90 consecutive days).

Compute Engine (Dremel & Borg): Query execution is powered by Dremel , a massively parallel execution engine orchestrated across thousands of dynamically assigned worker cores (slots) managed by Borg (Google's large-scale cluster management system).

Jupiter Network: BigQuery connects compute slots to Colossus storage nodes via Google's multi-terabit-per-second Jupiter bisection network. This petabit-scale backplane allows hundreds of compute workers to stream raw columnar blocks from storage with virtually zero disk I/O bottleneck.

Because storage does not require dedicated running compute instances, organizations can land petabytes of raw un-aggregated data into BigQuery without paying for idle CPUs. When a transformation query runs, BigQuery elastically allocates hundreds or thousands of slots to execute the transformation in parallel in seconds, releasing those compute resources immediately upon query completion.

Schema-on-Write vs. Schema-on-Read

A critical conceptual distinction in modern ingestion pipelines is when schema validation is enforced.

Dimension Schema-on-Write Schema-on-Read

Enforcement Point At the ingestion boundary, before data is persisted. At query or transformation execution time.

Typical Formats Strongly typed relational tables, Avro with strict schemas. JSON, CSV, semi-structured logs, Parquet.

Pipeline Latency Higher upfront ingestion latency due to validation overhead. Minimal ingestion latency; raw bytes land immediately.

Error Handling Incompatible records are rejected or dropped at the gate. Incompatible records are ingested; malformed values handled during query execution.

Flexibility Low; schema changes require database migrations and pipeline updates. High; fields can be added dynamically to source payloads without breaking ingestion.

Google Cloud Fit BigQuery native tables with strict column definitions, Cloud Spanner, Cloud SQL. BigQuery JSON data type, Cloud Storage raw landing zones, BigQuery external tables.

Schema-on-Read in BigQuery

BigQuery offers native support for schema-on-read paradigms through its native JSON data type. Rather than flattening deeply nested, rapidly evolving JSON payloads into static relational columns during ingestion, pipelines can load the raw JSON string directly into a JSON column. Downstream transformations can then query nested attributes dynamically using standard SQL dot-notation and JSON extraction functions: -- Querying semi-structured JSON on-read without upfront schema definition

  • SELECT
  • event_id,
  • customer_id,
  • JSON_VALUE(raw_payload.event_type) AS event_name,
  • SAFE_CAST(JSON_VALUE(raw_payload.transaction.amount) AS NUMERIC) AS transaction_amount
  • FROM
  • `my_project.raw_lake.clickstream_events`
  • WHERE
  • JSON_VALUE(raw_payload.status) = 'SUCCESS';
  • In-Warehouse Transformations vs. External Pipeline Compute Engines

While ELT pushes transformations into BigQuery, enterprise data architectures frequently employ a combination of in-warehouse and external compute engines. Selecting the correct engine depends on the processing paradigm, data structure, and technical requirements.

1. In-Warehouse: BigQuery SQL and Dataform

BigQuery SQL: Ideal for set-based tabular transformations, dimensional modeling (star/snowflake schemas), window functions, time-series aggregations, and deduplication.

Dataform: A serverless orchestration and modeling service integrated into BigQuery. Dataform enables data teams to build version-controlled (Git-integrated), testable, and documented SQL transformation workflows (using SQLX). It automatically constructs Directed Acyclic Graphs (DAGs) of table dependencies, manages execution order, and executes data quality assertions.

2. Stream & Complex Batch Processing: Cloud Dataflow

Architecture: A fully managed, serverless runner for the open-source Apache Beam SDK.

Capabilities: Supports both batch and low-latency streaming processing with exactly-once processing guarantees. Dataflow excels at complex event-time windowing, sessionization, multi-stream joins, and custom procedural logic written in Java, Python, or Go.

When to Use: When data arrives continuously from streaming sources (like Cloud Pub/Sub), requires sliding or session window calculations, or requires complex procedural parsing before landing.

3. Managed Open-Source Big Data: Cloud Dataproc

Architecture: Managed clusters for running open-source processing frameworks such as Apache Spark , Apache Hadoop, Hive, and Presto.

Capabilities: Fast cluster initialization (typically 90 seconds), ephemeral cluster lifecycles, and Dataproc Serverless for Spark.

When to Use: When migrating existing legacy Spark or PySpark codebases to Google Cloud without re-architecting transformations into SQL or Beam, or when executing distributed machine learning feature pipelines using MLlib.

The Hybrid ETLT Paradigm

While ELT is the standard for general analytics, pure ELT introduces compliance, regulatory, and security risks when dealing with sensitive information. This necessity created the ETLT (Extract, Transform, Load, Transform) hybrid paradigm.

The Compliance Dilemma

Privacy and security frameworks such as the EU General Data Protection Regulation (GDPR), the Health Insurance Portability and Accountability Act (HIPAA), and the Payment Card Industry Data Security Standard (PCI DSS) push organizations to minimize and protect sensitive data. Many organizations turn that into an internal rule: raw Personally Identifiable Information (PII) or cardholder data (CHD) must not land in plain text in broadly accessible analytical storage. The frameworks themselves do not all forbid storage. HIPAA data, for example, can be stored on Google Cloud under a Business Associate Agreement with appropriate controls. The exam scenario tells you whether such a no-raw-landing rule applies.

If an organization with that rule uses pure ELT, raw Social Security numbers, card numbers, or diagnoses land in BigQuery tables or Cloud Storage buckets before any masking SQL can run. The rule is already broken, and the raw values also persist in time travel and backups.

How ETLT Resolves the Problem

ETLT introduces a targeted, lightweight transformation step in transit before loading, while reserving heavy analytical transformations for the warehouse:

Extract: Data is pulled from source systems or event buses.

Transform (Lightweight In-Transit Pre-Processing):

Cloud Dataflow, Cloud Functions, or Cloud Run intercepts the raw stream.

PII fields (names, email addresses, national IDs) are masked, tokenized, hashed (SHA-256 with salt), or encrypted using Cloud KMS (Key Management Service) or the Sensitive Data Protection (formerly Cloud DLP) API.

Malformed records are filtered or routed to a dead-letter sink.

Load: The sanitized, tokenized data is safely written into BigQuery or Cloud Storage.

Transform (Heavy In-Warehouse Modeling):

BigQuery SQL and Dataform execute complex multi-table joins, historical deduplication, dimensional rollups, and aggregation queries across the securely ingested dataset. [Source] ---> (Extract) ---> [Dataflow / DLP: Mask PII] ---> (Load) ---> [BigQuery Staging] ---> (Dataform: Star Schema SQL) ---> [Data Mart]

Real-World Decision Matrix: Choosing the Transformation Paradigm

The following matrix synthesizes the architectural criteria used to evaluate transformation approaches on Google Cloud:

Evaluation Criteria Traditional ETL Cloud-Native ELT Hybrid ETLT

Primary Compute Engine Dedicated ETL servers (Dataflow / Dataproc / Informatica) BigQuery SQL / Dataform Dataflow / Cloud DLP (in transit) + BigQuery SQL (in warehouse)

Optimal Data Volume Low to Moderate (< 1 TB) Massive (Terabytes to Petabytes) Massive (Terabytes to Petabytes)

Ingestion Latency High (delayed by pre-load processing) Low (direct streaming or batch append) Moderate (micro-batch or stream masking)

Compliance & PII Safety High (data can be sanitized before warehouse load) Low (raw PII lands directly in warehouse storage) Highest (sensitive tokens replaced before persistence)

Schema Governance Schema-on-write (rigid, strict validation) Schema-on-read / Dynamic schema evolution Dual: Strict validation on sensitive fields; flexible on payload body

Skillset Required Specialized pipeline engineers (Java, Scala, Python) Analytics engineers, SQL developers Data engineers (Beam/APIs) + SQL analysts

Ideal Use Cases Legacy migrations with fixed schema formats; low-capacity targets. Enterprise reporting, data lakehouse modeling, marketing attribution. Healthcare analytics, financial transaction monitoring, GDPR-regulated user telemetry.

Common Exam Traps and Scenarios

Exam Tip: Watch out for scenario questions that tempt you to over-engineer pipelines. If the question describes batch transformation of structured tabular data already landing in Google Cloud without strict in-transit PII masking requirements, BigQuery SQL with Dataform is almost always the recommended, cost-effective Google-native solution—not a self-managed Spark cluster or complex Beam pipeline.

Trap 1: Conflating Dataflow with In-Warehouse Transformation

The Trap: Recommending Cloud Dataflow to join two existing BigQuery tables and write the result back to BigQuery.

The Reality: Dataflow is an external compute engine. Pulling data out of BigQuery into Dataflow workers to perform joins and write it back adds data-movement (read) costs, worker VM compute costs, and pipeline maintenance. BigQuery SQL performs distributed in-memory joins natively via Dremel slots far faster and at lower cost.

Trap 2: Ignoring Regulatory Landing Constraints

The Trap: Choosing pure ELT for medical or credit card processing to "keep ingestion simple," planning to run a scheduled BigQuery query that masks sensitive columns every night.

The Reality: When the requirement says raw sensitive values must never be persisted, a nightly masking job is too late: the raw values already sit in tables, time travel, and backups. An ETLT pattern that de-identifies values in flight with Sensitive Data Protection (formerly Cloud DLP) or Dataflow is the design that meets that requirement.

Trap 3: Assuming Schema-on-Write Is Obsolete

The Trap: Assuming schema-on-read is universally superior and that strict schemas should never be used.

The Reality: Core financial ledgers, billing transactions, and compliance records require strict schema-on-write validation. A corrupted currency field or missing account ID must fail at the gate rather than propagating downstream into reporting dashboards.