Caching Data with Timbr

Timbr includes a 4-tier cache engine that materializes data within its ontology-based semantic layer to improve performance and reduce costly computations.

Concepts, relationships, properties, views and cubes keep the exact same business definitions whether an answer is computed live against the source system or served from a materialized table. The same governed model continues to serve SQL, BI tools and AI agents.

---

How caching works

1. Meaning is defined once. Mappings bind source tables to ontology concepts; ontology views and cubes define reusable business logic on top of the graph.
2. You choose what to materialize. A mapping, an ontology view, a cube - or many of them at once.
3. Timbr builds a physical table. The cache engine runs the underlying query and writes the result into the storage tier you selected.
4. Queries are transparently rerouted. Subsequent queries against that concept, view or cube read the pre-built table instead of re-running the source query. The SQL sent by your BI tool, notebook or agent does not change at all.
5. Jobs keep it fresh. Scheduled or on-demand jobs refresh the materialization fully, incrementally, or through change data capture (CDC).

The key idea

Caching in Timbr is materialization of semantics, not a query-result cache. You are not storing "the answer to one question" - you are persisting a modeled dataset that any number of different questions can then be answered from, at any level of aggregation.

---

What can be materialized

Resource	What gets stored	Why you would do it
Mapping	The mapped source table or query, as the ontology sees it	Expensive source joins/filters, slow or rate-limited sources, offloading production systems
Ontology view	The result of a business-logic view over concepts	Reused logic, heavy joins and inference across the knowledge graph
Ontology cube	A pre-aggregated analytical dataset	Dashboards, repeated aggregations, AI agents that ask many questions per turn
An entire database or ontology	All of the above, in bulk	Offloading a full source database into the data lake, or serving a complete ontology from the in-memory tier

Materializations stack: a cached mapping is itself a source for the views and cubes built on top of it. A common design is to cache base mappings into the data lake, then cache the cubes that read them into the in-memory tier.

---

Where the data lands - the four cache tiers

The cache engine can materialize into one of four tiers, depending on your setup and license tier.

Tier	Where the data physically lives	Storage format	Best for	Availability
Local Database	The database already used in the data model	The database's own native tables	Simple full materialization with no extra infrastructure	All deployments
Data Lake	Your object storage (S3, Azure Data Lake Storage, Google Cloud Storage)	Open columnar table format - Delta by default, Iceberg also supported	Very large historical datasets, offloading whole source databases	Customer-side deployments and advanced SaaS tiers
SSD	Fast local storage attached to the Timbr virtualization cluster	Open columnar table format - Delta by default, Iceberg also supported	Large partitioned datasets that need better latency than remote object storage	Customer-side deployments
In-Memory (timbr-cache)	A dedicated cache service - durable SSD-backed storage, served primarily from memory	Compressed columnar storage, sorted by primary key	Low-latency BI/AI serving, CDC, near real-time data APIs	Customer-side deployments

1. Local Database

The simplest option: the materialized table is written back into the database that already backs your model, alongside your source data. Nothing extra to install. It is a good starting point and works well when the dataset is modest and the source database is not the bottleneck - but it means the cache competes for the same resources as your source workload.

2. Data Lake

The materialized data is written to your own object storage bucket or container as open columnar tables - Delta Lake by default, with Iceberg supported as well.

What this gives you:

• Columnar + partitioned. Readers touch only the columns a query references and only the partitions the filter matches, so scanning years of history stays affordable.
• Open and portable. The data sits in your storage account in an open format that other engines can read directly. No proprietary lock-in.
• Effectively unbounded capacity. Storage is cheap and elastic, which makes this the right home for full history and for offloading an entire source database out of an expensive or fragile production system.
• Executed by a virtualization engine. A Spark-family virtualization cluster performs the read and write work, which is what allows very large loads to be split and parallelized.

3. SSD (Timbr Virtualization storage)

The same open columnar formats (Delta by default, Iceberg supported), but stored on fast local SSD attached to the Timbr virtualization cluster instead of remote object storage. You keep the lakehouse table semantics - partitioning, incremental refresh, open format - while removing the network round trip to object storage, which noticeably improves scan latency for datasets that are queried often.

4. In-Memory (timbr-cache)

This is the fastest tier, and the one worth understanding precisely:

• It is persistent, not ephemeral. Data cached here is durably written to SSD. Restarting the service does not lose the cache and does not force a rebuild.
• It is memory-first. The engine keeps hot data resident in RAM and answers as much as it possibly can without touching disk. When it does go to disk, it reads only the specific columns and data blocks a query actually needs, so even "cold" reads stay fast. In practice: SSD-backed for durability and capacity, memory-served for speed.
• Columnar and vectorized. Data is stored column by column and compressed, and queries process it in batches rather than row by row. This is what makes aggregations over very large tables feel interactive.
• Sorted and indexed by primary key, which gives fast point lookups and range scans - and is what makes row-level CDC upserts possible. This is the only tier that supports CDC.
• It tunes itself. The tier maintains projections - alternative physical layouts of the same data - and derives them automatically from real query patterns. See below.

Automatic performance tuning (projections)

A single physical layout cannot be optimal for every query. A table sorted by date serves time-range filters well but is slow to filter by customer; an aggregation repeated on every dashboard load re-scans detail rows it does not need.

timbr-cache solves this with projections: additional physical representations of the same table stored alongside it. One projection may be sorted by a different set of columns so a common filter becomes a narrow range scan; another may hold a pre-aggregated rollup so a repeated aggregation is answered from a far smaller structure.

What makes this genuinely useful is that it happens automatically:

• It learns from real query patterns. The engine observes which filters, sort orders and aggregations actually run against a cached table, and derives the projections worth maintaining - no upfront guessing about how the data will be queried.
• It is transparent at query time. The optimizer decides, per query, whether a projection can answer it and silently routes to the cheapest option. Your SQL, your ontology views and cubes, and your BI and agent queries stay exactly as they are.
• It stays in sync on its own. Projections are part of the cached table, so every refresh - full, incremental or CDC - keeps them current. There is nothing extra to schedule and nothing to invalidate manually.

The practical effect is a cache that gets faster the more it is used: the more a dashboard, a report or an AI agent repeats a query shape, the better the physical layout underneath it becomes - without a DBA hand-building indexes, sort keys or aggregate tables.

Scaling: up first, then out

The in-memory tier is highly scalable in both directions, but the two directions are not equally cheap:

• Scale up first (vertical). Adding CPU cores and RAM to the same node is by far the highest-return change. Everything stays in shared memory with no network exchange between nodes, and a single query can use the added cores almost linearly. Most performance problems on this tier are solved here.
• Scale out later (horizontal). Once a node is already large and you are still constrained - by a dataset bigger than one machine can reasonably hold, or by concurrency beyond what one machine can serve - add nodes and distribute or replicate across them.

Best practice

Prefer vertical scaling by default. Only when you reach a genuinely large node size should you consider spanning out to multiple nodes. Distribution introduces network exchange between nodes and operational complexity, so distribute because you have to - not as a first move.

What the in-memory tier is optimized for

• AI and BI workloads. Many concurrent, filter-heavy and aggregate-heavy queries over modeled views and cubes. AI agents typically fire several questions to answer one user request, so per-query latency compounds - this tier is where that matters most.
• Near real-time API access. Timbr's [Swagger / OpenAPI endpoints] expose the semantic layer as a REST API. When the concepts, views and cubes behind those endpoints are materialized into timbr-cache, the API becomes a low-latency read API over governed, modeled data - suitable for applications and services, not just analysts.
• Freshest data. Incremental refresh and CDC lets the cache track source inserts, updates and deletes on a short cycle, so "materialized" does not have to mean "stale".

Typically, cubes and ontology views are served from timbr-cache while bulk history stays in the data lake - but if the workload calls for it, an entire ontology can be materialized here.

---

Keeping caches fresh

Three refresh strategies are available. Which ones apply depends on the tier you materialized into.

Full refresh

Rebuilds the entire materialization from scratch. Simple and always correct, available on every tier. Best when the dataset is small, when changes are spread across the whole table, or when you want a guaranteed clean rebuild.

Incremental refresh (partition replace)

For large, mostly-append datasets, rebuilding everything each night is wasteful. Instead:

1. Partition the cache on a column - usually a date or timestamp.
2. Bootstrap the cache once with the full history.
3. Refresh a window on a schedule (for example, the last day or the last week). Only the partitions matching that filter are rebuilt.

Two things are important to understand here:

• The filter selects which partition slices are replaced. The whole matching slice is swapped for freshly read source data - this is a partition-level replace, not a row-level merge.
• The same mechanism is your backfill and repair tool. Point the filter at an older window (a specific month, a specific region) and only that slice is corrected.

For the very first load of a huge table, a split option breaks the initial build into daily, monthly or yearly chunks so it runs in manageable, parallelizable pieces instead of one enormous operation. If the source cannot cheaply tell Timbr which chunks exist, you can declare the partition values up front and skip the discovery scan entirely.

Incremental refresh is supported on Data Lake, SSD (virtualization) and timbr-cache.

CDC - change data capture (timbr-cache only)

When source rows are updated in place and you need the cache to track them closely, CDC gives row-level accuracy:

• It is query-based (watermark) CDC - Timbr re-reads changed rows from the source using a modified-date/timestamp column. It does not read database transaction logs, so no special source privileges or log-shipping infrastructure are required.
• The cache is defined with a watermark column (the modified timestamp) and a primary key.
• The initial load brings in the current dataset; each subsequent refresh fetches rows changed since the watermark and upserts them by primary key - new keys are inserted, existing keys are updated to their latest state.
• Hard deletes - rows that simply vanish from the source - cannot be seen by a "what changed since" query. A separate validate pass reconciles them: it compares the primary keys live in the cache against those still present in the source and marks the missing ones as deleted so they stop appearing in results.
• Soft deletes (an is_deleted or deleted_at flag) need no validate pass at all - just filter them out in the mapping or view definition.

Choosing a strategy

Situation	Strategy
Small table, easy to rebuild	Full refresh
Large fact table growing by day/month, history is append-only	Incremental partition refresh
Huge first load	Incremental with split (and predeclared partition values, if the source scan is expensive)
Rows update in place by key and you need near-current values	CDC on timbr-cache
The source hard-deletes rows	CDC + a scheduled validate pass
The source soft-deletes rows	Filter in the mapping/view; CDC for updates only

---

Scheduling and orchestration

All materializations can be refreshed automatically on recurring time intervals, invoked on demand, or triggered externally to the Timbr platform through its SQL endpoints.

Jobs wrap cache operations so they can be scheduled and monitored:

• Schedules run daily, weekly, monthly, yearly, or on any cron expression - or none for jobs you only trigger manually.
• Retries can be configured with a retry count and an interval between attempts, so a transient source outage does not silently leave a cache stale.
• Bulk jobs group many cache operations into a single unit of work and run them through a thread pool whose size you control. All the parallel work must finish before any dependent job is invoked.

That last point is what makes real pipelines possible. A typical shape:

Refresh a dozen independent base mappings in parallel → wait for all of them → then run the dependent job that rebuilds the cube or ontology view sitting on top of them.

The one thing to keep sequential is work touching the same resource - a refresh and a validate pass on the same CDC cache should never run concurrently.

All caching activity across the platform is centralized and visible in the Scheduled Jobs component, regardless of whether it was started from the Data Mapper, Ontology Views, the SQL editor, or an external SQL endpoint.

---

Moving between tiers

The target tier is a property of the cache definition, not of the model. That means you can re-materialize the same view or mapping into a different tier without touching the ontology, the mappings, or any downstream query, dashboard or agent.

Users with more than one cache option can therefore promote or demote materializations between tiers as workloads change.