Incremental Merge to Update Existing Value

Help
1

The problem I’m having

I am having a hard time wrapping my head around incremental strategies that involve an UPDATE like SQL syntax for existing values.

The context of why I’m trying to do this

In SQL, I would normally have done an UPDATE like

UPDATE products
SET quantity = quantity + 3

where the Product table is:

Products
    Quantity
    5

and the result is

Quantity
8

I’ve been reading the dbt document around incremental strategies, and I can’t quick get the answer I am looking for. I’ve manually tested it, and it works the way I would think it does, but would love a confirmation that I am not misunderstanding

What I’ve already tried

My test table. I started with the single Timestamp ‘2022-01-01’, created my intermediate table with the incremental strategy, then added new row > ran my incremental model again, etc… for each of the other timestamp for ProductID = 1

{{ config(materialized='table') }}

SELECT
    product_id,
    event_timestamp,
    quantity
FROM VALUES
  (1, TIMESTAMP '2022-01-01', 10),
  (2, TIMESTAMP '2022-01-01', 1),
  (3, TIMESTAMP '2022-01-01', 1),
  (1, TIMESTAMP '2022-01-02', 1),
  (1, TIMESTAMP '2022-01-03', 10)
AS 
  t(product_id, event_timestamp, quantity)

My aggregation / incremental table

{{ config(
        materialized = 'incremental',
        unique_key = 'product_id',
        incremental_strategy='merge',
   ) 
}}

WITH new_events AS (

    SELECT
        product_id,
        MAX(event_timestamp) AS last_updated,
        SUM(quantity) AS quantity
    FROM
        {{ ref("base__test_products") }}
    {% if incremental %}
    WHERE
        event_timestamp > (SELECT MAX(last_updated) FROM {{ this }})
    {% endif %}
    GROUP BY
        product_id
)

SELECT
    product_id,
    last_updated,
    quantity
FROM
  new_events

Thanks in advance!!

2

same problem in redshift, waiting for answer…