> ## Documentation Index
> Fetch the complete documentation index at: https://docs.firebolt.io/llms.txt
> Use this file to discover all available pages before exploring further.
> 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.
# Incremental loading from Amazon S3
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](/guides/loading-data/working-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.
```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
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:
```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
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:
```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
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.
```text theme={"theme":{"light":"css-variables","dark":"css-variables"}}
┌────────────────────────────────────────────────┐
│ scheduler: run the same COPY every few minutes │
└───────────────────────┬────────────────────────┘
▼
┌────────────────────────────────────────────────┐
│ COPY INTO events ... WHERE │
│ $source_file_name LIKE 'events/dt=/%' │ ▶ 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.
```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
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:
```text theme={"theme":{"light":"css-variables","dark":"css-variables"}}
$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](#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](/guides/loading-data/working-with-external-tables) for the full list of
metadata virtual columns.
* [COPY FROM](/reference-sql/commands/data-management/copy-from) for the full syntax, including
metadata filtering in the `WHERE` clause.
* [Load data using SQL](/guides/loading-data/loading-data-sql) for an alternative
external-table-and-merge workflow that also updates changed rows.