On this page

    Creating and Editing Reflections

    By default, Dremio runs two jobs to create a reflection:

    • The first returns the result set for creating the reflection, running a REFRESH REFLECTION statement.
    • The second creates the metadata that the query optimizer can use to find out the definition and structure of the reflection, running a LOAD MATERIALIZATION METADATA statement.

    If the support key dremio.iceberg.enabled is turned on, then Dremio runs only the first job. When Dremio creates a reflection as an Apache Iceberg table, the metadata for the reflection is generated at the same time.

    This screenshot shows two jobs that Dremio ran to create a reflection named “Super-duper reflection”:

    The first pin shows the two jobs, and the second pin show the name of the reflection.

    Locations of the Reflections Editor

    You use the reflections editor to create, edit, and remove raw and aggregation reflections.

    If you know which dataset you want to create a reflection from, follow either of these steps to open the reflections editor:

    • In the space in which the anchor dataset is located, click the gear icon in the Action field for the anchor dataset. In the sidebar of the Dataset Settings window, select Reflections.
    • In the space in which the anchor dataset is located, click the name of the anchor dataset. Then, click Reflections in the bar at the top of the screen.

    You can also open the reflections editor from an existing reflection that is listed in the Reflections page. Follow these steps to open the reflections editor:

    1. Open the Reflections page.
      • If you are using Dremio, click the gear in the sidebar, and then select Reflections in the sidebar of the Settings page.
      • If you are using Dremio Cloud, hover over the gear in the sidebar, select Project Settings, and then select Reflections in the sidebar of the page that is opened.
    2. Click the name of the reflection. The Acceleration window is opened. The editor appears in this window.

    Creating Raw Reflections

    You can use the reflections editor to create two types of raw reflection:

    • A default raw reflection that includes all of the columns of the anchor dataset, but does not sort or horizontally partition on any columns

    • A raw reflection that includes all or a subset of the columns of the anchor dataset, and that does one or both of the following things:

      • Sorts on one or more columns
      • Horizontally partitions the data according to the values in one or more columns

    Prerequisites

    • If you want to accelerate queries on unoptimized data or data in slow storage, create a virtual dataset that is itself created from a physical dataset in a non-columnar format or on slow-scan storage. You can then create your raw reflection from that virtual dataset.
    • If you want to accelerate “needle-in-a-haystack” queries, create virtual dataset that includes a predicate to include only the rows that you want to scan. You can then create your raw reflection from that virtual dataset.
    • If you want to accelerate queries that perform expensive transformations, create a virtual dataset that performs those transformations. You can then create your raw reflection from that virtual dataset.
    • If you want to accelerate queries that perform joins, create a virtual dataset that performs the joins. You can then create your raw reflection from that virtual dataset.

    Creating Default Raw Reflections

    In the Basic view of the reflections editor, you can create a raw reflection that includes all of the fields that are in a physical dataset or virtual dataset. Creating a basic raw reflection ensures that Dremio never runs user queries against the underlying dataset, when the raw reflection is enabled.

    Restrictions of the Basic View

    • You cannot select fields to sort or create horizontal partitions on.
    • The name of the reflection that you create is restricted to “Raw Reflection”.
    • You can create only one raw reflection. If you want to create multiple raw reflections at a time, use the Advanced view.

    Procedure

    To create a raw reflection in the Basic view of the reflections editor:

    1. Open the reflections editor.
      See “Locations of the Reflections Editor” to find out where you can open the editor from.

    2. Click the toggle switch on the left side of the Raw Reflections bar.

    3. Click Save.

    For tips on what to do now after your raw reflection is created and enabled, see “What to Do Next”.

    Creating Customized Raw Reflections

    In the Advanced view of the reflections editor, you can create one or more raw reflections that include all or a selection of the fields that are in the anchor or supported anchor dataset. You can also choose sort fields and fields for partitioning horizontally.

    Dremio recommends that you follow the best practices listed in “Best Practices for Creating Raw and Aggregation Reflections” when you create customized raw reflections.

    If you make any of the following changes to a raw reflection when you are using the Advanced view, you cannot switch to the Basic view:

    • Deselect one or more fields in the Display column. By default, all of the fields are selected.
    • Select one or more fields in the Sort, Partition, or Distribute column.

    Procedure

    To create a raw reflection in the Advanced view of the reflections editor:

    1. Open the reflections editor.
      See “Locations of the Reflections Editor” to find out where you can open the editor from.

    2. If the Advanced view is not already displayed, click the Advanced View button in the top-right corner of the editor.

    3. Click the toggle switch in the table labeled Raw Reflection to enable the raw reflection.
      Queries do not start using the reflection, however, until after you finished editing the reflection and click Save in a later step.

    4. (Optional) Click in the label to rename the reflection.
      The purpose of the name is to help you understand, when you read job reports, which reflections the query optimizer considered and chose when planning queries.

    5. In the columns of the table, follow these steps, which you don’t have to do in any particular order:

      Note:
      Ignore the Distribution column. Selecting fields in it has no effect on the reflection.

    • Click in the Display column to include fields in or exclude them from your reflection.
    • Click in the Sort column to select fields on which to sort the data in the reflection. For guidance in selecting a field on which to sort, see the section “Sort Reflections on High-Cardinality Fields” in “Best Practices for Creating Raw and Aggregation Reflections”.
    • Click in the Partition column to select fields on which to horizontally partition the rows in the reflection. For guidance in selecting fields on which to partition, see the section “Horizontally Partition Reflections that Have Many Rows”.
    1. (Optional) Optimize the number of files used to store the reflection. You can optimize for fast refreshes or for fast read performance by queries. Follow these steps:

      a. Click the gear icon in the table in which you are defining the reflection.

      b. In the field Reflection execution strategy, select either of these options:

      • Options
        • Select Minimize Time Needed To Refresh if you need the reflection to be created as fast as possible. This option can result in the data for the reflection being stored in many small files. This is the default option.
        • Select Minimize Number Of Files when you want to improve read performance of queries against the reflection. With this option, there tend to be fewer seeks performed for a given query.
    2. (Optional) Have Dremio convert data from your reflection’s Parquet files to the Apache Arrow format when copying that data to executor nodes.

      Normally, Dremio copies data as-is from the Parquet files as-is to caches on executor nodes, which are nodes that carry out the query plans devised by the query optimizer.

      Enabling this option can improve query performance even more. However, data in the Apache Arrow format requires more space on the executor nodes than data in the default format.

      You can use this option if your distributed data storage supports Dremio’s Columnar Cloud Cache:

      • Amazon Simple Cloud Storage (S3)
      • S3-compatible object storage
      • HDFS
      • Microsoft Azure Data Lake Storage
      • Microsoft Azure Storage

      Follow these steps:

      a. Click the gear icon in the table in which you are defining the reflection.

      b. Click the Arrow caching toggle switch to turn the feature on.

    3. Click Save when you are finished.

    What to Do Next

    After you create a raw reflection that is enabled, test whether the query optimizer is making queries use it. See “Testing Reflections” for the steps.

    When you are sure that the reflection is being used, set the refresh type for all reflections on the underlying physical dataset and set the schedule according to which they are refreshed. See “Refreshing Reflections”.

    Creating Aggregation Reflections

    Aggregation reflections are summarized representations of data. Most BI tools generate aggregation and GROUP BY queries. Aggregation reflections optimize these kinds of query patterns.

    When you create aggregation reflections, keep in mind these best practices:

    • Use dimensions that have relatively low cardinality in a dataset. The higher the cardinality of a dimension, the less benefit an aggregation reflection has on query performance. Lower cardinality aggregation reflections require less time to scan.

    • For a single dataset, create one aggregation reflection for each important subset of dimensions in your queries, rather than one aggregation reflection that includes all dimensions. Multiple small aggregation reflections (versus one large one) are good for isolated pockets of query patterns on the same dataset that do not overlap. If your query patterns overlap, use fewer larger aggregation reflections.

      There are two cautions that accompany this advice, however:

      • Be careful of creating aggregation reflections that have too few dimensions for your queries.

        If a query uses more dimensions than are included in an aggregation reflection, the reflection cannot satisfy the query and the query optimizer does not run the query against it.

      • Be careful of creating more aggregation reflections than are necessary to satisfy queries against a dataset.

        The more reflections you create, the more time the query optimizer requires to plan the execution of queries. Therefore, creating more aggregation reflections than you need can slow down query performance, even if your aggregation reflections are low-cardinality.

    • If you want to include a computed measure, first create a virtual dataset with the computed column to use as a measure, and then create the aggregation reflection on the virtual dataset.

    Dremio recommends that you also follow the best practices listed in “Best Practices for Creating Raw and Aggregation Reflections” when you create customized aggregation reflections.

    Creating Default Aggregation Reflections

    You can use the Basic view of the reflections editor to create one aggregation reflection that includes fields, from the anchor or supported anchor dataset, that are recommended for use as dimensions or measures. You can add or remove dimensions and measures, too.

    Restrictions

    • You can create only one aggregation reflection in the Basic view. If you want to create multiple aggregations reflections at a time, use the Advanced view.
    • You cannot select fields for sorting or horizontally partitioning.
    • The name of the reflection is restricted to “Aggregation Reflection”.

    Procedure

    To create an aggregation reflection in the Basic view of the reflections editor:

    1. Open the reflections editor.
      See “Locations of the Reflections Editor” to find out where you can open the editor from.

      In the Aggregations Reflections section of the editor, fields to use as dimensions and measures are recommended for you.

    2. In the Aggregation Reflection section of the editor, modify or accept the recommendation for dimension fields and measure fields.

    3. To make the reflection available to the query optimizer after you create it, click the toggle switch on the left side of the Aggregation Reflections bar.

    4. Click Save.

    For tips on what to do now after your aggregation reflection is created and enabled, see “What to Do Next”.

    Creating Customized Aggregation Reflections

    You can use the Advanced view of the reflections editor to create one or more aggregation reflections that select which fields in the anchor or supporting anchor dataset to use as dimensions and measures. For each field that you use as a measure, you can use one or more of these SQL functions: APPROX_DISTINCT_COUNT, COUNT, MAX, and MIN. You can also choose sort fields and fields for partitioning horizontally.

    Procedure

    To create an aggregation reflection in the Advanced view of the reflections editor:

    1. Open the reflections editor.
      See “Locations of the Reflections Editor” to find out where you can open the editor from.
    2. Click the Advanced View button in the top-right corner of the editor.
    3. Click Aggregation Reflections. The Aggregation Reflections section is displayed, and one table for refining the aggregation reflection that appeared in the Basic view is ready.
    4. (Optional) Click in the name to rename the reflection.
      The purpose of the name is to help you understand, when you read job reports, which reflections the query optimizer considered and chose when planning queries.
    5. In the columns of the table, follow these steps, which you don’t have to do in any particular order:

      Note:
      Ignore the Distribution column. Selecting fields in it has no effect on the reflection.

    • Click in the Dimensions column to include or exclude fields to use as dimensions.

    • Click in the Measures field to include or exclude fields to use as measures. You can use one or more of these SQL functions for each measure: APPROX_DISTINCT_COUNT, COUNT, MAX, and MIN.

      The full list of SQL aggregation functions that Dremio supports is not supported in the reflections editor. If you want to create a reflection that aggregates data by using the sQL function AVG, CORR, HLL, SUM, VAR_POP, or VAR_SAMP, you must create a virtual dataset that uses the function, and then create a raw reflection from that virtual dataset.

    • Click in the Sort column to select fields on which to sort the data in the reflection. For guidance in selecting a field on which to sort, see the section “Sort Reflections on High-Cardinality Fields” in “Best Practices for Creating Raw and Aggregation Reflections”.

    • Click in the Partition column to select fields on which to horizontally partition the rows in the reflection. For guidance in selecting fields on which to partition, see the section “Horizontally Partition Reflections that Have Many Rows”.

    1. (Optional) Optimize the number of files used to store the reflection. You can optimize for fast refreshes or for fast read performance by queries. Follow these steps:

      a. Click the gear icon in the table in which you are defining the reflection.

      b. In the field Reflection execution strategy, select either of these options:

      • Options
        • Select Minimize Time Needed To Refresh if you need the reflection to be created as fast as possible. This option can result in the data for the reflection being stored in many small files. This is the default option.
        • Select Minimize Number Of Files when you want to improve read performance of queries against the reflection. With this option, there tend to be fewer seeks performed for a given query.
    2. (Optional) Have Dremio convert data from your reflection’s Parquet files to the Apache Arrow format when copying that data to executor nodes.

      Normally, Dremio copies data as-is from the Parquet files as-is to caches on executor nodes, which are nodes that carry out the query plans devised by the query optimizer.

      Enabling this option can improve query performance even more. However, data in the Apache Arrow format requires more space on the executor nodes than data in the default format.

      You can use this option if your distributed data storage supports Dremio’s Columnar Cloud Cache:

      • Amazon Simple Cloud Storage (S3)
      • S3-compatible object storage
      • HDFS
      • Microsoft Azure Data Lake Storage
      • Microsoft Azure Storage

      Follow these steps:

      a. Click the gear icon in the table in which you are defining the reflection.

      b. Click the Arrow caching toggle switch to turn the feature on.

    3. Click Save when you are finished.

    What to Do Next

    After you create an aggregation reflection that is enabled, test whether the query optimizer is making queries use it. See “Testing Reflections” for the steps.

    When you are sure that the reflection is being used, set the refresh type for all reflections on the underlying physical dataset and set the schedule according to which they are refreshed. See “Refreshing Reflections”.

    Editing Raw Reflections

    You can edit an existing raw reflection. You might want to do so if you are iteratively designing and testing a raw reflection, if the definition of the virtual dataset that the reflection was created from was changed, or if the schema of the underlying physical dataset was changed.

    If you created a raw reflection in the Basic view of the reflections editor, you must use the Advanced view to edit it.

    Dremio runs the job or jobs to recreate the reflection after you click Save.

    Procedure

    1. Open the reflections editor.
      See “Locations of the Reflections Editor” to find out where you can open the editor from.
    2. Click the Advanced View button in the top-right corner of the editor.
    3. In the Raw Reflections section of the Advanced view, locate the table that shows the definition of your reflection.
    4. (Optional) Click in the label to rename the reflection.
      The purpose of the name is to help you understand, when you read job reports, which reflections the query optimizer considered and chose when planning queries.
    5. In the columns of the table, follow these steps, which you don’t have to do in any particular order:

      Note:
      Ignore the Distribution column. Selecting fields in it has no effect on the reflection.

    • Click in the Display column to include fields in or exclude them from your reflection.
    • Click in the Sort column to select fields on which to sort the data in the reflection. For guidance in selecting a field on which to sort, see the section “Sort Reflections on High-Cardinality Fields” in “Best Practices for Creating Raw and Aggregation Reflections”.
    • Click in the Partition column to select fields on which to horizontally partition the rows in the reflection. For guidance in selecting fields on which to partition, see the section “Horizontally Partition Reflections that Have Many Rows”.
    1. (Optional) Optimize the number of files used to store the reflection. You can optimize for fast refreshes or for fast read performance by queries.

      If the underlying physical datasets for your reflection are set to Incremental Refresh, then changing the setting of this option affects only new data that is added to your reflection at each refresh.

      If the underlying physical datasets are set to Full Refresh, then changing the setting of this option affects the entire set of data in the reflection. The new setting takes effect at the next full refresh.

      Follow these steps:

      a. Click the gear icon in the table in which you are defining the reflection.

      b. In the field Reflection execution strategy, select either of these options:

      • Options
        • Select Minimize Time Needed To Refresh if you need the reflection to be created as fast as possible. This option can result in the data for the reflection being stored in many small files. This is the default option.
        • Select Minimize Number Of Files when you want to improve read performance of queries against the reflection. With this option, there tend to be fewer seeks performed for a given query.
    2. (Optional) Have Dremio convert data from your reflection’s Parquet files to the Apache Arrow format when copying that data to executor nodes.

      Normally, Dremio copies data as-is from the Parquet files as-is to caches on executor nodes, which are nodes that carry out the query plans devised by the query optimizer.

      Enabling this option can improve query performance even more. However, data in the Apache Arrow format requires more space on the executor nodes than data in the default format.

      You can use this option if your distributed data storage supports Dremio’s Columnar Cloud Cache:

      • Amazon Simple Cloud Storage (S3)
      • S3-compatible object storage
      • HDFS
      • Microsoft Azure Data Lake Storage
      • Microsoft Azure Storage

      Follow these steps:

      a. Click the gear icon in the table in which you are defining the reflection.

      b. Click the Arrow caching toggle switch to turn the feature on.

    3. Click Save when you are finished.

    Editing Aggregation Reflections

    You might want to edit an aggregation reflection if you are iteratively designing and testing an aggregation reflection, if the definition of the virtual dataset that the reflection was created from was changed, if the schema of the underlying physical dataset was changed, or if you want to revise one or more aggregations defined in the reflection.

    If you created an aggregation reflection in the Basic view of the reflections editor, you can edit that reflection either in the Basic view or in the Advanced view.

    Dremio runs the job or jobs to recreate the reflection after you click Save.

    Editing Aggregation Reflections in the Basic View of the Reflections Editor

    1. Open the reflections editor.
      See “Locations of the Reflections Editor” to find out where you can open the editor from.
    2. In the Aggregation Reflection section of the editor, modify or accept the recommendation for dimension fields and measure fields.
    3. Click Save.

    Editing Aggregation Reflections in the Advanced View of the Reflections Editor

    1. Open the reflections editor.
      See “Locations of the Reflections Editor” to find out where you can open the editor from.
    2. Click the Advanced View button in the top-right corner of the editor.
    3. Click Aggregation Reflections.
    4. (Optional) Click in the name to rename the reflection.
      The purpose of the name is to help you understand, when you read job reports, which reflections the query optimizer considered and chose when planning queries.
    5. In the columns of the table, follow these steps, which you don’t have to do in any particular order:

      Note:
      Ignore the Distribution column. Selecting fields in it has no effect on the reflection.

    • Click in the Dimensions column to include or exclude fields to use as dimensions.

    • Click in the Measures field to include or exclude fields to use as measures. You can use one or more of these SQL functions for each measure: APPROX_DISTINCT_COUNT, COUNT, MAX, and MIN.

      The full list of SQL aggregation functions that Dremio supports is not supported in the reflections editor. If you want to create a reflection that aggregates data by using the sQL function AVG, CORR, HLL, SUM, VAR_POP, or VAR_SAMP, you must create a virtual dataset that uses the function, and then create a raw reflection from that virtual dataset.

    • Click in the Sort column to select fields on which to sort the data in the reflection. For guidance in selecting a field on which to sort, see the section “Sort Reflections on High-Cardinality Fields” in “Best Practices for Creating Raw and Aggregation Reflections”.

    • Click in the Partition column to select fields on which to horizontally partition the rows in the reflection. For guidance in selecting fields on which to partition, see the section “Horizontally Partition Reflections that Have Many Rows”.

    1. (Optional) Optimize the number of files used to store the reflection. You can optimize for fast refreshes or for fast read performance by queries. Follow these steps:

      a. Click the gear icon in the table in which you are defining the reflection.

      b. In the field Reflection execution strategy, select either of these options:

      • Options
        • Select Minimize Time Needed To Refresh if you need the reflection to be created as fast as possible. This option can result in the data for the reflection being stored in many small files. This is the default option.
        • Select Minimize Number Of Files when you want to improve read performance of queries against the reflection. With this option, there tend to be fewer seeks performed for a given query.
    2. (Optional) Have Dremio convert data from your reflection’s Parquet files to the Apache Arrow format when copying that data to executor nodes.

      Normally, Dremio copies data as-is from the Parquet files as-is to caches on executor nodes, which are nodes that carry out the query plans devised by the query optimizer.

      Enabling this option can improve query performance even more. However, data in the Apache Arrow format requires more space on the executor nodes than data in the default format.

      You can use this option if your distributed data storage supports Dremio’s Columnar Cloud Cache:

      • Amazon Simple Cloud Storage (S3)
      • S3-compatible object storage
      • HDFS
      • Microsoft Azure Data Lake Storage
      • Microsoft Azure Storage

      Follow these steps:

      a. Click the gear icon in the table in which you are defining the reflection.

      b. Click the Arrow caching toggle switch to turn the feature on.

    3. Click Save when you are finished.