Caching Strategy

flAPI's caching layer is built on DuckLake — a snapshot-based table format over DuckDB. It is the flagship caching feature in flAPI and provides far more than a TTL cache: every refresh produces an immutable snapshot you can roll back to, expire on a retention policy, or use as a checkpoint for incremental merges.

Think of DuckLake caching like a versioned ledger: every refresh appends a new snapshot, the latest snapshot serves the API in milliseconds, and old snapshots are pruned by a retention policy you control.

Why DuckLake?

Data warehouses like BigQuery, Snowflake, and SAP are optimized for analytical workloads, not API serving:

Requirement	Warehouse	API Serving
Latency	2-10 seconds	< 5 ms
Concurrency	Low (10-100)	High (1000s)
Query Pattern	Few large queries	Many small queries
Frequency	Periodic	Continuous

DuckLake caching gives you warehouse-quality SQL semantics with serving-tier latency, plus:

• Snapshot isolation — readers always see a consistent table version.
• Time travel — query the cache as of any retained snapshot.
• Three refresh modes — full / append / merge, chosen automatically from your config.
• Retention & expiry — drop old snapshots by count or age.

The Global DuckLake Config

Caching is enabled per endpoint, but the DuckLake catalog itself is configured once in flapi.yaml:

# flapi.yaml
ducklake:
  enabled: true
  alias: cache                         # Catalog alias used in cache templates
  metadata-path: ./data/cache.ducklake # DuckLake metadata directory
  data-path: ./data/cache              # DuckLake data files directory
  retention:
    keep-last-snapshots: 10
    max-snapshot-age: 30d
  compaction:
    enabled: true
    schedule: '@daily'
  scheduler:
    enabled: true
    scan-interval: 5m                  # How often the scheduler checks endpoints

Without a ducklake: block, endpoint-level caching is inert.

Per-Endpoint Cache Configuration

A cache is declared on the endpoint YAML. All keys are real and verified against cache_manager.cpp:

# sqls/customers/customers-rest.yaml
cache:
  enabled: true
  table: customers_cache
  schema: analytics                # optional, default "main"
  schedule: 5m                     # 30s | 5m | 1h | 2d
  primary-key: [id]                # required for merge mode
  cursor:
    column: registration_date      # required for append / merge modes
    type: date                     # int | date | timestamp
  rollback-window: 2d
  retention:
    keep-last-snapshots: 5
    max-snapshot-age: 14d
  delete-handling: soft            # soft | hard
  template-file: customers_cache.sql   # optional custom refresh SQL

The Three Refresh Modes

flAPI does not have a strategy: key. The refresh mode is derived from the combination of cursor and primary-key, per CacheManager::determineCacheMode in cache_manager.cpp:

cursor	primary-key	Mode	What happens
absent	any	full	Cache table is rebuilt from scratch each refresh
present	absent	append	New rows past cursor.column are appended
present	present	merge	Inserts, updates and deletes reconciled by primary key + cursor

Full Refresh

Used when there is no cursor. The cache template runs a CREATE OR REPLACE TABLE against the DuckLake catalog. Good for small reference tables.

cache:
  enabled: true
  table: countries_cache
  schedule: 1h

Incremental Append

Add cursor and the engine switches to append mode. Best for append-only event-style data:

cache:
  enabled: true
  table: events_cache
  schedule: 5m
  cursor:
    column: occurred_at
    type: timestamp

Incremental Merge

Add both cursor and primary-key. flAPI merges inserts, updates and deletes:

cache:
  enabled: true
  table: customers_cache
  schedule: 1m
  primary-key: [id]
  cursor:
    column: updated_at
    type: timestamp

delete-handling: soft tombstones removed rows; hard deletes them physically.

Cache Template Variables

When a refresh runs, flAPI renders the cache SQL through the SQL template processor and injects a cache.* context. Verified against sql_template_processor.cpp:

Variable	Description
{{cache.catalog}}	DuckLake catalog alias from ducklake.alias
{{cache.schema}}	Cache schema (default main)
{{cache.table}}	Cache table name
{{cache.schedule}}	Configured refresh schedule
{{cache.mode}}	Refresh mode: full, append, or merge
{{cache.snapshotId}}	Current snapshot ID
{{cache.snapshotTimestamp}}	Current snapshot timestamp
{{cache.previousSnapshotId}}	Previous snapshot ID (for incremental)
{{cache.previousSnapshotTimestamp}}	Previous snapshot timestamp
{{cache.cursorColumn}}	cursor.column value
{{cache.cursorType}}	cursor.type value
{{cache.primaryKeys}}	Comma-separated primary key columns

Example: DuckLake Merge Template

-- sqls/customers/customers_cache.sql
CREATE OR REPLACE TABLE {{cache.catalog}}.{{cache.schema}}.{{cache.table}} AS
SELECT
    id,
    name,
    email,
    segment,
    registration_date,
    CURRENT_TIMESTAMP AS cache_updated_at,
    '{{cache.snapshotId}}' AS cache_snapshot_id
FROM read_parquet('{{conn.path}}')
ORDER BY registration_date DESC;

The example above is the real merge-mode template shipped with the customers example (examples/sqls/customers/customers_cache.sql).

Cost Math

A typical 50,000-requests/day workload against BigQuery (10 GB scan @ $0.05/query):

Refresh schedule	Daily cost	Monthly cost
Every 5 minutes	$14.40	$432
Every 15 minutes	$4.80	$144
Every hour	$1.20	$36
Every 6 hours	$0.20	$6
Daily	$0.05	$1.50

Direct access for the same workload costs $2,500/day. Caching trades freshness for cost in a predictable, snapshot-aware way.

Retention, Rollback & Audit

DuckLake snapshots are first-class. flAPI exposes:

• retention.keep-last-snapshots — keep N most recent snapshots per table.
• retention.max-snapshot-age — drop snapshots older than e.g. 30d.
• rollback-window — keep snapshots queryable for time-travel during this window.
• Audit table — <catalog>.audit.sync_events records every refresh with sync_type, status, message, timestamps and duration. Created automatically by CacheManager::initializeAuditTables.

Expiry is implemented by calling ducklake_expire_snapshots(...) against the catalog.

When to Use DuckLake Caching

Ideal

• High-frequency reads of moderately fresh data (dashboards, AI tool calls).
• Backends with per-query cost (BigQuery, Snowflake, SAP).
• Workloads that benefit from snapshot isolation (audit, reproducibility).

Not Ideal

• Sub-second freshness — refresh overhead is too high.
• Single-query workloads — no amortization.
• Datasets that don't fit on the flAPI host's local disk.

Next Steps

• Caching Setup Guide: Step-by-step DuckLake configuration
• How It Works: Understand the 3-step flAPI process
• Architecture Deep Dive: Technical details of the caching layer
• SQL Templating: Cache and API templates
• BigQuery Example: Complete caching implementation