Data Fusion for ETL in GCP - Commands & Configuration

Introduction

Data Fusion helps you move and change data from one place to another easily. It solves the problem of combining data from many sources and preparing it for use without writing complex code.

When you want to combine sales data from different stores into one place for analysis

When you need to clean and organize customer data before loading it into a database

When you want to move data from cloud storage to a data warehouse automatically

When you want to schedule regular data updates without manual work

When you want to build data pipelines visually without coding

Config File - pipeline.json

pipeline.json

{
  "name": "example-etl-pipeline",
  "description": "A simple ETL pipeline to move and transform data",
  "config": {
    "source": {
      "type": "GCS",
      "properties": {
        "path": "gs://example-bucket/input-data/"
      }
    },
    "transform": {
      "type": "Wrangler",
      "properties": {
        "script": "parse-as-csv body '\n' ','; drop-column 'unnecessary_column';"
      }
    },
    "sink": {
      "type": "BigQuery",
      "properties": {
        "dataset": "example_dataset",
        "table": "cleaned_data"
      }
    }
  }
}

This JSON defines a Data Fusion ETL pipeline named example-etl-pipeline.

The source section tells Data Fusion where to get the data, here from a Google Cloud Storage bucket.

The transform section uses a Wrangler script to parse CSV data and remove an unwanted column.

The sink section sends the cleaned data to a BigQuery table for analysis.

Commands

This command creates a new Data Fusion instance named 'example-instance' in the us-central1 region. The 'basic' type is suitable for simple ETL tasks.

Terminal

gcloud data-fusion instances create example-instance --location=us-central1 --type=basic

Expected Output

Create request issued for: [projects/project-id/locations/us-central1/instances/example-instance]

Waiting for operation [projects/project-id/locations/us-central1/operations/operation-id] to complete...

Create operation finished successfully.

→

--location - Specifies the region where the Data Fusion instance will be created

→

--type - Defines the edition of Data Fusion instance (basic or enterprise)

This command uploads and creates the ETL pipeline defined in 'pipeline.json' on the 'example-instance'.

Terminal

gcloud data-fusion pipelines create example-etl-pipeline --instance=example-instance --location=us-central1 --file=pipeline.json

Expected Output

Pipeline 'example-etl-pipeline' created successfully.

→

--file - Specifies the pipeline configuration file to upload

This command starts running the ETL pipeline on the Data Fusion instance to process data as defined.

Terminal

gcloud data-fusion pipelines run example-etl-pipeline --instance=example-instance --location=us-central1

Expected Output

Pipeline run started with run ID: run-1234567890abcdef

This command lists all pipelines on the Data Fusion instance to verify the pipeline exists and check its status.

Terminal

gcloud data-fusion pipelines list --instance=example-instance --location=us-central1

Expected Output

NAME                 STATUS

example-etl-pipeline RUNNING

Key Concept

If you remember nothing else from this pattern, remember: Data Fusion lets you build and run data pipelines visually to move and clean data without coding.

Common Mistakes

Not specifying the correct location when creating the Data Fusion instance or pipelines

The commands will fail or create resources in unexpected regions, causing confusion or errors.

Always use the --location flag with the correct region for your resources.

Uploading a pipeline JSON file with invalid or incomplete configuration

The pipeline creation will fail or the pipeline will not run as expected.

Validate the pipeline JSON file carefully and test it in the Data Fusion UI before running.

Running the pipeline before it is created or without an active Data Fusion instance

The run command will fail because the pipeline or instance does not exist.

Ensure the instance is created and the pipeline is uploaded successfully before running.

Summary

Create a Data Fusion instance to host your ETL pipelines.

Define your ETL pipeline in a JSON file specifying source, transform, and sink.

Upload the pipeline to the Data Fusion instance.

Run the pipeline to process and move your data automatically.

Practice

1. What is the main purpose of Google Cloud Data Fusion in ETL processes?

easy

A. To store large amounts of raw data permanently

B. To write complex SQL queries for data analysis

C. To visually build and automate data pipelines without coding

D. To create virtual machines for data processing

Solution

1. Step 1: Understand Data Fusion's role

   Data Fusion is designed to help users build data pipelines visually, making ETL easier without needing to write code.

2. Step 2: Compare options

   Options A, B, and D describe other tools or services, not Data Fusion's main purpose.

3. Final Answer:

   To visually build and automate data pipelines without coding -> Option C

4. Quick Check:

   Data Fusion = Visual ETL pipelines [OK]

Hint: Data Fusion is about visual pipelines, not coding or storage [OK]

Common Mistakes:

• Confusing Data Fusion with BigQuery for SQL queries
• Thinking Data Fusion stores data permanently
• Assuming Data Fusion manages virtual machines

2. Which of the following is the correct way to add a source plugin in a Data Fusion pipeline?

easy

A. Drag the source plugin from the palette and drop it onto the canvas

B. Write a SQL query to define the source

C. Create a virtual machine and install the source plugin manually

D. Use the command line to start the source plugin

Solution

1. Step 1: Recall Data Fusion UI usage

   Data Fusion uses a visual interface where you drag and drop plugins to build pipelines.

2. Step 2: Eliminate incorrect methods

   Options A, C, and D involve coding, manual installs, or command line, which are not how Data Fusion works for adding plugins.

3. Final Answer:

   Drag the source plugin from the palette and drop it onto the canvas -> Option A

4. Quick Check:

   Data Fusion = Drag & drop plugins [OK]

Hint: Data Fusion uses drag-and-drop for plugins, not code [OK]

Common Mistakes:

• Trying to write SQL to add plugins
• Thinking manual VM setup is needed
• Using command line instead of UI

3. Given a Data Fusion pipeline with a source plugin reading from Cloud Storage and a sink plugin writing to BigQuery, what happens when the pipeline runs successfully?

medium

A. The pipeline deletes data in BigQuery

B. Data is moved from Cloud Storage to BigQuery after any transformations

C. The pipeline creates a new Cloud Storage bucket

D. Data is copied from BigQuery to Cloud Storage

Solution

1. Step 1: Understand source and sink roles

   The source plugin reads data from Cloud Storage, and the sink plugin writes data to BigQuery.

2. Step 2: Identify pipeline behavior

   When the pipeline runs, data flows from source to sink, optionally transformed in between.

3. Final Answer:

   Data is moved from Cloud Storage to BigQuery after any transformations -> Option B

4. Quick Check:

   Source to sink = Cloud Storage to BigQuery [OK]

Hint: Source reads, sink writes; data flows forward [OK]

Common Mistakes:

• Confusing source and sink directions
• Thinking pipeline creates buckets automatically
• Assuming pipeline deletes data

4. You created a Data Fusion pipeline but it fails to start. Which of the following is the most likely cause?

medium

A. The pipeline has too many source plugins

B. The Cloud Storage bucket is empty

C. You did not write any SQL queries in the pipeline

D. The sink plugin is missing or not configured properly

Solution

1. Step 1: Check pipeline requirements

   Every Data Fusion pipeline needs a properly configured sink plugin to write data.

2. Step 2: Analyze failure reasons

   If the sink is missing or misconfigured, the pipeline cannot complete and will fail to start.

3. Final Answer:

   The sink plugin is missing or not configured properly -> Option D

4. Quick Check:

   Missing sink = pipeline start failure [OK]

Hint: Always configure sink plugin to start pipeline [OK]

Common Mistakes:

• Assuming empty bucket causes start failure
• Thinking SQL queries are mandatory
• Believing multiple sources cause failure

5. You want to build a Data Fusion pipeline that reads sales data from multiple Cloud Storage folders, cleans the data by removing duplicates, and writes the result to BigQuery daily. Which approach best fits this requirement?

hard

A. Use multiple source plugins for each folder, a Wrangler plugin to clean duplicates, and a BigQuery sink; schedule pipeline daily

B. Write a custom script outside Data Fusion to clean data, then use Data Fusion to load into BigQuery

C. Use a single source plugin pointing to one folder and manually merge data later

D. Create a pipeline with only a BigQuery source and sink without cleaning

Solution

1. Step 1: Identify data sources and cleaning needs

   Multiple folders require multiple source plugins or a pattern that reads all folders; cleaning duplicates needs a Wrangler or transform plugin.

2. Step 2: Design pipeline and scheduling

   Connect sources to cleaning plugin, then to BigQuery sink; schedule pipeline to run daily for automation.

3. Final Answer:

   Use multiple source plugins for each folder, a Wrangler plugin to clean duplicates, and a BigQuery sink; schedule pipeline daily -> Option A

4. Quick Check:

   Multiple sources + cleaning + sink + schedule = Use multiple source plugins for each folder, a Wrangler plugin to clean duplicates, and a BigQuery sink; schedule pipeline daily [OK]

Hint: Combine sources, cleaning plugin, sink, and schedule for ETL [OK]

Common Mistakes:

• Skipping cleaning step inside Data Fusion
• Using external scripts unnecessarily
• Ignoring scheduling for daily runs