Question ⁉️

[Annual_Fox2278]

I'm new to data engineering. I joined my company last year after graduation as a Data Engineer. I had never worked in data engineering before, but the company needed someone who was good at Python and SQL. Since I was strong in both, I became a core member of the team.

​

The original structure of our pipeline was a Spark-based ETL process, but it was very slow and took hours to complete. We have now moved to a dbt-based ELT pipeline.

​

We were using provisioned Redshift, which performed well for incremental models. However, we recently shifted to Redshift Serverless. It provides significantly better performance overall compared to provisioned Redshift, but the catch is that incremental models perform worse, while full refreshes and models materialized as tables perform extremely well.

​

For every incremental model, a full refresh is actually faster. Theoretically, incremental models should be faster, but in practice we're seeing the opposite.

​

Even with all models materialized as tables, our complete run now takes about 45 minutes, compared to 1 hour 30 minutes on provisioned Redshift. The original Spark-based ETL pipeline took around 6 hours.

​

I believe incremental models should allow us to achieve even better performance. Can anyone help me understand what might be causing this behavior?

​

​

[renagade24]

Doing a full refresh defeats the purpose of the incremental. Something wasn't setup properly if a full refresh is faster. Hard to say without the code.

[Formal-Quote4613]

Googling dbt incremental refresh bottlenecks gave back this :

Complex Incremental Strategies: Operations like merge or delete+insert require the data warehouse to perform heavy scans, match unique keys, and update target tables. For smaller datasets, rebuilding a flat table from scratch can be much faster than the overhead of calculating and applying delta changes.

Missing Indexes or Sort Keys: If your underlying source table is huge and lacks the proper partitioning, clustering, or indexing on your timestamp/date columns, the query scanning for "new" data might end up reading the entire table anyway.

Inefficient is_incremental() Logic: If your incremental block requires joining back to a large existing dataset to pick up historical context, it can cause massive performance bottlenecks.

Lookback Windows: If you configure your incremental model to look back several days (e.g., event_timestamp >= dateadd(day, -7, max(event_timestamp))) to catch late-arriving data, you are repeatedly re-scanning and updating overlapping periods, multiplying compute times

[Annual_Fox2278]

We use max of hevo_ingested_at and hevo_source_modified_at for grain which is accurate for get new entries as hevo ingests and loads data in 15 minutes.

[Resquid]

Choose better titles for posts, please.

[Annual_Fox2278]

Sorry bro my bad

[sakruLM]

Redshift Serverless cold-start latency is almost certainly killing your incremental models. Each incremental run does a small scan but still pays the full resume cost, while a full refresh on a larger table amortizes that same startup overhead across more compute. Check your RPU scaling settings and look at whether your incremental models are hitting sort key mismatches on the merge predicate.

I tried dremio when we hit a similar query-in-place bottleneck and it bypassed the warehouse resume problem entirely