Incremental loading from Amazon S3

Load every file from an Amazon S3 path into Firebolt exactly once, including files that arrive later, and keep each run cheap as the bucket grows.

A scheduled job watches an Amazon S3 prefix that grows over time and loads each new file into a Firebolt table exactly once: none missed, none loaded twice. The first section is the simplest version and works for any file layout; it lists the whole prefix on every run, which is fine until the bucket grows large. The second section keeps each run cheap by listing only recent files, which requires the files to be laid out by date.

This pattern identifies each file by its name, so treat files as immutable: write every object once and never modify or overwrite it. A file overwritten after it has been loaded is not re-ingested (its name is already recorded), so its new contents are silently missed.

This guide uses COPY FROM, which loads CSV, TSV, and Parquet. The same metadata columns and listing pruning also apply when files are read through an external table or a read_* table-valued function (read_parquet, read_csv, read_json), so the windowing carries over to those readers; JSON, for example, is read that way rather than with COPY FROM. The pattern relies on two per-file metadata columns that Firebolt populates for every external file (the leading $ marks a metadata virtual column; for the full list, see Using metadata virtual columns in Work with external tables):

Column	Type	Role
$source_file_name	TEXT	The object key without the bucket. A stable unique id per file: it is the deduplication key, and the value you filter on to restrict which keys are listed.
$source_file_timestamp	TIMESTAMPTZ	The object’s last-modified time.

Load new files exactly once

Record the identity of the file each row came from, so later runs can tell what is already loaded.

CREATE TABLE events (
    -- data columns, replace these with your actual schema
    id                     INTEGER,
    val                    TEXT,
    -- bookkeeping
    source_file_name       TEXT        NOT NULL,   -- from $source_file_name (unique per file)
    source_file_timestamp  TIMESTAMPTZ NOT NULL    -- from $source_file_timestamp
);

A file contributes many rows, all sharing the same source_file_name and source_file_timestamp, so an aggregating index grouped by those two columns holds one entry per file. The NOT EXISTS check then reads one row per file from the index, so it needs no explicit DISTINCT and never scans the base table:

CREATE AGGREGATING INDEX events_files ON events (source_file_timestamp, source_file_name);

Each run issues the same COPY FROM, keyed on the file name:

COPY INTO events (id, val, source_file_name $source_file_name, source_file_timestamp $source_file_timestamp)
FROM 's3://my_bucket/events/'
WITH TYPE = PARQUET PATTERN = '*.parquet' AUTO_CREATE = FALSE
WHERE NOT EXISTS (SELECT 1 FROM events e WHERE e.source_file_name = $source_file_name);

$source_file_name is the object key, a unique id for a file. The NOT EXISTS clause skips any file already in the table, so a repeated or retried run never duplicates a row, and every new file is picked up on the next run. Two rules make this reliable:

• Do not let two runs overlap. Each run reads the set of already-loaded files before it inserts, so two runs at once would both see a new file as absent and load it twice (COPY inserts rows and enforces no unique constraint on source_file_name). Serialize the job to at most one run in flight, for example with a scheduler concurrency limit of one or an external lock.
• “No new files” is reported as an error. When the WHERE clause matches nothing, COPY FROM returns No file found at URL: ... Check url, object pattern, and condition in WHERE clause. Treat this as a successful no-op meaning zero new files.

To load history that predates the job, run this same statement once as a backfill.

This version lists every object under events/ on each run. That is fine for a modest bucket. Once the history grows large, the next section removes that cost.

Scale to large buckets by pruning the listing

Listing the whole prefix costs work proportional to the number of files, so a job that runs forever does work proportional to n squared over its lifetime. S3 listing is a real cost factor: each ListObjects request is billed and returns object keys in sorted (lexicographic) order, and the only server-side filter it accepts is a common key prefix. So the one way to list less is to restrict the object name to a prefix, which works when the files you want share a leading one.

┌────────────────────────────────────────────────┐
│ scheduler: run the same COPY every few minutes │
└───────────────────────┬────────────────────────┘
                        ▼
┌────────────────────────────────────────────────┐
│ COPY INTO events ... WHERE                     │
│   $source_file_name LIKE 'events/dt=<day>/%'   │   ▶ list only recent prefixes in S3
│   AND NOT EXISTS (row already loaded)          │   ▶ drop already-loaded files
└───────────────────────┬────────────────────────┘
                        ▼
┌────────────────────────────────────────────────┐
│ rows for new files only, inserted once         │
└────────────────────────────────────────────────┘

What the layout must provide

A day’s files must share a prefix that the query can pin. Hive partitioning, one dt=YYYY-MM-DD/ directory per day, fits this naturally and is what the examples here use. More generally, three properties of the object keys make the pruning work:

• A date appears as a fixed, literal path segment, and it is the first thing that varies. Hive events/dt=2026-07-17/part-0.parquet, plain events/2026/07/17/..., and a events/2026-07-17-*.parquet filename prefix all qualify: the date sits at a known position with nothing variable before it. What matters is that a string like events/dt=2026-07-17/ is a genuine prefix of exactly that day’s keys (recall that $source_file_name is the key relative to the bucket, so the prefix includes the leading path). If another partition comes first, such as events/region=us/dt=2026-07-17/, a date alone is not a leading prefix: pin the earlier partitions too (one prefix per region and date) or the listing is not pruned.
• New files arrive under a recent date. The window lists recent date partitions, so a new file must land in one of them. Files written into old partitions after the fact are not seen by a windowed run; load them with a one-time backfill.

The windowed load statement

Add a prefix window to the same statement. Here it is today and yesterday, written with CURRENT_DATE so the statement stays fixed. Two days is enough for any job that runs at least once a day; the second day absorbs runs that straddle midnight and short listing delays.

COPY INTO events (id, val, source_file_name $source_file_name, source_file_timestamp $source_file_timestamp)
FROM 's3://my_bucket/events/'
WITH TYPE = PARQUET PATTERN = '*.parquet' AUTO_CREATE = FALSE
WHERE ( $source_file_name LIKE ('events/dt=' ||  CURRENT_DATE      :: TEXT || '/%')
     OR $source_file_name LIKE ('events/dt=' || (CURRENT_DATE - 1) :: TEXT || '/%') )
  AND NOT EXISTS (
        SELECT 1 FROM events e
        WHERE e.source_file_name = $source_file_name
          AND ( e.source_file_name LIKE ('events/dt=' ||  CURRENT_DATE      :: TEXT || '/%')
             OR e.source_file_name LIKE ('events/dt=' || (CURRENT_DATE - 1) :: TEXT || '/%') ));

The date prefixes limit the listing to those partitions, so each run lists only recent files. The NOT EXISTS subquery uses the same prefixes, so the dedup covers every file a run can list while reading only those partitions (one row per file, from the events_files index). The no-overlap and empty-run rules from the first section apply here too.

How the pruning works

Firebolt restricts the listing automatically when a filter pins a literal prefix of $source_file_name (LIKE 'prefix%', =, or IN (...)); an OR of prefixes lists each one. Two conditions must hold:

• The prefix must be a constant known when the query is planned. Literals, CURRENT_DATE and arithmetic on it, and query parameters all qualify, so they restrict the listing.
• The prefix must not contain LIKE wildcards. _ and % are wildcards, and only the text before the first one restricts the listing. Hive date paths are wildcard-free; if a path segment contains an underscore, escape it, for example LIKE 'my\_events/dt=2026-07-17/%'.

Express the window as prefixes: an OR of one LIKE prefix per day, which is the form the listing prunes on. A range bound such as $source_file_name >= 'events/dt=...' returns the same rows, but a range is not a prefix, so use the per-day prefixes.

Confirm it from the query plan

Run EXPLAIN (physical) on the statement and find the list_objects(...) entry for the scan. Its mandatory_prefixes argument is the set of prefixes Firebolt will actually list:

$0 = list_objects(url => 's3://my_bucket/events/', pattern => '*.parquet',
                  mandatory_prefixes => ['events/dt=2026-07-17/', 'events/dt=2026-07-16/'])

Those entries mean only the two day-prefixes are listed. If instead mandatory_prefixes is an empty list [] (or none), the filter was not turned into a prefix and the whole path is listed: check that the prefix is a plan-time constant, is free of LIKE wildcards, and is not a range. The same plan also shows the deduplication anti-join reading the events_files aggregating index rather than scanning the table.

Resume from the last loaded file, for irregular schedules

The fixed CURRENT_DATE window works as long as the job runs often enough that the window always covers the time since the previous run. If it can pause for longer than the window, for example during an outage or on an infrequent schedule, a fixed look-back skips the days between where it stopped and the current window. When that is possible, anchor the window to the newest file already loaded rather than to the wall clock:

1. Read the high-water mark: SELECT MAX(source_file_timestamp) FROM events (served by the events_files index, so it does not scan the table).
2. In the scheduler, expand it into the list of dates from that day (minus a small margin) through today.
3. Run the same COPY, supplying those dates in both the listing and dedup LIKE prefixes as query parameters.

This is the one place source_file_timestamp drives the window, so it assumes last-modified is close to the partition date; widen the margin in step 2 if they differ. Exactly-once still does not depend on it, since the dedup uses the same prefixes as the listing.

Step 3 uses parameters, not a subquery, on purpose. The prefix has to be a constant at planning time to prune the listing (see How the pruning works). A bound parameter is one; a prefix computed inline from a subquery, such as LIKE (SELECT MAX(source_file_timestamp) ... FROM events), is not, so it would return the right rows but list the whole prefix. Folding a subquery-derived prefix into the listing is a planned improvement.

Automate it

The data movement is entirely SQL; the automation around it is small and belongs in a scheduler such as cron or Apache Airflow. Run the COPY FROM on a timer, allow only one run at a time, and treat the “No file found” error as zero new files. With the CURRENT_DATE window the statement never changes; the resume-from-watermark variant adds one step before the COPY to read MAX(source_file_timestamp) and bind the derived date prefixes as parameters.

Related

• Work with external tables for the full list of metadata virtual columns.
• COPY FROM for the full syntax, including metadata filtering in the WHERE clause.
• Load data using SQL for an alternative external-table-and-merge workflow that also updates changed rows.