Icon

04.1_​ETL_​Customers

04.1_ETL_Customers_solution

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". We export the final status of the workflow. In the case some processes fail, we notify responsible people via an automated email.


Extract Transform Load Session 4 OrchestrationExercise 04.1 ETL Customers 2 Handle errorsConnect the Try (VariablePorts) node with the bothData Access components 3 Handle errorsVariable Creator node connected to DatabaseUpdate component creates a flow variable ETL_failedequal to 0. Second Variable Creator creates a flowvariable ETL_failed equal to 11. Provide the variable ETL_failed = 0 to the upperport of the Catch Errors (Var Ports) node2. Provide the variable ETL_failed = 1 to the bottomport of the Catch Errors (Var Ports) node 1 Import thecredentialsImport flow variables fromthe caller (WorkflowService Input, flow variableports) ExercisesAll the tasks can be found in the yellow annotations and the yellow components.Make sure you have executed the 00_Setup_PostgreSQL_Database or 04.0_Reset_DB&Big_Data_Environment workflow before running this workflow. Provide credentials andparameters NOTE. Credentials andparameters provided here will beused during this workflowdevelopment and will be ignoredwhen this workflow is called fromthe 04.3 Orchestration workflow.The credentials and parametersprovided in the caller workflowwill be used instead. 4 Export workflow status1. Export the variable ETL_failed to thecalling workflow (Workflow ServiceOutput node, flow variable ports)2. Wrap the node into a component andexport only the necessary variables:ETL_failed, FailingNodeMessage, andFailingNode Blend the data fromdifferent sourcesStatus: failureImport credentialsfrom the callerStatus: successCredentialsConfiguration Concatenate Try (VariablePorts) Database Update Variable Creator Catch Errors(Var Ports) WorkflowService Input Data Anonymization Variable Creator ParametersConfiguration WorkflowService Output Data AccessUS Customers Data AccessEurope Customers Extract Transform Load Session 4 OrchestrationExercise 04.1 ETL Customers 2 Handle errorsConnect the Try (VariablePorts) node with the bothData Access components 3 Handle errorsVariable Creator node connected to DatabaseUpdate component creates a flow variable ETL_failedequal to 0. Second Variable Creator creates a flowvariable ETL_failed equal to 11. Provide the variable ETL_failed = 0 to the upperport of the Catch Errors (Var Ports) node2. Provide the variable ETL_failed = 1 to the bottomport of the Catch Errors (Var Ports) node 1 Import thecredentialsImport flow variables fromthe caller (WorkflowService Input, flow variableports) ExercisesAll the tasks can be found in the yellow annotations and the yellow components.Make sure you have executed the 00_Setup_PostgreSQL_Database or 04.0_Reset_DB&Big_Data_Environment workflow before running this workflow. Provide credentials andparameters NOTE. Credentials andparameters provided here will beused during this workflowdevelopment and will be ignoredwhen this workflow is called fromthe 04.3 Orchestration workflow.The credentials and parametersprovided in the caller workflowwill be used instead. 4 Export workflow status1. Export the variable ETL_failed to thecalling workflow (Workflow ServiceOutput node, flow variable ports)2. Wrap the node into a component andexport only the necessary variables:ETL_failed, FailingNodeMessage, andFailingNode Blend the data fromdifferent sourcesStatus: failureImport credentialsfrom the callerStatus: successCredentialsConfiguration Concatenate Try (VariablePorts) Database Update Variable Creator Catch Errors(Var Ports) WorkflowService Input Data Anonymization Variable Creator ParametersConfiguration WorkflowService Output Data AccessUS Customers Data AccessEurope Customers

Nodes

Extensions

Links

Download

To use this workflow in KNIME, download it from the below URL and open it in KNIME:

Download Workflow
Created by: Lada.Rudnitckaia
Created at: 2021-10-07
On NodePit since: 2023-04-06
Last update: 2024-04-27
Created with KNIME version: v4.7.1
Tags: Best practicesData engineerData engineeringEducation

Deploy, schedule, execute, and monitor your KNIME workflows locally, in the cloud or on-premises – with our brand new NodePit Runner.

Try NodePit Runner!