Most export features are built as a nightly job: at 02:00, write yesterday. It is the obvious shape, it demos well, and it is wrong in four ways that only appear in production.
- A partition that fails gets its next attempt in 24 hours.
- A re-export somebody asks for at 10am waits until tomorrow.
- Every tenant's export lands in the same hour, so the load is at its peak exactly when nobody is watching.
- A backfill is a separate code path — which means it is the path with the bugs, because it runs a hundred times less often than the one that works.
There is a better organising idea, and everything else follows from it.
A partition is a unit of work that can be redone
Model the export as a ledger of (dataset, day) pairs rather than as a schedule. Each pair has a state: never attempted, queued, running, completed, or failed with a next-attempt time. A sweep runs frequently — every fifteen minutes, say — asks which pairs are due, and does a bounded number of them.
The scheduled export, a ninety-day backfill, a retry after a bucket outage and a re-export queued by hand in the UI are then the same code path reaching the same row. That is what makes it impossible for any of them to double a row in the warehouse, and it is worth preserving through every future change to the feature.
Three rules keep the ledger honest:
- Claim the row before writing a byte. An overrunning sweep must not start a partition a previous sweep is still working on. A claim older than a few hours is assumed dead and reclaimed — that is the case where a worker was killed mid-upload.
- Cap the work per account per sweep. One tenant catching up on three months must not hold the pipeline while everyone else waits. Order queued work first, then oldest day, so a backfill fills forward rather than leaving a hole in the middle.
- Never export today. Today's partition is not closed. Writing it produces a file that has to be replaced tomorrow, and in the meantime somebody's dashboard shows a partial day as if it were a real one.
The commit marker is the whole reliability story
Object storage has no transactions. A run that dies halfway leaves real files in a real folder, and a loader reading that folder cannot tell them from a complete set.
The fix is a marker file written last. Write the data parts, delete any stale parts a longer earlier run left behind, and only then write a manifest. A reader now has one cheap test: if the manifest exists, the partition is complete and safe to load. If it does not, the next sweep will overwrite what is there.
Make the manifest carry its weight while you are at it — the schema, per-file row counts, byte counts and checksums, and a line saying where the rows came from. That last field answers the first question anyone ever asks about an export: "why does this day have fewer rows than I expected?"
The stale-part deletion matters more than it looks. A re-run that produces three parts where the previous run produced five leaves two orphans behind, and a wildcard load will happily read all seven. "Replace the partition" has to be literally true, not approximately true.
Layout details that decide whether anyone can load it
Two file-naming conventions are worth treating as non-negotiable.
Use Hive-style partition directories. A folder named dt=2026-09-05 is recognised as a partition column, with no configuration, by BigQuery, Athena, Redshift Spectrum, Spark and DuckDB. A folder named 2026-09-05 means the analyst declares every partition by hand.
Prefix data files and metadata files differently. Data as part-*, metadata with a leading underscore. Loaders read .../dt=.../part-*, so a manifest and a SQL script sitting in the same folder can never be parsed as data. Under a plain * glob, they will be — and the resulting error message will not mention the manifest.
One more: use an opaque account identifier in the path, not a sequential integer. An integer id is guessable and means nothing outside the platform that minted it.
Fix the schema, then treat it as a public API
Once a partition is loaded, its column names are in somebody's dbt models and somebody else's dashboard. Adding a column at the end is safe. Renaming, reordering or retyping one is a breaking change for every consumer, and it will be discovered weeks later by a number that looks slightly wrong.
The hard part is per-tenant custom fields, and the answer is to resist the obvious approach. Do not widen the table with each account's own attribute names: every tenant then has a different table and a shared schema becomes impossible. Put the open-ended parts in a single JSON column. It stays addressable in NDJSON and Parquet, warehouses can query into it, and a new attribute never breaks yesterday's model.
Two type details cause quiet damage:
- Keep dates separate from timestamps. Rendering a daily counter's date as midnight-in-some-timezone is how a statistic ends up attributed to the wrong day in a warehouse an hour to the left.
- Pass the schema explicitly to any native load, never auto-detect. Autodetection infers types per file, so a day where a column happened to be entirely null lands as a different type, and the loads start failing weeks later for no visible reason.
Say what a partition means
Not every dataset partitions the same way, and the difference has to be documented or it will be discovered by a wrong number.
- Events partition by when the event happened, taken from the event itself — not by when a batch was archived. Otherwise a late-arriving batch scatters a day across two partitions.
- Profiles and visitors are an incremental feed, not a daily snapshot: a record appears in every partition in which it was active, and the newest one holds its newest state. Current state in the warehouse is then a window function over the identifier ordered by last-seen, and stating that up front saves an entire support conversation.
- Daily statistics partition by the stat date of the row.
Generate the load SQL — it is the half people use
A bucket full of correct Parquet is still a support ticket if the analyst has to work out the column types, the partition column and the format options by hand. Getting that wrong is how a timestamp silently becomes a string in somebody's model.
Write a ready-to-run script beside each partition, generated from the same schema the files were written with — a COPY INTO for Snowflake, a LOAD DATA for BigQuery, a CREATE EXTERNAL TABLE for Athena or Trino. Because it comes from the same source as the data, the two cannot disagree. It is a starting point rather than a migration tool: it names a table, it does not manage one.
Where a native load is offered, make it replace a day rather than append to it. Appending doubles every row on the first retry, and the first retry always comes.
Whose bucket, and why it matters commercially
The last decision is not technical. Writing to the customer's own storage, with their credentials, means the storage bill, the retention policy and the residency question are all theirs. It answers the two objections that actually close enterprise deals — "we cannot get our data out" and "we do not want another copy of our data in a vendor's account" — and it costs a vendor nothing except the ability to charge for storage.
One practical note if you build it: test the connection by writing a small object and deleting it. A read-only key passes every other check and then fails at 03:00 on a partition nobody is watching.
See the datasets, formats and partition ledger on the Warehouse & Storage Export page, or the day-to-day questions the reporting module answers without a warehouse at all.