External-table metadata cache and refresh

Last updated: 6 Oct 20265 min read

tutorial

An external query can be slow or incomplete before it scans a single row: its engine first has to discover a trustworthy file set.

Locate the planning bottleneck

Separate catalog lookup, object listing, file-footer reads and actual row scan in query traces. When millions of objects sit behind a broad prefix, listing may dominate latency even if the filter selects one hour of data. Record files considered, files opened, metadata age and scan bytes for the same predicate. An index that speeds partition lookup does not automatically reduce physical bytes scanned. Partition planning is a separate design decision.

Treat freshness as a contract

A cache of paths, partition values and statistics cuts remote metadata calls. It also represents an earlier view of the object store. Define an allowed metadata age for each consumer and record the latest successful refresh generation. The query must either use metadata within that window, refresh before reading or fail closed when the required freshness cannot be met. Do not report a successful empty result when the relevant files have merely not been discovered.

Refresh after a published batch

An ingest job should write data files, validate them and publish its own batch marker before requesting metadata refresh. For predictable arrivals, refreshing only changed prefixes can avoid a full inventory. Make the refresh idempotent and bind its success record to the batch marker. If the job fails halfway through a listing, leave the prior usable cache intact and expose the lag. A refresh timestamp without a completed generation is weak evidence.

Distinguish cache from table snapshot

A metadata cache is a planning aid, not an atomic table commit. Concurrent object writes and deletions can still make an ordinary external-table listing inconsistent with another engine or with a previous query. For a transactional lakehouse table, pin its committed snapshot and let the table format define file membership. Snapshot publication supplies a stronger boundary than refreshing a list of keys.

Measure the exchange

Compare cold and warm plans with the same predicate and data version. Count catalog calls, listed objects, refresh work, median planning time and p95 end-to-end latency. A tighter freshness budget can force more listings and higher object-store cost; a longer one may miss a new batch. Keep both numbers visible. Test a changed table location too: an old cache can stay internally consistent while pointing at the wrong prefix.

Implementation

from datetime import datetime, timedelta, timezone

cache = {"generation": "telemetry-47", "refreshed_at": datetime(2026, 9, 27, 8, tzinfo=timezone.utc),
         "paths": ("hour=08/events-47.parquet",)}
published_generation = "telemetry-48"
query_time = datetime(2026, 9, 27, 10, tzinfo=timezone.utc)

def may_serve(metadata, required_generation, now, maximum_age):
    return (metadata["generation"] == required_generation
            and now - metadata["refreshed_at"] <= maximum_age)

assert not may_serve(cache, published_generation, query_time, timedelta(hours=3))
cache.update(generation=published_generation, refreshed_at=query_time,
             paths=("hour=08/events-47.parquet", "hour=09/events-48.parquet"))
assert may_serve(cache, published_generation, query_time, timedelta(minutes=47))

Performance and operating cost

Checking one cache generation is O(1) time and space; rebuilding an inventory is at least O(F) metadata work for F discovered files unless the platform supports a narrower refresh. Caching reduces repeated listings but adds refresh jobs and lag monitoring. A scan can still be expensive after a fast plan if file layout and predicate pruning are poor.

Common Mistakes

Do not equate a warm metadata cache with an atomic data snapshot.
Do not call an empty query correct when a new partition was never discovered.
Do not refresh before the writer finishes and publishes its batch.