observabilitycostClickHouse

Observability Recipes for Cost Hotspots in ClickHouse and Snowflake

UUnknown

2026-02-15

11 min read

Practical recipes to detect query cost hotspots in ClickHouse and Snowflake, attribute them to teams, and automate cost-saving recommendations.

Stop guessing where your analytics dollars go: observability recipes to find cost hotspots in ClickHouse and Snowflake

Hook: If you run analytics at scale, you already feel the pain: sudden spikes in cloud bills, slow ad-hoc queries, and teams pointing fingers at each other while costs climb. In 2026, with ClickHouse adoption surging and Snowflake still dominant in many clouds, you need reliable observability that ties query-level behaviour to billing and to the teams that own the queries — then surfaces automated, actionable ways to reduce spend.

Why this matters now (2025–2026 trends)

Two trends make query-cost observability urgent in early 2026:

Expanding OLAP options and migrations: ClickHouse’s rapid growth and funding rounds (late 2025) accelerated migrations and hybrid deployments. More teams run mixed ClickHouse + Snowflake architectures, which fragments where cost is generated.
Cloud cost pressure and power-users: Organizations demand predictable analytics spend. New billing features from cloud providers and finer-grained metering make it possible — and necessary — to attribute costs to queries, teams, and datasets.

This article gives hands-on recipes: how to instrument ClickHouse and Snowflake to detect cost hotspots, attribute them to business teams, and produce automated recommendations (and guardrails) for lower-cost alternatives.

All observability work follows the same lifecycle. For query-cost hotspots you need four capabilities:

Detect — collect query telemetry, profile events, and metering data in real time.
Attribute — map queries to teams, owners, datasets, and billing units.
Recommend — compute candidate low-cost alternatives and estimate savings.
Remediate — automate actions (alerts, PRs, enforced tags, materialized objects, throttles).

Recipes below show practical implementation for ClickHouse and Snowflake. Each recipe includes the telemetry to collect, example queries or SQL, dashboards, alerting thresholds, and automations for recommendations.

Recipe A — ClickHouse: instrumenting an OLAP cluster to find query-cost hotspots

1) Telemetry: what to collect

Collect these fields from ClickHouse system tables and metrics exporters (Prometheus, OpenTelemetry):

system.query_log: query_id, query, query_start_time, query_duration_ms, read_rows, read_bytes, result_rows, memory_usage, user, exception
system.metrics / system.events: CPU_seconds, disk_reads, merges, inserted_rows per table over time
Network and OS metrics: CPU, memory, disk throughput (node-level), and cgroup/container metrics if running in containers
Application-level tags: session variables like query_id, team tag, or ticket id propagated via connection settings

2) Enforce tagging at the client layer

Require every client to set a team tag and a ticket reference in the session. ClickHouse supports a per-query query_id and arbitrary settings; surface these by convention:

Set application libraries to run: SET query_id = 'team:billing;ticket:INC-1234'; or pass custom HTTP headers that are logged.
Add a connection-time policy: if no team tag is present, assign queries to unknown and mark for automated alerts.

3) Compute cost proxies for ClickHouse

Because ClickHouse is often self-hosted, compute a cost proxy to translate resource usage into dollars:

Map node CPU-hours and disk-GiB to cloud unit costs using your cloud provider rates.
Attribute fractions of node utilization to queries based on their CPU and read_bytes over the query window.
Compute cost-per-query = (CPU_seconds * $/CPUs) + (read_bytes * $/GiB_read) + (disk_io_io_ops * $/IO).

Store this per-query cost in an aggregated table for dashboarding and alerting.

4) Dashboard metrics and visualizations

Key panels to build:

Top 20 queries by estimated cost (last 7 / 30 days).
Cost by team (stacked area), with drilldown to query text and frequency.
Hotspot heatmap: query_duration_ms vs read_bytes to catch high-IO slow queries.
Long-tail churn: repeated identical queries and query storm detection.

5) Example ClickHouse SQL (pattern)

Use a scheduled job to aggregate query logs into a cost-attribution table. Example pattern (adapt column names to your ClickHouse version):

<-- example pseudocode SQL -->
INSERT INTO analytics.query_costs
SELECT
  query_id,
  user,
  extractTag(query) AS team, -- parse team from query_id or settings
  min(query_start_time) AS start_ts,
  sum(query_duration_ms) AS duration_ms,
  sum(read_bytes) AS bytes_read,
  sum(read_rows) AS rows_read,
  estimate_cost(sum(query_duration_ms), sum(read_bytes)) AS est_cost
FROM system.query_log
WHERE event_date >= today()-30
GROUP BY query_id, user, team;

6) Alerting rules (suggested)

High-cost single query: alert if a single query > 1% of monthly analytics budget (or $X/day).
Repeated heavy query: alert when identical query executed > N times per hour and aggregated cost > threshold.
Unknown-tag spike: if unknown-team costs exceed 5% of daily spend, open enforcement task.

7) Automated recommendations for ClickHouse

Build a rule engine that suggests ranked actions and ROI estimates. Examples:

Rewrite candidate: if query reads full table and applies aggregation, recommend creating an AggregatingMergeTree or a materialized view and present estimated savings (use historical read_bytes reductions).
Partition / primary key change: when queries scan date ranges, suggest adding or changing partitioning to limit reads.
Result cache: detect identical queries within a short window and recommend a result cache or TTL on materialized pre-aggregations.
Throttle / quota: for ad-hoc users or unknown-team queries, auto-assign lower priority or rate-limit using settings.

Practical tip: show an estimated monthly ROI next to every recommendation—engineers act on suggestions when they can see hard dollars saved.

Recipe B — Snowflake: instrumenting for query-cost visibility and remediation

1) Telemetry: Snowflake views to pull from

Snowflake exposes rich metering and query history views in the ACCOUNT_USAGE and INFORMATION_SCHEMA schemas. Pull these on a frequent schedule (every 5–15 minutes) into your observability pipeline:

SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY: query_text, user_name, warehouse_name, execution_status, start_time, end_time, total_elapsed_time
SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY: warehouse hours and credits consumed
SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY or LOGS: for dataset-level access patterns

Note: column names and retention windows may change by account/edition—treat these as canonical places to start and confirm in your environment.

2) Tagging and query attribution

Snowflake supports QUERY_TAG and session parameters. Enforce a policy where application or user sessions must set a structured tag like team=marketing;ticket=JIRA-123. For ad-hoc analysts, integrate with your SSO and require tagging via a gateway SQL wrapper or CLI shim.

3) Mapping warehouse consumption to queries

Snowflake bills by credits consumed on virtual warehouses. To attribute warehouse credit consumption to individual queries:

Use QUERY_HISTORY to get per-query time windows.
Join with WAREHOUSE_METERING_HISTORY aggregated by time slices to apportion credits over concurrent queries on the same warehouse.
Compute per-query credit fraction = (query_cpu_seconds / total_cpu_seconds_in_window) * credits_in_window.

Keep a rolling ledger of credits attributed to queries and to teams via QUERY_TAG.

4) Dashboard panels

Top cost-producing warehouses and top queries by credits.
Cost per team (credits and dollars) with drilldown into query examples.
Trendline of credits vs result cache hit rate — to show potential for caching.

5) Example Snowflake SQL (pattern)

Sample pattern to aggregate query-level credits (simplified):

<-- example pseudocode SQL -->
INSERT INTO analytics.snowflake_query_costs
SELECT
  q.query_id,
  q.user_name,
  q.query_text,
  parse_tag(q.query_tag,'team') AS team,
  q.start_time,
  q.end_time,
  q.total_elapsed_time,
  apportion_credits(q.start_time, q.end_time, q.warehouse_name) AS est_credits
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY q
WHERE q.start_time >= DATEADD(day,-30,CURRENT_TIMESTAMP());

Implement apportion_credits as a windowed join into WAREHOUSE_METERING_HISTORY to divide credits across overlapping queries.

6) Alerting rules (suggested)

Single-query credit spike: alert if a query uses > X credits (e.g., > 0.5% of monthly credits).
Warehouse-level runaway: if average credits/minute exceed expected baseline by > 3x for N minutes.
Low cache but high bytes scanned: alert when cache hit rate < 10% and average scanned bytes/query high.

7) Automated recommendations for Snowflake

Recommendation engine examples:

Result caching / clustering: suggest enabling result cache or creating materialized views for high-scan queries. Estimate savings by comparing historical scanned bytes pre/post candidate.
Warehouse sizing change: if many small, short queries run on a large warehouse, recommend moving them to a smaller warehouse or multi-warehouse scaling policy.
Query rewrite: for queries scanning whole tables, recommend predicate pushdown, using selective columns, or adding clustering keys.

Actionability note: include an automated “Apply” button for low-risk changes (e.g., create a materialized view or lower warehouse size) and an audit trail that creates PRs for schema changes.

Cross-platform patterns: unify ClickHouse and Snowflake observability

Most organizations will run mixed stacks. Use these cross-cutting patterns:

Central query-telemetry lake: ETL query logs and metering from both systems into a central analytics store. Standardize fields: query_id, start_ts, end_ts, user, team, bytes_read, cpu_seconds_est, est_cost, dataset_tag.
Team registry and tag enforcement: Central service maps team tag to cost center, Slack channel, and on-call owner. Enforce via client wrappers and SSO hooks.
Recommendations engine: Shared engine that runs the same ROI math across sources and presents ranked recommendations in a single UI with playbooks (ClickHouse advice vs Snowflake advice).
Billing tie-in: reconcile the estimated query costs with actual cloud bills (AWS/GCP/Azure) and Snowflake invoices. Use Cost Explorer APIs to validate and refine cost proxies.

Advanced strategies: ML-based anomaly detection and auto-remediation (2026)

By 2026, teams increasingly use ML to reduce noise and suggest higher-value optimizations:

Anomaly detection: use time-series models (Prophet, ETS, or lightweight LSTMs) on per-query cost time series to find novel hotspots instead of threshold-only alerts.
Similarity clustering: cluster query fingerprints to find redundant or repeated variants that can be consolidated or cached.
Automated suggestion ranking: score recommendations by confidence, implementation cost (developer hours), and monthly savings to prioritize fixes.
Policy-as-code: enforce mandatory tags and per-team quotas by integrating with CI/CD and DB gateway layers; auto-block or downgrade non-compliant sessions. See guidance on building a developer experience platform to support this.

Concrete remediation playbooks (step-by-step)

Playbook 1 — Turn a recurring heavy query into a materialized view (ClickHouse)

Identify top recurring query (freq > N/day) that consumes high bytes_read and CPU.
Estimate benefit: compute average bytes_read per run * runs/day = bytes_saved/day if replaced by MV.
Create materialized view using aggregation keys matching query GROUP BY, or use AggregatingMergeTree for pre-aggregation.
Run A/B: route 10% of traffic to read from the MV for 1 week and measure latency and resource usage.
On success, flip consumers and deprecate the original heavy query with a PR and migration notes.

Playbook 2 — Right-size Snowflake warehouse for bursty ad-hoc queries

Find workloads with short runtimes (< 2 minutes) but running on large warehouses.
Estimate credits saved per query by moving to a smaller warehouse or using an auto-scale policy.
Provision a dedicated small warehouse for ad-hoc users with auto-suspend < 60s and resource monitors.
Implement a migration plan: update connection configs, notify users, and monitor for increased latency.

Measuring success: KPIs to track

Monthly analytics spend (normalized) and % reduction from baselines.
Top-10 queries’ share of spend — target to reduce long-tail concentration (e.g., top-10 from 60% to < 40%).
Mean time to remediation for high-impact recommendations (goal: < 7 days).
Compliance rate for query tags (goal: 100% for production clients).

Real-world example (illustrative)

A mid-market company ran Snowflake and ClickHouse. After implementing the recipes above they found a daily reports pipeline that ran a full table scan in both systems and accounted for 18% of monthly analytics credits. By creating two materialized views (one in ClickHouse; one as a Snowflake materialized view) and routing scheduled jobs to them, the company reduced the reports’ combined cost by 86% and cut query latency from minutes to seconds—payback within two weeks. Consider caching and planner improvements discussed in caching strategies.

Operational checklist before you start

Ensure retention and access to system logs (ClickHouse system tables; Snowflake ACCOUNT_USAGE).
Deploy an ingestion pipeline to land query telemetry to a central store (e.g., Kafka > lake > metrics DB).
Enforce tagging via client libraries, SSO, or a gateway.
Establish a team registry to map tags to billing codes and owners.

Common pitfalls and how to avoid them

Blind spots from incomplete logs: Some clients bypass tagging; instrument DB drivers and set default tags for unknown sessions.
Over-optimizing premature queries: Don’t create materialized views for low-frequency single-shot analyses unless ROI is clear.
Incorrect cost proxies: continually reconcile estimated query costs with actual cloud bills and Snowflake invoices to calibrate your model.
Alert fatigue: rank alerts by estimated monthly impact; mute low ROI notifications.

Future-facing predictions (2026 and beyond)

Observability layers will standardize on OpenTelemetry for query traces; expect vendor-neutral trace schemas for DB queries in 2026.
Query engines will expose cost-estimates in planning phases; Snowflake and ClickHouse are already evolving toward richer planner telemetry, making cost prediction more accurate.
Automated policy enforcement (tagging, cost guards) will become a first-class feature in analytics platforms and cloud consoles.

Final actionable checklist (do this in the next 30 days)

Start collecting query logs from ClickHouse system tables and Snowflake ACCOUNT_USAGE into a central store.
Require and enforce team tags; patch client libs to set QUERY_TAG or query_id.
Build a top-20 cost-by-query dashboard and set alerts for single-query >1% monthly spend.
Create one low-risk automated remediation: a materialized view or warehouse right-size action that can be applied via UI with an audit trail (see developer platform patterns at Build DevEx Platform).

Call to action

Start small but instrument everywhere. If you want a proven starter pack, export your last 30 days of query logs from ClickHouse and Snowflake to a shared workspace and run the “top-20 cost queries” recipe above — you’ll uncover the low-hanging fruit that pays for the whole observability pipeline. Need help designing the telemetry schema or the recommendation engine? Contact your internal infra team or reach out for a design review that maps these recipes to your architecture and billing model.

Unknown

Contributor

Senior editor and content strategist. Writing about technology, design, and the future of digital media. Follow along for deep dives into the industry's moving parts.