Dbt Incremental Model using outdated reflection

1

I am using dbt-dremio to update my models in dremio.

I have the below dependency path in dbt

1.) fct_daily_shareholding_history (incremental)

2.) fct_daily_shareholding_change_history (incremental)

Item 2 depends on item 1.

I have issue with item 2, where new records are not found. Item 1 has the required new records.

I suspect that the
cause is that I have a reflection on item 1.

The reflection is also managed by dbt-dremio (using model=reflection).

However, dbt_dremio immediately returns after it kick-start the reflection and does not wait for it’s completion.

Now, when item 2 starts, it queries the outdated reflection (I can see it being accelerated on the UI).

So I suspect this explains why new records are only found in item 1, but not in item 2.

Screenshot_20240730_094027_Chrome
Screenshot_20240730_094027_Chrome1079×1371 181 KB

2

Is there a way to temporarily stop the query planner from considering reflection during dbt run?

Or is there a way, in dbt, to wait for the reflection to complete, before moving on to the next model??

3

Found this ALTER session command which can disable using reflection in the session.

Let me try this. :pray:

4

HI all,

I followed the documentation here (Influencing the Choice of Reflections Used to Satisfy Queries | Dremio Documentation)

But I am getting EOF error in the dremio UI

image
image1496×816 41.9 KB

============

It is now working after I remove the double quotes.

image
image1496×816 44.7 KB

5

I finally settled with this solution in my dbt project, where I want none of my models to use reflections.

  • In dbt_project.yaml
on-run-start:
  - '{{ turn_off_reflections() }}'

on-run-end:
  - '{{ turn_on_reflections() }}'
  • In the macro folder
{% macro turn_off_reflections() %}
ALTER SYSTEM SET reflections.planning.no_reflections=true;
{% endmacro %}

{% macro turn_on_reflections() %}
ALTER SYSTEM SET reflections.planning.no_reflections=false;
{% endmacro %}

The advantage of using SYSTEM (alter system) ensures that everything is run without reflections during dbt run. This circumvents the problem of SESSION (alter session), where only queries which run in a multi-statement block work. While this is quite doable on the Dremio UI, this is a bit difficult with dbt-dremio.

1 Like
6

I created a video walkthrough of my solution here :slight_smile:

2 Likes
7

Awesome work @Ken Thanks for sharing

1 Like