Dynamic Table Mapping

Dynamic Table Mapping automatically identifies and creates destination tables by extracting table names from file paths using a defined regex pattern with a named capture group (?<table>...). This feature automatically creates and maps destination tables based on your file naming patterns, eliminating the need for manual intervention.

Use Cases

Dynamic Table Mapping is useful when your source application exports multiple entities as files with a consistent naming pattern, such as:

• export/sales/20250115.csv - sales
• export/inventory/20250115.csv - inventory
• export/orders/20250115.csv - orders

A common integration scenario involves Guidewire Cloud Data Access (CDA), which delivers incremental InsuranceSuite data as Parquet files into your Amazon S3 bucket. The data is updated in near real time and organized in a consistent directory structure, with each table represented as a separate folder.

Setup instructions

Choose file mapping method

In the Fivetran dashboard, under Configure Files section, select Dynamic extract tables.

Enter table extraction pattern

Write a regex pattern with exactly one named capture group called table. This capture group defines which part of the filename becomes your table name.

• Pattern structure: (?<table>...)
• The ... represents the part of the file name you want to extract as the table name.

Validate table extraction pattern

Click Preview to see how your pattern will map files to tables. Fivetran will show up to 5 examples of discovered tables based on your current files.

After you specify your table extraction pattern, Fivetran automatically handles file detection and table creation as follows:

• Initial sync: Fivetran scans your source location for all files matching the specified pattern and creates tables based on the extracted names.

• Ongoing syncs: When new files arrive that match the pattern (for example, refunds_20250120.csv), Fivetran applies your pattern as follows:

  • If Schema Change Handling is set to Allow all, Fivetran automatically creates the new table refunds and begins syncing the data.

  • Otherwise, the new table is created but remains unselected until you review and enable it manually.

File pattern examples

This section helps you understand the file pattern through examples, including the table extraction pattern and the key components of a table pattern.

Files with date suffixes

• 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 in categorized folders

• 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

Standard pattern components

• \w+ – Matches one or more alphanumeric characters or underscores. Commonly used to capture table or file name components.
• \d{8} – Matches exactly eight digits, often used to represent dates in YYYYMMDD format (for example, 20250115).
• \d{4} – Matches exactly four digits, often used to capture years (for example, 2025).
• \.csv – Matches the .csv file extension.
• .* – Matches any sequence of characters. Use carefully, as it can match more than intended and reduce precision.

Best practices

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