Prepare and test your skills
Prepare and test your skills
Designing and implementing simple data pipelines means choosing where transformation runs, in what order data moves, and which Google Cloud service does the work. Raw data flows from sources such as files, databases, or streaming events through transformation compute into BigQuery tables ready for analysis. The choice depends on data volume, speed needs, team skills, and budget.
Selecting a data transformation tool means matching business needs for volume, speed, skills, and budget to the service that fits best. Dataflow (opens in a new tab) provides serverless batch and streaming processing where work is expressed as a graph of steps that run in parallel across workers. Dataproc, also called Managed Service for Apache Spark, provisions managed clusters for Apache Hadoop and Spark workloads and keeps storage and compute separate. Cloud Data Fusion (opens in a new tab) provides visual data integration with prebuilt connectors, Cloud Composer schedules and coordinates tasks that run in other systems, and Dataform defines SQL transformations that run directly in BigQuery.
Business needs that shape the choice are data volume and scale, processing speed, ease of integration, and cost. High-volume streaming from Pub/Sub through Dataflow into BigQuery supports near-real-time analytics over clicks, server events, and Internet of Things (IoT) data. Heavy parallel calculations, such as risk simulations, benefit from Dataflow graph execution that scales automatically. Budget favors managed and elastic services over custom clusters because teams pay only for what they use and avoid idle capacity.
The best-fit workload for each tool depends on interface and processing model:
| Tool | Primary use | Choose when |
|---|---|---|
| Dataflow | Serverless batch and streaming | Data flows continuously or in large parallel batches and must scale automatically |
| Dataproc | Managed Spark and Hadoop | The team has existing Spark code and needs a managed Spark platform |
| Cloud Data Fusion | Visual integration | Builders prefer a low-code interface with connectors for extract, transform, load (ETL) |
| Cloud Composer | Workflow orchestration | The work coordinates scheduled tasks and dependencies across systems |
| Dataform | SQL in BigQuery | Transformations are defined in SQL and run inside the warehouse |
Exam tip: Dataproc provisions Hadoop and Spark clusters, Dataflow runs graph-based execution, and Cloud Composer orchestrates workflows across other services.
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.
Two parallel flows showing ETL transforms in Dataflow before BigQuery, while ELT loads raw data into BigQuery first then transforms with Dataform SQL inside.
Data flow order defines the BigQuery storage boundary that separates the two patterns. In ETL, data flows from the source system to a transformation engine and only then to BigQuery, so BigQuery mainly holds finished tables. In ELT, data flows from the source system to Cloud Storage and BigQuery first, 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.
| Strategy | Transformation location | Data flow |
|---|---|---|
| ETL with Dataflow | Outside BigQuery in Dataflow | Source to Cloud Storage to Dataflow to BigQuery |
| ELT with BigQuery | Inside BigQuery after loading | Source to Cloud Storage to BigQuery to transformed tables |
Choose Dataflow ETL when transformation is complex, when sensitive fields must be removed before loading, or when an existing process must be preserved. A typical Dataflow ETL flow moves in order:
Choose BigQuery ELT when raw data can land fast, when SQL can handle cleaning, and when raw history must be kept for reprocessing. Raw files move from the source to Cloud Storage to BigQuery, then SQL tools such as Dataform aggregate clicks, impressions, or campaign data into reporting tables. This pattern fits high-volume and semi-structured data because BigQuery supports large-scale ingestion and nested and repeated fields.
Choosing products for basic transformation pipelines means picking the service whose working style fits the data type and performance need. Dataflow Data Pipelines (opens in a new tab) move 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 applies cleaning, joining, and aggregation in parallel across worker virtual machines (VMs), and processed data flows from Dataflow to the destination, often BigQuery.
Managed scale means Google allocates a pool of worker VMs when a job starts and deletes those VMs when the job completes, with billing based on compute used. Dataflow can autoscale by adding workers when demand rises and can rebalance work among workers. Dataflow is built on Apache Beam, with software development kits (SDKs) for Java, Python, and Go, so teams can write Beam code, reuse a Dataflow template, use Google-provided templates, or iterate with notebooks. Streaming pipelines also choose a record guarantee based on correctness and cost.
| 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 for lower cost and improved latency |
Cloud Data Fusion integration designs pipelines once in a design environment and executes them in an execution environment. The design environment resides in a Google-managed tenant project, while the execution environment resides in the customer project where the pipeline runs on an ephemeral Managed Service for Apache Spark cluster. Data flows from source plugins, such as a Multi Table source for Microsoft SQL Server, through transform and blend steps to sink plugins such as BigQuery Multi Table, which preserves separate schemas. Choose Cloud Data Fusion when the workflow needs low-code visual integration across diverse sources.
Write from Dataflow to BigQuery (opens in a new tab) is the common pattern where BigQuery serves as the analytics destination for transformed output. Source data is copied to a Cloud Storage staging bucket, Dataflow reads from that bucket, transforms the data, and writes directly to BigQuery. Data also flows from Pub/Sub messages through a Dataflow template to BigQuery, and from Managed Service for Apache Kafka through Dataflow to BigQuery. When database changes need reshaping, data flows from the source database through Datastream to Cloud Storage, Cloud Storage sends Pub/Sub notifications about new files, Dataflow processes those files, and Dataflow transfers the changes into BigQuery datasets.
Continue with this study guide: 3.1 Design and implement simple data pipelines
Scheduling, automating, and monitoring basic data processing means running work on time, connecting steps in order, and watching health from logs and metrics. Time-based schedules handle single recurring queries, orchestration handles multi-step dependencies, and event triggers start work only when something happens. Monitoring then traces failures from symptom back to the slow stage or failed task.
BigQuery scheduled queries automate repeated SQL without manual runs or custom pipeline code. A BigQuery (opens in a new tab) scheduled query is managed by the BigQuery Data Transfer Service as a transfer configuration that stores the query text, repeat schedule, destination table, data location, and identity. A scheduled query can run a GoogleSQL SELECT, DDL, or DML statement, and a SELECT query can write results to a destination table with Append to table or Overwrite table.
Periodic execution turns a tested query into a recurring transfer that writes to the same destination each time. Setup follows a fixed order: first test the SQL and confirm correct results, then create the schedule with a display name and repeat interval, then set the destination table and write preference, then set location and service account identity, then enable monitoring. Schedules use Cron-like values with a minimum interval of 5 minutes between runs, such as every 6 hours or every mon 23:30. An On-demand option saves the query with no schedule for manual runs.
Failure handling combines automatic retries with alerts so repeated failures are not silent. Temporary errors such as rateLimitExceeded or unavailable are retried with exponential backoff and jitter, where waits grow progressively longer. Owners can enable email notifications for failures and set a Pub/Sub topic to receive run status. Optimization for cost and performance means scanning less data by using partitioned tables, persisting heavy results in tables or materialized views, using dry runs and cached results, and inspecting the query plan to add filters earlier.
Monitoring Dataflow in the Google Cloud console means tracking a job from pipeline definition to running execution to finished result. A pipeline defines how data flows from sources through transforms to sinks, and a job is the running execution of that Apache Beam pipeline on managed workers. The Dataflow job interface shows job status, stage graphs, and performance charts to verify success and locate bottlenecks.
Job status is the operational state reported for each job in the jobs list, and it moves through an ordered lifecycle. Preparation states come first, active processing comes next, and terminal states come last. Starting and Queued come before Running, while Canceling, Draining, and Updating are transitions that end in Canceled, Drained, or Updated. Succeeded means batch work finished normally, while Failed means the job did not complete and needs investigation. Draining lets in-flight data finish while new input stops.
The Job details page combines a visual pipeline graph with logs and diagnostics for troubleshooting. Job logs show service-level messages for the whole job, Worker logs show step-level messages from Compute Engine instances for the selected graph step, and Diagnostics shows where errors clustered on a timeline. A typical streaming flow moves in order from Pub/Sub ingestion to Dataflow processing to BigQuery storage to Looker analysis, so a delay in Dataflow propagates downstream as late warehouse data and stale dashboards. The bottleneck detector marks a streaming step when delay exceeds five minutes, though catching up from backlog at startup is normal and does not require intervention.
Cloud Logging (opens in a new tab) is a fully managed service that stores, searches, and analyzes log data and events. Log data flows from Google Cloud services into Cloud Logging, while data from additional applications and hybrid systems flows in through connectors such as Bindplane. Cloud Monitoring turns performance information and log-based metrics into charts, dashboards, and alerts. Together they let a data team trace a pipeline failure from symptom back to cause.
Log categories describe who wrote each entry and what question it answers. Platform logs are written by Google Cloud services for debugging, component logs come from Google-provided software on customer systems, security logs answer who did what and when, and user-written entries come from custom applications. For containers, node logs record Pod lifecycle and resource problems, app logs from stdout and stderr record crashes inside containers, and audit logs record control-plane API calls such as who deleted a Deployment.
Isolation in Logs Explorer follows a fixed order:
The Log Router forwards copies of entries from their source to destinations including a log bucket, a BigQuery dataset for analytics, a Cloud Storage bucket for archival, and a Pub/Sub topic for third-party tools. Log-based metrics turn matching entries into time-series data in Cloud Monitoring for counting errors, observing latency trends, and charting extracted values. An alerting policy watches a metric and notifies channels such as email, PagerDuty, and Slack when error count or latency crosses a threshold.
Selecting a data orchestration solution means matching how work is defined, triggered, and connected to needs for timing, control, and management effort. Cloud Composer, also called Managed Service for Apache Airflow, is a fully managed workflow orchestration service for ETL and ELT workloads across systems. Workflows is a fully managed, serverless service for sequencing loosely coupled services in a defined order. Dataproc Workflow Templates provide managed templates for Spark-based ETL execution.
In Cloud Composer, data flows from one operator task to the next inside a Directed Acyclic Graph (DAG) in the order the DAG defines. A DAG is a workflow definition that ensures each task runs at the right time, and each task is a single unit of work such as preparing data, monitoring an API, or running a BigQuery job. A downstream BigQuery job runs only after the upstream tasks it depends on have finished. Each managed Airflow environment connects to other services through built-in connectors and includes a Google Kubernetes Engine (GKE) cluster for schedulers and workers, an Airflow web server for the UI, an Airflow database for metadata, and a Cloud Storage bucket for DAG files and logs.
Workflows coordination separates coordination logic from business logic so dependencies stay explicit. A workflow is defined as steps in YAML or JSON that call HTTP-based APIs and connectors in order, often event-driven. For example, data flows from Workflows to language and vision APIs for recognition after a Cloud Storage upload triggers the workflow, then tags flow to Firestore for storage. Timing and location also matter: long-running work should run in the region with the highest carbon-free energy percentage, while flexible batch work can shift to off-peak hours.
| Service | Definition model | Execution and integration model |
|---|---|---|
| Cloud Composer | DAGs with Airflow code and operators | Managed Airflow environment across cloud and on-premises |
| Workflows | Steps in YAML or JSON | Serverless execution calling HTTP APIs in order, often event-driven |
| Dataproc Workflow Templates | Managed template for ETL | Runs with Managed Service for Apache Spark for Spark execution |
Event-driven ingestion from Pub/Sub to BigQuery means producers emit events as they happen and BigQuery stores them for near-immediate analysis. Pub/Sub is an asynchronous and scalable messaging service that decouples publishers from subscribers, and BigQuery is the analytics warehouse where events land. Real-time ingestion keeps data continuously fresh, while batch processing collects data on a schedule for complete and validated results.
Pub/Sub decouples publishers and subscribers through shared topics and independent subscriptions. Publishers send events to a Pub/Sub topic without knowing how they will be processed, and subscribers receive events from subscriptions independently with typical latency on the order of 100 milliseconds. Data flows from publishers to a shared topic and then from the topic to each subscriber, so both sides scale and fail independently. The subscription also buffers data if downstream processing stops, and the same event can feed BigQuery, Bigtable, and Cloud Storage at the same time.
The ordered pipeline from Pub/Sub to BigQuery moves through dependent stages:
A BigQuery subscription is a type of Pub/Sub subscription that writes messages directly into a BigQuery table without an intermediate service. The choice depends on whether events need processing: use a BigQuery subscription when low latency and low overhead matter and little transformation is needed, and use Dataflow when events need cleaning, enrichment, joins, or delivery to multiple systems. Batch processing fits deadline-driven work such as a daily regulator report, where raw events may land first and an hourly pipeline transforms them into curated tables.
Eventarc (opens in a new tab) is a Google Cloud event-routing service that carries events from a source to a destination. Eventarc Standard is the routing layer, and an Eventarc trigger is the rule that listens for a specific event and starts work when it occurs. This model improves efficiency because processing runs only when something happens instead of polling. For example, a new Cloud Storage object can start a flow and a Pub/Sub message can call Cloud Run.
A trigger connects a source to a destination through matching rules and identity. The source produces the event, Eventarc routes the event when filters match, and the destination such as Dataform, Dataflow, Cloud Functions, Cloud Run, or Cloud Composer receives the event as a request to start work. Trigger type selects the category of source, event provider selects the product, and event type selects the exact action such as google.cloud.pubsub.topic.v1.messagePublished. Optional filters narrow the match to a specific bucket or topic using exact match without wildcards.
Creation follows an ordered sequence:
The trigger service account is the Identity and Access Management (IAM) identity that Eventarc uses to call the destination, which creates a trust boundary between routing and compute. Production should use a dedicated account with least privilege, such as Cloud Run Invoker for Cloud Run and Pub/Sub Subscriber for Pub/Sub delivery. Reliability depends on placing the trigger, resource, and destination in the same region and on retry settings. Saving only stores the rules; publishing deploys the rules and moves the trigger to active, which can take up to two minutes.
Exam tip: Saving a trigger only stores its rules — the integration must be published before the trigger becomes active and starts processing events.
Continue with this study guide: 3.2 Schedule, automate, and monitor basic data processing tasks
In extract, transform, load (ETL), data is cleaned and reshaped outside BigQuery using compute such as Dataflow before it is stored, so BigQuery mainly holds finished tables. In extract, load, transform (ELT), raw data is loaded into BigQuery first through Cloud Storage and then transformed inside BigQuery with SQL tools such as Dataform, so BigQuery holds both raw and transformed tables.
Dataflow fits serverless batch and streaming work that must scale automatically, while Dataproc fits teams with existing Spark code that need a managed Spark and Hadoop platform. Cloud Data Fusion fits low-code visual integration with connectors for extract, transform, load (ETL), Cloud Composer fits workflow orchestration that coordinates scheduled tasks across systems, and Dataform fits transformations defined in SQL that run inside BigQuery.
Use a BigQuery subscription when low latency and low overhead matter and little transformation is needed because it writes Pub/Sub messages directly into a BigQuery table without an intermediate service. Use Dataflow when events need cleaning, enrichment, joins, or delivery to multiple systems, since a streaming Dataflow pipeline can read from the subscription, process events, and write rows with the Storage Write API.
An Eventarc trigger stays inactive after saving because saving only stores its rules. The integration must be published to deploy the rules and move the trigger to active, which can take up to two minutes.
Last updated