Simple Data Pipelines with Dataflow and BigQuery

Selecting a data transformation tool means matching business needs for volume, speed, skills, and budget to the Google Cloud service that fits best. The five main options are Dataproc, Dataflow (opens in a new tab), Cloud Data Fusion, Cloud Composer (opens in a new tab), and Dataform. Each tool has a different architecture for where compute runs and what the service controls.

1.1Tool Architectures and Processing Models

Tool architectures describe where transformation compute runs and whether the service provisions clusters, runs parallel graphs, or orchestrates other systems. Dataproc, also called Managed Service for Apache Spark, provisions managed clusters for Apache Hadoop and Spark workloads instead of requiring teams to build clusters themselves. Those Dataproc clusters connect to separate analytic data stores, so storage and compute stay separate while processing runs on the cluster. Dataflow provides graph-based execution where work is expressed as a graph of steps that run in parallel across workers, which fits compute-intensive simulations such as Monte Carlo simulations. Cloud Composer, also called Managed Service for Apache Airflow, does not transform data itself but schedules, orders, and coordinates tasks that run in other systems, with data flowing from one orchestrated task to the next.

1.2Business Needs That Shape the Choice

Business needs describe data volume and scale, processing speed and latency, ease of integration, and budget limits including time to market. Processing speed matters most for heavy calculations and trading: HSBC used Dataflow to run risk calculations 16 times faster than its previous approach. Conversations with customers show the 80/20 Pareto principle applies to trading, where more than 80% of trading applications do not need extremely low latency and gain resilience, security, and elasticity from the cloud, while less than 20% need latency of less than a millisecond with determinism and fairness and conventionally run in rigid and expensive colocated facilities. Data volume and elasticity point to BigQuery as a fully managed, petabyte-scale warehouse for near-real-time analytics over vast data, and to Pub/Sub as asynchronous messaging that ingests and distributes server events, user interactions, and Internet of Things (IoT) streaming data.

Integration ease describes how simply sources, microservices, and analytic stores connect into one pipeline. Data can flow from Pub/Sub through Dataflow into BigQuery for streaming analysis, while Pub/Sub also connects decoupled microservices that activate only when events occur. Eventarc manages the flow of state changes in event-driven pipelines for uses such as automated error remediation, resource labeling, and image retouching. Budget analysis favors managed services and elasticity over custom infrastructure, because proprietary pub-sub systems and message brokers have become commodities and cloud bursting can improve elasticity, security posture, and monitoring without paying for idle colocated capacity.

1.3Matching Each Tool to Its Best-Fit Workload

Matching each tool to its workload means checking scalability, cost-efficiency, ease of integration, and performance against the specific batch, streaming, or SQL job:

• Scalability: the ability to handle growing data and peak loads without redesign or manual cluster management.
• Cost-efficiency: paying only for what is used while avoiding idle clusters and heavy operations work.
• Ease of integration: connectors, SQL support, and managed connections to sources and BigQuery.
• Performance metrics: measured speed, freshness, and reliability for the required batch or streaming workload.

The selection depends on interface and processing model:

Evaluating ETL and ELT means deciding where transformation happens relative to BigQuery storage. In extract, transform, load (ETL), data is cleaned and reshaped before it is stored in BigQuery using outside compute such as Dataflow. In extract, load, transform (ELT), raw data is loaded into BigQuery first and transformed afterward inside BigQuery with SQL.

2.1How ETL and ELT Order Changes Data Flow

Data flow order defines the BigQuery storage boundary that separates ETL from ELT. In ETL, data flows from the source system to a transformation engine and only then to BigQuery, so BigQuery mainly holds finished, ready-to-use tables. In ELT, data flows from the source system to Cloud Storage (opens in a new tab) and BigQuery first, and transformation happens later inside BigQuery, so BigQuery holds both raw and transformed tables. Cloud Storage is the shared staging dependency in both patterns, ideally in the same location as the BigQuery dataset to minimize latency. The choice therefore depends on whether heavy work should stay outside the warehouse or raw data and SQL work should stay inside it.

2.2When to Use Dataflow ETL Before BigQuery Storage

Dataflow ETL transforms and enriches data before the data is stored in BigQuery. Dataflow jobs are defined with the Apache Beam model as graphs of transforms, and the service is fully managed with virtually limitless processing capacity. This capacity matters when on-premises compute would limit throughput or when enrichment would need extra transfer time and bandwidth. In a Dataflow ETL flow, data moves through an ordered sequence:

1. Extract to files: Export source data to vendor-neutral files, preferably AVRO, ORC, or Parquet where schema is embedded with the data.
2. Upload to staging: Copy the files to a Cloud Storage bucket, ideally in the same location as the BigQuery dataset.
3. Transform in Dataflow: Dataflow reads from staging or directly through connectors such as JDBC or SnowflakeIO, then applies trimming, cleansing, and enrichment transforms in order.
4. Load to BigQuery: Dataflow writes the transformed result directly to BigQuery through the BigQuery I/O connector.

Choose Dataflow ETL when transformation is complex, when an existing transformation process must be preserved, when sensitive fields must be removed before loading, or when the goal is to reduce resource usage inside BigQuery. Managed Service for Apache Spark supports legacy Hadoop, Spark, or Flink ETL with ephemeral clusters that scale per job, charge only while jobs run, and require no long-term cluster maintenance.

2.3When to Use BigQuery ELT After Loading

BigQuery ELT loads raw data first and uses BigQuery compute and columnar storage for transformation afterward. Raw files move from the source to Cloud Storage to BigQuery, and SQL then processes that raw data into cleaned tables and metrics. Common load paths from staging include BigQuery Data Transfer Service for scheduled loads from Cloud Storage and sources such as Amazon Redshift, Teradata, and Google Ads, the LOAD command with the bq tool and client libraries for batch loads, and the BigQuery Storage Write API for high-volume streaming and batch ingestion. After raw data becomes loaded, it becomes transformed through scheduled SQL such as Dataform workflows that aggregate clicks, impressions, keywords, or campaign performance into new tables.

A typical BigQuery ELT workflow follows this order:

1. Extract: Pull data from a source such as Google Ads, another warehouse, or files exported as AVRO, ORC, Parquet, or CSV.
2. Load: Stage extracted files in a Cloud Storage bucket and load them into BigQuery tables, often on a recurring schedule.
3. Transform: Transform raw BigQuery tables into metrics and reporting tables with SQL-based tools such as Dataform.

Choose BigQuery ELT when raw data can land fast, when SQL can handle cleaning and aggregation, and when flexible reprocessing is needed. This pattern fits high-volume, unstructured or semi-structured data because BigQuery supports large-scale ingestion and nested and repeated fields, and it preserves raw history so the same tables can be transformed again later.

Choosing products for basic transformation pipelines means picking the service whose working style fits the data type, complexity, and performance need. Dataflow (opens in a new tab) provides programmatic batch and streaming ETL at large scale, Cloud Data Fusion (opens in a new tab) provides visual pipeline design and execution, and BigQuery (opens in a new tab) provides destination tables where transformed data lands for analysis.

3.1Dataflow for Batch and Streaming Transformation

Dataflow transformation moves data through an ordered pipeline where each stage feeds the next 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.

Managed scale means Google allocates a pool of worker VMs when a job starts and automatically deletes those VMs when the job completes or is cancelled, with billing based on compute used. Dataflow can autoscale by adding worker VMs when demand rises and shutting them down when fewer are needed, and it can dynamically rebalance work among VMs. Dataflow is built on Apache Beam, an open-source model with software development kits (SDKs) for Java, Python, and Go, so the same pipeline code can later run on runners such as Apache Flink or Apache Spark. Teams can write Beam code for full control, deploy a Dataflow template for reuse, use Google-provided templates without learning Beam, or iterate with JupyterLab notebooks.

3.2Cloud Data Fusion for Visual Integration

Cloud Data Fusion integration designs pipelines once in a design environment and executes them in one or more execution environments. A Cloud Data Fusion instance is a unique deployment where design and execution happen, with 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 controlled by the Cloud Data Fusion Service Agent, while the execution environment resides in the customer project where the pipeline runs on an ephemeral Managed Service for Apache Spark cluster controlled by the Compute Engine default service account or a custom service account.

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. A basic Cloud Data Fusion pipeline moves in order from extract from on-premises or cloud sources, through transform, blend, and aggregate steps, to load into BigQuery or Bigtable. Choose Cloud Data Fusion when the workflow needs low-code visual integration across diverse sources with reusable source-to-sink mapping.

3.3BigQuery as the Analytics Destination

BigQuery as an analytics destination is the warehouse where transformed pipeline output lands as tables ready for querying and reporting. In a common pattern, source data is first copied to a Cloud Storage staging bucket, then Dataflow reads from that bucket, transforms the data, and writes directly to BigQuery using the Beam BigQuery I/O connector. Data also flows from Pub/Sub JSON messages through a Dataflow template to a BigQuery table, from Managed Service for Apache Kafka through Dataflow to BigQuery, and from Pub/Sub ecommerce events through Dataflow to both BigQuery and Bigtable. In this pattern the pipeline handles extraction, movement, and initial shaping, while BigQuery holds the resulting datasets alongside destinations such as Bigtable, Cloud Storage, and Lakehouse tables.

Dataflow also adds transformation control to database replication that involves 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 path for simple replication and the Datastream-to-Dataflow-to-BigQuery path when streamed changes must be reshaped before analysis.

Key Facts

• Dataproc provisions managed Hadoop and Spark clusters that connect to separate analytic data stores, while Dataflow runs serverless graph-based batch and streaming execution that scales automatically.
• Cloud Composer schedules and coordinates tasks that run in other systems and does not run transformation compute itself.
• ETL transforms data with Dataflow, Cloud Data Fusion, or Spark before the result is written to BigQuery, while ELT loads raw data into BigQuery first and transforms it inside BigQuery with SQL.
• Dataflow uses exactly-once processing by default and uses at-least-once mode only when a streaming pipeline can tolerate duplicates for lower cost and improved latency.
• Cloud Data Fusion designs pipelines in a Google-managed tenant project and executes them on an ephemeral Managed Service for Apache Spark cluster in the customer project.