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​

There are two ways that reflections on Apache Iceberg tables can be refreshed:

  • Incremental refreshes

    There are two type of incremental refreshes for Iceberg tables:

    • Incremental refreshes when changes to the base table are only appends

      This type of incremental refresh is used only when the changes to the base table are appends.

      Refreshes are based on the differences between the current snapshot of the base table and the snapshot at the time of the last reflection refresh. The changes to the base table must be appends only.

      Iceberg tables in all supported file-system sources (Amazon S3, Azure Data Lake Storage, Google Cloud Storage, and HDFS) and non-file-system sources (AWS Glue, Hive, and Nessie) can be refreshed this way.

    • Incremental refreshes when changes to the base table include DML operations

      This type of incremental refresh is used when the changes to the base table are DML operations (INSERT, 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.

      Iceberg tables in all supported file-system sources (Amazon S3, Azure Data Lake Storage, Google Cloud Storage, and HDFS) and non-file-system sources (AWS Glue, Hive, and Nessie) can be refreshed this way.

      note
      • If a reflection is defined on a view, partition-based incremental refreshes are possible only if the view is defined on a single, partitioned table.
      • The initial refresh of a reflection is always a full refresh.

      Refreshes are based on Iceberg metadata that is used to identify modified partitions and to restrict the scope of the refresh to only those partitions.

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

      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 base 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, the reflection being refreshed is dropped, recreated, and loaded.

The following algorithm determines which refresh method is used:

  1. The initial refresh of a reflection is always a full refresh.
  2. If the reflection is created from a view that uses nested group-bys, joins, unions, or window functions, then a full refresh is performed.
  3. If the changes to the base table are only appends, then an incremental refresh based on table snapshots is performed.
  4. If the changes to the base table include DML operations (such as INSERT, UPDATE, DELETE, and more), then a partition-based incremental refresh is attempted.
  5. If the partitions of the base table and the partitions of the reflection are not compatible, or if either the base table or the reflection is not partitioned, then a full refresh is performed.

Because of this algorithm, you do not select a type of refresh for reflections in the settings of the base table.

note

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, the reflection will be refreshed in full. To avoid this, 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.

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 like S3 and HDFS: 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 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.

Procedures​

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. 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 and hover over the table.
  2. 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.

Procedure​

  1. Go to the space that lists the table or view from which the reflection was created.
  2. Hover over the row for the table or view.
  3. In the Actions field, click The Settings icon.
  4. In the sidebar of the Dataset Settings window, select Reflections.
  5. Click History in the heading for the reflection.

Result​

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 statement.
  • The second runs a LOAD MATERIALIZATION METADATA statement to create metadata that the query optimizer can use to find out the definition and structure of the reflection.

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.

Procedure​

  1. Open the Reflections page, hover over The Settings icon in the sidebar, select Project Settings, and then select Reflections in the sidebar of the page that is opened.
  2. On the Reflections page, click The Settings icon in the top-right corner and select Acceleration Settings.
  3. In the Maximum attempts for reflection job failures field, specify the number of retries to allow.
  4. Click Save. The change goes into effect immediately.

Routing Refresh Jobs to Particular Engines​

You can use an SQL command to route jobs for refreshing reflections directly to specified engines. See ROUTE REFLECTIONS TO in ALTER TABLE in the SQL reference.