dbt-labs / dbt-labs/dbt-adapters
[Feature] Improve incremental materialized views refresh time
- Dominant language
- Python
- Stars
- 233
- Forks
- 362
- Avg merge
- 3d 22h
- Merged PRs (30d)
- 9
Description
### Is this a new bug in dbt-redshift?
- [X] I believe this is a new bug in dbt-redshift
- [X] I have searched the existing issues, and I could not find an existing issue for this bug
### Current Behavior
Refreshing materialized views manually is significantly faster than refreshing via dbt.
### Expected Behavior
Refreshing via dbt is faster, especially when the underlying data has not changed and the model configs have not changed.
### Steps To Reproduce
1. Set project config.
```
# test_mv\dbt_project.yml
models:
test_mv:
+auto_refresh: false
+backup: true
+materialized: materialized_view
+on_configuration_change: fail
```
2. Create 8 models.
```
-- test_mv\models\example\model1.sql
select * from {{ source("qa", "source1") }}
```
3. First `dbt run --threads 8` to create incremental materialized views.
```
Finished running 8 materialized_view models in 0 hours 0 minutes and 44.23 seconds (44.23s).
```
4. Second `dbt run --threads 8` to do initial refresh.
```
Finished running 8 materialized_view models in 0 hours 0 minutes and 32.66 seconds (32.66s).
```
5. Third `dbt run --threads 8` to get timing for refreshing via dbt.
```
Finished running 8 materialized_view models in 0 hours 0 minutes and 32.45 seconds (32.45s).
```
6. In DBeaver, click SQL Editor, then click Execute queries in separate tabs.
```
refresh materialized view "skynet"."dbt_mv"."model1";
refresh materialized view "skynet"."dbt_mv"."model2";
refresh materialized view "skynet"."dbt_mv"."model3";
refresh materialized view "skynet"."dbt_mv"."model4";
refresh materialized view "skynet"."dbt_mv"."model5";
refresh materialized view "skynet"."dbt_mv"."model6";
refresh materialized view "skynet"."dbt_mv"."model7";
refresh materialized view "skynet"."dbt_mv"."model8";
```
```
Materialized view model1 is already up to date.
Materialized view model2 is already up to date.
Materialized view model3 is already up to date.
Materialized view model4 is already up to date.
Materialized view model5 is already up to date.
Materialized view model6 is already up to date.
Materialized view model7 is already up to date.
Materialized view model8 is already up to date.
```
```
Total time (ms) 4371
```
### Relevant log output
_No response_
### Environment
```markdown
- OS: Windows 11 Pro
- Python: 3.11.5
- dbt-core: 1.7.2
- dbt-redshift: 1.7.0
- Cluster: test environment with no other users
- Node type: ra3.xlplus
- Number of nodes: 2
```
### Additional Context
I'm evaluating if dbt can be used for near real time reporting using incremental materialized views in staging and core models.
Contributor guide
Assessment
This issue has not been assessed yet.