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

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 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) 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 (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) 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

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

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

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	Implements ExternalPool, cache lifecycle management, snapshot loading, and name sanitization
crates/dbx-core/src/external/types.rs	Defines ExternalTableSnapshot, ExternalTableRef, and CacheState enums
crates/dbx-core/src/schema.rs	Provides DuckDB-specific helper functions for table and column discovery
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 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.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}