Syncing from S3

Polytomic supports syncing data from S3 buckets using Bulk Syncs (for warehouse destinations) or Model Syncs (for SaaS applications and webhooks). This section describes how the different connection options interact and how to configure patterns for discovering and reading files.

Supported file formats

S3 syncs support the following file formats:

• CSV (.csv)
• JSON (.json, .jsonl)
• Parquet (.parquet)

Files may also be compressed with .gz, .bz2, or .zst (e.g., users.csv.gz). Compressed Parquet files are not supported.

For CSV files, uncheck CSV files have headers if your files don't have a header row with field names.

File handling modes

When configuring an S3 connection for syncing, you choose how Polytomic should interpret the files in your bucket. There are three modes:

Individual files (default)

Each file in the bucket is treated as its own table. Polytomic lists the files at the configured bucket path and presents each one as a selectable schema. This is the simplest mode and requires no additional configuration.

Use this when: you have a small number of files, each representing a distinct dataset.

Files are time-based snapshots (single table)

Enable "Files are time-based snapshots" to merge multiple files into a single table. All files matching the configured format are concatenated together as if they were one dataset.

You must specify:

• Collection name -- the name of the resulting table
• File format -- which file type to read (CSV, JSON, or Parquet)
• Skip first lines (optional) -- number of lines to skip at the start of each CSV file

Use this when: your data pipeline writes periodic exports to S3, where each file is a snapshot of the same data at a different point in time (e.g., orders-2024-01-01.csv, orders-2024-01-02.csv).

Multi-directory patterns (directory tables)

Enable "Files are time-based snapshots" and then enable the multi-directory option to use a directory pattern. In this mode, Polytomic discovers tables automatically based on the directory structure of your bucket, using the pattern you provide.

You must specify:

• Tables glob path -- a directory pattern describing your bucket's file layout (see below)
• File formats -- which file types to read (you can select more than one)

Use this when: your bucket contains data for multiple tables organized into directories, or when you need to extract metadata from the file path and include it as columns in your synced data.

Directory patterns

Directory patterns tell Polytomic how to discover tables in your bucket and which files belong to each table. Patterns support three building blocks that can be freely combined:

• * (wildcard) -- matches exactly one path segment (one or more characters between / separators)
• {{ table() }} -- marks which part of the file path should be used as the table name
• {{ capture("field_name") }} -- extracts a value from the file path and adds it as a column in every synced record

You can use just wildcards, just expressions, or mix them together in the same pattern.

Wildcard-only patterns

The simplest patterns use * to match variable directory names. Each matching directory becomes a table, and all supported files within that directory are concatenated together.

Note: Patterns without any {{ }} expressions automatically get a trailing / appended if one is not present, since they match directories.

Example: given a bucket with these files:

region=us/latest/category=orders/data.csv
region=us/latest/category=events/data.csv
region=eu/latest/category=orders/data.csv

• Pattern region=*/latest/category=*/ discovers three tables: region=us/latest/category=orders/, region=us/latest/category=events/, and region=eu/latest/category=orders/.
• Pattern region=*/latest/ discovers two tables: region=us/latest/ and region=eu/latest/ (each containing all files within).

Using {{ table() }}

The table() expression specifies where in the path the table name appears. Each distinct value becomes a separate table.

Using {{ table() }} with wildcard extension

Use {{ table() }}.* when your files may have different formats or compression. The .* matches any file extension, and Polytomic automatically strips known data format and compression extensions to determine the table name.

Files without any extension (e.g., data/users) do not match {{ table() }}.*.

Using {{ capture() }}

The capture() expression extracts a value from the path and injects it as an additional column in every record read from that file. This is useful when metadata is encoded in the directory structure.

When capture() is used without table(), the table name defaults to the directory path of the matching file.

Important: Captured field names must not conflict with column names in the source files. If a capture name matches an existing column, Polytomic will return a configuration error.

Combining *, {{ table() }}, and {{ capture() }}

All three building blocks can be used together in a single pattern.

Wildcards vs. {{ capture() }}

Both * and {{ capture("name") }} match a path segment, but they differ in an important way:

• *: Matches any value but does not create a field. Use this to skip over variable path segments you don't need as data.
• {{ capture("name") }}: Matches any value and adds it as a column named name in every record. Use this when the path segment contains meaningful data.

Example:

# Wildcard -- ignores the date directory
exports/{{ table() }}/*
# Matches: exports/orders/20260301/data.parquet
# Table: orders, no extra fields

# Capture -- includes the date as a field
exports/{{ table() }}/{{ capture("date") }}
# Matches: exports/orders/20260301
# Table: orders, fields: date = "20260301"

What does and doesn't match

Patterns are anchored at the start of the file path. A file must match the entire pattern prefix to be included. Here are some examples of what matches and doesn't match for common patterns:

Pattern: /data/{{ table() }}.*

Pattern: /projects/{{ capture("id") }}/{{ table() }}.csv

Pattern: data/{{ table() }}/*/file.csv

How patterns interact with connection configuration

The bucket name field in the S3 connection accepts an optional path prefix (e.g., my-bucket/output/customers). When a prefix is specified, all pattern matching is relative to that prefix. For example, if your bucket is configured as my-bucket/exports and your pattern is /data/{{ table() }}.*, Polytomic looks for files at exports/data/users.csv, exports/data/orders.json, etc.

Patterns with a leading / are automatically normalized -- the leading slash is stripped before matching against object keys, since S3 keys don't start with /.

Pattern rules

• A pattern must not be empty.
• When using {{ }} expressions, the pattern must include at least one {{ capture() }} or {{ table() }}.
• Only one {{ table() }} is allowed per pattern.