Summary
When querying a physical dataset in Dremio, algebraic reflection matching can cause queries to use reflections built from views rather than directly reading the latest data from the table. If the reflection is unexpired, but it's data is out-of-date, the query results will not include the most recent data.
To address this, you can exclude specific or all reflections using reflection hints in your SQL sessions, or you can refresh the reflection on the view to ensure it contains the latest data.
Reported Issue
A query directly selecting from a physical dataset does not show recently added records, even after refreshing metadata for the table, or after un-formatting the table's root folder and adding it back to the Dremio catalog. On closer inspection, the query against the physical dataset is using a reflection anchored to a view (virtual dataset) that selects from this same physical dataset. The reflection has data from an earlier version of the physical table.
Relevant Versions
All documented Dremio software releases.
Troubleshooting Steps
If a query does not show newly added records, always first check to see if it has a reflection substitution in the query plan. The planning and execution summary for the query in the Dremio "Jobs" window will list the reflections used and note their "Last Refresh From Table" time. All reflections used in a query are, by definition, unexpired, and therefore still valid for substitution, but they may not have been rebuilt (refreshed) from with newer data.
Cause
This reflection substitution behavior is an example of algebraic reflection matching. This matching process involves taking a subtree of the user query plan and comparing it to the query plans of the reflections that are being considered for substitution. If a reflection plan is essentially the same as a the subtree, the reflection will be substituted in the final user query plan for that subtree.
To illustrate this, consider an S3 dataset of JSON files with path "S3_data".transactions. It has a directory partition structure in S3 like this:
s3://{s3-root-path}/transactions/2025-06-11/data-file.json
s3://{s3-root-path}/transactions/2025-06-12/data-file.json
s3://{s3-root-path}/transactions/2025-06-13/data-file.jsonAs part of a semantic layer, we might build a simple view over this physical dataset, encapsulating a SELECT * query. We put it in a space called "Lakehouse Analytics" under "site_metrics"
-- View path: "Lakehouse Analytics"."site_metrics"."transactions" -- View SQL definition: SELECT * FROM "S3_data".transactions
Because these are JSON files and we want to optimize read access to the data, we build a raw reflection for this view and it get's ID 44b24e81-d6d2-4336-9d63-8a98557503a8.
This raw reflection is the materialized results of the above query, with the data written as Parquet files, which Dremio reads very fast.
If we issue a user query against the view like this, it will be accelerated with the data from the reflection:
SELECT * FROM Lakehouse Analytics"."site_metrics"."transactions WHERE dir0 = '2025-06-11'
To get a sense of the algebraic reflection matching, substitute the the SQL definition of the view:
SELECT * FROM Lakehouse Analytics"."site_metrics"."transactions WHERE dir0 = '2025-06-11' -- after substituion: SELECT * FROM (SELECT * FROM "S3_data".transactions) WHERE dir0 = '2025-06-11' -- which is equivalent to: SELECT * FROM "S3_data".transactions WHERE dir0 = '2025-06-11'
The reflection is more accurately thought of as being tied to the SQL than the view name itself when doing the algebraic match. Indeed, if you run that last query, which is directly against the physical dataset, it will be accelerated by the reflection on the view. Here this is shown in the raw profile:
However, a problem is introduced if the reflection is unexpired but new data is added to the physical table. Let's say we add another partition to "S3_data".transactions:
s3://{s3-root-path}/transactions/2025-06-11/data-file.json
s3://{s3-root-path}/transactions/2025-06-12/data-file.json
s3://{s3-root-path}/transactions/2025-06-13/data-file.json
# added
s3://{s3-root-path}/transactions/2025-06-14/data-file.json
After refreshing the metadata for this table, we query it for the new partition.
SELECT * FROM "S3_data".transactions WHERE dir0 = '2025-06-14'
This does not return any records. The job summary shows that it was accelerated with the unexpired raw reflection on "Lakehouse Analytics"."site_metrics"."transactions" which does not contain data from 2025-06-14:
Steps to Resolve
There a few options to resolve the problem.
1. Disable the reflection on the view. This is likely too extreme of a solution.
2. After identifying the reflection that is serving stale data, use a reflection hint to specifically exclude it from substitution. In this example, the hint would look like this:
ALTER SESSION SET "reflections.planning.no_reflections"=true; SELECT * FROM "S3_data".transactions WHERE dir0 = '2025-06-14';
3. Use a reflection hint to exclude all reflections:
ALTER SESSION SET "reflections.planning.no_reflections"=true; SELECT * FROM "S3_data".transactions WHERE dir0 = '2025-06-14';
4. Refresh the reflection on the view. This will ensure it has the most recent data.