Dynamic Table Mapping

Dynamic Table Mapping allows you to automatically create destination tables and route files according to their file paths using a defined regex pattern. This eliminates the need for manual table configuration by mapping files to destination tables based on your file naming patterns.

Use Cases

Dynamic table mapping is suitable for the following scenarios:

• Your application exports multiple entities, such as sales, orders, and inventory, as separate files or folders.
• The files or directories follow a consistent naming pattern, such as export/<entity>/<date>.csv.
• New entities, and therefore new destination tables, may appear over time. Dynamic Table Mapping eliminates the need to configure these tables manually. You can still control the creation of new tables through the Schema Change Handling settings.

An example use case is Guidewire Cloud Data Access (CDA), which delivers incremental InsuranceSuite data as Parquet files into an Amazon S3 bucket. Since each Guidewire table resides in its own folder, Dynamic table mapping can use the folder name as the table name, allowing the folder structure to map directly to destination tables.

Setup instructions

Choose file mapping method

In the connection setup form, under the Configure Files section, select Dynamically extract tables.

Specify table extraction pattern

Specify a regex pattern with exactly one named capture group called table. This group identifies the part of the file path that becomes the table name.

• Required structure: (?<table>...)
• The ... represents the part of the file path you want to extract as the table name. For example, you can use \w+ to capture a word.

The following sections contain some examples of the regex pattern types supported by Fivetran.

Files with a date suffix in the name

• Files: sales_2025_01.json, transactions_2025_01.json, inventory_2025_01.json
• Table extraction pattern: (?<table>\w+)_\d{4}_\d{2}\.json
• Extracted table names: sales, transactions, and inventory

Files grouped by folder

• Files: /exports/customers/data_20251016.csv, /exports/products/data_20251016.csv
• Table extraction pattern: /exports/(?<table>\w+)/data_\d{8}\.csv
• Extracted table names: customers and products

Multiple nested folders

• Files: /client/acme/invoices/file.csv, /client/acme/contracts/file.csv
• Table extraction pattern: /client/\w+/(?<table>\w+)/.*\.csv
• Extracted table names: invoices and contracts

Validate table extraction pattern

1. Click Preview. Fivetran displays up to 5 examples of how your existing files map to tables.
2. Review the table mappings in the examples.
3. If required, modify the regex pattern and preview the mappings again.

Initiate sync

1. Click Save & Test. Fivetran tests and validates the connection.

2. Once the connection tests pass, click Continue.

3. Choose one of the following sync options:

   • Start syncing all my data now
   • I need to customize data before syncing
   • I'll sync later

4. Click Start initial sync.

During the initial sync, we do the following:

• Scan all files that match your configuration.
• Use the table extraction pattern to determine table names.
• Create the destination tables and start loading data.

On subsequent syncs, new files that match the pattern are automatically routed to the correct tables, and based on your Schema Change Handling settings, new tables are created as required.

Regex components

Regex patterns consist of the following components:

• \w+ – Matches one or more letters, digits, or underscores. Use it to capture table names.
• \d{8} – Matches exactly eight digits, used for dates in the YYYYMMDD format (for example, 20250115).
• \d{4} – Matches exactly four digits, used for years (for example, 2025).
• \.csv – Matches a literal .csv file extension. Escape the dot with a backslash.
• .* – Matches any sequence of characters. Use cautiously, as it can match more than intended and reduce precision.

Best practices

We recommend following these best practices to create a regex for table extraction patterns:

• Use \w+ to match word characters, including letters, numbers, and underscores.
• Escape special characters with a backslash. For example, use \.csv instead of .csv.
• Use \d{n} to match exactly n digits.
• Test your regular expression here.
• Keep patterns simple and specific.
• Include only one instance of (?<table>...).
• Avoid using .* in your table capture group.