Database Warehousing Support and Managed Services on ClickHouse: 7 Proven Operating Practices

Positioning

Why database warehousing on ClickHouse, and when a cloud warehouse is still the right call

A warehouse has two jobs that pull in opposite directions: absorb everything the business produces, and answer questions about it in the time a person is willing to wait. Cloud warehouses solved the first job with elastic storage and the second with elastic compute, priced per query or per credit. ClickHouse solves both with a columnar MergeTree engine that compresses aggressively, a sparse primary index that skips most of the data on every read, and vectorised execution across every core, at a fixed and predictable infrastructure cost. For workloads that are event-scale, latency-sensitive or served to many concurrent users, that is a different economic and operational proposition.

Modelling

Database warehousing models on ClickHouse: how facts, dimensions and aggregates take physical form

Dimensional modelling survives the move to ClickHouse intact; what changes is the physical form each object takes. Small, slowly changing dimensions become dictionaries, so a join is an in-memory lookup rather than a hash join. Facts become wide MergeTree tables with a sort key derived from how they are filtered. History-dependent attributes keep a versioned copy for point-in-time joins. Aggregates that dashboards hit continuously are precomputed as mergeable states.

-- ClickHouse 25.x / 26.x LTS: database warehousing objects, fact table, dimension dictionary and aggregate mart
CREATE TABLE mart.fact_sales ON CLUSTER '{cluster}'
(
    sale_id      UInt64,
    sale_date    Date,
    sale_time    DateTime64(3, 'UTC')  CODEC(DoubleDelta, ZSTD(3)),
    region_id    UInt16,
    store_id     UInt32,
    product_id   UInt32,
    customer_id  UInt64,
    qty          UInt32,
    net_amount   Decimal(18, 2)        CODEC(ZSTD(3)),
    discount     Decimal(18, 2)        CODEC(ZSTD(3)),
    _version     UInt64,
    PROJECTION by_product (SELECT * ORDER BY (product_id, sale_date)),
    PROJECTION daily_totals (SELECT sale_date, store_id, sum(net_amount), sum(qty), count() GROUP BY sale_date, store_id)
)
ENGINE = ReplicatedReplacingMergeTree('/clickhouse/tables/{shard}/mart/fact_sales', '{replica}', _version)
PARTITION BY toYYYYMM(sale_date)
ORDER BY (region_id, store_id, sale_date, sale_id)
SETTINGS index_granularity = 8192, storage_policy = 'tiered';

CREATE DICTIONARY mart.dim_product ON CLUSTER '{cluster}'
(
    product_id UInt32,
    category   String,
    brand      String,
    unit_cost  Decimal(18, 2)
)
PRIMARY KEY product_id
SOURCE(CLICKHOUSE(TABLE 'stg_product' DB 'staging'))
LAYOUT(HASHED())
LIFETIME(MIN 3600 MAX 4200);

CREATE MATERIALIZED VIEW mart.sales_daily_mv ON CLUSTER '{cluster}'
ENGINE = ReplicatedAggregatingMergeTree('/clickhouse/tables/{shard}/mart/sales_daily', '{replica}')
PARTITION BY toYear(sale_date)
ORDER BY (region_id, store_id, sale_date)
AS SELECT region_id, store_id, sale_date,
          sumState(net_amount)  AS revenue,
          countState()          AS orders,
          uniqState(customer_id) AS customers
   FROM mart.fact_sales
   GROUP BY region_id, store_id, sale_date;

-- A dashboard query: dictionary lookup instead of a join, merge of precomputed states instead of a scan
SELECT dictGet('mart.dim_product', 'category', product_id) AS category,
       sumMerge(revenue) AS revenue
FROM mart.sales_daily_mv
WHERE region_id = 7 AND sale_date >= today() - 30
GROUP BY category
ORDER BY revenue DESC;

Row-level security belongs to the model rather than to the BI tool: a ROW POLICY on region_id scopes every analyst role, and column-masking views serve the same facts to teams that must not see identifiers. Every new mart is reviewed against the fingerprints it will serve with EXPLAIN indexes = 1 before it ships, and its cost is recorded as read rows and read bytes in system.query_log.

Support

Database warehousing support in practice: the incidents a warehouse produces

A warehouse fails in ways a transactional database does not. Parts accumulate, dashboards drift onto unindexed columns, a materialized view definition silently stops matching its source, an upgrade changes a default. ChistaDATA’s database warehousing support is built around the incidents we actually see, each with the system table that reveals it and the first runbook step, so that the 24×7 desk starts from evidence rather than from a guess.

-- Database warehousing support: the first three queries the 24x7 desk runs on a "dashboards are slow" ticket
SELECT normalizedQueryHash(query) AS fingerprint, count() AS runs,
       quantile(0.95)(query_duration_ms) AS p95_ms,
       formatReadableSize(avg(read_bytes)) AS avg_read, avg(read_rows) / greatest(avg(result_rows), 1) AS amplification,
       any(substring(query, 1, 100)) AS sample
FROM system.query_log
WHERE type = 'QueryFinish' AND event_time > now() - INTERVAL 1 HOUR
GROUP BY fingerprint ORDER BY runs * p95_ms DESC LIMIT 10;

SELECT database, table, partition, count() AS parts, sum(rows) AS rows, formatReadableSize(sum(bytes_on_disk)) AS size
FROM system.parts WHERE active GROUP BY database, table, partition
HAVING parts > 100 ORDER BY parts DESC LIMIT 20;

SELECT database, table, elapsed, progress, num_parts, formatReadableSize(total_size_bytes_compressed) AS size
FROM system.merges ORDER BY elapsed DESC;

Every incident closes with a root-cause note in the engagement record and, where warranted, a runbook update and a new monitoring rule, so the same class of incident is caught earlier the next time. Upgrades are part of support rather than a separate project: each ClickHouse LTS release is qualified against your top fingerprints on a restored backup, rolled one replica per shard at a time with compatibility pinned, and adopted setting by setting with the effect measured in system.query_log.

Migration

Migrating database warehousing from Redshift, Snowflake or BigQuery into ClickHouse

Legacy database warehousing estates migrate into this architecture one mart at a time, behind the same reconciliation gates ChistaDATA uses for every data movement. The source’s schemas and query history are exported; the top fingerprints are translated and validated with EXPLAIN against the new physical design; history is loaded through Parquet on object storage; the tail is kept current by the same pipelines the new warehouse will run on; and the two warehouses serve the same dashboards in parallel until counts, sums and p95 agree for an agreed window.

The full method, including cutover gates and rollback, is on the ClickHouse migration services page; migration and the support contract are normally scoped together so the warehouse is operated by the team that built it.

Engagement

How ChistaDATA delivers database warehousing: consulting, support and managed services

Every engagement starts with the pre-engagement questionnaire and a read-only evidence pack, so the first conversation is about your fingerprints and your sizes rather than about generic best practice. Migrations, archival tiers for transactional databases and data fabric platforms are scoped from the same assessment.

FAQ

Database warehousing on ClickHouse: questions we hear most often