Skip to main content

Refreshing Reflections

The data in a reflection can become stale and need to be refreshed. The refresh of a reflection causes two updates:

  • The data stored in the Apache Iceberg table for the reflection is updated.
  • The metadata that stores details about the reflection is updated.

Both of these updates are implied in the term "reflection refresh".

note

Dremio does not refresh the data that external reflections are mapped to.

Types of Reflection Refresh

How reflections are refreshed depend on the format of the base table.

Types of Refresh for Reflections on Apache Iceberg Tables and on Certain Types of Datasets in Filesystem Sources, and Glue Sources

There are two methods that can be used to refresh reflections that are defined either on Iceberg tables or on these types of datasets in filesystem and Glue sources:

  • Parquet datasets in Filesystem sources (on S3 or ADLS)
  • Parquet datasets, or non-transactional ORC datasets on Glue sources

Iceberg tables in all supported file-system sources (Amazon S3 and Azure Data Lake Storage) and non-file-system sources (AWS Glue and Arctic) can be refreshed with either of these methods.

Incremental refreshes

There are two types of incremental refreshes:

note
Incremental refreshes when changes to an anchor table are only append operations
note

Optimize operations on Iceberg tables are also supported for this type of incremental refresh.

This type of incremental refresh is used only when the changes to the anchor table are appends and do not include updates or deletes. There are two cases to consider:

  • When a reflection is defined on one anchor table

    When a reflection is defined on an anchor table or on a view that is defined on one anchor table, an incremental refresh is based on the differences between the current snapshot of the anchor table and the snapshot at the time of the last refresh.

  • When a reflection is defined on a view that joins two or more anchor tables

    When a reflection is defined on a view that joins two or more anchor tables, whether an incremental refresh can be performed depends on how many anchor tables have changed since the last refresh of the reflection:

    • If just one of the anchor tables has changed since the last refresh, an incremental refresh can be performed. It is based on the differences between the current snapshot of the one changed anchor table and the snapshot at the time of the last refresh.
    • If two or more tables have been refreshed since the last refresh, then a full refresh is used to refresh the reflection.
Incremental refreshes when changes to an anchor table include non-append operations

For Iceberg tables, this type of incremental refresh is used when the changes are DML operations that delete or modify the data (UPDATE, DELETE, etc.) made either through the Copy-on-Write (COW) or the Merge-on-Read (MOR) storage mechanism. For more information about COW and MOR, see Row-Level Changes on the Lakehouse: Copy-On-Write vs. Merge-On-Read in Apache Iceberg.

For sources in filesystems or Glue, non-append operations can include, for example:

  • In filesystem sources, files being deleted from Parquet datasets
  • In Glue sources, DML-equivalent operations being performed on Parquet datasets, Avro datasets, or non-transactional ORC datasets

Both the anchor table and the reflection must be partitioned, and the partition transforms that they use must be compatible.

There are two cases to consider:

  • When a reflection is defined on one anchor table

    When a reflection is defined on an anchor table or one a view that is defined on one anchor table, an incremental refresh is based on Iceberg metadata that is used to identify modified partitions and to restrict the scope of the refresh to only those partitions.

  • When a reflection is defined on a view that joins two or more anchor tables

    When a reflection is defined on a view that joins two or more anchor tables, whether an incremental refresh can be performed depends on how many anchor tables have changed since the last refresh of the reflection:

    • If just one of the anchor tables has changed since the last refresh, an incremental refresh can be performed. It is based on Iceberg metadata that is used to identify modified partitions and to restrict the scope of the refresh to only those partitions.
    • If two or more tables have been refreshed since the last refresh, then a full refresh is used to refresh the reflection.
note

Dremio uses Iceberg tables to store metadata for filesystem and Glue sources.

For information about partitioning reflections and applying partition transforms, see the section "Horizontally Partition Reflections that Have Many Rows" in Best Practices for Creating Raw and Aggregation Reflections.

For information about partitioning reflections in ways that are compatible with the partitioning of anchor tables, see "Partition Reflections to Allow for Partition-Based Incremental Refreshes" in Best Practices for Creating Raw and Aggregation Reflections.

Full refreshes

In a full refresh, a reflection is dropped, recreated, and loaded.

note

Algorithm for Determining Whether an Incremental or a Full Refresh is Used

The following algorithm determines which refresh method is used:

  1. If the reflection has never been refreshed, then a full refresh is performed.
  2. If the reflection is created from a view that uses nested group-bys, joins other than inner or cross joins, unions, or window functions, then a full refresh is performed.
  3. If the reflection is created from a view that joins two or more anchor tables and more than one anchor table has changed since the previous refresh, then a full refresh is performed.
  4. If the reflection is based on a view and the changed anchor table is used multiple times in that view, then a full refresh is performed.
  5. If the changes to the anchor table are only appends, then an incremental refresh based on table snapshots is performed.
  6. If the changes to the anchor table include non-append operations, then the compatibility of the partitions of the anchor table and the partitions of the reflection is checked:
    • If the partitions of the anchor table and the partitions of the reflection are not compatible, or if either the anchor table or the reflection is not partitioned, then a full refresh is performed.
    • If the partition scheme of the anchor table has been changed since the last refresh to be incompatible with the partitioning scheme of a reflection, and if changes have occurred to data belonging to a prior partition scheme or the new partition scheme, then a full refresh is performed. To avoid a full refresh when these two conditions hold, update the partition scheme for reflection to match the partition scheme for the table. You do so in the Advanced reflection editor or through the ALTER DATASET SQL command.
    • If the partitions of the anchor table and the partitions of the reflection are compatible, then an incremental refresh is performed.

Because this algorithm is used to determine which type of refresh to perform, you do not select a type of refresh for reflections in the settings of the anchor table.

Type of Refresh for Reflections on Delta Lake tables

Only full refreshes are supported. In a full refresh, the reflection being refreshed is dropped, recreated, and loaded.

Types of Refresh for Reflections on all other tables

  • Incremental refreshes

    Dremio appends data to the existing data for a reflection. Incremental refreshes are faster than full refreshes for large reflections, and are appropriate for reflections that are defined on tables that are not partitioned.

    There are two ways in which Dremio can identify new records:

    • For directory datasets in file-based data sources: Dremio can automatically identify new files in the directory that were added after the prior refresh.
    • For all other datasets (such as datasets in relational or NoSQL databases): An administrator specifies a strictly monotonically increasing field, such as an auto-incrementing key, that must be of type BigInt, Int, Timestamp, Date, Varchar, Float, Double, or Decimal. This allows Dremio to find and fetch the records that have been created since the last time the acceleration was incrementally refreshed.
    caution

    Use incremental refreshes only for reflections that are based on tables and views that are appended to. If records can be updated or deleted in a table or view, use full refreshes for the reflections that are based on that table or view.

  • Full refreshes

    In a full refresh, the reflection being refreshed is dropped, recreated, and loaded.

    Full refreshes are always used in these three cases:

    • A reflection is partitioned on one or more fields.
    • A reflection is created on a table that was promoted from a file, rather than from a folder, or is created on a view that is based on such a table.
    • A reflection is created from a view that uses nested group-bys, joins, unions, or window functions.

Best practice: Time reflection refreshes to occur after metadata refreshes of tables

Time your refresh reflections to occur only after the metadata for their underlying tables is refreshed. Otherwise, reflection refreshes do not include data from any files that were added to a table since the last metadata refresh, if any files were added.

For example, suppose a data source that is promoted to a table consists of 10,000 files, and that the metadata refresh for the table is set to happen every three hours. Subsequently, reflections are created from views on that table, and the refresh of reflections on the table is set to occur every hour.

Now, one thousand files are added to the table. Before the next metadata refresh, the reflections are refreshed twice, yet the refreshes do not add data from those one thousand files. Only on the third refresh of the reflections does data from those files get added to the reflections.

Setting the Refresh Policy for Reflections

In the settings for a data source, you specify the schedule for refreshes of all reflections that are on the tables in that data source. The default schedule is 1 hour between each refresh.

In the settings for a table that is not in the Iceberg or Delta Lake format, you can specify the type of refresh to use for all reflections that are ultimately derived from the table. The default refresh type is Full refresh.

For tables in all supported table formats, you can specify a schedule for reflection refreshes that overrides the schedule specified in the settings for the table's data source. The default schedule is the schedule set at the source of the table.

To set the refresh schedule on a data source:

  1. Right-click a data lake or external source.
  2. Select Edit Details.
  3. In the sidebar of the Edit Source window, select Reflection Refresh.
  4. When you are done making your selections, click Save. Your changes go into effect immediately.

To edit the refresh settings on a table:

  1. Locate the table.
  2. Hover over the row in which it appears and click The Settings icon to the right.
  3. In the sidebar of the Dataset Settings window, click Reflection Refresh.
  4. When you are done making your selections, click Save. Your changes go into effect immediately.

Manually Triggering a Refresh

You can click a button to start the refresh of all of the reflections that are defined on a table or on views derived from that table.

To trigger a refresh:

  1. Locate the table.
  2. Hover over the row in which it appears and click The Settings icon to the right.
  3. In the sidebar of the Dataset Settings window, click Reflection Refresh.
  4. Click Refresh Now. The message "All dependent reflections will be refreshed." appears at the top of the screen.
  5. Click Save.

Viewing the Refresh History for Reflections

You can find out whether a refresh job for a reflection has run, and how many times refresh jobs for a reflection have been run.

To view the refresh history:

  1. Locate and hover over the table or view.
  2. Click The Settings icon to the right.
  3. In the sidebar of the Dataset Settings window, select Reflections.
  4. Click History in the heading for the reflection.

The Jobs page is opened with the ID of the reflection in the search box and only jobs related to that ID listed.

When a reflection is created or refreshed, Dremio runs two jobs by default:

  • The first writes the query results as a materialization to the distributed acceleration storage by running a REFRESH REFLECTION command.
  • The second runs a LOAD MATERIALIZATION METADATA command to create metadata that the query optimizer can use to find out the definition and structure of the reflection.

To find out which type of refresh was performed:

  1. Click the ID of the job that ran the REFRESH REFLECTION command.
  2. Click the Raw Profile tab.
  3. Click the Planning tab.
  4. Scroll down to the Refresh Decision section.

Setting the Maximum Number of Times to Reattempt Reflection Refreshes

You can specify how many times a job should retry refreshing a reflection after an attempt fails. Doing so can help keep reflection-refresh jobs moving at an acceptable rate through an engine queue, so that the data in the corresponding reflections does not become too stale.

  • After a failure to refresh a reflection that has never been refreshed before, further attempts are made every 30 minutes for a maximum of n retries, where n is the number that you specified.

  • After a failure to refresh a reflection that has been refreshed before at least once, the retry behavior depends on the Reflection Refresh settings on the underlying table:

    • If Never refresh is selected, then the reflection is refreshed only when the Refresh Now button is clicked.

    • If Never refresh is not selected, then the reflection is set to be refreshed automatically at specified intervals. After the current interval elapses, a refresh is attempted again. If this second attempt fails, a third attempt is made at the end of the next interval. After every failure, another attempt is made, up to n retries, where n is the number that you specified.

To set the maximum number of attempts for reflection refreshes:

  1. Click The Settings icon in the left navbar.
  2. Select Reflections in the left sidebar.
  3. On the Reflections page, click The Settings icon in the top-right corner and select Acceleration Settings.
  4. In the Maximum attempts for reflection job failures field, specify the number of retries to allow.
  5. Click Save. The change goes into effect immediately.

Routing Refresh Jobs to Particular Queues

You can use an SQL command to route jobs for refreshing reflections directly to specified queues. See Queue Routing in the SQL reference.