Differentiate between different data manipulation methodologies (e.g., ETL, ELT, ETLT)

Data manipulation methodology is the question of where transformation work runs and at what point in the pipeline raw source records become analytics-ready records. The three patterns called out on the exam, ETL, ELT, and ETLT, differ in the order of the Extract, Load, and Transform steps and in which compute engine performs the transform step. The choice matters because it determines what data lands in the warehouse, how reproducible downstream queries are, and how cost is distributed between an external processing engine and the warehouse itself.

ETL, Extract Transform Load, originated in the era of on-premises data warehouses that charged for storage and were expensive to scale for ad hoc compute. An ETL pipeline pulls raw data from sources, runs business logic in a dedicated transformation engine like Dataflow or a Dataproc cluster, and lands only the cleaned, conformed output in the warehouse. The warehouse never sees raw rows. ETL is the right choice when source data contains sensitive fields that must be filtered or tokenized before they reach the analytics platform, when the warehouse is expensive per byte stored, or when the transformation includes joins against external systems that the warehouse engine cannot reach. The trade-off is that any change to business logic requires re-extracting from the source, which can be slow, or re-running the pipeline from a staging area, which requires keeping that staging area.

ELT, Extract Load Transform, inverts the last two steps. Raw data lands directly in the warehouse, often in a staging dataset, and the warehouse engine itself runs the transformation as SQL. ELT became the default on BigQuery because BigQuery separates storage from compute, charges for storage at rates close to object storage, and scales compute on demand for each query. There is no penalty for keeping raw data permanently, and re-running a transformation is a matter of re-issuing a SQL statement, not re-extracting from the source. ELT is the right choice when the source data fits naturally into the warehouse, when business logic changes often, or when the team prefers SQL over imperative pipeline code. The trade-off is that raw data sits in the warehouse, which can be a problem when the source contains regulated fields that must never appear in clear text inside the analytics environment.

ETLT, Extract Transform Load Transform, is a hybrid pattern that splits the transform step into two phases. The first transform happens before the warehouse load and handles only the things that must happen before the data crosses the trust boundary: redaction, tokenization, format normalization, schema unification across heterogeneous sources. The second transform happens inside the warehouse using SQL and handles business logic: aggregations, joins, derived metrics, business rules. ETLT is the right choice in regulated industries where some fields must be masked before landing, but where downstream business logic is iterative and benefits from SQL agility. A typical Google Cloud ETLT pipeline uses Dataflow with a Cloud Data Loss Prevention (Cloud DLP) inspection template to perform the pre-load redaction, lands the redacted data in BigQuery, and uses Dataform or scheduled queries for the post-load business transformations.

Beyond the three named patterns, two adjacent concepts often appear in exam scenarios. Reverse ETL takes data out of the warehouse and pushes it back into operational systems, for example pushing a customer segmentation model from BigQuery into a CRM. Change data capture (CDC) is a technique used inside any of the three patterns to ship only the rows that changed since the last sync rather than performing a full reload, and on Google Cloud it is implemented by Database Migration Service or Datastream.

The placement of the transform step has direct consequences for cost. In ETL, transformation compute is billed by the external engine; on Google Cloud that is Dataflow's vCPU-hour and memory-hour pricing, or Dataproc's per-second VM pricing. In ELT, transformation compute is billed by the warehouse; on BigQuery that is per-TB scanned (on-demand) or per-slot-hour (capacity-based). For ad hoc work that touches small data, on-demand BigQuery is cheaper than spinning up a Dataflow job. For repeated heavy transformations over many terabytes, slot-reserved BigQuery or Dataflow can be cheaper, depending on the workload shape. ETLT inherits the cost profile of both: an external engine bill for the pre-load step plus a warehouse bill for the post-load step.

Flow comparison of ETL, ELT, and ETLT

A canonical ELT pattern on BigQuery looks like this. The pipeline lands a CSV export from an OLTP database into a staging dataset, then a CREATE OR REPLACE TABLE statement applies business rules into a conformed table.

-- Stage table is loaded raw from CSV (string columns, no parsing)
CREATE OR REPLACE TABLE acme_warehouse.orders_clean AS
SELECT
 SAFE_CAST(order_id AS INT64) AS order_id,
 SAFE_CAST(customer_id AS INT64) AS customer_id,
 SAFE_CAST(order_total AS NUMERIC) AS order_total,
 PARSE_TIMESTAMP('%Y-%m-%d %H:%M:%S', ts) AS order_ts,
 LOWER(TRIM(country)) AS country
FROM acme_warehouse.orders_stage
WHERE order_id IS NOT NULL
 AND SAFE_CAST(order_total AS NUMERIC) >= 0;

A canonical ETL pattern using Dataflow performs the transform before the load, using the Python SDK. This is the right pattern when raw rows must never reach BigQuery.

import apache_beam as beam
from apache_beam.options.pipeline_options import PipelineOptions

def parse_and_clean(line):
 fields = line.split(",")
 return {
 "order_id": int(fields[0]),
 "customer_id": int(fields[1]),
 "order_total": float(fields[2]),
 "country": fields[3].strip().lower(),
 }

options = PipelineOptions(
 runner="DataflowRunner",
 project="acme-data",
 region="us-central1",
 temp_location="gs://acme-raw/tmp",
)

with beam.Pipeline(options=options) as p:
 (p
 | "Read CSV" >> beam.io.ReadFromText("gs://acme-raw/orders/*.csv", skip_header_lines=1)
 | "Parse" >> beam.Map(parse_and_clean)
 | "Drop bad" >> beam.Filter(lambda r: r["order_total"] >= 0)
 | "Write BQ" >> beam.io.WriteToBigQuery(
 table="acme-data:acme_warehouse.orders_clean",
 schema="order_id:INT64,customer_id:INT64,order_total:FLOAT64,country:STRING",
 write_disposition=beam.io.BigQueryDisposition.WRITE_APPEND))

Limits and quotas to remember

📊 Limit: BigQuery batch load jobs are free, but each job is bounded by 15 TB per load operation and 1,500 load jobs per table per day. Streaming inserts and the Storage Write API have separate billing and quota tracks.

📊 Limit: Dataflow jobs have a per-region quota of concurrent worker CPUs and in-use IP addresses. A pipeline that fails with a quota error is almost always hitting CPUS or IN_USE_ADDRESSES in the target region.

💡 Exam Trap: When the scenario says "raw data must be kept for audit and re-processing", the methodology is ELT, not ETL. ETL discards raw rows after the transform step unless an explicit staging area is kept, while ELT lands raw rows by design.

💡 Exam Trap: When the scenario says "PII must never reach the analytics platform in clear text but analysts must be able to iterate on business logic in SQL", the methodology is ETLT, not pure ETL. ETLT does the pre-load redaction in Dataflow with a Cloud DLP template and then lets analysts iterate on the redacted rows in BigQuery.

⚠️ Anti-Pattern: Building a long Dataflow pipeline that reads from BigQuery, transforms, and writes back to BigQuery to implement business logic that could be expressed as a SQL MERGE. The Dataflow round-trip adds vCPU-hour cost and shuffle latency for no benefit when the warehouse engine can do the same work cheaper.

🎯 Scenario: A retail company exports order data from a regulated OLTP database. Auditors require that credit card BIN numbers are tokenized before the data leaves the regulated environment, but the analytics team rewrites segmentation logic every week. The correct pattern is ETLT. Dataflow with a Cloud DLP template tokenizes the BIN at the pre-load step, BigQuery stores the redacted rows, and Dataform models the segmentation logic so the analytics team can iterate in SQL.

Decision Anchor

Choose ETL when raw source rows must not land in the warehouse, when transformation requires joins against systems the warehouse cannot reach, or when the warehouse charges per stored byte at a rate that makes raw retention uneconomical. Choose ELT when the warehouse separates storage from compute (BigQuery does), business logic changes often, and the team is fluent in SQL. Choose ETLT when a regulatory requirement forces a pre-load redaction step but downstream business logic still benefits from SQL agility.