Custom Function Syntax error Unexpected keyword MERGE

[ks007]

Below is the scd_utils.js code which is used for scd type 2 operation.

// includes/scd_utils.js
 
/**
* Generates SQL for SCD Type 2 operations using MERGE
*
*  {string} sourceTable - The source table or CTE
*  {string} targetTable - The target table
*  {Array<string>} keyColumns - List of key columns
*  {Array<string>} scdColumns - List of SCD columns
*  {string} loadDatetimeColumn - Column for load datetime
* @returns {string} SQL code for SCD Type 2 operations
*/
function scdType2(sourceTable, targetTable, keyColumns, scdColumns, loadDatetimeColumn) {
  const keyColumnsStr = keyColumns.join(", ");
  const keyColumnsMatch = keyColumns.map(col => `target.${col} = source.${col}`).join(" AND ");
  const scdColumnsStr = scdColumns.join(", ");
 
  return `
    MERGE INTO ${targetTable} AS target
    USING ${sourceTable} AS source
    ON (${keyColumnsMatch})
    WHEN MATCHED AND target.is_current = TRUE
        AND (target.${scdColumns.map(col => `target.${col} != source.${col}`).join(" OR ")})
    THEN
      UPDATE SET
        target.scd_valid_to = source.${loadDatetimeColumn},
        target.is_current = FALSE
    WHEN NOT MATCHED
    THEN
      INSERT (${keyColumnsStr}, ${scdColumnsStr}, scd_valid_from, scd_valid_to, is_current)
      VALUES (${keyColumnsStr}, ${scdColumnsStr}, source.${loadDatetimeColumn}, NULL, TRUE)
  `;
}
 
module.exports = { scdType2 }

The below .sqlx code calls the scd type 2 function :

-- Use the SCD Type 2 logic from the JavaScript file
${scd_utils.scdType2(
  `temp`,  // Source table (the CTE "temp" prepared above)
  `${self()}`,  // Target table (the table being defined here)
  ["id"],  // Key columns
  ["post_code", "suburb", "geometry"],  // SCD tracked columns
  "load_dt"  // Load datetime column
)}

Getting Compile Error : Syntax error: Unexpected keyword MERGE at [24:5]

[caryna]

Hi @ks007,

Welcome to the Google Cloud Community!

From what I understand, the JavaScript code you’ve provided helps you track historical changes and understand how dimensions have evolved over time by identifying and updating new or inserting new rows. The .sqlx file then calls the function (scdType2) with the required parameters from scd_utils.js to generate SQL. 

The error message "Unexpected keyword MERGE" means Dataform is having trouble with the MERGE statement generated by scdType2 function within your .sqlx file. It seems that Dataform isn't recognizing the MERGE statement as a valid SQL command.

Try to check the SQL dialect you're using. Verify that the SQL produced by your scdType2 function is compatible with the SQL dialect used by Dataform. Remember that the MERGE statement is not supported in Legacy SQL. For querying data, it's recommended to use GoogleSQL or known as Standard SQL, which is the preferred query syntax.

I hope the above information is helpful.