CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platformdynamic-tables
Dudelstein
Dudelstein

Reputation: 674

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

Upvotes: 1

Views: 437

Answers (1)

Dudelstein
Dudelstein

Reputation: 674

I did not find support for this in Snowflake documentation, but INCREMENTAL refresh does not like having the same tabled JOINed in multiple times. Once a copy of the table is created INCREMENTAL refresh works again.

Note a CTE still prevents INCREMENTAL, the table copy needs to exist stand-alone.

Working code:

CREATE TABLE BONUS_SCHEME_COPY AS 
SELECT *
FROM BONUS_SCHEME;

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 = INCREMENTAL 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_COPY B2 ON PRODUCT2 = B2.PRODUCT_CODE
where STOREID = 1234;

Upvotes: 1

Related Questions