Snowflake: mixed-case column names break MERGE during schema delta replay

[kang8]

Bug Description

When a PostgreSQL source table has mixed-case column names (e.g., "pulseArmor", "highGold"), the Snowflake CDC normalization fails with:

SQL compilation error: error line 14 at position 281 invalid identifier '"pulseArmor"'

Root Cause

There is an inconsistency in column name handling between ALTER TABLE ADD COLUMN (schema delta replay) and MERGE statement generation:

• snowflake.go:377-378 (ALTER TABLE ADD COLUMN) uses strings.ToUpper(addedColumn.Name):

  fmt.Sprintf("ALTER TABLE %s ADD COLUMN IF NOT EXISTS \"%s\" %s",
      schemaDelta.DstTableName, strings.ToUpper(addedColumn.Name), sfColtype)

  This creates the column as "PULSEARMOR" in Snowflake.

• merge_stmt_generator.go uses SnowflakeIdentifierNormalize(column.Name):

  targetColumnName := SnowflakeIdentifierNormalize(column.Name)

  For mixed-case identifiers, SnowflakeIdentifierNormalize preserves the original case (per client.go:19-27), producing "pulseArmor" in the MERGE statement.

Since Snowflake treats quoted identifiers as case-sensitive, "PULSEARMOR" ≠ "pulseArmor", causing the MERGE to fail.

Suggested Fix

In flow/connectors/snowflake/snowflake.go:377-378, replace:

fmt.Sprintf("ALTER TABLE %s ADD COLUMN IF NOT EXISTS \"%s\" %s",
    schemaDelta.DstTableName, strings.ToUpper(addedColumn.Name), sfColtype)

with:

fmt.Sprintf("ALTER TABLE %s ADD COLUMN IF NOT EXISTS %s %s",
    schemaDelta.DstTableName, SnowflakeIdentifierNormalize(addedColumn.Name), sfColtype)

This ensures both ALTER TABLE and MERGE use the same normalization logic.

Steps to Reproduce

1. Create a PG → Snowflake CDC mirror
2. Add a mixed-case column to the source table:

   ALTER TABLE users ADD COLUMN "pulseArmor" INTEGER;

3. Insert a row with data in the new column
4. Wait for the next sync cycle — normalization fails

Environment

• PeerDB version: latest main branch
• Source: PostgreSQL (RDS)
• Destination: Snowflake

[JGustavo0]

Altering the PeerDB user's QUOTED_IDENTIFIERS_IGNORE_CASE setting could mitigate this, according to my tests:
ALTER USER PEERDB_USER SET QUOTED_IDENTIFIERS_IGNORE_CASE = TRUE

@kang8, am I missing something?

Thanks!

[kang8]

Thanks for the workaround suggestion — setting QUOTED_IDENTIFIERS_IGNORE_CASE = TRUE on the PeerDB user does resolve the immediate error, and I can confirm it works in practice.

That said, this masks the real inconsistency rather than fixing it. Looking at the Snowflake connector codebase, SnowflakeIdentifierNormalize() is the established convention for all user-defined identifiers — it's used in 9 places across snowflake.go (L664, L701, L777, L782) and merge_stmt_generator.go (L45, L78, L92, L118, L180). The single exception is the ADD COLUMN path:

peerdb/flow/connectors/snowflake/snowflake.go

Line 378 in 85f2c07

schemaDelta.DstTableName, strings.ToUpper(addedColumn.Name), sfColtype),

The correct fix is to replace strings.ToUpper(addedColumn.Name) with SnowflakeIdentifierNormalize(addedColumn.Name) at that line. This ensures the column is created and referenced through the same normalization logic everywhere, without relying on a Snowflake user-level parameter to paper over the mismatch.

[kang8]

There is one more latent occurrence of the same pattern that may be worth fixing together for consistency: flow/connectors/snowflake/merge_stmt_generator.go:84.

if m.peerdbCols.SyncedAtColName != "" {
    // append synced_at column
    quotedUpperColNames = append(quotedUpperColNames,
        fmt.Sprintf(`"%s"`, strings.ToUpper(m.peerdbCols.SyncedAtColName)),
    )
}

The column on this same line is created at snowflake.go:692 with:

createTableSQLArray = append(createTableSQLArray, config.SyncedAtColName+" TIMESTAMP DEFAULT SYSDATE()")

So if a user configures SyncedAtColName to a mixed-case value (e.g. MySyncedAt):

• Table creation stores it as MYSYNCEDAT (unquoted DDL, folded to uppercase by Snowflake).
• The MERGE references "MYSYNCEDAT" (quoted, case-sensitive lookup for MYSYNCEDAT). This happens to work.
• But if either side ever switches to SnowflakeIdentifierNormalize (e.g. to be consistent with regular columns), the two sides will diverge in the same way as Snowflake: mixed-case column names break MERGE during schema delta replay #4186.

In practice the default _PEERDB_SYNCED_AT is already uppercase so this is currently a no-op, but the call still violates the "use the same normalization rule for create and reference" invariant. Suggest applying SnowflakeIdentifierNormalize(m.peerdbCols.SyncedAtColName) here (and similarly normalizing at create time) as part of the same cleanup.