[SQL] Dynamic table FULL refresh when adding a second LEFT JOIN

I have an issue with a dynamic table. Using a single LEFT JOIN, I have Incremental refresh as desired. When adding one more LEFT JOIN, the refresh changes into FULL. Do you know why the refresh changes to FULL and how INCREMENTAL can be achieved?

My dataset contains data on stores and transactions, specifically which products were bought (12 is the maximum amount that can be bought). I want to join in a table BONUS_SCHEME which has information on whether the given product has associated bonus, Below is the script with 2 joins, which already triggers a FULL refresh. Thank you for help!

create or replace dynamic table PRODUCT_BONUSES (
STOREID,
TRANSACTION_NUMBER,
PRODUCT1, PRODUCT2, PRODUCT3, PRODUCT4, PRODUCT5, PRODUCT6,
PRODUCT7, PRODUCT8, PRODUCT9, PRODUCT10, PRODUCT11, PRODUCT12,
BONUS1, BONUS2
) lag = ‘2 hours’ refresh_mode = AUTO initialize = ON_CREATE warehouse = ENGINEERING
as
SELECT
STOREID,
TRANSACTION_NUMBER,
PRODUCT1, PRODUCT2, PRODUCT3, PRODUCT4, PRODUCT5, PRODUCT6,
PRODUCT7, PRODUCT8, PRODUCT9, PRODUCT10, PRODUCT11, PRODUCT12,
B1.BONUS1,
B2.BONUS2
FROM multilinestage0
LEFT JOIN BONUS_SCHEME B1 ON PRODUCT1 = B1.PRODUCT_CODE
LEFT JOIN BONUS_SCHEME B2 ON PRODUCT2 = B2.PRODUCT_CODE
where STOREID = 1234;


When I change the code to refresh_mode = INCREMENTAL I get an error:

002758 (0A000): SQL compilation error: Invalid refresh mode ‘INCREMENTAL’: Change tracking is not supported on queries with UNION ALLs or outer joins that would produce conflicting ROW_IDs.


According to documentation:

Supported join types for incremental refresh include inner joins, outer-equi joins, cross joins
​

Continue reading...