Skip to main content

Optimize Performance

Dremio uses a variety of tools to help you autonomously optimize your lakehouse. These tools apply at four stages: (1) source files, (2) intermediate transformations, (3) final or production transformations, and (4) client queries. Dremio also offers tools that allow you to manually fine-tune performance. Both approaches can coexist, enabling Dremio to manage most optimizations automatically while still giving you the flexibility to take direct action when desired.

For details on how Dremio autonomously manages your tables, see Automatic Optimization, which focuses on Iceberg table management.

This section focuses instead on accelerating views and SQL queries, including those from clients such as AI agents and BI dashboards. The principal mechanism is Reflections: precomputed materializations that Dremio uses to transparently rewrite and accelerate queries without any change to the queries themselves.

  • Autonomous Reflections – Let Dremio analyze your query patterns and manage Reflections automatically. Available for Iceberg tables, Parquet datasets, and any views built on these datasets.

  • Manual Reflections – Define and manage your own Reflections. Use this primarily for data formats not supported by Autonomous Reflections, or when you need direct control.

  • Results Cache – Understand how Dremio caches the results of queries from AI agents and BI dashboards.

How Reflections Work​

When Dremio receives a query, the planner doesn't just check whether any Reflection has the same name or is directly referenced. It inspects the query's logical plan and determines whether any available Reflection could produce a correct, equivalent result. If one can, Dremio compares the cost of using it against the cost of running against the raw source data, and picks the cheaper plan.

This means a Reflection defined on a view can accelerate queries that were written against the underlying tables, or against different views built from the same tables, without any change to those queries and without anyone having to know the Reflection exists.

Raw Reflections​

A raw Reflection stores a copy of the rows from its anchor (the table or view it is defined on), optionally with a subset of columns, a sort order, and horizontal partitioning. It is most useful for:

  • Materializing complex or expensive views so the transformation cost is paid once
  • Converting data from slow or non-columnar formats into Dremio's optimized Iceberg storage
  • Pre-filtering data (via a view with a predicate) to enable fast needle-in-a-haystack lookups
  • Pre-joining tables so join cost is not paid at query time

A raw Reflection accelerates queries that are logically equivalent to scanning some or all of the stored rows. Column pruning, filter pushdown, and sort exploitation all apply.

Aggregate Reflections​

An aggregate Reflection is for accelerating GROUP BY queries: analytical workloads that summarize large amounts of data, such as those submitted by BI dashboards or AI agents.

Dremio materializes the aggregation at the finest grain you define, grouped by the full set of dimensions in the Reflection. This is a single base aggregate, not a cube of every possible dimension combination. Dremio does not pre-compute all rollup permutations.

For example, if your Reflection is defined with dimensions [region, product, month], Dremio stores one row per unique (region, product, month) combination, along with the measures you configured (counts, sums, min/max, and so on).

When a query asks for a coarser summary (say, grouped by [region] alone), Dremio's planner recognizes that it can satisfy the query by re-aggregating the Reflection's already-grouped rows instead of scanning the raw source data. The key insight is that the expensive part, collapsing millions or billions of raw rows into a compact grouped result, has already been done. Re-aggregating the much smaller Reflection dataset to produce a coarser rollup is fast by comparison.

This means a single aggregate Reflection defined at a fine grain covers the entire family of queries that group by any subset of its dimensions. You do not need a separate Reflection for every possible GROUP BY combination your users might write. Define one at the finest grain that makes sense for your data, and Dremio will use it to satisfy coarser rollups automatically.

Reflection Freshness and Correctness​

Dremio checks Reflection staleness at query-planning time. Those considered too stale are excluded; those considered fresh may be used to accelerate the query.

For Autonomous and Manual Reflections on Iceberg tables, Dremio interrogates the table's metadata directly to verify freshness.

For Reflections on non-Iceberg tables (CSV, JSON, databases, and other formats without queryable metadata), Dremio checks the configured expiration policy instead. There is no table metadata to interrogate, so refresh schedules should align with how frequently the source data changes.