SSC-EWI-RS0009

Semantic information not found for the source table.

Severity

Low

Description

In Snowflake, the MERGE with REMOVE DUPLICATES clause is not supported. To fully support the original code, a workaround is needed. An INSERT WHEN NOT MATCHED clause needs to be generated in the output code, and it requires the source table columns. If the source table was not found during migration, no columns are generated, and an error will be thrown.

Code Example

Input Code:

Redshift

IN -> Redshift_01.sql

MERGE INTO target USING source ON target.id = source.id REMOVE DUPLICATES;

Output Code:

Snowflake

OUT -> Redshift_01.sql

CREATE TEMPORARY TABLE source_duplicates AS
SELECT DISTINCT
source.*
FROM
source
INNER JOIN
target
ON target.id = source.id;
!!!RESOLVE EWI!!! /*** SSC-EWI-RS0009 - SEMANTIC INFORMATION NOT FOUND FOR THE SOURCE TABLE. COLUMNS TO BE INSERTED MAY BE ADDED MANUALLY. ***/!!!
--** SSC-FDM-RS0005 - REDSHIFT MERGE STATEMENT DOESN'T ALLOW DUPLICATES IN THE SOURCE TABLE. SNOWFLAKE BEHAVIOR MAY DIFFER IF THERE ARE DUPLICATE VALUES. **
MERGE INTO target
USING source ON target.id = source.id
WHEN MATCHED THEN
DELETE
WHEN NOT MATCHED THEN
INSERT
VALUES ();
INSERT INTO target
SELECT
*
FROM
source_duplicates;
DROP TABLE IF EXISTS source_duplicates CASCADE;

Recommendations

• Search to see if the source table is present in the migrated code and manually add the columns in the insert operation.

• If you need more support, you can email us at [email protected]