[BUG] Snowflake resync fails due to case mismatch in _resync table suffix

[kang8]

Bug Description

When performing a resync on a CDC mirror with Snowflake as the destination, the resync fails because of inconsistent case handling of the _resync table name suffix. Tables are created with a lowercase suffix (e.g., ACCOUNTS_resync), but subsequent queries reference them with an uppercase suffix (ACCOUNTS_RESYNC). Since Snowflake treats quoted identifiers as case-sensitive, the table is not found.

Error Message

failed to copy stage to destination: failed to get columns from destination table: failed to run getTableColumnList query: 002003 (42S02): SQL compilation error: Table 'RAW.MY_SCHEMA.ACCOUNTS_RESYNC' does not exist or is not authorized.

Meanwhile, the INFO logs show the table was created as:

replicated 1 partitions to destination for table MY_SCHEMA.ACCOUNTS_resync

Root Cause

There are two code paths with inconsistent case handling:

1. Table creation — lowercase _resync suffix

flow/workflows/cdc_flow.go:597:

mapping.DestinationTableIdentifier += "_resync"

This produces mixed-case identifiers like ACCOUNTS_resync.

2. Table query — unconditional ToUpper()

flow/connectors/snowflake/qrep.go:187:

fq := fmt.Sprintf("%s.%s", strings.ToUpper(schemaTable.Namespace), strings.ToUpper(schemaTable.Table))

This converts the table name to ACCOUNTS_RESYNC, which doesn't match the created table.

Why the mismatch happens

The SnowflakeIdentifierNormalize() function in flow/connectors/snowflake/client.go:19-27 correctly handles mixed-case identifiers by preserving the original case in quotes. This function is used when creating the table.

However, getColsFromTable() in qrep.go:187 bypasses this normalization and uses raw strings.ToUpper(), breaking the case-sensitive match in Snowflake.

Impact

Resync is completely broken for Snowflake destinations. All tables fail during the initial load phase of resync.

Suggested Fix

Either:

1. Change the suffix to uppercase in cdc_flow.go:597: mapping.DestinationTableIdentifier += "_RESYNC" — simplest fix, consistent with Snowflake's uppercase convention.
2. Use SnowflakeIdentifierNormalize() (or SnowflakeQuotelessIdentifierNormalize()) in qrep.go:187 instead of raw strings.ToUpper() — more correct long-term fix.

Environment

• PeerDB version: latest (self-hosted from main branch)
• Source: PostgreSQL (RDS)
• Destination: Snowflake
• Mirror type: CDC