Simplicity of a Database, but the Speed of a Cache: OLAP Caches for DuckDB

OLAP Cubes Are Dead?

Traditionally, you would add an OLAP cube, or modern OLAP systems to speed up this process if you need sub-second response times. But these are harder to maintain and especially the ingestion part typically needs data engineering as schemas are changing, data will be wrong, and all the plumbing that data engineers do will happen at some point.

But OLAP cubes are essentially a cache too. But what we want is a cache that takes us less effort to build. The perfect examples are DuckDB and MotherDuck, which are quick and easy to use. DuckDB is a couple of MBs binary that can run anywhere, even in the browser. MotherDuck lets you scale and share it across by just changing the path to md: instead of local DuckDB.

Again, we want these three things mostly from a cache:

• Speed: Fast answers in our frontend-facing dashboards, reports and web apps.
• Convenience: Instead of materializing BI queries manually.
• Utilization: It can be easy to run anywhere, to move. A little like a Swiss knife that can do multiple things, simple and easy.

Customer-facing or business-critical data, meaning it must be fast. To build an additional layer with an OLAP system has the downside of being more expensive. An additional OLAP layer needs an additional ingestion step with data pipelines and engineering. On the other hand, an advantage of adding a simple cache is simplicity, no extra work needed (everything happens under the hood).

Different Kinds of Caches (Different Levels)

Let's list the different levels and compare them. Caches can be on different stages along the ETL process.

If we look at the data flow of a data engineering project, we can persist at different levels. The most effective is the closer we are to the visualization, the frontend the user is using. Caching before will only speed up the pipeline and nightly batch job, but not the actual dashboards as they would not profit from that cache earlier in the process.

Potential caching spots, from the customer-facing side, typically right where the visualization happens, to logical and different temperatures of caching:

• Data Apps: application-level caching
  • BI Tools: built-in caches
  • Notebooks, frontend web apps
  • WASM: Open standard for executing binary code in the web and web browser, allowing developers to leverage single binary databases like SQLite or DuckDB. Allowing for more advanced caches directly in the browser while getting the instant speed of DuckDB, created for analytics. E.g. Evidence is using this technology, powering their universal SQL engine built in the browser.
• Hot Cache: Typical application of hot caches in the data warehouse realm is an ODS (Operational Data Store) where the data is prepared for daily and fast consumption when the core data warehouse is too slow as it has too much historical data, and the source database can't be queried. Hot cache is very generic, and any data that is cached, and what we talk about here, could be called hot cache. Another example is message queue that stores data short term (weeks).
• SQL intermediate storage: Probably the most widely used are persistent SQL-based tables. These are tables we either persist as materialized views or executed dbt models. They work best at the data mart level where we prepare and aggregate data in the right granularity for fast and convenient consumption.
• Logical Caches:
  • Virtualization and Federation: Not physically stored, but logically joined data tables across different sources, which are then cached in data virtualization tools like Trino, Presto, Dremio.
  • Semantic Layer or OLAP Cubes: Typically logical caches as well as we model the data inside a logical model, and then the semantic layer optimizes cache for potential and actual queries. Caching queries and aggregate data efficiently and optimized for consumption.
• Cold Cache: Data Lakes are not really considered a cache, but I'd say we are caching dbt results, old results as backups and even active data to it. Usually we use another technology to warm up this data for fast consumption with MotherDuck, Starlake, and others.
• Zero-copy ETL: DuckDB, Apache Arrow and other approaches that can be used as an intermediate utility to query any data in a fast manner, or zero-copy clone.

I'm sure these layers are not 100% distinct, and there are more categories, but I'd say these are the major ones and they give us a good overview of how to look at caching more broadly, and especially how to apply this for OLAP caches.

The History of Caching BI Workloads

Besides the different levels, we can also compare two decades back how caching has been implemented differently over time.

As optimizing cache for BI workloads has been one of the most complex problems for a long time, we can take inspiration from it. If caching was solved properly in the past, powering analytics hugely, let's save this work and see how they implemented an additional layer of persisted data with caches over the years.

The chronological history, though not respecting every detail, could go something like this:

It's interesting how the pendulum is swinging forth and back a couple of times from being on the server side to client side to back and in between. From MV, One-Big-Table (OBT) on server-side data warehouses to bringing the data directly to the web application (e.g. WASM), no caching needed as data is super fast available with no latency, or not caching at all with a zero-copy layer with DuckDB and reading super fast with client-powered hardware.

But we can say, to this day, it remains challenging to cache your data independently and in best cases automated. Caching means constantly duplicating data, storing it optimally, and updating data in case the source changes. However, because of the significant outcomes, we still use it in every data engineering solution. Also check out the history of general architecture in data that Hannes Mühleisen was presenting, which gives a lot of insights on how the architecture has not changed much from 1985 to 2015 with adding cloud servers, but shifting more to the clients and small data as we have more powerful clients again.

Key Insights: Positioning, Metadata Management, Freshness Strategies

Key here is also cache positioning. For example, a semantic layer lives between the DWH and the customer, but we might have another web app cache before. So where we should use a cache is always an important question.

Potentially equally important is to query data the fastest way. For this we need a lot of metadata on how our data is stored, what indices we have, what partitions, how wide our tables are, how many rows etc. In a traditional database this is taken care of for us in a declarative way: with SQL!

It's done with indices and even more so with a query planner. Each database has one that interprets the SQL query and tries to find the fastest possible way based on existing metadata it has to query this data without a full table scan, or avoiding other traps that might take an order of magnitude longer to return the data. Metadata management if you will.

Such a query planner also deals with statistics from indices that already tackle probably the biggest challenge of caching, data freshness (vs. staleness). Meaning can I trust the statistics enough to not do a full table scan and return it or is the data outdated and I need to re-read the full table or column.

Strategies and terms we use here are TTL (Time To Live) strategies, cache invalidation patterns, incremental materialization. Or Hot/Warm/Cold data tiering with moving data between tiers based on access patterns and cost optimization.

The Obstacles to Building a Cache

However, the hard part is to implement a cache. That the cache is always up to date, and not already outdated when we query the cache instead of the real data. That we don't have inconsistencies. See for example the story of Cube and their own-grown Cube Store cache which they built. They initially used Redis for it, but quickly hit the limitations and replaced it with a Rust-written implementation based on Apache Arrow.

But lucky us, with DuckDB, there are open-source implementations we can just use. For example QuackStore or DuckDB Diskcache let you add a cache with maximal convenience. These are especially helpful when we want a cache for a SQL interface. Everything we use SQL for, we might already use DuckDB to query S3 or database tables, or if not DuckDB but SQL, we might use DuckDB as a client and with that get the cache out of the box as explained further down.

What we want is simplicity of a database, but the speed of a cache. Let's look at some examples.

QuackStore

QuackStore speeds up your data queries by caching remote files locally.

The extension uses block-based caching to automatically store frequently accessed file portions in a local cache, dramatically reducing load times for repeated queries on the same data.

Copy code
INSTALL quackstore FROM community;
LOAD quackstore;

Copy code
SET GLOBAL quackstore_cache_path = '/tmp/my_duckdb_cache.bin';
SET GLOBAL quackstore_cache_enabled = true;

Copy code
.timer on
-- Slow on first try (cold)
select count(*) FROM read_csv('https://noaa-ghcn-pds.s3.amazonaws.com/csv.gz/by_year/2025.csv.gz');

Copy code
count_star()
------------
26016543    
Run Time (s): real 49.366 user 51.777825 sys 0.449690

Copy code
count_star()
------------
26016543    
Run Time (s): real 3.304 user 7.630344 sys 0.237343

Copy code
SUMMARIZE FROM read_csv('quackstore://https://noaa-ghcn-pds.s3.amazonaws.com/csv.gz/by_year/2025.csv.gz');

Copy code
-- Cache a CSV file from GitHub
SELECT * FROM 'quackstore://https://raw.githubusercontent.com/owner/repo/main/data.csv';
-- Cache a single Parquet file from S3
SELECT * FROM parquet_scan('quackstore://s3://example_bucket/data/file.parquet');
-- Cache whole Iceberg catalog from S3
SELECT * FROM iceberg_scan('quackstore://s3://example_bucket/iceberg/catalog');
-- Cache any web resource
SELECT content FROM read_text('quackstore://https://example.com/file.txt');

cache_httpfs (DiskCache for Remote Files)

The cache_httpfs extension adds a local disk cache layer on top of DuckDB's httpfs extension. When you query remote files on S3, HTTP, or Hugging Face, it automatically caches data blocks locally and reducing bandwidth costs, improving latency, and adding reliability when connections are flaky.

Copy code
INSTALL cache_httpfs FROM community;
LOAD cache_httpfs;

Copy code

-- First query: downloads from S3 
select count(*) FROM read_csv('https://noaa-ghcn-pds.s3.amazonaws.com/csv.gz/by_year/2025.csv.gz');
-- Run Time: 42.407s

-- Configure cache location (optional - has sensible defaults)
SET cache_httpfs_cache_directory = '/tmp/duckdb_cache';

-- Second query: caches locally
SELECT count(*) FROM 's3://my-bucket/large-dataset/*.parquet';
-- Run Time: 44.028s

-- Third query: served from local disk cache
SELECT count(*) FROM 's3://my-bucket/large-dataset/*.parquet';
-- Run Time: 1.995s

Copy code
SELECT cache_httpfs_get_profile();
┌────────────────────────────┐
│ cache_httpfs_get_profile() │
│          varchar           │
├────────────────────────────┤
│ (noop profile collector)   │
└────────────────────────────┘

Copy code
SELECT cache_httpfs_get_ondisk_data_cache_size();
┌───────────────────────────────────────────┐
│ cache_httpfs_get_ondisk_data_cache_size() │
│                   int64                   │
├───────────────────────────────────────────┤
│                 131048289                 │
│             (131.05 million)              │
└───────────────────────────────────────────┘

Copy code
SELECT cache_httpfs_clear_cache();
┌────────────────────────────┐
│ cache_httpfs_clear_cache() │
│          boolean           │
├────────────────────────────┤
│ true                       │
└────────────────────────────┘

DiskCache

DiskCache is a DuckDB extension that adds disk (SSD) caching to DuckDB's built-in RAM cache.

DuckDB already caches remote Parquet data in RAM via its ExternalFileCache. DiskCache adds a local disk layer underneath, so when RAM fills up, data spills to SSD rather than requiring another network fetch.

Copy code
-- Configure cache with regex to match NYC taxi data
FROM diskcache_config('/tmp/diskcache', 8192, 24, '.*d37ci6vzurychx.cloudfront.net.*');

-- First query: downloads ~450MB of parquet files
SELECT count(*) FROM read_parquet([
    'https://d37ci6vzurychx.cloudfront.net/trip-data/fhvhv_tripdata_2024-01.parquet',
    'https://d37ci6vzurychx.cloudfront.net/trip-data/yellow_tripdata_2024-01.parquet',
    'https://d37ci6vzurychx.cloudfront.net/trip-data/green_tripdata_2024-01.parquet'
]);

-- Second query: served from disk cache
SELECT count(*) FROM read_parquet([...]);

-- Inspect cache contents
FROM diskcache_stats();

In-Process Columnar Caching with DuckDB with Striim

Striim wrote a great example of how to go Beyond Materialized Views using DuckDB for in-process columnar caching. They decided to not use Materialized Views (MVs) in Postgres for their use case because they have a lot of dynamic queries and therefore work with imperative languages for cache maintenance logic. The second reason was the infrastructure overhead with MVs and the limited flexibility that Postgres materialized views brought to the table by speeding up complex queries but requiring manual refreshes and lacking incremental updates for frequent changes.

Another benefit they had with DuckDB was to control the cache maintenance logic in Python.

DuckDB runs embedded in their control plane, refreshing static data (users, tenants) daily and dynamic metrics every minute. PostgreSQL stays the source of truth for writes while DuckDB handles all analytical reads.

On modest hardware (4 vCPUs, 7GB RAM) they showcase a 5–10x speedup with zero additional infrastructure costs:

John Kutay notes in above article that it isn't true HTAP since there's no real-time consistency between systems, but for operational analytics where slight staleness is acceptable, it's a pragmatic middle ground: pluggable OLAP performance without the complexity.

Can We Skip Redis? Immutable DataLake?

Typically Redis is used as a key-value store cache for data that require quick access. So could we replace Redis with DuckDB?

As long as the data is frozen (immutable), we could use something like DiskCache above. We'd need benchmarks to compare actual speed, but focusing on functionality alone, it's a pragmatic and simple solution.

You could extend this further with an immutable DuckLake, called Frozen DuckLake: a read-only, serverless data lake with no moving parts. It's just a DuckDB file on cloud storage with near-zero cost overhead. No servers, no refresh jobs, no cache invalidation because the data never changes.

This pattern works especially well for caching historical reference data (e.g., past fiscal years, archived reports), lookup tables that rarely update, or snapshots for auditing or compliance.

The cache becomes the database. Or rather, the database becomes the cache.

Other Examples

There are many more examples that we could talk about. You could use Apache Arrow for an in-memory cache, but you'd need to implement an application logic for that yourself, or use pg_duckdb to run a |HTAP Database directly on top of the OLTP source database, meaning we could avoid ETL and duplication of data.

You can also use an out-of-the-box solution that manages the cache for you in the cloud like MotherDuck. Working well with the examples shown here with DuckDB, easy to switch from local to cloud. Something that just works.