# Architecture of DBX's External DuckDB Cache for Instant File Previews

> Discover the architecture of DBX's external DuckDB cache. Learn how it materializes local table snapshots via ExternalPool to provide instant file previews and eliminate network latency.

- Repository: [skyler/dbx](https://github.com/t8y2/dbx)
- Tags: architecture
- Published: 2026-07-04

---

**DBX implements an in-memory DuckDB cache for each external data source, managed by the `ExternalPool` type, which materializes table snapshots locally to eliminate network latency for preview queries.**

DBX implements a sophisticated caching layer to provide instant file previews for external data sources like JDBC and Presto. This architecture centers on an in-memory DuckDB connection that acts as a fast, read-only mirror of remote tables. By decoupling the UI from direct external driver calls according to the t8y2/dbx source code, DBX eliminates network latency for preview queries while maintaining data freshness through asynchronous cache refreshes.

## Core Components of the Cache Architecture

The architecture is built around several key Rust types defined in [`crates/dbx-core/src/external/duckdb_cache.rs`](https://github.com/t8y2/dbx/blob/main/crates/dbx-core/src/external/duckdb_cache.rs) and related files.

### ExternalPool and CacheState

The **`ExternalPool`** struct is the central manager, holding the external source connection, a shared DuckDB `Connection`, a `CacheState` enum, and a mapping of sanitized table names to `ExternalTableRef` objects.

The **`CacheState`** enum tracks the cache lifecycle through three states:

- `Empty` – Initial state, no data loaded
- `Loading` – Currently refreshing from the external source
- `Fresh` – Ready for queries with current data

### ExternalTableSnapshot and Type Definitions

The **`ExternalTableSnapshot`** struct (defined in [`crates/dbx-core/src/external/types.rs`](https://github.com/t8y2/dbx/blob/main/crates/dbx-core/src/external/types.rs)) contains the column definitions, row data, and originating table reference needed to reconstruct tables in DuckDB. This snapshot serves as the portable data transfer object between the external source and the local cache.

## How the Cache Lifecycle Works

### Cache Initialization and Warm-Up

When a connection to an external source opens, DBX creates an `ExternalPool` with an in-memory DuckDB connection via `duckdb::Connection::open_in_memory`. The cache warm-up process begins when `ExternalPool::refresh_cache` is triggered, typically on the first UI request or manual refresh.

According to the source code in [`duckdb_cache.rs`](https://github.com/t8y2/dbx/blob/main/duckdb_cache.rs) (lines 117-163), the refresh process:

1. Marks `cache_state` as `Loading`
2. Queries the external driver for available tables via `list_tables`
3. Fetches an `ExternalTableSnapshot` for each table via `load_table`

### Snapshot Materialization Process

The **`load_snapshot_to_duckdb`** function (lines 7-63 in [`duckdb_cache.rs`](https://github.com/t8y2/dbx/blob/main/duckdb_cache.rs)) handles the actual data transfer. This function:

- Drops any existing table with the same name
- Builds a `CREATE TABLE` statement from the snapshot's column definitions
- Inserts rows via a prepared statement
- Returns early for empty snapshots (schema-only tables)

### Name Sanitization and Collision Handling

The **`sanitize_table_name`** function (lines 84-94) ensures external table names become valid DuckDB identifiers. It replaces non-alphanumeric characters with underscores and prefixes names starting with digits. If sanitization creates collisions, DBX appends numeric suffixes (`_2`, `_3`, etc.) to maintain unique mappings in the `table_map` HashMap.

## Implementation Examples

### Creating and Refreshing the Cache

```rust
use std::sync::Arc;
use dbx_core::external::{traits::ExternalTabularSource, duckdb_cache::ExternalPool};
use duckdb::Connection;

// `my_source` implements `ExternalTabularSource` (e.g. a JDBC driver)
let my_source: Arc<dyn ExternalTabularSource> = // ...;

// Create an in-memory DuckDB connection wrapped in a Mutex
let duckdb_conn = Arc::new(std::sync::Mutex::new(
    Connection::open_in_memory().expect("failed to open DuckDB")
));

let pool = ExternalPool::new(my_source.clone(), duckdb_conn.clone());

// Warm-up the cache (runs asynchronously)
tokio::spawn(async move {
    pool.refresh_cache().await.expect("cache refresh failed");
});

```

### Loading a Snapshot into DuckDB

```rust
use dbx_core::external::duckdb_cache::load_snapshot_to_duckdb;
use dbx_core::external::types::ExternalTableSnapshot;

// Assume `snapshot` was obtained from the external source
let conn = duckdb::Connection::open_in_memory().unwrap();
load_snapshot_to_duckdb(&conn, &snapshot)
    .expect("failed to materialize snapshot");

```

### Querying the Cached Table for Previews

```rust
let conn = pool.cache.lock().unwrap(); // lock the DuckDB connection
let preview_sql = format!("SELECT * FROM \"{}\" LIMIT 100", sanitized_name);
let mut stmt = conn.prepare(&preview_sql).unwrap();
let rows = stmt.query_map([], |row| row.get::<usize, String>(0)).unwrap();
for r in rows {
    println!("{}", r.unwrap());
}

```

## Key Source Files

| File | Purpose |
|------|---------|
| [`crates/dbx-core/src/external/duckdb_cache.rs`](https://github.com/t8y2/dbx/blob/main/crates/dbx-core/src/external/duckdb_cache.rs) | Implements `ExternalPool`, cache lifecycle management, snapshot loading, and name sanitization |
| [`crates/dbx-core/src/external/types.rs`](https://github.com/t8y2/dbx/blob/main/crates/dbx-core/src/external/types.rs) | Defines `ExternalTableSnapshot`, `ExternalTableRef`, and `CacheState` enums |
| [`crates/dbx-core/src/schema.rs`](https://github.com/t8y2/dbx/blob/main/crates/dbx-core/src/schema.rs) | Provides DuckDB-specific helper functions for table and column discovery |
| [`crates/dbx-core/src/query_execution_sql.rs`](https://github.com/t8y2/dbx/blob/main/crates/dbx-core/src/query_execution_sql.rs) | Generates SQL for instant previews via `build_dropped_file_preview_sql` |

## Summary

- **In-memory DuckDB**: Each external source gets a dedicated `Connection::open_in_memory` instance acting as a fast, local mirror
- **ExternalPool management**: The `ExternalPool` struct coordinates cache states, table mappings, and refresh operations according to the t8y2/dbx source code
- **Snapshot-based loading**: Data transfers via `ExternalTableSnapshot` objects materialized through `load_snapshot_to_duckdb`
- **Automatic name sanitization**: `sanitize_table_name` prevents identifier collisions while maintaining stable table references
- **Async refresh pattern**: `refresh_cache` runs asynchronously, allowing the UI to query stale data or wait for fresh loads without blocking

## Frequently Asked Questions

### How does DBX handle table name collisions in the DuckDB cache?

When external table names sanitize to the same identifier, DBX automatically appends numeric suffixes (`_2`, `_3`, etc.) to create unique DuckDB table names. The `sanitize_table_name` function in [`duckdb_cache.rs`](https://github.com/t8y2/dbx/blob/main/duckdb_cache.rs) maintains a mapping between these sanitized names and the original `ExternalTableRef` objects, ensuring UI components can still reference the correct source tables while querying the cache.

### What happens when the cache is empty or stale?

The `CacheState` enum tracks cache availability through `Empty`, `Loading`, and `Fresh` states. When empty or explicitly refreshed, `refresh_cache` marks the state as `Loading` and asynchronously fetches fresh snapshots from the external source. The UI can check this state to show loading indicators or decide whether to wait for fresh data or serve cached results immediately.

### Why does DBX use DuckDB specifically for external table previews?

DuckDB provides an embeddable, in-memory analytical database that requires no external server process, making it ideal for temporary caching. Its `Connection::open_in_memory` method creates isolated, fast SQL execution environments per external source, while its compatibility with standard SQL dialects allows DBX to run `SELECT * FROM table LIMIT 100` style preview queries efficiently without the overhead of network round-trips to remote JDBC or Presto drivers.

### How does the snapshot materialization process handle schema-only tables?

The `load_snapshot_to_duckdb` function handles empty snapshots by returning early after creating the table DDL. This allows DBX to cache table schemas even when no data rows are present, ensuring the UI can still display column structures and metadata for preview purposes without attempting to insert empty result sets into DuckDB.