How to ETL data from tables matching a wildcard

Overview

This flow extracts data from multiple tables or views that match a wildcard pattern in the source database and loads it into another database or schema. Etlworks automatically maps each matched source object to the corresponding destination, with options to include, exclude, transform, and run in parallel.

Step-by-Step Instructions

Step 1. Go to Connections and create a connection for the source relational database. Test the connection.

Step 2. Create a connection for the destination relational database. Test the connection.

Step 3. Go to Flows and create a new Database to database flow.

Step 4. Add a new transformation. Select the FROM and TO connections.

Step 5. In the FROM field, enter the fully qualified source name using a wildcard pattern, for example:

public.*

In the TO field, use the asterisk character (*) to dynamically map source names to destination names. For example:

public.* → public.patient if the source is test.dbo.patient.

Step 6.  (Optional): Under Coniguration > Parameters, configure how to Exclude or Include tables:

• Exclude objects to skip certain tables/views. Use a comma-separated list of fully qualified names. Wildcards are allowed.

  Example: exclude all views with *.v_*

• Include objects to load only specific tables/views. Use fully qualified names with or without wildcards.

To exclude all views, enter all views in the Exclude objects field.

To exclude all tables, enter all tables in the Exclude objects field.

Step 7. (Optional): Under Source Query, use the {table} token to reference the dynamic source table.

Example:

Step 8. (Optional) Enable MERGE (UPSERT) if you want to update existing records.

Note: Enabling Predict Lookup Fields allows flow to automatically select keys for MERGE. Alternatively you can manually specify them using fully qualified table names and semicolon-separated pairs in the Lookup Fields:

Step 8. (Optional): Configure Change Replication to track and load only modified data

Step 9. (Optional): To fully refresh destination tables, add the following SQL to the Before SQL field:

truncate table {TABLE}

Step 10. (Optional): Enable Use Parallel Threads when processing sources by a wildcard to improve performance. Be cautious if destination tables have foreign key constraints.

Hint: You can change the number of threads for better control. The default is 5 threads.

Step 11. Save the Flow and execute it manually.

Step 12. Schedule the flow

• Go to Schedules

• Create a schedule to run the flow periodically