Prepare and test your skills
Prepare and test your skills
Assessing data transformation patterns means deciding where cleaning and shaping work happens in the pipeline and which Google Cloud service does that work with the least management effort. The two core patterns are extract, transform, and load (ETL) and extract, load, and transform (ELT). In Google Cloud, BigQuery (opens in a new tab) provides high-performance SQL-based processing for ELT, while Cloud Dataprep by Trifacta (Dataprep) provides visual exploration and preparation for ETL flows.
Extract, transform, and load (ETL) is the pattern where data is extracted from the source, transformed in an intermediate processing service, and only then loaded into BigQuery as cleaned production data. A file-based ETL flow moves data from the source system to staging, then copies that data to a Cloud Storage bucket, then reads from the Cloud Storage bucket into a transformation service that writes the result to BigQuery. A JDBC-based ETL flow reads directly from the source database through a JDBC driver into the transformation service, which then writes the transformed rows to BigQuery. Choose ETL when data must be trimmed, cleansed, or enriched before loading, or when an existing transformation process must be preserved.
A process flow comparing two data patterns: ETL extracts from a source, transforms with Dataflow, Data Fusion, Spark, or Dataprep, then loads into BigQuery, while ELT loads raw data into a BigQuery staging table first and then transforms it with SQL into the production table.
Transformation before loading is performed by fully managed processing services that read from Cloud Storage or JDBC and write to BigQuery:
Where the transformation runs affects throughput and network cost. If data is transformed in the existing on-premises or source environment, available compute capacity and tooling can limit throughput, and enrichment can require additional transfer time and network bandwidth. If data is transformed on Google Cloud, the pipeline depends on Cloud Storage as the staging landing zone and on Dataflow, Data Fusion, Managed Service for Apache Spark, or Dataprep for compute.
Extract, load, and transform (ELT) is the pattern where raw data is loaded into BigQuery first and transformed inside BigQuery afterward. Data flows from the source system to a BigQuery staging table using any extract and load option, then SQL queries read from the staging table and write their output into the final production table. A typical marketing-analytics ELT workflow periodically extracts new customer data with BigQuery Data Transfer Service, loads the raw data into BigQuery tables, and then runs scheduled SQL transformations with Dataform to produce metrics such as campaign performance. Choose ELT when the raw data can land unchanged and the warehouse itself can supply the compute for cleaning, joining, and aggregation.
Exam tip: ETL transforms data before it is loaded into BigQuery with Dataflow, Data Fusion, Dataproc, or Dataprep, while ELT loads data into a BigQuery staging table first and then transforms it with SQL queries that write to the production table.
BigQuery is a serverless cloud data warehouse that separates storage from compute so ELT transformations scale without cluster resizing. Storage lives in the Colossus distributed file system in a compressed columnar format called Capacitor, and BigQuery operates directly on that compressed data without decompressing it. Compute is supplied as slots, which are units of computational capacity managed by Google and allocated to queries by a fair scheduler, with memory supplied by a remote distributed service over a petabit network. While columns are encoded, BigQuery persists statistics that the engine later uses to compile optimal plans and select runtime algorithms.
This architecture reduces operational work for transformation workloads. The team does not provision nodes, manage operating systems, or rebalance storage and compute, because storage grows to dozens of petabytes independently and compute scales per query. Columnar storage, in-memory execution, automatic storage optimization, and diagnostic query plans keep large SQL transformations fast while keeping management limited to choosing slot capacity through on-demand or reservation pricing.
Cloud Dataprep by Trifacta is a data service for visually exploring, cleaning, and preparing structured and unstructured data for analysis, reporting, and machine learning. Source data is exported to JSON or CSV files, the analyst builds cleaning steps visually in Dataprep instead of writing pipeline code, and Dataflow executes those steps and loads the result toward BigQuery. This flow moves data from the source files to Dataprep for recipe design and then from Dataflow to BigQuery for storage. Choose Dataprep when business users need to inspect and shape messy data interactively before it becomes a production table.
Comparing ETL and ELT for operational efficiency means looking at where compute runs, what skills are required, and how much infrastructure the team manages:
| Pattern | Where transformation runs | Primary tools |
|---|---|---|
| ETL | Transformation runs before the BigQuery load in a processing service that reads from Cloud Storage or JDBC | Dataflow with Beam, Data Fusion with graphical pipelines, Managed Service for Apache Spark with Spark SQL or code, Dataprep with visual recipes executed by Dataflow |
| ELT | Transformation runs after the BigQuery load as SQL that reads from a staging table and writes to a production table | BigQuery Data Transfer Service for scheduled loads, BigQuery SQL for transformation, Dataform for versioned and scheduled SQL pipelines |
Operational overhead is lowest when ingestion is automated and transformation uses managed or visual services. BigQuery Data Transfer Service automates ingestion at scale on a scheduled or ad hoc basis without writing code, Cloud Data Fusion builds pipelines through a code-free graphical interface, and Dataflow runs Beam transformations as a serverless fully managed service with virtually limitless capacity. Use ELT with BigQuery SQL and Dataform to minimize new clusters when SQL can express the logic, and use ETL with Dataflow, Data Fusion, or Dataprep when data must be cleaned or enriched before it should land in the warehouse.
Evaluating managed versus serverless processing models means deciding who manages compute capacity and how pipelines are written. Managed cluster-based processing provisions clusters that run existing open source engines, while serverless processing removes cluster management and autoscales workers for each job. On Google Cloud, Cloud Dataproc (opens in a new tab) represents the cluster model for existing Hadoop and Spark workloads, and Cloud Dataflow (opens in a new tab) represents the serverless model for unified batch and stream processing with Apache Beam.
Cloud Dataproc is a managed service for open source data tools such as Apache Spark, Flink, and Presto for batch processing, SQL, streaming, and machine learning. The service runs jobs on Managed Service for Apache Spark clusters that are ephemeral, which means each cluster exists only as long as the processing stage runs and is deleted when the job ends. This model fits teams with existing Hadoop or Spark code because pipelines can be ported and run unmodified instead of being rewritten for a new framework.
Orchestration of an ephemeral Spark workflow is commonly handled by Managed Airflow, which runs an ordered sequence:
Because batch jobs can be recreated and restarted, recovery focuses on the data itself: ingested files in Cloud Storage, processed columnar files such as Parquet and ORC, and table metadata in Dataproc Metastore. Choose this model when the priority is compatibility with an existing Spark or Hadoop ecosystem, control over the engine and job lifecycle, and reuse of current libraries and skills.
Cloud Dataflow is a fully managed, serverless service for running Apache Beam (Beam) jobs at scale for both batch and streaming data. Beam provides one programming model for bounded historical data and unbounded real-time data, with windowing and session-analysis primitives and source and sink connectors including BigQuery. Data flows from one or more sources through Beam transforms to a destination, and the same pipeline code can run on Dataflow or on other Beam runners such as Apache Flink or Apache Spark without rewriting.
The serverless approach of Dataflow removes cluster operations because performance, scaling, availability, security, and compliance are handled automatically. When a job runs, Dataflow allocates a pool of worker virtual machines (VMs), distributes work in parallel across workers, autoscales workers up or down with load, and dynamically rebalances slow work among workers. When the job completes or is cancelled, Dataflow automatically deletes the workers, and billing is for the compute resources the job used.
Typical uses for Dataflow pipelines include:
Dataflow provides exactly-once processing of every record by default, with an at-least-once mode for streaming pipelines that can tolerate duplicates to reduce cost and improve latency. Pipelines can be built with the Beam Java, Python, and Go software development kits (SDKs), deployed as reusable Dataflow templates, or developed iteratively in JupyterLab notebooks. Jobs are observable through the Dataflow monitoring interface, which shows the pipeline graph and execution details for each stage, plus centralized logging.
Choosing between Dataproc and Dataflow turns on code reuse versus operational simplicity and unified streaming. Dataproc keeps the engine and cluster lifecycle visible so existing Spark jobs move with minimal change, while Dataflow hides workers and scales one Beam pipeline across batch and stream. Regional controls also differ in expression: a Dataflow job uses regional endpoint, worker region, and worker zone settings to control where workers run and where job metadata is stored, while sources, sinks, and temporary files must be placed to avoid sending data across regions.
| Model | Operational model | Programming model | Choose when |
|---|---|---|---|
| Cloud Dataproc | Managed ephemeral Spark clusters created and deleted per job | Existing Spark, Hadoop, Flink, or Presto code and SQL | The workload already runs on Hadoop or Spark and rewriting would be impractical |
| Cloud Dataflow | Serverless workers allocated, autoscaled, rebalanced, and deleted automatically | Apache Beam in Java, Python, or Go for unified batch and stream | The workload needs one pipeline for batch plus low-latency streaming without managing clusters |
Exam tip: Keep existing Spark or Hadoop jobs on Dataproc to avoid a rewrite, and choose Dataflow when one Beam pipeline must handle both batch and streaming with automatic scaling.
Analyzing real-time and event-driven architectures means understanding how event producers and consumers communicate asynchronously through a messaging layer instead of waiting on direct calls. In Google Cloud, Cloud Pub/Sub (opens in a new tab) provides the ingestion and distribution layer, while Cloud Dataflow (opens in a new tab) and Cloud Functions provide the processing layer for low-latency transformation and immediate insight. Publishers send events without knowing how or when they will be processed, and Pub/Sub delivers those events to all reacting services with latencies typically on the order of 100 milliseconds.
Cloud Pub/Sub is an asynchronous and scalable messaging service that decouples services producing messages from services processing those messages. Publishers send events to a named topic in the Pub/Sub service, and the service delivers copies of each event to interested subscribers without requiring publishers to wait for subscribers as with synchronous remote procedure calls (RPCs). This asynchronous integration increases flexibility and robustness because producers and consumers evolve and scale independently.
Cloud Pub/Sub uses per-message parallelism rather than partition-based messaging. The Pub/Sub service leases individual messages to subscriber clients and tracks whether each message is successfully processed, which maximizes parallelism and keeps publishers and subscribers independent. By contrast, partition-based systems such as Apache Kafka force subscribers to process messages in each partition in order and limit concurrent clients to the number of partitions. Cloud Pub/Sub also combines horizontal scalability with middleware features such as dead-letter queues and filtering.
Cloud Pub/Sub supports common real-time ingestion patterns across teams and organizations:
A Pub/Sub subscription can store unacknowledged messages for up to 7 days without restriction on the number of messages. If processing pipelines stop, the subscription buffers backed-up events with low recovery point objective (RPO), and processing resumes when pipelines restart. For locality control, publishers can use the global Pub/Sub endpoint accessible from any Google Cloud location, configure customer-managed encryption keys (CMEK) with Cloud Key Management Service (Cloud KMS) to meet encryption-based locality rules while keeping high availability, or set a message storage policy that persists messages only in specified regions with trade-offs concerning availability.
Cloud Dataflow is a fully managed Google Cloud service for unified stream and batch data processing at scale. Dataflow executes pipelines written with the open source Apache Beam programming model, which provides windowing and session-analysis primitives plus source and sink connectors including a connector for BigQuery. The same programming model handles both streaming for real time and batch for historical data with equal reliability and expressiveness. Typical uses include data movement and ingestion, extract-transform-load (ETL) into BigQuery, business intelligence dashboard support, real-time machine learning analysis, and sensor or log processing at scale.
Cloud Dataflow removes operational overhead through a serverless approach where performance, scaling, availability, security, and compliance are handled automatically. When a job runs, the Dataflow service allocates a pool of worker virtual machines (VMs) to execute the pipeline in parallel, autoscales by adding or removing workers, dynamically rebalances work among VMs, and automatically deletes the VMs when the job completes or is cancelled. Pipelines can be created with Apache Beam software development kits (SDKs) for Java, Python, and Go, deployed as reusable Dataflow templates including Google-provided templates for common scenarios, or developed iteratively in JupyterLab notebooks. Apache Beam pipelines are also portable and can run on other runners such as Apache Flink or Apache Spark without rewriting code.
Dataflow streaming pipelines provide exactly-once processing of every record by default. For streaming pipelines that can tolerate duplicates, at-least-once mode can reduce cost and improve latency. When locality matters, the locations of sources, sinks, and temporary files must stay in the job region or data may cross regions, and a job can be restricted with regional endpoint, worker region, and worker zone parameters that control where workers deploy and where job metadata is stored. CMEK in Dataflow can encrypt pipeline state artifacts and access Cloud KMS-protected sources and sinks, which can allow use of regions otherwise blocked by locality rules.
Cloud Functions is a serverless compute option for workflow automation triggered by Pub/Sub events. Pub/Sub offers push-based delivery of messages as HTTP POST requests to webhooks, and Cloud Functions or other serverless products run automation logic in response to each delivery. In this pattern, data flows from the publisher to the Pub/Sub topic, then from the topic subscription to the function, then from the function to its destination such as a database, notification, or next workflow step.
Cloud Functions fits small, event-driven reactions where provisioning a full streaming pipeline would be excess overhead. A message placed in a topic by an application, by the Pub/Sub API, or manually with the Google Cloud CLI can invoke the function without the publisher knowing which function will react. Choose this path when the work is a discrete response to a single event rather than continuous transformation, windowing, or enrichment across many events over time.
The combined real-time pipeline is the ordered sequence that moves data from producers to consumers to storage and analytics while preserving decoupling at each boundary. The sequence keeps latency low enough for immediate insight because Pub/Sub ingestion, Dataflow or Functions processing, and warehouse delivery are connected but independently scalable. Monitoring, alerting, and logging products observe pipeline health, while Identity and Access Management (IAM) with standard OAuth controls access to individual Pub/Sub resources.
The combined pipeline moves data through an ordered sequence:
| Option | Primary unit | Choose when |
|---|---|---|
| Cloud Dataflow | Apache Beam pipeline | The work needs continuous streaming transforms, windowing, enrichment, or delivery to analytics stores at scale |
| Cloud Functions | Event-triggered function | The work is a short, single-event reaction or workflow automation step |
| Cloud Pub/Sub alone with storage sink | Topic and subscription | The work is durable event distribution or buffering without complex transformation |
Exam tip: Cloud Dataflow provides exactly-once processing by default, with an at-least-once mode for lower cost and latency when duplicates are acceptable; Cloud Pub/Sub subscriptions buffer unacknowledged messages for up to 7 days to allow delayed processing.
Extract, transform, and load (ETL) transforms data before it is loaded into BigQuery in a processing service that reads from Cloud Storage or JDBC, while extract, load, and transform (ELT) loads raw data into a BigQuery staging table first and then transforms it with SQL that writes to the production table. ETL uses Cloud Dataflow with Beam, Cloud Data Fusion with graphical pipelines, Managed Service for Apache Spark with Spark SQL or code, or Dataprep by Trifacta with visual recipes executed by Dataflow, while ELT uses BigQuery Data Transfer Service for scheduled loads, BigQuery SQL for transformation, and Dataform for versioned and scheduled SQL pipelines.
Use Cloud Dataproc when the workload already runs on Hadoop or Spark and rewriting would be impractical, because it runs existing code on ephemeral Managed Service for Apache Spark clusters that are created for a job and deleted when the job ends. Choose Cloud Dataflow when one Apache Beam pipeline must handle both batch and streaming with automatic scaling, because its serverless workers are allocated, autoscaled, rebalanced, and deleted automatically.
Event sources publish messages to a predefined Cloud Pub/Sub topic, the Pub/Sub service holds messages and leases each message to subscriber clients, and data flows from the subscription to Cloud Dataflow for streaming enrichment, windowing, and joins or to Cloud Functions for single-event automation before delivery to destinations such as BigQuery, Bigtable, or Cloud Storage. Cloud Pub/Sub decouples publishers from subscribers, buffers unacknowledged messages for up to 7 days, and delivers events with latencies typically on the order of 100 milliseconds, while Cloud Dataflow provides exactly-once processing by default with an at-least-once mode when duplicates are acceptable.
A retail company is designing an event-driven architecture to process user interactions across its e-commerce website in real time. The solution must satisfy the following technical requirements:
Which architecture should the company implement?
Ingest user events into Compute Engine instances managed by an instance group, buffer data on local Persistent Disks, and run cron jobs to insert records into BigQuery
Ingest user events with Cloud Pub/Sub and invoke Cloud Functions (2nd gen) to execute windowed aggregations and persist records into BigQuery
Ingest user events with Cloud Pub/Sub and process the stream using a Dataflow pipeline that outputs to BigQuery
Upload events into Cloud Storage buckets and orchestrate hourly Dataproc Spark batch jobs using Cloud Composer to load BigQuery
Last updated