8.3 Cloud Data Fusion: Visual ETL/ELT Pipelines, CDAP Plugins, and Data Lineage
Key Takeaways
- Cloud Data Fusion is a fully managed, code-free visual data integration service built on the open-source Cask Data Application Platform (CDAP), enabling enterprise ETL and ELT pipeline development through a graphical web studio.
- Data Fusion compiles visual Directed Acyclic Graphs (DAGs) into distributed Apache Spark or MapReduce execution binaries and executes them dynamically on automatically provisioned ephemeral Cloud Dataproc clusters.
- Wrangler provides an interactive, visual data preparation and cleansing interface that applies declarative data transformation directives (e.g., parse-as-csv, mask-number, set-column) on sampled data in real time.
- CDAP plugins provide extensible connectors categorized into Sources, Transforms, Analytics, and Sinks, allowing seamless connectivity across BigQuery, Cloud Storage, on-premises relational databases via JDBC, and SaaS applications.
- Cloud Data Fusion automatically captures operational metadata, schema evolution, and end-to-end dataset-level and field-level (column-level) data lineage, natively integrating with Dataplex Universal Catalog for enterprise governance.
8.3 Cloud Data Fusion: Visual ETL/ELT Pipelines, CDAP Plugins, and Data Lineage
Exam Focus: The Google Cloud Professional Data Engineer exam evaluates your knowledge of Cloud Data Fusion as an enterprise visual data integration platform. You must understand its architecture based on CDAP, how visual DAGs are compiled into distributed Apache Spark jobs executed on ephemeral Cloud Dataproc clusters, how to use Wrangler for visual data preparation and directive execution, how to establish hybrid connectivity to on-premises databases using Private IP and VPC Network Peering, and how to track end-to-end dataset and field-level data lineage natively integrated with Dataplex.
While code-centric frameworks like Apache Beam (Cloud Dataflow) and Apache Spark (Cloud Dataproc) offer maximum programmatic flexibility, enterprise data architectures frequently demand rapid, visual pipeline development accessible to data analysts, compliance officers, and ETL developers. Cloud Data Fusion satisfies this need as a fully managed, cloud-native, code-free visual data integration service built upon the open-source Cask Data Application Platform (CDAP). It bridges the gap between drag-and-drop visual design and distributed cloud compute.
1. Cloud Data Fusion Architecture & Open-Source CDAP Foundation
Cloud Data Fusion operates with an architectural separation between the Design & Control Plane (the Data Fusion management instance) and the Execution Data Plane (distributed processing clusters):
+───────────────────────────────────────────────────────────────────────────────────+
| CLOUD DATA FUSION SERVICE TOPOLOGY |
+───────────────────────────────────────────────────────────────────────────────────+
| |
| +─────────────────────────────────────────────────────────────────────────────+ |
| | GOOGLE-MANAGED TENANT PROJECT (Control Plane) | |
| | - Cloud Data Fusion UI (Pipeline Studio & Wrangler) | |
| | - CDAP Microservices (Metadata Engine, Lineage Tracker, Scheduler) | |
| | - CDAP Plugin Registry (JDBC Drivers, BigQuery/GCS Plugins) | |
| +─────────────────────────────────────────────────────────────────────────────+ |
| │ |
| ▼ VPC Network Peering |
| +─────────────────────────────────────────────────────────────────────────────+ |
| | CUSTOMER PROJECT VPC (Execution Data Plane) | |
| | | |
| | [ Ephemeral Cloud Dataproc Cluster ] <── Auto-provisioned per pipeline run | |
| | - Executes Compiled Apache Spark Application | |
| | - Reads from Sources (Cloud Storage, BigQuery, On-Prem JDBC) | |
| | - Writes to Sinks (BigQuery, Bigtable, Cloud Storage) | |
| +─────────────────────────────────────────────────────────────────────────────+ |
| │ |
| ▼ Lineage & Metadata Export |
| +─────────────────────────────────────────────────────────────────────────────+ |
| | DATAPLEX & DATA CATALOG (Central Data Governance Plane) | |
| | - End-to-End Dataset Lineage & Column/Field-Level Transformation Lineage | |
| +─────────────────────────────────────────────────────────────────────────────+ |
+───────────────────────────────────────────────────────────────────────────────────+
Open-Source CDAP Heritage
CDAP is an open-source framework that standardizes the building of data applications on distributed systems. By leveraging CDAP, Cloud Data Fusion provides:
- Portability: Pipelines authored in Data Fusion are stored as standardized JSON definitions that can run on any CDAP environment (on-premises Hadoop, multicloud, or GCP).
- Extensibility: Standard CDAP plugin APIs allow developers to build and import custom sources, transforms, and sinks packaged as standard JAR files.
- Component Abstraction: Pipeline developers connect visual nodes without needing to write Java, Scala, or Python code.
Instance Editions: Basic vs. Enterprise
- Basic Edition: Designed for development, prototyping, and low-volume batch integration. Features lower instance cost, standard SLA, and single-zone deployment.
- Enterprise Edition: Engineered for mission-critical production workloads. Provides multi-zone High Availability (HA), support for private IP networking and VPC peering, higher pipeline concurrency, enterprise security controls, and guaranteed uptime SLAs.
2. Operational Execution: Visual DAGs to Ephemeral Dataproc
A central concept tested on the exam is how Cloud Data Fusion executes pipelines. Data Fusion is not a compute cluster itself; it is a visual design and control plane that delegates distributed compute execution to Cloud Dataproc.
+───────────────────────────────────────────────────────────────────────────────────+
| PIPELINE EXECUTION LIFECYCLE |
+───────────────────────────────────────────────────────────────────────────────────+
| 1. DESIGN: Developer builds visual DAG in Pipeline Studio & configures Wrangler |
| │ |
| 2. COMPILE: Data Fusion compiles visual DAG into an Apache Spark JAR application |
| │ |
| 3. PROVISION: Calls Dataproc API to spin up an Ephemeral Dataproc Cluster |
| │ |
| 4. SUBMIT: Submits the compiled Spark application to the Dataproc cluster |
| │ |
| 5. MONITOR: Streams execution metrics, task logs, and record counters to UI |
| │ |
| 6. TEARDOWN: Automatically deletes Dataproc cluster upon job completion |
| │ |
| 7. GOVERNANCE: Pushes execution metadata and field-level lineage to Dataplex |
+───────────────────────────────────────────────────────────────────────────────────+
Compute Profiles
Data Fusion uses Compute Profiles to specify where and how pipelines execute:
- Ephemeral Dataproc (Default Profile): When a pipeline triggers, Data Fusion automatically calls the Compute Engine and Dataproc APIs in the customer's project to provision a brand-new Dataproc cluster with tailored VM machine types, runs the Spark job, and terminates the cluster immediately upon pipeline completion. The customer pays only for Dataproc and Compute Engine VM runtime during pipeline execution.
- Existing Dataproc Cluster: For organizations running continuous pipelines where provisioning latency (60-90 seconds) is unacceptable, Data Fusion can submit Spark applications to a pre-existing, persistent Dataproc cluster.
- Dataproc on GKE: Runs containerized Spark jobs on Google Kubernetes Engine clusters for unified container orchestration.
3. Visual Pipeline Studio and Node Ecosystem
The Pipeline Studio provides a drag-and-drop canvas for designing Directed Acyclic Graphs (DAGs). The node ecosystem is divided into distinct categories:
[ Source Node ] ──> [ Transform Node ] ──> [ Analytics Node ] ──> [ Sink Node ]
(e.g., GCS) (e.g., Wrangler) (e.g., GroupBy) (e.g., BigQuery)
│
▼ Error Port
[ Error Collector ] ──> [ Dead-Letter Sink ]
Core Node Categories
- Source Plugins: Ingestion endpoints that read structured, semi-structured, or unstructured data. Supported sources include Cloud Storage (CSV, Parquet, Avro, JSON), BigQuery, Cloud Spanner, Cloud SQL, on-premises relational databases via JDBC, Apache Kafka, Pub/Sub, and SaaS applications (Salesforce, SAP, ServiceNow).
- Transform Plugins: Manipulate and enrich records passing through the pipeline. Key transforms include:
- Wrangler: Multi-step visual data cleansing and manipulation.
- Projection: Selecting, dropping, or renaming specific schema fields.
- JavaScript / Python: Custom programmatic logic for complex conditional evaluations.
- Masking: Masking sensitive PII data (credit cards, social security numbers, emails).
- Analytics Plugins: Distributed aggregations and relational operations, including
GroupBy,Distinct,Joiner(inner, outer, and cross joins), andWindowoperations. - Sink Plugins: Output destinations where transformed data is written. Popular sinks include BigQuery (with direct table creation, partitioning, and clustering), Cloud Storage, Cloud Spanner, Cloud Bigtable, and Elasticsearch.
- Error Collector: Captures malformed or invalid records that fail validation checks. Instead of failing the entire distributed Spark job, bad records are routed out of an error port to a designated dead-letter sink (such as a Cloud Storage bucket) for audit and correction.
Macro Parameterization and Runtime Arguments
Production pipelines must never hardcode file paths, database passwords, or date partitions. Data Fusion supports Macro Substitution using ${macro_name} syntax:
- Dynamic Paths: Set source path to
gs://my-bucket/raw/${logical_date}/to dynamically process the partition corresponding to the pipeline run timestamp. - Secure Credentials: Combine macros with Cloud Key Management Service (Cloud KMS) or Secret Manager to inject database credentials securely at runtime without exposing plain text in pipeline definitions.
4. Wrangler: Interactive Visual Data Preparation
Wrangler is Data Fusion's interactive data preparation interface. It enables data engineers and analysts to inspect sample data, understand distributions, and author transformation recipes using point-and-click operations or declarative text directives.
+───────────────────────────────────────────────────────────────────────────────────+
| WRANGLER INTERACTIVE PREPARATION |
+───────────────────────────────────────────────────────────────────────────────────+
| RAW INPUT DATA (1,000 to 10,000 Row In-Memory Sample) |
| "101,John Doe,jdoe@example.com,2026-09-15,$1250.50,4111-2222-3333-4444" |
| │ |
| APPLIED WRANGLER RECIPE DIRECTIVES ▼ |
| 1. parse-as-csv :body ',' true |
| 2. drop :body |
| 3. clean-names |
| 4. format-date :tx_date 'yyyy-MM-dd' 'yyyy/MM/dd' |
| 5. filter-rows-on condition :amount < 0 |
| 6. mask-number :credit_card 'xxxx-xxxx-xxxx-####' |
| │ |
| CLEANSED OUTPUT SCHEMA & DATA ▼ |
| { id: 101, name: "John Doe", email: "...", cc: "xxxx-xxxx-xxxx-4444" } |
+───────────────────────────────────────────────────────────────────────────────────+
How Wrangler Works Under the Hood
- Design Phase (Interactive Sampling): When a user opens Wrangler, Data Fusion loads a sample of the dataset (typically 1,000 to 10,000 rows) into browser and instance memory. Each visual transformation or typed directive executes in sub-seconds against this sample, providing immediate visual feedback.
- Recipe Compilation: The sequence of transformations is recorded as an ordered list of declarative directives called a Recipe.
- Execution Phase (Distributed Compilation): When the pipeline is deployed to Pipeline Studio, the Wrangler recipe is embedded into a Spark transformation stage. The compiled Spark application executes the exact recipe directives across billions of records in parallel on the ephemeral Dataproc cluster.
Essential Wrangler Directives for the Exam
| Directive Syntax | Operational Purpose |
|---|---|
parse-as-csv <column> <delimiter> <has_header> | Splits an unformatted text string into structured tabular columns based on a delimiter. |
parse-as-json <column> [depth] | Unpacks nested JSON strings into structured, addressable top-level fields. |
set-column <column> <expression> | Applies a Java Expression Language (JEXL) expression to compute or mutate a field value. |
mask-number <column> <format> | Masks sensitive financial numbers or PII (e.g., masking credit cards to show only the last 4 digits). |
format-date <column> <source_fmt> <target_fmt> | Standardizes disparate date/time strings into ISO 8601 or target database formats. |
filter-rows-on condition <expr> | Drops records that match or fail a specific boolean validation condition. |
drop <column1>, <column2> | Prunes unnecessary columns to reduce memory overhead and optimize downstream sink writes. |
5. CDAP Plugins & Hybrid On-Premises Ingestion (Private IP & VPC Peering)
A common enterprise architecture tested on the exam is ingesting data from on-premises relational databases (Oracle, SQL Server, IBM DB2) into BigQuery using Cloud Data Fusion.
+───────────────────────────────────────────────────────────────────────────────────+
| PRIVATE IP HYBRID INGESTION ARCHITECTURE |
+───────────────────────────────────────────────────────────────────────────────────+
| |
| +─────────────────────────────────────────+ |
| | TENANT PROJECT (Managed Data Fusion) | |
| | - Private IP Instance (No Public IP) | |
| +─────────────────────────────────────────+ |
| │ |
| ▼ VPC Network Peering |
| +─────────────────────────────────────────────────────────────────────────────+ |
| | CUSTOMER VPC NETWORK | |
| | - Allocated /22 Peering Subnet Range | |
| | - Cloud Router & Dedicated Interconnect / Partner Interconnect Gateway | |
| +─────────────────────────────────────────────────────────────────────────────+ |
| │ |
| ▼ Cloud Interconnect / VPN |
| +─────────────────────────────────────────────────────────────────────────────+ |
| | ON-PREMISES ENTERPRISE DATACENTER | |
| | - Private Oracle / Microsoft SQL Server Database Instance | |
| | - Secure JDBC Connection over Private Dedicated Line | |
| +─────────────────────────────────────────────────────────────────────────────+ |
+───────────────────────────────────────────────────────────────────────────────────+
The JDBC Ingestion Workflow
- Upload JDBC Driver: Download the vendor's official JDBC driver JAR file (e.g.,
ojdbc8.jarfor Oracle ormssql-jdbc.jarfor SQL Server). In the Data Fusion Hub, upload the JAR as a CDAP Plugin of typejdbc. - Configure Database Source: In Pipeline Studio, add a Database Source node. Select the uploaded JDBC plugin, enter the private IP address of the on-premises database server, configure the JDBC connection string (
jdbc:oracle:thin:@//10.200.0.15:1521/PRODDB), provide credentials via secure macros, and specify the extraction SQL query. - Private IP Networking: By deploying Cloud Data Fusion with Private IP, the instance has no public internet access. Google establishes VPC Network Peering between the managed tenant project and the customer's VPC. The ephemeral Dataproc cluster routes queries across Cloud Interconnect or Cloud VPN directly to the internal on-premises IP address, ensuring sensitive corporate data never traverses the public internet.
6. Enterprise Data Governance: Lineage and Dataplex Integration
Data governance is a critical component of the Professional Data Engineer exam. Organizations subject to regulatory compliance (BCBS 239, GDPR, HIPAA) must prove where every data point originated, how it was transformed, and who consumed it.
+───────────────────────────────────────────────────────────────────────────────────+
| END-TO-END FIELD-LEVEL DATA LINEAGE TRACKING |
+───────────────────────────────────────────────────────────────────────────────────+
| |
| UPSTREAM SOURCES DATA FUSION PIPELINE DOWNSTREAM SINKS |
| ┌───────────────────┐ ┌──────────────────────┐ ┌─────────────────┐ |
| | Oracle Database | ─────> | [Wrangler Directive: | ─────> | BigQuery | |
| | Column: raw_ssn | | mask-number | | Column: | |
| └───────────────────┘ | raw_ssn -> enc_ssn] | | enc_ssn | |
| └──────────────────────┘ └─────────────────┘ |
| │ |
| ▼ Lineage Graph Ingested Automatically |
| +─────────────────────────────────────────────────────────────────────────────+ |
| | DATAPLEX & DATA CATALOG GOVERNANCE | |
| | - Complete Audit Provenance: Shows raw_ssn transformed into enc_ssn | |
| | - Impact Analysis: Warns if source schema modification breaks BigQuery view| |
| +─────────────────────────────────────────────────────────────────────────────+ |
+───────────────────────────────────────────────────────────────────────────────────+
Lineage Capabilities in Cloud Data Fusion
- Dataset-Level Lineage: Automatically diagrams the macroscopic flow of data across storage boundaries (e.g., showing that
Cloud Storage Bucket AandOracle Table BfedData Fusion Pipeline X, which loadedBigQuery Dataset Y). - Field-Level (Column-Level) Lineage: Tracks the granular lifecycle of individual fields. For example, it records that column
tax_amountin BigQuery was calculated by multiplyinggross_salesbytax_rateinside a specific Data Fusion Wrangler transformation node. - Time-Travel Lineage Auditing: Allows compliance auditors to select any historical date and inspect the exact schema and pipeline lineage active on that date.
- Dataplex Universal Catalog Integration: Every time a Data Fusion pipeline deploys and executes, its structural metadata, schema definitions, and lineage relationships are automatically published to Dataplex Universal Catalog. Enterprise data stewards can search Dataplex Universal Catalog to discover datasets and visually trace their end-to-end lineage across the entire GCP organization.
7. Technology Selection Matrix: Data Fusion vs. Dataflow vs. Dataproc
A classic scenario on the Data Engineer exam tests your ability to choose the correct data processing service:
| Architectural Attribute | Cloud Data Fusion | Cloud Dataflow | Cloud Dataproc |
|---|---|---|---|
| Core Philosophy | Code-free, visual ETL/ELT integration | Developer-centric, programmatic stream/batch | Open-source ecosystem lift-and-shift |
| Underlying Engine | CDAP running on ephemeral Spark | Apache Beam running on Dataflow service | Apache Spark, Hadoop, Hive, Presto/Trino |
| Target User Persona | Data analysts, ETL developers, citizen integrators | Data engineers, software developers | Hadoop/Spark engineers, big data architects |
| Authoring Interface | Visual Web UI (Pipeline Studio & Wrangler) | Java, Python, Go SDKs (Code-based) | PySpark, Spark SQL, Scala, Shell (Code-based) |
| Streaming Latency | Minutes (Micro-batch Spark streaming) | Sub-second (True event-driven streaming) | Seconds (Spark Structured Streaming) |
| Data Lineage | Automated out-of-the-box (Field-level) | Custom implementation required | Custom implementation required |
| On-Prem Connector Hub | Extensive pre-built CDAP plugins (JDBC, SAP, etc.) | Requires custom Beam I/O connectors | Standard Hadoop/Spark drivers |
Realistic Exam Scenarios & Architecture Pitfalls
| Problem Scenario | Architectural Anti-Pattern | Correct Google Cloud Architecture |
|---|---|---|
| Citizen Integrator Visual Pipeline<br>A business analyst team with zero Java/Python coding skills needs to cleanse messy CSV files from Cloud Storage, validate schemas, and load clean data into BigQuery. | Writing custom Apache Beam streaming pipelines in Java running on Cloud Dataflow. | Use Cloud Data Fusion. The team can visually map sources to sinks in Pipeline Studio and use Wrangler directives to visually clean, format, and filter records without writing code. |
| Regulatory Field-Level Lineage Audit<br>A global bank must provide auditors with a visual graph proving that customer Social Security Numbers are masked before landing in analytical BigQuery tables. | Writing custom logging scripts inside an Apache Spark job that export text logs to Cloud Storage. | Build the pipeline in Cloud Data Fusion. Data Fusion automatically generates field-level lineage tracking the transformation from raw_ssn to masked_ssn and publishes the governance graph directly into Dataplex Universal Catalog. |
| Sub-Second Real-Time Fraud Ingestion<br>A payment processing platform requires real-time fraud scoring on financial transactions arriving via Pub/Sub with end-to-end latency under 100 milliseconds. | Building a streaming pipeline in Cloud Data Fusion using the micro-batch Spark streaming plugin. | Use Cloud Dataflow (Apache Beam). Dataflow's Streaming Engine provides true event-driven stream processing with sub-second latency, dynamic windowing, and millisecond state management. |
| Private Ingestion Network Peering Failure<br>A private Cloud Data Fusion instance fails to connect to an on-premises database via Cloud Interconnect, throwing connection refused errors during pipeline runs. | Assigning public IP addresses to the on-premises database to allow public internet ingress. | Configure VPC Network Peering between the Cloud Data Fusion tenant VPC and the customer VPC network, and export/import custom routes so the tenant project can route traffic across Cloud Interconnect to private on-prem IP ranges. |
An ETL engineer designs a visual data pipeline in Cloud Data Fusion Pipeline Studio that consumes raw delimited log files from Cloud Storage, applies data validation rules, and writes clean records into BigQuery. During test runs against a 50 TB dataset, approximately 0.5% of the source records contain malformed formatting (such as non-numeric characters in a currency column), which causes the entire distributed Spark execution to abort with parsing errors. The engineer needs to ensure that the pipeline successfully processes all valid records while isolating malformed records for inspection without writing custom transformation code. What is the recommended architectural design?
A data engineer is designing a Cloud Data Fusion pipeline to ingest sensitive financial customer records from an on-premises Microsoft SQL Server database into BigQuery. The security team mandates that the database connection password must never appear as plain text in pipeline JSON configurations or source control repositories, and all database extraction parameters (such as the target extraction batch date) must be dynamically populated by an external orchestrator at execution time. How should the pipeline configuration be structured in Pipeline Studio?
A multinational healthcare enterprise needs to establish an automated data integration pipeline that extracts patient encounter records from an on-premises Oracle database, cleanses malformed diagnostic codes, and loads the records into BigQuery. Regulatory compliance officers mandate that the system must track and visually display end-to-end field-level (column-level) lineage showing exactly how each patient attribute was transformed. The ETL development team consists of SQL analysts who do not write Java or Python code. Which Google Cloud solution satisfies all architectural and compliance requirements?
A data architecture team is planning the migration of an on-premises ETL environment to Google Cloud. The team is evaluating Cloud Data Fusion, Cloud Dataflow, and Cloud Dataproc. The enterprise needs to choose the right service for a specific workload: a real-time event processing system that consumes financial trading events from Cloud Pub/Sub, detects market manipulation anomalies using complex sliding 5-minute session windows, and emits alerts to a trading dashboard with strict sub-second (under 100 milliseconds) latency SLAs. Which service should the team choose and why?