ETL and ELT Pipelines on Google Cloud

ETL (Extract, Transform, Load) and ELT (Extract, Load, Transform) are two patterns for moving data from source systems to a target data warehouse or data lake. The core difference lies in the order of operations: ETL transforms data before loading, while ELT loads raw data first and transforms it later within the target system. This seemingly simple distinction has profound implications for architecture, cost, latency, and scalability.

In traditional on-premises data warehousing, ETL was the dominant pattern because compute resources were expensive and limited. The transformation step cleaned, enriched, and aggregated data before loading it into the warehouse, minimizing storage consumption and ensuring that only high-quality data entered the warehouse. However, this approach required significant upfront design and could not easily accommodate new data sources or schema changes.

With the advent of cloud data warehouses like BigQuery, which separate compute and storage, ELT has become increasingly popular. BigQuery's massive parallel processing (MPP) engine can transform petabytes of data in seconds, making it efficient to load raw data and transform it on demand. ELT allows for greater flexibility — raw data is always available for reprocessing or ad-hoc analysis — and reduces the time to load data into the warehouse.

A typical ETL pipeline on Google Cloud uses Cloud Storage as a landing zone, Cloud Dataflow or Dataproc for transformation, and BigQuery or Cloud SQL as the target. The steps are:

Key services: - Cloud Dataflow: A fully managed, serverless service for stream and batch processing based on Apache Beam. It handles auto-scaling, checkpointing, and exactly-once processing semantics. - Cloud Dataproc: A managed Spark and Hadoop service. It is ideal for existing Spark jobs or when you need fine-grained control over cluster configuration. Dataproc clusters can be created on-demand and autoscaled using preemptible VMs to reduce cost. - Cloud Data Fusion: A fully managed, code-free data integration service that provides a visual interface to build ETL/ELT pipelines. It is based on the open-source CDAP project and supports 150+ connectors.

ELT flips the order: load first, transform later. The typical pipeline is:

Key services: - BigQuery: A serverless, highly scalable data warehouse. It uses a columnar storage format (Capacitor) and a distributed query engine (Dremel). BigQuery charges for storage and for the number of bytes processed by queries. Using clustered and partitioned tables reduces query costs. - Cloud Dataform: A service for managing SQL-based data transformation pipelines. It allows you to define dependencies, version control transformations, and schedule execution. - BigQuery Data Transfer Service: Automates loading data from SaaS applications (Google Ads, Google Analytics, etc.) into BigQuery.

Choose ETL when: - The target system has limited compute capacity (e.g., traditional RDBMS). - You need to enforce strict data quality rules before data enters the warehouse. - The transformations are complex and require custom code (e.g., machine learning inference). - You are dealing with sensitive data that must be masked or anonymized before storage.

Choose ELT when: - The target is a scalable cloud data warehouse like BigQuery or Snowflake. - You need to load data quickly for near-real-time availability. - You want to preserve raw data for future reprocessing or unknown use cases. - The transformations are SQL-based and can leverage the warehouse's processing power.

Cloud Storage: Default bucket locations are multi-regional (e.g., US, EU) but can be regional for lower latency. Object versioning is disabled by default. Lifecycle policies can automatically delete or archive objects.

Cloud Dataflow: Default worker machine type is n1-standard-2. The default disk size per worker is 250 GB (HDD) or 50 GB (SSD). The service uses a streaming engine for unbounded data and a batch engine for bounded data. The default worker region is determined by the pipeline's region.

Cloud Dataproc: Default master machine type is n1-standard-4; worker type is n1-standard-4. The default number of workers is 2. Clusters use a staging bucket in Cloud Storage. Preemptible workers can be used for cost savings (up to 80% discount) but may be terminated at any time.

BigQuery: Default table expiration is never. Partitioning is by ingestion time (_PARTITIONTIME) or by a DATE/TIMESTAMP column. Clustering can be on up to 4 columns. The default query job priority is INTERACTIVE (immediate execution); BATCH priority is available for lower cost.

To create a Dataflow pipeline from the command line:

gcloud dataflow jobs run my-pipeline \
  --gcs-location gs://my-bucket/templates/my-template \
  --parameters input=gs://my-bucket/input,output=gs://my-bucket/output \
  --region us-central1

To submit a Spark job to Dataproc:

gcloud dataproc jobs submit spark \
  --cluster my-cluster \
  --region us-central1 \
  --jar gs://my-bucket/my-job.jar \
  -- my-arg1 my-arg2

To load data into BigQuery:

bq load --source_format=PARQUET mydataset.mytable gs://my-bucket/data/*.parquet

To transform data in BigQuery using SQL:

CREATE OR REPLACE TABLE mydataset.curated AS
SELECT 
  user_id,
  TIMESTAMP_MICROS(event_timestamp) AS event_time,
  event_type,
  LOWER(page_title) AS page_title
FROM mydataset.raw_events
WHERE event_type IS NOT NULL;

ETL and ELT pipelines often integrate with: - Cloud Pub/Sub: For streaming ingestion. Dataflow can subscribe to a Pub/Sub topic and process messages in near-real-time. - Cloud Functions: For lightweight, event-driven transformations. For example, a Cloud Function can be triggered when a new file lands in Cloud Storage to perform a quick validation or enrichment. - AI Platform: For ML-based transformations. Dataflow can call AI Platform Prediction to score records during transformation. - Data Catalog: For metadata management. Pipelines can automatically register datasets in Data Catalog to enable discovery and lineage tracking.

A large retailer ingests daily sales data from thousands of stores into a central data warehouse for reporting. They use Cloud Data Fusion to build an ETL pipeline: extracts from on-premises Oracle databases via JDBC, transforms by cleaning invalid store IDs, joining with product catalogs, and aggregating sales by hour. The transformed data is loaded into BigQuery partitioned tables. The pipeline runs nightly, completing within 4 hours for 10 TB of data. Key considerations: Data Fusion uses a Cloud Dataproc cluster under the hood; they configured 10 preemptible workers to reduce costs. A common issue was schema drift — when new columns were added to source tables, the pipeline failed. They mitigated this by using a flexible schema in Data Fusion that allows new columns to pass through. The pipeline is monitored via Cloud Monitoring alerts on Dataflow job failures and BigQuery load job errors.

A manufacturing company collects sensor data from thousands of IoT devices every second. They use Pub/Sub to ingest streaming data, which is then loaded into BigQuery via the Storage Write API in near-real-time (latency under 10 seconds). Raw data is stored in a partitioned table by timestamp. Every hour, a Dataform pipeline transforms the raw data into aggregated tables: average temperature per machine per hour, anomaly scores, and rolling 24-hour summaries. The ELT approach allows data scientists to query raw sensor data for ML model training without waiting for ETL jobs. Cost management is critical: they use BigQuery's max_bytes_billed setting to prevent runaway queries. A misconfiguration once caused a $10,000 query; they now enforce a 1 TB limit per user. They also use clustered tables on machine_id and timestamp to reduce query costs by 60%.

A bank needs to process transaction data for regulatory reporting. They use Cloud Dataproc with Spark to run complex transformations: joining transaction data with customer master data, applying business rules for suspicious activity detection, and masking PII. The transformed output is loaded into BigQuery and also exported to encrypted Cloud Storage for archival. ETL is chosen because regulations require that PII is masked before it enters the warehouse. The pipeline runs in batch mode every 15 minutes to meet near-real-time reporting SLAs. They use Dataproc's autoscaling with preemptible workers to handle peak loads. A common failure point is shuffle spill — when intermediate data exceeds memory, Spark writes to disk, slowing the job. They tuned the shuffle partitions and increased executor memory to 8 GB to avoid this.