Study plan

Start hereStudy noteData Preparation and Ingestion

When to load first and when to transform first, and what sits between the two

Objective 1 of 8 in Data Preparation and Ingestion, the first section in the guide. Not tried yet. See all 4 exam sections

What this note covers

ETL, ELT, and ETLT describe the order in which data extraction, transformation, and loading occur, and Google Cloud supports all three patterns depending on the target warehouse and transformation needs. Choosing between them depends on where you want transformation logic to run (in a dedicated processing engine vs. in the warehouse itself) and how much raw data you need to retain.

The facts the exam tests

• 1ETL (Extract, Transform, Load) transforms data before loading using tools like Dataflow or Dataproc, and is typical when data must be cleaned, masked, or restructured before it reaches the destination, such as loading into Cloud SQL or Bigtable.
• 2ELT (Extract, Load, Transform) loads raw data first, then transforms it using the destination engine's compute, and is the dominant pattern with BigQuery because it can run large-scale SQL transformations directly on loaded data.
• 3ETLT (Extract, Transform, Load, Transform) applies light transformations (e.g., filtering, format conversion, PII masking) before loading, then applies further business-logic transformations inside the warehouse, combining benefits of both approaches.
• 4Cloud Data Fusion and Dataflow are commonly used for ETL or the first-T in ETLT, while BigQuery SQL, scheduled queries, and dbt-style transformations are used for the ELT/second-T stage.
• 5ELT is generally preferred on Google Cloud when using BigQuery because it separates storage and compute, allowing raw data to be retained for reprocessing and reducing pipeline complexity.
• 6ETL is often chosen when transformations must occur before data reaches a destination that lacks strong transformation capabilities, or when compliance requires masking/redaction prior to storage.