[destination-snowflake] Confusing Error - `No active warehouse selected in the current session...` This should be a config error and mention permissions #40615

hongbo-miao · 2024-06-28T20:30:10Z

Connector Name

destination-snowflake

Connector Version

~~v3.9.0 ~ v3.10.1 have the issue.~~ (Turns out false alarm, please check comment here)
v3.8.4 works well.

What step the error happened?

Configuring a new connector

Relevant information

I found this bug starts to show since destination-snowflake v3.9.0, v3.10.1 (latest as of today) also has this issue.
However, v3.8.4 works well.

Here is my Terraform config

resource "airbyte_destination_snowflake" "hm_airbyte_destination_snowflake" {
  name         = "my-connector"
  workspace_id = "b076756a-703a-47d2-9cf7-e0b6fb651233"
  configuration = {
    host      = "hongbomiao.snowflakecomputing.com"
    warehouse = "PRODUCTION_HM_AIRBYTE_WH"
    database  = "PRODUCTION_HM_AIRBYTE_DB"
    schema    = "DATA_SCIENCE_PRIME_RADIANT_DATABASE_PRT_SCHEMA"
    role      = "HM_PRODUCTION_HM_AIRBYTE_DB_OWNER_ROLE"
    username  = "HM_PRODUCTION_HM_AIRBYTE_DB_OWNER_USER"
    credentials = {
      key_pair_authentication = {
        private_key          = var.snowflake_user_private_key
        private_key_password = var.snowflake_user_private_key_passphrase
      }
    }
    retention_period_days = 90
    raw_data_schema       = "_AIRBYTE_INTERNAL"
    disable_type_dedupe   = false
  }
}

Relevant log output

v3.8.4

Here is the screenshot when test the destination:

v3.9.0 to v3.10.1 (latest as of today)

Here is the screenshot when test the destination:

airbyte-worker log:

2024-06-28 09:39:14 �[46mplatform�[0m > WARN main c.z.h.p.ProxyConnection(checkException):178 HikariPool-1 - Connection net.snowflake.client.jdbc.SnowflakeConnectionV1@79eeff87 marked as broken because of SQLSTATE(57P03), ErrorCode(606) net.snowflake.client.jdbc.SnowflakeSQLException: No active warehouse selected in the current session.  Select an active warehouse with the 'use warehouse' command.

	at net.snowflake.client.jdbc.SnowflakeUtil.checkErrorAndThrowExceptionSub(SnowflakeUtil.java:144) ~[snowflake-jdbc-3.14.1.jar:3.14.1]
	at net.snowflake.client.jdbc.SnowflakeUtil.checkErrorAndThrowException(SnowflakeUtil.java:77) ~[snowflake-jdbc-3.14.1.jar:3.14.1]
	at net.snowflake.client.core.StmtUtil.pollForOutput(StmtUtil.java:501) ~[snowflake-jdbc-3.14.1.jar:3.14.1]
	at net.snowflake.client.core.StmtUtil.execute(StmtUtil.java:407) ~[snowflake-jdbc-3.14.1.jar:3.14.1]
	at net.snowflake.client.core.SFStatement.executeHelper(SFStatement.java:482) ~[snowflake-jdbc-3.14.1.jar:3.14.1]
	at net.snowflake.client.core.SFStatement.executeQueryInternal(SFStatement.java:199) ~[snowflake-jdbc-3.14.1.jar:3.14.1]
	at net.snowflake.client.core.SFStatement.executeQuery(SFStatement.java:133) ~[snowflake-jdbc-3.14.1.jar:3.14.1]
	at net.snowflake.client.core.SFStatement.execute(SFStatement.java:769) ~[snowflake-jdbc-3.14.1.jar:3.14.1]
	at net.snowflake.client.core.SFStatement.execute(SFStatement.java:677) ~[snowflake-jdbc-3.14.1.jar:3.14.1]
	at net.snowflake.client.jdbc.SnowflakeStatementV1.executeQueryInternal(SnowflakeStatementV1.java:267) ~[snowflake-jdbc-3.14.1.jar:3.14.1]
	at net.snowflake.client.jdbc.SnowflakePreparedStatementV1.executeQuery(SnowflakePreparedStatementV1.java:122) ~[snowflake-jdbc-3.14.1.jar:3.14.1]
	at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52) ~[HikariCP-5.1.0.jar:?]
	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java) [HikariCP-5.1.0.jar:?]
	at io.airbyte.cdk.db.jdbc.DefaultJdbcDatabase.unsafeQuery(DefaultJdbcDatabase.kt:120) [airbyte-cdk-core-0.37.1.jar:?]
	at io.airbyte.cdk.db.jdbc.JdbcDatabase.unsafeQuery(JdbcDatabase.kt:176) [airbyte-cdk-core-0.37.1.jar:?]
	at io.airbyte.cdk.db.jdbc.JdbcDatabase.queryJsons(JdbcDatabase.kt:196) [airbyte-cdk-core-0.37.1.jar:?]
	at io.airbyte.integrations.destination.snowflake.operation.SnowflakeStagingClient.copyIntoTableFromStage(SnowflakeStagingClient.kt:180) [io.airbyte.airbyte-integrations.connectors-destination-snowflake.jar:?]
	at io.airbyte.integrations.destination.snowflake.operation.SnowflakeStorageOperation.writeToStage(SnowflakeStorageOperation.kt:69) [io.airbyte.airbyte-integrations.connectors-destination-snowflake.jar:?]
	at io.airbyte.integrations.destination.snowflake.operation.SnowflakeStorageOperation.writeToStage(SnowflakeStorageOperation.kt:27) [io.airbyte.airbyte-integrations.connectors-destination-snowflake.jar:?]
	at io.airbyte.cdk.integrations.destination.staging.operation.StagingStreamOperations.writeRecords(StagingStreamOperations.kt:54) [airbyte-cdk-s3-destinations-0.37.1.jar:?]
	at io.airbyte.integrations.destination.snowflake.SnowflakeDestination.check(SnowflakeDestination.kt:155) [io.airbyte.airbyte-integrations.connectors-destination-snowflake.jar:?]
	at io.airbyte.cdk.integrations.base.IntegrationRunner.runInternal(IntegrationRunner.kt:160) [airbyte-cdk-core-0.37.1.jar:?]
	at io.airbyte.cdk.integrations.base.IntegrationRunner.run(IntegrationRunner.kt:116) [airbyte-cdk-core-0.37.1.jar:?]
	at io.airbyte.cdk.integrations.base.adaptive.AdaptiveDestinationRunner$Runner.run(AdaptiveDestinationRunner.kt:68) [airbyte-cdk-core-0.37.1.jar:?]
	at io.airbyte.integrations.destination.snowflake.SnowflakeDestinationKt.main(SnowflakeDestination.kt:345) [io.airbyte.airbyte-integrations.connectors-destination-snowflake.jar:?]

Contribute

Yes, I want to contribute

hongbo-miao · 2024-07-01T09:11:06Z

I found even "test the destination" succeed, but when actually run the job, I still got error no matter which Snowflake destination connector version:

2024-07-01 05:37:10 destination > WARN type-and-dedupe c.z.h.p.ProxyConnection(checkException):178 HikariPool-1 - Connection net.snowflake.client.jdbc.SnowflakeConnectionV1@d66a18f marked as broken because of SQLSTATE(57P03), ErrorCode(606) net.snowflake.client.jdbc.SnowflakeSQLException: No active warehouse selected in the current session.  Select an active warehouse with the 'use warehouse' command.
      at net.snowflake.client.jdbc.SnowflakeUtil.checkErrorAndThrowExceptionSub(SnowflakeUtil.java:144) ~[snowflake-jdbc-3.14.1.jar:3.14.1]
      at net.snowflake.client.jdbc.SnowflakeUtil.checkErrorAndThrowException(SnowflakeUtil.java:77) ~[snowflake-jdbc-3.14.1.jar:3.14.1]
      at net.snowflake.client.core.StmtUtil.pollForOutput(StmtUtil.java:501) ~[snowflake-jdbc-3.14.1.jar:3.14.1]
      at net.snowflake.client.core.StmtUtil.execute(StmtUtil.java:407) ~[snowflake-jdbc-3.14.1.jar:3.14.1]
      at net.snowflake.client.core.SFStatement.executeHelper(SFStatement.java:482) ~[snowflake-jdbc-3.14.1.jar:3.14.1]
      at net.snowflake.client.core.SFStatement.executeQueryInternal(SFStatement.java:199) ~[snowflake-jdbc-3.14.1.jar:3.14.1]
      at net.snowflake.client.core.SFStatement.executeQuery(SFStatement.java:133) ~[snowflake-jdbc-3.14.1.jar:3.14.1]
      at net.snowflake.client.core.SFStatement.execute(SFStatement.java:769) ~[snowflake-jdbc-3.14.1.jar:3.14.1]
      at net.snowflake.client.core.SFStatement.execute(SFStatement.java:677) ~[snowflake-jdbc-3.14.1.jar:3.14.1]
      at net.snowflake.client.jdbc.SnowflakeStatementV1.executeQueryInternal(SnowflakeStatementV1.java:267) ~[snowflake-jdbc-3.14.1.jar:3.14.1]
      at net.snowflake.client.jdbc.SnowflakePreparedStatementV1.executeQuery(SnowflakePreparedStatementV1.java:122) ~[snowflake-jdbc-3.14.1.jar:3.14.1]
      at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52) ~[HikariCP-5.1.0.jar:?]
      at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java) ~[HikariCP-5.1.0.jar:?]
      at io.airbyte.cdk.db.jdbc.DefaultJdbcDatabase.unsafeQuery(DefaultJdbcDatabase.java:129) ~[airbyte-cdk-core-0.23.2.jar:?]
      at io.airbyte.cdk.db.jdbc.JdbcDatabase.unsafeQuery(JdbcDatabase.java:200) ~[airbyte-cdk-core-0.23.2.jar:?]
      at io.airbyte.cdk.db.jdbc.JdbcDatabase.queryJsons(JdbcDatabase.java:216) ~[airbyte-cdk-core-0.23.2.jar:?]
      at io.airbyte.integrations.destination.snowflake.typing_deduping.SnowflakeV1V2Migrator.doesAirbyteInternalNamespaceExist(SnowflakeV1V2Migrator.java:42) ~[io.airbyte.airbyte-integrations.connectors-destination-snowflake.jar:?]
      at io.airbyte.integrations.base.destination.typing_deduping.BaseDestinationV1V2Migrator.doesValidV2RawTableAlreadyExist(BaseDestinationV1V2Migrator.java:114) ~[airbyte-cdk-typing-deduping-0.23.2.jar:?]
      at io.airbyte.integrations.base.destination.typing_deduping.BaseDestinationV1V2Migrator.shouldMigrate(BaseDestinationV1V2Migrator.java:47) ~[airbyte-cdk-typing-deduping-0.23.2.jar:?]
      at io.airbyte.integrations.base.destination.typing_deduping.BaseDestinationV1V2Migrator.migrateIfNecessary(BaseDestinationV1V2Migrator.java:27) ~[airbyte-cdk-typing-deduping-0.23.2.jar:?]
      at io.airbyte.integrations.base.destination.typing_deduping.DefaultTyperDeduper.lambda$runMigrationsAsync$0(DefaultTyperDeduper.java:138) ~[airbyte-cdk-typing-deduping-0.23.2.jar:?]
      at java.base/java.util.concurrent.CompletableFuture$AsyncRun.run(CompletableFuture.java:1804) ~[?:?]
      at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1144) ~[?:?]
      at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:642) ~[?:?]
      at java.base/java.lang.Thread.run(Thread.java:1583) [?:?]
Stack Trace: net.snowflake.client.jdbc.SnowflakeSQLException: No active warehouse selected in the current session.  Select an active warehouse with the 'use warehouse' command.
      at net.snowflake.client.jdbc.SnowflakeUtil.checkErrorAndThrowExceptionSub(SnowflakeUtil.java:144)
      at net.snowflake.client.jdbc.SnowflakeUtil.checkErrorAndThrowException(SnowflakeUtil.java:77)
      at net.snowflake.client.core.StmtUtil.pollForOutput(StmtUtil.java:501)
      at net.snowflake.client.core.StmtUtil.execute(StmtUtil.java:407)
      at net.snowflake.client.core.SFStatement.executeHelper(SFStatement.java:482)
      at net.snowflake.client.core.SFStatement.executeQueryInternal(SFStatement.java:199)
      at net.snowflake.client.core.SFStatement.executeQuery(SFStatement.java:133)
      at net.snowflake.client.core.SFStatement.execute(SFStatement.java:769)
      at net.snowflake.client.core.SFStatement.execute(SFStatement.java:677)
      at net.snowflake.client.jdbc.SnowflakeStatementV1.executeQueryInternal(SnowflakeStatementV1.java:267)
      at net.snowflake.client.jdbc.SnowflakePreparedStatementV1.executeQuery(SnowflakePreparedStatementV1.java:122)
      at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)
      at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)
      at io.airbyte.cdk.db.jdbc.DefaultJdbcDatabase.unsafeQuery(DefaultJdbcDatabase.java:129)
      at io.airbyte.cdk.db.jdbc.JdbcDatabase.unsafeQuery(JdbcDatabase.java:200)
      at io.airbyte.cdk.db.jdbc.JdbcDatabase.queryJsons(JdbcDatabase.java:216)
      at io.airbyte.integrations.destination.snowflake.typing_deduping.SnowflakeV1V2Migrator.doesAirbyteInternalNamespaceExist(SnowflakeV1V2Migrator.java:42)
      at io.airbyte.integrations.base.destination.typing_deduping.BaseDestinationV1V2Migrator.doesValidV2RawTableAlreadyExist(BaseDestinationV1V2Migrator.java:114)
      at io.airbyte.integrations.base.destination.typing_deduping.BaseDestinationV1V2Migrator.shouldMigrate(BaseDestinationV1V2Migrator.java:47)
      at io.airbyte.integrations.base.destination.typing_deduping.BaseDestinationV1V2Migrator.migrateIfNecessary(BaseDestinationV1V2Migrator.java:27)
      at io.airbyte.integrations.base.destination.typing_deduping.DefaultTyperDeduper.lambda$runMigrationsAsync$0(DefaultTyperDeduper.java:138)
      at java.base/java.util.concurrent.CompletableFuture$AsyncRun.run(CompletableFuture.java:1804)
      at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1144)
      at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:642)
      at java.base/java.lang.Thread.run(Thread.java:1583)

hongbo-miao · 2024-07-01T09:20:43Z

It turns out a false alarm. I think all verisons including latest v3.10.1 all work well!

I actually made several mistakes.

Here are tips to make it work, let's based on this Snowflake destination settings:

(I made a mistake here) Make sure you granted the Snowflake role you use in Airbyte of the warehouse permission, in my case, meaning PRODUCTION_HM_AIRBYTE_DB_OWNER_ROLE should have permission of PRODUCTION_HM_AIRBYTE_WH.
(I made a mistake here) Make sure your warehouse in settings has no typo, in my case it should be PRODUCTION_HM_AIRBYTE_WH.
(I think this one is optional, but I always set) Snowflake user has default warehouse and default role set. In my case PRODUCTION_HM_AIRBYTE_DB_OWNER_USER default warehouse is PRODUCTION_HM_AIRBYTE_WH and default role is PRODUCTION_HM_AIRBYTE_DB_OWNER_ROLE.

Hope it helps future users! ☺️

I will leave this open as I feel the error is kind of misleading, maybe worth to throw different errors such as

PRODUCTION_HM_AIRBYTE_DB_OWNER_ROLE does not have PRODUCTION_HM_AIRBYTE_WH permission
XXX_WH warehouse does not exist.

marcosmarxm · 2024-07-04T14:03:05Z

@airbytehq/destinations can someone take a look into this?

HishamAssi · 2024-07-17T01:27:43Z

Can I take a jab at this as a first time contributor?

I see the only time that string is used is at:

AssertionsForClassTypes.assertThat(standardCheckConnectionOutput.message)
            .contains(
                SnowflakeInsertDestinationAcceptanceTest.Companion.NO_ACTIVE_WAREHOUSE_ERR_MSG
            )

It seems like a pretty generic catch all error message so I can just edit the ERROR Message (NO_ACTIVE_WAREHOUSE_ERR_MSG) to allude to a permission issue as well.

marcosmarxm · 2024-07-17T18:17:44Z

@airbytehq/destinations would be someone able to check the suggestion to fix the issue? Thanks!

evantahler · 2024-07-17T19:53:02Z

@HishamAssi - sure, please link the PR once you have it ready

evantahler · 2024-09-04T22:32:04Z

We'll tackle this in-house

octavia-squidington-iii · 2025-03-09T09:00:53Z

At Airbyte, we seek to be clear about the project priorities and roadmap. This issue has not had any activity for 180 days, suggesting that it's not as critical as others. It's possible it has already been fixed. It is being marked as stale and will be closed in 20 days if there is no activity. To keep it open, please comment to let us know why it is important to you and if it is still reproducible on recent versions of Airbyte.

hongbo-miao added area/connectors Connector related issues needs-triage type/bug Something isn't working labels Jun 28, 2024

octavia-squidington-iii added autoteam team/connectors-python community labels Jun 28, 2024

hongbo-miao mentioned this issue Jun 30, 2024

Airbyte hongbo-miao/hongbomiao.com#17463

Closed

hongbo-miao closed this as completed Jul 1, 2024

hongbo-miao mentioned this issue Jul 1, 2024

Snowflake active warehouse not selected #9633

Closed

hongbo-miao reopened this Jul 1, 2024

marcosmarxm changed the title ~~No active warehouse selected in the current session. Select an active warehouse with the 'use warehouse' command.~~ [destination-snowflake] No active warehouse selected in the current session. Select an active warehouse with the 'use warehouse' command. Jul 4, 2024

marcosmarxm added connectors/destination/snowflake team/destinations Destinations team's backlog and removed needs-triage team/connectors-python autoteam labels Jul 4, 2024

evantahler added the good-first-issue label Jul 6, 2024

natikgadzhi added good first issue and removed good-first-issue labels Jul 9, 2024

github-project-automation bot added this to Community Contributions Connectors Jul 9, 2024

github-project-automation bot moved this to Todo in Community Contributions Connectors Jul 9, 2024

evantahler added the good-first-issue label Jul 16, 2024

HishamAssi mentioned this issue Jul 21, 2024

bug(destination-snowflake): improve error message for non-active warehouse #42086

Closed

evantahler assigned jitenoswal Sep 9, 2024

octavia-squidington-iii added the Stale label Mar 9, 2025

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

[destination-snowflake] Confusing Error - `No active warehouse selected in the current session...` This should be a config error and mention permissions #40615

[destination-snowflake] Confusing Error - `No active warehouse selected in the current session...` This should be a config error and mention permissions #40615

hongbo-miao commented Jun 28, 2024 •

edited

Loading

hongbo-miao commented Jul 1, 2024 •

edited

Loading

hongbo-miao commented Jul 1, 2024 •

edited

Loading

marcosmarxm commented Jul 4, 2024

HishamAssi commented Jul 17, 2024 •

edited

Loading

marcosmarxm commented Jul 17, 2024

evantahler commented Jul 17, 2024

evantahler commented Sep 4, 2024

octavia-squidington-iii commented Mar 9, 2025

[destination-snowflake] Confusing Error - No active warehouse selected in the current session... This should be a config error and mention permissions #40615

[destination-snowflake] Confusing Error - No active warehouse selected in the current session... This should be a config error and mention permissions #40615

Comments

hongbo-miao commented Jun 28, 2024 • edited Loading

Connector Name

Connector Version

What step the error happened?

Relevant information

Relevant log output

v3.8.4

v3.9.0 to v3.10.1 (latest as of today)

Contribute

hongbo-miao commented Jul 1, 2024 • edited Loading

hongbo-miao commented Jul 1, 2024 • edited Loading

marcosmarxm commented Jul 4, 2024

HishamAssi commented Jul 17, 2024 • edited Loading

marcosmarxm commented Jul 17, 2024

evantahler commented Jul 17, 2024

evantahler commented Sep 4, 2024

octavia-squidington-iii commented Mar 9, 2025

[destination-snowflake] Confusing Error - `No active warehouse selected in the current session...` This should be a config error and mention permissions #40615

[destination-snowflake] Confusing Error - `No active warehouse selected in the current session...` This should be a config error and mention permissions #40615

hongbo-miao commented Jun 28, 2024 •

edited

Loading

hongbo-miao commented Jul 1, 2024 •

edited

Loading

hongbo-miao commented Jul 1, 2024 •

edited

Loading

HishamAssi commented Jul 17, 2024 •

edited

Loading