6.2 Visual Data Pipelines with Cloud Data Fusion

6.2 Visual Data Pipelines with Cloud Data Fusion

[!IMPORTANT] On the Google Cloud Professional Data Engineer exam, questions frequently test your ability to select the appropriate data transformation tool. Choose Cloud Data Fusion when the primary requirement is visual, code-free ETL/ELT pipeline design for business analysts or data integration specialists who need pre-built enterprise connectors (e.g., SAP, Salesforce, Oracle) and turnkey data lineage without managing low-level Spark or Beam code.

Modern enterprise data architectures encompass diverse stakeholders. While software engineers and data scientists often prefer writing custom Python, Scala, or Java pipelines, enterprise data integration teams and business intelligence analysts require high-productivity, visual tools that accelerate time-to-insight. Google Cloud Data Fusion satisfies this need by delivering a fully managed, cloud-native data integration service built upon the open-source CDAP (Cask Data Application Platform) project.

---

The Visual Integration Paradigm: CDAP Under the Hood

Cloud Data Fusion operates on an architecture that strictly decouples the visual design and management interface from the distributed compute execution engine:

1. Control Plane (Design Time): Hosted in a Google-managed tenant project. It provides the web-based Pipeline Studio graphical user interface, the Wrangler interactive data preparation tool, metadata management, and the plugin repository (Data Fusion Hub).
2. Compute Plane (Execution Time): Resides inside the customer's Virtual Private Cloud (VPC) network. When a visual pipeline is triggered, Data Fusion translates the graphical directed acyclic graph (DAG) into an optimized Apache Spark application (or MapReduce for legacy plugins) and executes it on distributed compute infrastructure.

Because Data Fusion is built on open-source CDAP, pipelines are completely portable. Workflows designed within Cloud Data Fusion can be exported as JSON configuration files and executed on any on-premises or multi-cloud CDAP deployment, preventing cloud vendor lock-in.

Cloud Data Fusion Editions

When provisioning a Data Fusion instance, architects select between three distinct editions:

• Developer Edition: Designed for single-developer sandbox prototyping and unit testing. Does not provide high availability or enterprise SLAs; limited to single-pipeline concurrent execution.
• Basic Edition: Suitable for standard departmental batch workloads. Supports multiple concurrent pipeline runs, basic SLA guarantees, and standard Hub plugins.
• Enterprise Edition: Built for mission-critical enterprise production environments. Offers multi-zone high availability, dedicated CDAP master services, private IP connectivity with VPC Service Controls (VPC-SC) integration, concurrent pipeline scaling, and 99.9% uptime SLAs.

---

Service Selection Decision Matrix: Data Fusion vs. Dataflow vs. Dataproc

A central competency for Google Cloud data engineers is discerning when to use Cloud Data Fusion versus Cloud Dataflow versus Cloud Dataproc. The table below details the architectural decision matrix:

---

Pipeline Studio Architecture: Sources, Transforms, and Sinks

In Cloud Data Fusion's Pipeline Studio, data engineers construct end-to-end data processing pipelines by dragging and connecting visual nodes representing Sources, Transforms, and Sinks.

+---------------------------------------------------------------------------------------------------+
|                                 Cloud Data Fusion Pipeline Studio                                 |
+---------------------+      +-------------------------------+      +-------------------------------+
|    SOURCE NODES     |      |       TRANSFORM NODES         |      |          SINK NODES           |
+---------------------+      +-------------------------------+      +-------------------------------+
| • Cloud Storage     | ---> | • Wrangler (Data Preparation) | ---> | • Google BigQuery             |
| • Cloud SQL / JDBC  |      | • Joiner (Shuffle / Broadcast)|      | • Cloud Bigtable              |
| • Oracle / Teradata |      | • Group By / Aggregator       |      | • Cloud Spanner               |
| • Salesforce / SAP  |      | • Conditional Splitter        |      | • Cloud Storage (Parquet/ORC) |
| • Apache Kafka      |      | • Deduplicator / Masker       |      | • External REST / JDBC DB     |
+---------------------+      +-------------------------------+      +-------------------------------+

1. Sources

Sources extract data from external storage systems, transactional databases, and streaming brokers. Data Fusion provides dozens of enterprise connectors via the Data Fusion Hub:

• Relational Databases via JDBC: Connectors for Oracle, Microsoft SQL Server, PostgreSQL, MySQL, DB2, and Teradata with automated schema discovery.
• Google Cloud Native Sources: Cloud Storage, BigQuery, Pub/Sub, and Cloud Spanner.
• SaaS & Enterprise Applications: Salesforce, ServiceNow, SAP ERP, Marketo, and Google Analytics 4.

2. Transforms and Wrangler

Transforms manipulate, enrich, filter, and restructure incoming records:

• Wrangler: The flagship interactive data cleansing tool. Analysts sample raw data and apply transformations using over 100 built-in visual directives (e.g., parse-as-csv, set-type :age integer, mask-number :ssn XXX-XX-####, split-to-rows :tags ,). Wrangler immediately displays the transformed schema and sample rows.
• Core Transform Plugins:
  • Joiner: Executes relational joins (Inner, Left Outer, Right Outer, Full Outer). Supports broadcast joins for small dimension tables to avoid costly network shuffles.
  • Group By: Performs distributed aggregations (Sum, Min, Max, Count, Average) across grouped keys.
  • Conditional Splitter: Routes records along different pipeline branches based on boolean logic (e.g., routing compliant records to the main sink and invalid records to an audit bucket).
  • Deduplicator: Eliminates duplicate records based on a unique composite key.
• Error Collector Plugin: In production batch processing, dirty records (e.g., unparseable dates or schema mismatches) should not cause the entire multi-hour pipeline to fail. The Error Collector plugin intercepts malformed records and routes them to a designated dead-letter sink (such as a Cloud Storage bucket or BigQuery quarantine table) alongside error diagnostics, allowing valid records to proceed uninterrupted.

3. Sinks

Sinks materialize the transformed datasets into target analytics warehouses, operational databases, or object storage:

• BigQuery Sink: Automatically creates or appends to BigQuery tables, with options for partitioned and clustered table targets.
• Cloud Bigtable / Spanner Sinks: Writes high-throughput operational records using configured row keys and mutation batches.
• Cloud Storage Sink: Writes partitioned Avro, Parquet, JSON, or Delimited text files.

---

Execution Runtime: The Ephemeral Dataproc Engine

Although data pipelines are authored visually in Pipeline Studio, Cloud Data Fusion does not process data directly within the control plane web instance. Instead, it delegates computational execution to Compute Profiles:

1. Pipeline Compilation: When a user clicks "Deploy" and "Run," CDAP compiles the visual DAG into a native Apache Spark application jar.
2. Ephemeral Cluster Provisioning: By default, Data Fusion uses an Ephemeral Dataproc Compute Profile. It calls the Cloud Dataproc API to provision an ephemeral cluster in the customer's VPC network, sized according to the profile's specifications.
3. Distributed Execution: The Spark application executes on the Dataproc cluster, reading directly from the configured sources, performing distributed transformations across Spark executors, and streaming results into the target sinks.
4. Teardown and Metrics Capture: Upon pipeline completion, Data Fusion extracts execution logs, error metrics, and lineage records, and immediately issues an API call to delete the Dataproc cluster.

Static Cluster Alternative: For development environments or pipelines that run every 5 minutes, waiting 90 to 120 seconds for ephemeral cluster creation introduces unacceptable latency. In these scenarios, administrators configure a persistent Dataproc Compute Profile to reuse an existing, long-running cluster.

---

Change Data Capture (CDC) and Replication Jobs

In addition to traditional batch ETL pipelines, Cloud Data Fusion provides Replication Jobs designed for continuous, near-real-time Change Data Capture (CDC) from operational databases directly into BigQuery:

• Supported Sources: Oracle, Microsoft SQL Server, MySQL, and PostgreSQL.
• Mechanics: Reads database binary transaction logs (e.g., Oracle Redo logs, MySQL binlogs) without impacting transactional database CPU or requiring triggers.
• Automated Schema Drift: If an administrator adds, modifies, or drops columns in the source database table, Data Fusion automatically detects the schema modification and applies the corresponding DDL schema alteration to the destination BigQuery table in real time.
• Continuous Synchronization: Writes incremental inserts, updates, and deletes into BigQuery staging tables and periodically merges changes into target analytics tables with zero data engineering coding required.

---

Enterprise Governance, Data Lineage, and Dataplex Integration

Regulatory frameworks such as GDPR, CCPA, BCBS 239, and HIPAA require financial and healthcare enterprises to maintain rigorous audit trails tracking how sensitive data originates, transforms, and disseminates.

Cloud Data Fusion provides automated, native integration with Google Cloud Dataplex Catalog:

+-------------------------------------------------------------------------+
|                    Automated Lineage Architecture                       |
+-------------------------------------------------------------------------+
|  [Source: Oracle Table]                                                 |
|         |                                                               |
|         v                                                               |
|  [Data Fusion Pipeline: Cleanse_Customer_PII]                           |
|    • Directive: mask-number raw_ssn -> masked_ssn                       |
|    • Directive: set-type birth_date date                                |
|         |                                                               |
|         v                                                               |
|  [Sink: BigQuery Table: curated_customers]                              |
|         |                                                               |
|         +-----> Automatically Published to Dataplex Catalog           |
|                 (Visual Column-Level Dependency Graph)                  |
+-------------------------------------------------------------------------+

Lineage Granularity

• Dataset-Level Lineage: Automatically records relationships between source assets (e.g., an on-premises Oracle database table or Cloud Storage bucket) and destination assets (e.g., a BigQuery curated table).
• Field-Level (Column-Level) Lineage: Traces the exact historical transformation of individual attributes. An auditor can click on the masked_ssn column in BigQuery within the Dataplex UI and visually trace its origin back to the raw_ssn field in Oracle, viewing every intermediate Wrangler directive and join operation.
• Impact Analysis: If an upstream transactional database alters a schema definition, data engineers can perform impact analysis in Dataplex to identify every downstream Data Fusion pipeline, BigQuery view, and BI dashboard that will be impacted before deploying changes.

---

Network Topologies: Private Data Fusion and VPC Peering

In enterprise deployments, data security mandates that pipelines process confidential data without exposing network traffic to the public internet. Deploying a Private Cloud Data Fusion instance requires careful network planning.

Network Peering Mechanics

• In a private instance, the Data Fusion control plane is provisioned in an isolated Google-managed tenant project.
• The tenant project communicates with the customer's VPC network via VPC Network Peering.
• During instance creation, the customer must designate an unallocated /22 IP address range (1,024 addresses) that Google Cloud allocates to the tenant network.

Connecting to On-Premises and Cloud SQL Databases

When a pipeline must extract data from an on-premises database (via Dedicated Interconnect or Cloud VPN) or an internal Cloud SQL instance using private IP:

1. VPC Peering Custom Routes: Standard VPC Peering only propagates local subnet routes between the two peered networks. To allow the Data Fusion tenant project to reach on-premises IP subnets across a Cloud Router, the customer VPC peering configuration must explicitly enable "Export custom routes", and the tenant peering must enable "Import custom routes".
2. Firewall Rules: Ingress firewall rules must be created in the customer VPC to permit the Data Fusion tenant /22 CIDR block to communicate with target database ports (e.g., port 1521 for Oracle, port 5432 for PostgreSQL, port 1433 for SQL Server).
3. Private IP Dataproc Execution: Ephemeral Dataproc clusters spawned by Data Fusion must be configured without external public IP addresses (--no-address), using Private Google Access to communicate with Google Cloud APIs.