Prepare and test your skills
Prepare and test your skills
Evaluating GCP services for data transformation means comparing how Dataflow, Dataprep, and BigQuery move and reshape data for analysis. Dataflow (opens in a new tab) is the central managed service for unified stream and batch processing at scale. BigQuery acts as the cloud data warehouse where transformed results land for querying and dashboards. Together they support scalable, integrated, and cost-effective pipelines that handle both scheduled bulk loads and fluctuating real-time volumes.
Dataflow moves data through an ordered pipeline where each stage feeds the next stage until results reach the destination. Data flows from sources such as Cloud Storage, streaming queues, or databases into the pipeline. Dataflow then applies cleaning, enrichment, joining, and aggregation logic in parallel across worker virtual machines (VMs). Finally, the processed data flows from Dataflow to the destination system, often BigQuery for analytics and dashboards.
Dataflow is commonly chosen for basic transformation work in these situations:
Managed scale in Dataflow means Google allocates and removes the compute resources needed to run each job. When a Dataflow job starts, the Dataflow service allocates a pool of worker VMs to execute the pipeline, and when the job completes or is cancelled, Dataflow automatically deletes those VMs. Billing is based on the compute resources the job uses, so teams do not provision or manage servers directly. Teams monitor jobs through the Dataflow monitoring interface in the Google Cloud console, which shows a graphical pipeline with progress and execution details for each stage.
Dataflow processes data in parallel across multiple VMs and can autoscale by adding worker VMs when demand rises and shutting down VMs when fewer are needed. Dataflow can also dynamically rebalance work among VMs so parallel work completes more efficiently. This parallel execution supports large batch and streaming workloads and helps spot bottlenecks, high latency, and CPU or memory issues. The choice of streaming correctness affects both cost and latency:
| Streaming mode | Record guarantee | Choose when |
|---|---|---|
| Exactly-once, the default | Every record is processed exactly once | Correctness matters and duplicates cannot be tolerated |
| At-least-once | Records may be duplicated | The pipeline can tolerate duplicates and needs lower cost and improved latency |
Exam tip: Dataflow uses exactly-once processing by default; enable at-least-once mode only for streaming pipelines that can tolerate duplicates to reduce cost and improve latency.
Apache Beam is the open-source programming model that Dataflow executes, and it makes Dataflow pipelines portable across runners. Developers write pipelines with Beam software development kits (SDKs) for Java, Python, and Go, including multi-language pipelines. The same pipeline code can later run on a different runner such as Apache Flink or Apache Spark without rewriting the code. This portability protects basic pipelines as needs grow from simple data movement to advanced streaming analytics and from batch to streaming.
Teams can create and run Dataflow pipelines in different ways depending on coding skill and reuse needs. Developers can write code directly with the Beam SDKs for full control, deploy a Dataflow template that packages a predefined pipeline for on-demand reuse, use Google-provided templates without learning Beam programming, or develop iteratively with JupyterLab notebooks. Choose custom Beam code when transformation logic is complex or unique, choose templates when the same pattern must be reused by analysts or data scientists, and choose notebooks when logic needs rapid iteration before production deployment.
How Dataflow and BigQuery work together is a source-to-processing-to-warehouse flow where BigQuery stores the finished tables. In a common pattern, source data is first copied to a Cloud Storage staging bucket with the gcloud CLI, then Dataflow reads from that staging bucket, transforms the data, and writes directly to BigQuery using the Beam built-in Google BigQuery I/O connector. After the initial load, teams can expand the transferred tables, run scheduled transfers for recurrent updates, and reroute upstream feeds to BigQuery to reduce dependence on an older warehouse.
Streaming events via Pub/Sub and batch files via Cloud Storage flow into a Dataflow pipeline that cleans and aggregates data, then writes results to BigQuery for BI queries in Looker.
Dataflow also adds transformation control to database replication flows that involve Datastream, Cloud Storage, Pub/Sub, and BigQuery. Datastream can stream change data from Oracle, MySQL, and PostgreSQL databases directly into BigQuery datasets, which is simplest when no custom logic is needed. When more control is required, such as transforming records or manually setting logical primary keys, data flows from the source database through Datastream to a Cloud Storage bucket, the bucket sends Pub/Sub notifications about new files, Dataflow processes those files, and Dataflow transfers the changes into BigQuery datasets. Choose the direct Datastream-to-BigQuery path for simple replication, and choose the Datastream-to-Dataflow-to-BigQuery path when streamed changes must be reshaped before analysis.
Matching service capabilities to transformation workflows means choosing the Google Cloud service whose working style fits the transformation job. Cloud Data Fusion provides visual pipeline design and execution, Dataflow (opens in a new tab) provides programmatic batch and streaming ETL at large scale, and BigQuery (opens in a new tab) provides destination tables where transformed data lands for analysis. The choice depends on whether the team needs visual integration, Beam code, or SQL-based ELT tables.
Cloud Data Fusion is a visual integration service where pipelines are designed once in a design environment and then executed in one or more execution environments. A Cloud Data Fusion instance is a unique deployment where design and execution happen, and each instance runs its own services for pipeline lifecycle management, orchestration, coordination, and metadata management. Design work uses the Cloud Data Fusion UI, including Studio pages for building pipelines and Wrangler and Preview services for exploring and checking data.
Cloud Data Fusion separates responsibilities across two projects with different trust boundaries. The design environment resides in a Google-managed tenant project, where access is controlled by the Cloud Data Fusion Service Agent during design. The execution environment resides in the customer project, where the pipeline runs on an ephemeral Managed Service for Apache Spark cluster and data access is controlled by the Compute Engine default service account or a custom service account. This separation lets the same designed pipeline extract, transform, blend, aggregate, and load data from on-premises and cloud sources, then run at scale on Spark without redesigning the logic.
Cloud Data Fusion connects many sources to Google Cloud sinks through configured source and sink plugins. The Multi Table source reads from multiple tables in a Microsoft SQL Server database, outputs data with multiple schemas, and includes a table name field that identifies the source table. Data flows from the Multi Table source to a multi-table sink, such as BigQuery Multi Table or GCS Multi File, which preserves the separate schemas in the destination. Choose Cloud Data Fusion when the workflow needs visual integration across diverse sources with reusable source-to-sink mapping.
Dataflow for programmatic ETL is a managed pipeline service for large-scale batch and streaming transformation built on Apache Beam. A pipeline reads from a source system, applies transforms in Dataflow, and writes results to a destination system, with compute provisioned and scaled by the service. Teams start work with Dataflow templates, the Dataflow job builder, and Beam notebooks rather than managing the underlying processing cluster.
Dataflow fits continuous movement and transformation into analytics storage across many source types:
Choose Dataflow when the workflow needs programmatic ETL with Beam code, templates, or ML transforms, especially for streaming sources such as Pub/Sub and Kafka and for combined batch plus streaming logic.
BigQuery as an analytics destination is the warehouse where transformed pipeline output lands as tables ready for querying and reporting. Data flows from Dataflow transforms or Cloud Data Fusion pipelines into BigQuery tables, including multi-table loads that preserve separate source schemas. In this ELT-style pattern, the pipeline handles extraction, movement, and initial shaping, while BigQuery holds the resulting datasets for downstream analysis alongside destinations such as Bigtable, Cloud Storage, and Lakehouse tables. Choose BigQuery as the endpoint when the goal is centralized, large-scale tables that other tools and users can analyze after the pipeline finishes.
Choosing the correct service for a scenario means dividing work by how logic is built and where it runs. Cloud Data Fusion centers on visual design with managed Spark execution, Dataflow centers on Beam-based batch and streaming code, and BigQuery centers on storing transformed results. The pipeline depends on the chosen engine for connectors and compute, while BigQuery depends on the pipeline to deliver clean tables.
| Service | Primary unit | Choose when |
|---|---|---|
| Cloud Data Fusion | Visually designed pipeline in Studio and Wrangler | The work needs visual integration from many on-premises and cloud sources with Preview before running on Spark |
| Dataflow | Beam pipeline run from a template, job builder, or notebook | The work needs programmatic ETL for streaming or batch at large scale, including Pub/Sub, Kafka, Lakehouse ingestion, or ML inference |
| BigQuery | Destination table | The work needs transformed data landed in analytics tables for querying after Data Fusion or Dataflow completes movement |
Exam tip: Choose Cloud Data Fusion for visual design with Wrangler and Preview running on Spark, Dataflow for Beam-based streaming and batch ETL from Pub/Sub or Kafka to BigQuery, and BigQuery as the destination table, not the pipeline engine.
Assessing transformation requirements means checking what data must be processed, how complex the changes are, and how fast the pipeline must run before choosing a Google Cloud product. Data types decide which connectors and formats are needed, complexity decides whether a visual tool or code-based framework fits best, and performance decides whether a simple batch job or a scalable streaming engine is required. This three-part check keeps basic transformation pipelines aligned with operational needs and development preferences.
Data types describe the format and origin of the data that flows into the transformation pipeline. Basic pipelines on Google Cloud commonly handle file data, table data, streaming messages, and database changes, and each type points toward different product strengths. The source system and the destination system must both be named, because the pipeline depends on reading from the source and writing to the destination without losing fidelity.
Transformation complexity describes how much the data must change between source and destination. Simple transformations include filtering, parsing JSON, converting types, and loading Parquet or Iceberg data into lakehouse tables. Complex transformations include joining multiple sources, blending and aggregating ecommerce events, handling deletes and deduplication, and running machine learning inference. Cloud Data Fusion (opens in a new tab) fits low-code, repeatable blending work because pipelines are designed once with visual tools such as Wrangler and Preview and then executed in multiple environments. Dataflow (opens in a new tab), which is built on Apache Beam, fits code-based batch and streaming logic at large scale, including highly parallel workloads and RunInference transforms with GPUs.
A basic transformation pipeline in Cloud Data Fusion moves through an ordered sequence where order matters:
Choose a lift-and-shift ETL/ELT approach when an existing integration tool already supports BigQuery and the team wants to keep current jobs and only change storage stages to BigQuery. Choose rearchitecture with Cloud Data Fusion or Dataflow when the pipeline needs new connectors, streaming behavior, or scalable Beam processing.
Performance requirements describe data volume, freshness, and impact on source systems. Streaming pipelines that continuously move Pub/Sub or Kafka data into BigQuery need an engine that scales automatically, while small scheduled batches can run on ephemeral compute. Managed Service for Apache Spark clusters show this tradeoff in Cloud Data Fusion, where the design environment lives in a Google-managed tenant project and the execution environment runs on an ephemeral Spark cluster in the customer-owned project.
Source-side limits also shape the choice because extraction methods place different loads on the source database. Oracle LogMiner polling can strain a very active source database and should be used carefully when redo volume is more than 10 GB per hour, depending on CPU, memory, and I/O capacity. Database triggers that log full rows can double single-row data manipulation latency, while storing only the primary key reduces overhead but then requires a JOIN with the original table and misses intermediate changes. SQL-based polling needs an index on the monotonically increasing key or timestamp column, and timestamp columns should use up to six fractional digits because microseconds is the maximum precision supported in BigQuery.
Exam tip: Cloud Data Fusion design runs in the tenant project under the Cloud Data Fusion Service Agent role, while pipeline execution runs in the customer project under the Compute Engine service account.
Matching requirements to products means comparing pipeline style, development approach, and scale using data type, complexity, and performance facts. The pipeline depends on the chosen product for connectors, execution compute, and access controls, so the trust boundary between tenant project and customer project applies only to Cloud Data Fusion. This comparison prevents choosing a simple batch loader for continuous streaming or a code-heavy engine for simple visual blending.
| Product | Pipeline style | Choose when |
|---|---|---|
| Cloud Data Fusion | Visual extract, transform, blend, aggregate, and load executed on Managed Service for Apache Spark | Data comes from many on-premises and cloud sources and the team prefers low-code design with Wrangler and Preview |
| Dataflow | Apache Beam batch and streaming at large scale, including templates and ML inference | Data is high-volume streaming from Pub/Sub or Kafka, large file and lakehouse ingestion, or parallel and ML transforms are needed |
| BigQuery with existing ETL/ELT tool | Lift-and-shift jobs with BigQuery as the storage stage | The current platform already supports BigQuery and time or cost constraints favor keeping existing jobs |
When data types are files or lakehouse tables with simple loads, Dataflow job builder ingestion or a lift-and-shift load is often enough. When transformations require blending many sources with repeatable visual logic, Cloud Data Fusion provides the design-once, run-in-many-environments model. When volume, velocity, or custom logic grows, Dataflow provides the scalable Beam execution for both batch and streaming.
Choose Dataflow when the work needs programmatic Extract-transform-load (ETL) for streaming or batch at large scale with Apache Beam code, templates, or machine learning (ML) inference, especially for streaming sources such as Pub/Sub and Kafka. Choose Cloud Data Fusion when the work needs visual integration from many on-premises and cloud sources with Preview before running on Spark. BigQuery serves as the destination table where transformed data lands for querying after Dataflow or Cloud Data Fusion completes movement.
In Dataflow, exactly-once processing guarantees every record is processed exactly once by default, while at-least-once processing may duplicate records but offers lower cost and improved latency. Choose exactly-once when correctness matters and duplicates cannot be tolerated, and choose at-least-once only when the streaming pipeline can tolerate duplicates.
Dataflow reads data from sources such as Cloud Storage, streaming queues, or databases, applies cleaning, enrichment, joining, and aggregation logic in parallel across worker virtual machines (VMs), and writes results directly to BigQuery using the Beam built-in Google BigQuery I/O connector. BigQuery then stores the finished tables as the cloud data warehouse where transformed results land for querying and dashboards.
A retail analytics team needs to build a new data pipeline to ingest, clean, and transform transactional records from multiple database sources before loading them into their analytics storage.
The team has established the following requirements:
Which Google Cloud service should the team select?
Last updated