Optimizing Incremental Reflections in Dremio: Handling Newly Inserted Data with Timestamp Columns

1

Hi team,

In Dremio, when I configure the ID column as the incremental reflection, it effectively scans only the newly inserted data based on the query-level WHERE condition for the ID. However, when using a timestamp column for the incremental reflection, a full table scan occurs even after new records are inserted and the reflection is manually refreshed.

How can Dremio be optimized to scan only the newly inserted data during a manual reflection refresh? Additionally, how does Dremio internally manage incremental updates when using a timestamp column?

Thanks in Advance

2

Hi team,

Can you give please give an update on this?

Thanks in Advance

3

@JoiceJacob Can you please send us the profile of the reflection that scanned all rows? plus the first full refresh reflection profile

4

Hi @balaji.ramaswamy

Please find the Query profile of first full refresh reflection and reflection that scanned all rows

First Full refresh reflection :
5174de1b-3504-4f4c-8b3e-e403393cbe56.zip (13.7 KB)

Reflection scanned full row :
4d99ab39-9660-42be-a1eb-902170df5528.zip (13.8 KB)

5

Hi team,

Can you give please give an update on this?

Thanks in Advance

6

Hi team,

Could you please provide an update on this?

Thanks in Advance

7

@JoiceJacob If you see the second refresh that you had expected to be incremental, the message (in planning tab of the raw profile) shows “Full Refresh.
Change in refresh method, doing an initial refresh.”

Was there a change done from full to incremental after the first full refresh? Can you try below steps?

  • Create a reflection and choose type incremental
  • Click on refresh now
  • Again click on refresh now

Check all three profiles-planning tab to see if in the third you see “Incremental Refresh”

Also, I see the source is RDBMS, the incremental refresh column should be

  • 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.