ETL vs ELT in GCP: BigQuery, Dataflow, and dbt

Simple explanation

ETL and ELT describe the order of three steps: extracting data from a source, transforming it into a useful shape, and loading it into a destination. In ETL, you clean and reshape the data in a processing engine (like Dataflow) before it reaches the warehouse. In ELT, you load the raw data straight into the warehouse (BigQuery) and clean it there using SQL.

ETL vs ELT: quick comparison

How ETL works in GCP

In an ETL pipeline, data is extracted from a source system, transformed in a processing engine, and loaded into the destination in its final shape. The destination only ever receives clean, modelled data.

The primary GCP tool for ETL is Dataflow, which runs Apache Beam pipelines. You write pipeline code in Python or Java that reads from a source, applies transformations (parsing, filtering, joining, aggregating), and writes to a sink.

Batch ETL example

A nightly job reads CSV exports from Cloud Storage, validates and normalises the records, and writes clean rows to BigQuery:

Cloud Storage (CSV files) → Dataflow batch job → BigQuery (clean table)

Streaming ETL example

An application publishes events to Pub/Sub. A streaming Dataflow job consumes those events, parses the payload, masks PII fields, applies windowed aggregations, and writes results to BigQuery:

Pub/Sub → Dataflow (parse, mask PII, aggregate) → BigQuery
              ↓
       Dead-letter topic

Why ETL still matters

ETL adds operational complexity. You maintain Beam pipeline code, manage Dataflow worker scaling, and redeploy when logic changes. That overhead is justified when:

• Data arrives as a continuous stream and needs real-time windowing or aggregation before storage
• Sensitive fields must be masked or redacted before any persistence (compliance requirement)
• The raw format (binary, encrypted, proprietary protocol) cannot be loaded directly into BigQuery
• You need to join streaming events with reference data at high throughput before writing

How ELT works in GCP

In ELT, raw data is extracted and landed in BigQuery (or Cloud Storage as an intermediate layer) without significant transformation. Transformation happens afterward inside BigQuery using SQL.

Common loading paths

• Batch loads from Cloud Storage: upload CSV, JSON, Avro, or Parquet files to a Cloud Storage bucket, then load them into BigQuery with bq load or the Storage Write API
• BigQuery Data Transfer Service: scheduled imports from SaaS sources (Google Ads, YouTube, S3) directly into BigQuery tables
• Direct streaming inserts: applications write rows directly to BigQuery using the Storage Write API
• Federated queries: query data in Cloud Storage directly from BigQuery without loading it

Transformation tools

Dataform is Google Cloud’s native SQL transformation tool. It is integrated into the BigQuery console, requires no separate infrastructure, and manages dependencies between SQL models. Dataform is a strong default for teams fully committed to GCP.

dbt (data build tool) is the cross-platform standard. It compiles SQL models, runs them in BigQuery, materialises results as tables or views, handles dependency ordering, runs data quality tests, and generates documentation. dbt is the better choice when you need portability across cloud providers or want access to the larger package ecosystem.

Scheduled queries in BigQuery can handle simple, standalone transformations without any external tool.

ELT example flow

SaaS export / API → Cloud Storage (raw JSON) → BigQuery raw table
                                                      ↓
                                              Dataform or dbt
                                                      ↓
                                          BigQuery marts (clean tables)

-- Example Dataform model: models/marts/daily_revenue.sqlx
config {
  type: "table",
  schema: "marts"
}

SELECT
  DATE(order_timestamp) AS order_date,
  SUM(order_total)      AS daily_revenue,
  COUNT(DISTINCT order_id) AS order_count
FROM ${ref("raw_orders")}
WHERE status = 'completed'
GROUP BY 1

Changing the transformation means editing the SQL file and running the model. No Dataflow pipeline to rebuild and redeploy.

When to use ETL

• Streaming ingestion with real-time requirements. An IoT platform publishes sensor readings to Pub/Sub every second. Dataflow consumes the stream, applies windowed aggregations (5-minute averages), and writes results to BigQuery. The transformation must happen at ingestion time because downstream consumers need near-real-time aggregated data, not raw events.
• Pre-storage PII masking. A healthcare application processes patient records. Regulatory requirements prohibit storing unmasked patient identifiers in the data warehouse. Dataflow hashes or redacts sensitive fields before writing to BigQuery, so raw PII never exists in warehouse storage.
• Binary or proprietary format parsing. A legacy system exports data in a custom binary protocol. BigQuery cannot parse it. A Dataflow pipeline reads the binary files, deserialises them into structured records, and loads the parsed output.
• High-throughput stream enrichment. An ad-tech pipeline joins clickstream events arriving at 100K events/second with a reference dataset (campaign metadata). This join needs to happen at ingestion speed. Dataflow side inputs handle it efficiently, while a post-load BigQuery join would introduce latency and repeated full-table scans.

When to use ELT

• Batch analytics on structured or semi-structured data. A retail company loads daily sales CSV exports into BigQuery and builds dimensional models (fact tables, dimension tables) with Dataform. Analysts iterate on business logic in SQL without waiting for engineering pipeline changes.
• SaaS data consolidation. Marketing data from Google Ads, Facebook Ads, and Salesforce lands in BigQuery via the Data Transfer Service and third-party connectors. dbt models normalise the different source schemas into a unified reporting layer.
• Ad-hoc exploration and prototyping. A data scientist loads a new dataset into BigQuery to evaluate whether it is useful. Raw data goes in first; transformation happens if and when the data proves valuable. No upfront pipeline investment.
• Evolving requirements. The business changes how it defines “active user” quarterly. With ELT, the raw event data stays in BigQuery. The team updates the SQL model definition and reruns it. No re-extraction from the source system is needed.

When to use both together

Many production pipelines use ETL and ELT in the same data flow. The boundary is the BigQuery raw landing zone: Dataflow handles everything upstream, and Dataform or dbt handles everything downstream.

Hybrid example: e-commerce event pipeline

Mobile app → Pub/Sub → Dataflow (parse JSON, mask email, validate schema)
                                    ↓
                          BigQuery raw.events table
                                    ↓
                     Dataform (sessionise, build fact tables, aggregate)
                                    ↓
                          BigQuery marts.daily_metrics

The ETL layer (Dataflow) handles streaming ingestion, PII masking, and schema validation. These are things that must happen before data reaches storage.

The ELT layer (Dataform) handles sessionisation, dimensional modelling, and metric aggregation. These benefit from iterative SQL development and do not require real-time processing.

The key decision: if a transformation must happen before storage (compliance, format, latency), it belongs in the ETL layer. If it can happen after storage, it belongs in the ELT layer where iteration is faster and cheaper.

Dataflow vs Dataform/dbt: which tool for which job

The real decision most teams face is not “ETL vs ELT” in the abstract. It is “should I write this transformation in a Dataflow pipeline or in a SQL model?” Here is how to decide:

Rule of thumb: if the data is already in BigQuery and the logic can be expressed in SQL, use Dataform or dbt. Reach for Dataflow only when you need streaming, pre-load processing, or logic that SQL cannot express (custom Python/Java functions on individual records at scale).