Icon

04.1_​ETL_​Customers

The company has two branches: one in the United States and another one in Europe. New US customer data comes from a web service. New Europe customer data comes from Amazon S3.

We access, validate, prepare, and blend the data, anonymize it, and insert it to the database table "customers".

Here, we additionally export the final status of the workflow. In the case some processes fail, we notify responsible people via an automated email.

Session 4 - Orchestration

Exercise 01 ETL Customers

Learning objective: Exercises


Workflow description: The company has two branches: one in the United States and another one in Europe. New US customer data comes from a web service. New Europe customer data comes from Amazon S3.

We access, validate, prepare, and blend the data, anonymize it, and insert it to the database table "customers".

Here, we additionally export the final status of the workflow. In the case some processes fail, we notify responsible people via an automated email.


You'll find the instructions to the exercises in the yellow annotations. Make sure you have executed the 04.0_Reset_DB&Big_Data_Environment workflow before running this workflow.

Provide credentials and parameters

NOTE. Credentials and parameters provided here will be used during this workflow development and will be ignored when this workflow is called from the 04.3 Orchestration workflow. The credentials and parameters provided in the caller workflow will be used instead.



1. Import the credentials

Import flow variables from the caller (Workflow Service Input, flow variable ports)



2.1 Handle errors

Connect the Try (Variable Ports) node with the both Data Access components



Extract

Transform

Load

2.2 Handle errors

Variable Creator node connected to Database Update component creates a flow variable ETL_failed equal to 0. Second Variable Creator creates a flow variable ETL_failed equal to 1

  1. Provide the variable ETL_failed = 0 to the upper port of the Catch Errors (Var Ports) node

  2. Provide the variable ETL_failed = 1 to the bottom port of the Catch Errors (Var Ports) node



3. Export workflow status

  1. Filter out all the variables except ETL_failed, FailingNodeMessage, and FailingNode to the calling workflow (Variable Filter node)

  2. Export the mentioned variables to the caller workflow (Workflow Service Output node, flow variable ports)



Blend the data from different sources
Concatenate
Data Access Europe Customers
Catch Errors (Var Ports)
Status: failure
Variable Creator
Status: success
Variable Creator
Data Access US Customers
Workflow execution status
Workflow Service Output
Data Anonymization
Try (Variable Ports)
Variable Filter
Import credentials from the caller
Workflow Service Input
Webservice and S3 Credentials Configuration
Database Update

Nodes

Extensions

Links