DuckDb and Dotnet/EFCore: Tiered storage (Hot + Cold Archive)

Hot DuckDB tables + cold Parquet archive — keep a year of data hot, offload the rest to cheap storage, and query it all as one without leaving EF Core.

A follow-up to *“DuckDB for dotnet: Columnar Analytics using EF Core” *~14 min read

DuckDB behind a native Entity Framework Core provider gives you LINQ in and vectorised OLAP out — SaveChanges, Parquet queried in place, migrations. But an embedded database has a physical limit: the file keeps growing. Events, telemetry, invoices, audit logs accumulate forever, yet the part you touch day-to-day is small — last week, this month, maybe the trailing year.

Tiered storage (shipped in 1.2.0) closes that gap:

Keep recent data hot in the writable DuckDB file, roll older data out to hive-partitioned Parquet, and query the whole history as if it never moved.

Simple to state; the work is making it correct — crash-safe, relational, and pleasant from EF Core.

A memory hierarchy for your data

The working set is small and hot; the archive is large, cold, and read-mostly. So put the hot set on the fastest writable medium (the DuckDB file) and the cold set on cheap, columnar, immutable storage (Parquet, on disk or object storage) — with one logical view over both.

The whole design turns on one stored value: the watermark — a timestamp, one per dataset, that marks the boundary.

The watermark isn’t inferred — you set it by running an archive job with a cutoff, typically a rolling now − 1 year. Each run peels the newly-aged period off the hot table, writes it to Parquet, and advances the watermark. Stop running the job and nothing moves. It's scheduled maintenance, not magic.

One table first

The provider already read Parquet in place ([FromParquet] → read_parquet(...)). Tiering adds the other half: writing the cold side and stitching the two together. For a single table, configure once and run the offload on a schedule:

// Roll data older than a year out to Parquet, monthly partitions.
var result = await db.Database.ArchiveTierAsync<Event>(DateTime.UtcNow.AddYears(-1));

That one call does four things, and the order is the whole crash-safety story:

1. Align the cutoff down to the partition boundary (a month cutoff snaps to the 1st), so a period is never split across two runs.
2. Copy the aged rows to Parquet, foldered year=2024/month=03/….
3. Advance the watermark in a tiny control table.
4. Delete the copied rows from the hot table, then checkpoint.

The unified read is a generated DuckDB view:

CREATE OR REPLACE VIEW events_tiered AS
  SELECT * FROM events WHERE ts >= (SELECT watermark FROM __tier_control …)
UNION ALL BY NAME
  SELECT * FROM read_parquet('archive/events/**/*.parquet', hive_partitioning=true)
    WHERE ts < (SELECT watermark FROM __tier_control …);

The invariant that makes it safe: the view filters the hot side to ts ≥ watermark and the cold side to ts < watermark. A row is never on both sides — even in the window between the Parquet copy and the delete. If the process dies mid-archive, reads stay correct and the next run self-heals.

Pair that with an idempotent, partition-scoped COPY … OVERWRITE_OR_IGNORE — re-running the same cutoff rewrites exactly the partitions it touched, no duplicate files — and the offload is safe to put on a cron and forget.

But data is relational

Nobody archives a lone Event table. They archive an Invoice — which owns InvoiceLines, which sometimes own LineAllocations: an aggregate spread across FK-linked tables. Collapsing it into a single JSON column is document-database thinking, and it throws away the reason you're on a relational engine.

The unit of tiering isn’t a table. It’s the aggregate — and the root’s date governs all of it.

A line has no date of its own; its tier is inherited from its invoice. So you declare the aggregate boundary — the root’s timestamp property — and one watermark on the root governs the whole graph:

modelBuilder.ToTieredStore<Invoice>(i => i.InvoiceDate, "/data/archive/invoices", TierGranularity.Month)
    .WithReadModel<InvoiceReport>()
    .Including<InvoiceLine>(i => i.Lines, line => line
        .WithReadModel<InvoiceLineReport>()
        .Including<LineAllocation>(l => l.Allocations, a => a.WithReadModel<AllocationReport>()));

That first argument — i => i.InvoiceDate — is the pivotal choice. Each aggregate root names its own timestamp property, and it's chosen per aggregate: Invoice can tier on InvoiceDate, Order on PlacedUtc, AuditEvent on OccurredOn — each independent, each with its own archive path and boundary. That property is the single thing every hot/cold decision for the aggregate is measured against.

.Including(...) marks the true children — the ones that archive with the root. Anything you don't include (say, an InvoiceLine → Product reference) stays hot. An archived aggregate is a snapshot: it keeps foreign keys into still-live tables, which can dangle if a referenced row is later deleted. For financial history that's exactly right — but worth stating out loud.

Why not shared-type entities

The tempting design reuses one CLR type for both tiers via EF’s shared-type entities. It works for a lone table, then breaks the moment you add a relationship for the aggregate:

// The entity type 'InvoiceLine' cannot be added to the model because
// its CLR type has been configured as a shared type.

You can’t hang a normal relationship off a shared-type entity when the child type is also shared, and no ordering trick fixes it. The design that works splits the two worlds by type:

Hot — your ordinary EF Core entities. Regular classes with real relationships. You use them exactly as always: SaveChanges, cascade, Include. Tiering never touches them.

Cold — a keyless read-model per table, mapped to the union view. Keyless is EF Core’s HasNoKey(): no primary key, so the type is read-only — no change tracking, no SaveChanges — and it carries no navigations. That fits a projection over immutable history exactly, and it's the reason a cross-table report joins on the foreign-key column instead of navigating invoice.Lines.

Your write model stays pristine, idiomatic EF Core; the cost is one small read-model POCO per tiered table. Since cold access is reporting-only — you run analytics over history, you don’t rehydrate a 2021 invoice into a live object graph — that’s a fair trade.

Archiving the aggregate

Archiving is a topological walk of the aggregate tree. Each child is copied by joining up to the root, so the boundary and the partition columns come from the root’s date even though the child has none of its own:

COPY (SELECT l.*, year(i.InvoiceDate) AS year, month(i.InvoiceDate) AS month
      FROM invoice_lines l JOIN invoices i ON l.InvoiceId = i.Id
      WHERE i.InvoiceDate >= :from AND i.InvoiceDate < :cutoff)
TO 'archive/invoice_lines' (FORMAT PARQUET, PARTITION_BY (year, month), OVERWRITE_OR_IGNORE);

Deletes then run leaf → root in a single transaction, so foreign keys stay satisfied the whole way down. One watermark, one atomic cleanup, the aggregate moved as a unit.

A child is hot if and only if its root is hot

The invariant has to hold transitively: a child row is hot only if its root is on the hot side of the watermark — a semijoin that chains up the parent links.

SELECT * FROM invoice_lines
  WHERE InvoiceId IN (SELECT Id FROM invoices WHERE InvoiceDate >= :watermark)   -- hot
UNION ALL BY NAME
  SELECT * EXCLUDE (year, month) FROM read_parquet('archive/invoice_lines/**/*.parquet', …);  -- cold

That “root is hot” rule also gives the crash-window guarantee for free: if an archive dies after copying but before deleting, the orphaned hot children are excluded because their now-archived root no longer qualifies as hot. For very deep aggregates where the per-query semijoin isn’t worth it, .WithoutHotChildFilter() trades the transient-crash guard for a plain, faster child view.

Schema evolution

The cold branch is SELECT * EXCLUDE (year, month), not an explicit column list. Add a column, regenerate the view, and UNION ALL BY NAME fills it with NULL for the older Parquet files that predate it, instead of throwing. Old archives stay readable through a schema change — for a multi-year archive, a requirement, not a nicety.

Guardrails

Model validation rejects, up front, the handful of ways this goes wrong — each with a clear message at model-build time:

• A property mapped to the reserved partition column names year/month/day.
• A declared child without a single-column foreign key to its parent.
• A read-model whose column doesn’t exist on its source table.
• Two aggregates pointed at overlapping archive directories (their globs would read each other’s files).

Cold storage on S3 and Azure

The cold tier doesn’t have to live on local disk. Point the archive at an s3:// URL — or gcs://, r2:// (all via DuckDB's httpfs extension), or azure:///abfss:// (via the separate azure extension) — and DuckDB reads and writes the Parquet there directly. Recent data stays in the local .duckdb file; years of history sit on object storage at a few cents a gigabyte; the union views span both.

modelBuilder.ToTieredStore<Invoice>(i => i.InvoiceDate,
    "s3://my-bucket/archive/invoices", TierGranularity.Month)   // cold tier lives on S3/Azure
    .WithReadModel<InvoiceReport>()
    .Including<InvoiceLine>(i => i.Lines, l => l.WithReadModel<InvoiceLineReport>());

Object storage has no atomic “replace this directory” operation, so does the crash-safe archive still hold? I ran the whole thing against MinIO: partitioned writes, incremental month-by-month appends, re-running the same cutoff after a simulated crash, reads with partition pruning. Every invariant survived — a re-run overwrites exactly the same object keys, incremental writes leave older partitions untouched, and the tiered views return full history with no duplicates. The same partitioned-COPY-and-read_parquet pattern checks out on Azure too (verified against Azurite) — via DuckDB's azure extension rather than httpfs.

Two things to know. Setup is a connection interceptor that loads httpfs and your credentials — and because the provider opens its connections through EF Core, it runs for the archive operations too. And only retention differs: ArchiveTierAsync runs against S3 exactly as on local disk, but PurgeArchiveOlderThan is the one exception — it throws for a remote archive, since object stores can't delete files through DuckDB, and points you at an S3 lifecycle rule instead, which (given the hive-partitioned layout) is the idiomatic way to expire old data anyway. Reads stay cheap: partition pruning and Parquet row-group statistics fetch only the byte ranges a range-scoped report needs.

End to end

Model, a scheduled offload, and reports that span hot and cold:

// 1. Configure once. Hot entities are your normal EF Core model.
modelBuilder.ToTieredStore<Invoice>(i => i.InvoiceDate, archivePath, TierGranularity.Month)
    .WithReadModel<InvoiceReport>()
    .Including<InvoiceLine>(i => i.Lines, l => l.WithReadModel<InvoiceLineReport>());

// 2. On a schedule (single-writer: run it from the writing process).
await db.Database.ArchiveTierAsync<Invoice>(DateTime.UtcNow.AddYears(-1));
db.Database.PurgeArchiveOlderThan<Invoice>(DateTime.UtcNow.AddYears(-7));  // retention

// 3. Report across all of history — hot + cold. Most reports need no join:
var invoicesByYear = db.InvoiceHistory
    .GroupBy(i => i.InvoiceDate.Year)
    .Select(g => new { Year = g.Key, Count = g.Count() });

// Read-models are keyless (no navigations), so a cross-table report joins on the FK column:
var revenueByYear = db.LineHistory
    .Join(db.InvoiceHistory, l => l.InvoiceId, i => i.Id, (l, i) => new { i.InvoiceDate.Year, l.Amount })
    .GroupBy(x => x.Year)
    .Select(g => new { Year = g.Key, Revenue = g.Sum(x => x.Amount) });

Notice ArchiveTierAsync<Invoice> takes only a cutoff — no path. The where (the archive location) and the which date (the timestamp property) live in the ToTieredStore config, resolved by the root type; the call supplies only the when. Each table lands under <archivePath>/<table>/year=…/month=…/, and the views read back from that same path — so reads and writes never disagree on the location.

Neither report knows or cares that half those invoices are Parquet files. DuckDB prunes the partitions it doesn’t need; EF hands you objects. And you can prove the split is clean without trusting the framework: count the hot base table, count read_parquet over the archive glob, and the tiered view equals their sum — hot + cold, no double-count, no gap.

Run the demo

A runnable sample tiers two independent roots — Invoice on InvoiceDate and AuditEvent on OccurredOn, each on its own cutoff — and reports across hot + cold, on local disk or S3:

dotnet run --project samples/TieredStorage          # cold archive on the local filesystem

# S3 mode: the sample ships a compose file that starts MinIO and creates the bucket, then:
docker compose -f samples/TieredStorage/docker-compose.yml up -d
dotnet run --project samples/TieredStorage -- s3

After a run you can see the two stores directly: the hot rows are BASE TABLEs in the .duckdb file, the cold rows are hive-partitioned Parquet in the archive, and EXPLAIN SELECT * FROM Invoices_tiered shows a UNION of a table scan and a read_parquet — the union made structural.

When to use it

Good fit: time-series, events, telemetry, audit logs, financial records — anything with a small hot working set, a large read-mostly tail, and a natural date to tier on. Bonus when the archive doubles as an interchange format (that Parquet is readable by Spark, Polars, Athena, or DuckDB anywhere).

Not the tool: data you frequently mutate after it ages (cold is a read-only snapshot), workloads without a temporal boundary, or anything needing multi-writer OLTP concurrency — DuckDB is single-writer by design, and so is the archive job.

DuckDB.EFCoreProvider 1.2.0 is on NuGet. Docs: docs/TIERED-STORAGE.md. Runnable sample: samples/TieredStorage (add -- s3 for the object-store demo). Built for EF Core 10 on .NET 10.