ClickHouse vs Snowflake

Audits tables approaching the 300 active parts threshold and monitors active background merges to preempt insert rejections.

-- Inspect active part count, merge pressure, and unmerged parts by table
SELECT database, table, count() AS total_parts,
       round(sum(bytes_on_disk) / 1024 / 1024 / 1024, 2) AS size_gb,
       countIf(active = 1) AS active_parts
FROM system.parts
WHERE active = 1
GROUP BY database, table
HAVING countIf(active = 1) > 100
ORDER BY active_parts DESC
LIMIT 10;

-- Audit active background merges and memory consumption
SELECT thread_name, elapsed, progress, bytes_read_uncompressed, memory_usage
FROM system.merges
ORDER BY elapsed DESC;

Identifies high-consumption warehouses, runaway queries, and compilation overhead over the preceding 24 hours to eliminate credit wastage.

-- Non-blocking telemetry: identify top warehouse credit consumption by query type
SELECT warehouse_name,
       COUNT(*) AS query_count,
       round(AVG(total_elapsed_time)/1000, 2) AS avg_duration_sec,
       round(AVG(compilation_time)/1000, 2) AS avg_compile_sec,
       round(SUM(credits_used_cloud_services), 4) AS total_cloud_credits
FROM snowflake.account_usage.query_history
WHERE start_time >= DATEADD(hours, -24, CURRENT_TIMESTAMP())
GROUP BY warehouse_name
ORDER BY total_cloud_credits DESC
LIMIT 10;