Why ODAC.JS Declarative Rollups Crush Boilerplate

Time-series data is a wild beast. When you are swallowing millions of telemetry events every single minute, your storage costs scale exponentially and your query speeds degrade linearly.

Enter ClickHouse, the gold standard for analytical database engines. It handles massive, append-heavy analytical workloads with ease, but its raw power comes with a steep developer experience tax.

Traditionally, orchestrating multi-tier data retention and downsampling policies in ClickHouse is a chore. It demands custom table engines, materialized views, and intricate TTL expressions that feel more like a dark art than clean engineering.

We built ODAC.JS to eliminate this complexity. Our declarative rollup engine lets you define sophisticated analytical ladders with a clean, unified syntax, compiling your high-level intent into highly optimized ClickHouse schema migrations automatically.

Show Me The Code: The Declarative Analytical Ladder

Let us look at a real-world telemetry schema. Instead of hand-writing dozens of lines of DDL and raw SQL clauses, you declare your analytical retention rules directly in your schema file.

Create your analytical telemetry schema inside your ODAC.JS database migration directory, specifically at schema/analytics/app_stat.js:

// schema/analytics/app_stat.js
module.exports = {
  engine: 'MergeTree',
  partitionBy: 'toYYYYMM(t)',
  rollup: {
    time: 't',
    by: ['resource_id'],
    tiers: [
      { olderThan: '24 HOUR', bucket: 'tenMinutes' },
      { olderThan: '30 DAY', bucket: 'day' },
      { olderThan: '2 YEAR', delete: true }
    ],
    set: {
      cpu: 'sum',
      mem_used: 'sum',
      net_rx_total: 'max',
      pids: 'max'
    }
  },
  columns: {
    resource_id: { type: 'string' },
    server_id: { type: 'string' },
    t: { type: 'timestamp' },
    cpu: { type: 'float' },
    mem_used: { type: 'bigInteger' },
    net_rx_total: { type: 'bigInteger' },
    pids: { type: 'integer' }
  }
}

This single block of configuration is compiled during migration. The ODAC.JS compiler translates this high-level definition into a fully optimized ClickHouse table with complex, nested TTL and GROUP BY parameters.

Run the migration command via the command line interface to execute the generated DDL:

npx odac migrate --db=analytics

The Architectural Magic Under the Hood

The ODAC.JS compiler does some heavy lifting during migrations. It automatically derives the strict ORDER BY prefix needed to make ClickHouse's background merges function properly.

ClickHouse requires the table's primary key to match the sorting key used in your rollup's GROUP BY clause. ODAC.JS automatically structures the generated primary key so your background rollups run with zero runtime overhead or query stalls.

The compiler also injects a custom samples column (UInt64 DEFAULT 1) automatically into your schema. This column is the secret weapon that preserves perfect telemetry calculations across multiple downsampling tiers.

To understand why this is necessary, consider how averages work. Calculating an average of already-averaged numbers across different time granularities causes significant mathematical drift.

By injecting the samples column, ODAC.JS tracks the raw event count of every bucket. On the read side, you get 100% accurate averages by dividing the aggregated sum of your column by the aggregated sum of the samples column.

// Read-side high-performance aggregate query
const stats = await Odac.DB.analytics.app_stat
  .select('resource_id')
  .select('sum(cpu) / sum(samples) AS avg_cpu')
  .select('max(pids) AS peak_pids')
  .where('t', '>=', '2026-08-01 00:00:00')
  .groupBy('resource_id')
  .limit(10)

Common Gotchas and Mathematical Realities

When working with declarative downsampling, aggregate function selection is critical. ODAC.JS intentionally rejects the avg function (along with mean and average) inside the set configuration object.

As explained above, averaging averages ruins your data integrity. If you want average CPU utilization over a week, you must accumulate the sum of CPU ticks and the sum of samples, performing the division at query time.

Another important rule is that active downsampling tiers must get progressively coarser as they age. A finer bucket defined after a coarser one will throw a schema validation error during migration.

The valid bucket vocabulary in ODAC.JS is rich. It supports minute, fiveMinutes, tenMinutes, fifteenMinutes, hour, day, week, month, quarter, and year.

Additionally, we designed a reserve: true attribute for tiers. Because the sorting key of a ClickHouse table can only be set at table creation, introducing a new bucket granularity later would normally require dropping and recreating your massive analytical table.

By declaring a bucket with reserve: true, ODAC.JS includes that bucket in the primary key immediately without emitting a rollup step. When you are ready to activate that tier later, simply swap reserve: true with an olderThan interval, triggering a clean, zero-recreate table modification.