dbt-labs / dbt-labs/dbt-adapters

[Feature] Improve incremental materialized views refresh time

Open
#641 7 comments 0 reactions 0 assignees View on GitHub
feature:materialized-views feature:ra3-node pkg:dbt-redshift type:enhancement
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.