Any to Any ETL

When to use this Flow type

In Etlworks, ETL Flow templates are typically designed to work with specific types of sources and destinations, such as database-to-database, file-to-database, API-to-database, and so on. These templates help streamline configuration but also introduce an artificial limitation where each Flow type is expected to handle only a specific combination of source and destination types.

The Any-to-Any ETL Flow removes this limitation by allowing users to create a single Flow that includes multiple source-to-destination transformations, regardless of the type of sources and destinations involved. This makes it a flexible option for complex data integration scenarios that involve diverse data sources and multiple output targets.

Here is an example of how to use Any-to-Any ETL Flow to apply JOIN and SET transformations to the multiple heterogeneous data sources. 

Note that you can achieve the same goal by using Nested Flow. Unlike nested Flow that can include any Flow type (even other nested Flows), Any-to-Any ETL Flow is limited to source-to-destination transformations. 

Process

To create a Flow, go to Flows window, click +, and type in any to any. Select the Flow and add one or multiple source-to-destination transformations.

Do not use  Any to Any ETL Flow to load data into data warehouses like Snowflake and Redshift

Snowflake, Amazon Redshift and other cloud data warehouses are columnar databases, optimized for data retrieval operations, so loading data into using DML statements (INSERT/ UPDATE/ DELETE) can be quite slow, especially for larger datasets.

While it is possible to load data in Snowflake and Redshift using Any to any ETL Flow, it is highly recommended to use specialized Flows for Snowflake and Redshift instead.