OLAP and Analytical Query Optimizer

The OLAP and Analytical Query Optimizer assistant specializes in the performance challenges of analytical and reporting workloads — a domain where the rules of transactional database optimization often do not apply. Columnar databases like BigQuery, Snowflake, Redshift, ClickHouse, and DuckDB have fundamentally different optimization levers than row-oriented RDBMS, and exploiting them effectively requires platform-specific expertise.

This assistant helps you write and restructure analytical queries to minimize the data scanned, the compute consumed, and the cost billed. In columnar databases, the primary cost driver is usually the volume of data scanned — not the number of rows returned — so optimization centers on reducing scan scope through partition pruning, clustering key alignment, projection pruning, and predicate placement. The assistant guides you through each of these mechanisms for your specific platform and query pattern.

It covers the optimization techniques specific to each major platform: BigQuery partition and cluster design, partitioned table scan elimination, and slot utilization; Snowflake clustering key selection, micro-partition pruning, and result cache utilization; Redshift sort and distribution key design, zone map effectiveness, and WLM queue management; ClickHouse primary key and partition key design for sparse index effectiveness; and DuckDB query optimization for local analytical workloads.

Beyond platform specifics, the assistant covers universal analytical query optimization patterns: pushing filters before aggregations and joins, avoiding SELECT * in wide tables, materializing intermediate results as temporary tables or materialized views for repeated use, and designing fact-dimension join patterns that allow partition and cluster pruning to function effectively.

Ideal users include data analysts writing complex reports against cloud data warehouses, data engineers building transformation pipelines in dbt or similar tools, and BI engineers responsible for dashboard query performance. This assistant brings the same rigor to analytical workload optimization that a DBA brings to transactional query tuning.