Connector Name

destination-snowflake

Connector Version

4.0.17

What step the error happened?

During the sync

Relevant information

When using 'Full Refresh | Overwrite' sync mode with a Snowflake destination, future table grants (e.g., SELECT privileges) are lost after the second sync completes. The grants remain intact after the first sync but disappear on subsequent syncs. This issue does not occur with 'Incremental | Append + Deduped' sync mode.

We are using Snowflake destination v4.0.17.

Steps to reproduce

1. Create a connection from a source with 2 streams (we use MySQL) to a Snowflake destination
2. For stream 1, configure sync mode 'Incremental | Append + Deduped'
3. For stream 2, configure sync mode 'Full refresh | Overwrite'
4. In Snowflake, grant a role X SELECT privilege on all future tables in the target schema, e.g. grant select on future tables in schema dev.default to role X;
5. Run a sync.
6. Run a sync again.

Expected behavior

1. After the first sync completes, Snowflake role X has the SELECT privilege on both tables in the target schema.
2. After the second sync completes, Snowflake role X has the SELECT privilege on both tables in the target schema.

Actual behavior

1. After the first sync completes, Snowflake role X has the SELECT privileges on both tables in the target schema.
2. After the second sync completes, Snowflake role X no longer has the SELECT privileges on the table for stream 2 (using "Full refresh | Overwrite").
   The role does still have the privilege on the table for stream 1 ("Incremental | Append + Deduped").

Notes

1. I confirmed the behavior is independent on the type of source (e.g. MySQL database or REST API).

2. I suspect, but am not certain, this behavior started appearing when we upgraded the Snowflake destination from version 3.15.x to 4.0.17.

3. I noticed that for "Full refresh | Overwrite", the connector executes a query like:

   ALTER TABLE "DEV"."airbyte_internal"."AIRBYTEMYTABLE2250aad69847d22e372f3298ba9e52df9" SWAP WITH "DEV"."DEFAULT"."MYTABLE2"

   I suspect the connector creates replacement tables in the airbyte_internal schema and uses ALTER TABLE...SWAP WITH to replace the target table, but Snowflake's future grants only apply to new tables in the original schema, causing the swapped table to lose privileges.
   In contrast, "Incremental | Append + Deduped" appears to use a MERGE INTO statement.

Relevant log output

2025-10-28 15:51:16 info INFO main i.a.c.l.d.p.PipelineRunner(run):74 Destination Pipeline Completed — Successfully
2025-10-28 15:51:16 info INFO DefaultDispatcher-worker-3 i.a.c.l.d.DestinationLifecycle$finalizeIndividualStreams$2$1$1(invokeSuspend):93 Finalizing stream null:MYTABLE1
2025-10-28 15:51:16 info INFO DefaultDispatcher-worker-5 i.a.c.l.d.DestinationLifecycle$finalizeIndividualStreams$2$1$1(invokeSuspend):93 Finalizing stream null:MYTABLE2
2025-10-28 15:51:16 info INFO DefaultDispatcher-worker-3 i.a.i.d.s.s.SnowflakeDirectLoadSqlGeneratorKt(andLog):32 CREATE STAGE IF NOT EXISTS "DEV"."DEFAULT"."airbyte_stage_MYTABLE1"
2025-10-28 15:51:16 info INFO DefaultDispatcher-worker-5 i.a.c.l.o.d.d.DirectLoadTableAppendTruncateStreamLoader(close):239 Overwriting airbyte_internal.AIRBYTEMYTABLE22c11da0411ad033a972bd70d42383b4a with AIRBYTE.MYTABLE2 for stream MYTABLE2
2025-10-28 15:51:16 info INFO DefaultDispatcher-worker-5 i.a.i.d.s.s.SnowflakeDirectLoadSqlGeneratorKt(andLog):32 SELECT COUNT(*) AS TOTAL FROM "DEV"."DEFAULT"."MYTABLE2"
2025-10-28 15:51:16 info INFO DefaultDispatcher-worker-5 i.a.i.d.s.c.SnowflakeAirbyteClient(overwriteTable):122 overwriteTable: source=airbyte_internal.AIRBYTEMYTABLE22c11da0411ad033a972bd70d42383b4a, target=AIRBYTE.MYTABLE2, targetExists=true
2025-10-28 15:51:16 info INFO DefaultDispatcher-worker-5 i.a.i.d.s.c.SnowflakeAirbyteClient(overwriteTable):128 Using SWAP operation since target table exists
2025-10-28 15:51:16 info INFO DefaultDispatcher-worker-5 i.a.i.d.s.s.SnowflakeDirectLoadSqlGeneratorKt(andLog):32 ALTER TABLE "DEV"."airbyte_internal"."AIRBYTEMYTABLE22c11da0411ad033a972bd70d42383b4a" SWAP WITH "DEV"."DEFAULT"."MYTABLE2"
2025-10-28 15:51:17 info INFO DefaultDispatcher-worker-3 i.a.i.d.s.s.SnowflakeDirectLoadSqlGeneratorKt(andLog):32 DESCRIBE TABLE "DEV"."DEFAULT"."MYTABLE1"
2025-10-28 15:51:17 info INFO DefaultDispatcher-worker-3 i.a.i.d.s.s.SnowflakeDirectLoadSqlGeneratorKt(andLog):32 WITH records AS (

Contribute

• Yes, I want to contribute