airbytehq / airbytehq/airbyte

source-snowflake: Discovery fails when "Check table and column access privileges" is enabled with views using session variables; schema filtering may not work correctly

オープン
#72,488 コメント 5 件 リアクション 0 件 担当者 1 名 @jrbarkin が担当を希望しています GitHub で見る
area/connectors autoteam connectors/source/snowflake hyd-review team/extensibility type/bug
主要言語
Python
スター
22.1k
フォーク
5.3k
PR マージ指標
PR 指標を取得中

説明

## Summary

When using `source-snowflake` with a role that has read access to multiple schemas, discovery fails when the "Check table and column access privileges" option is enabled. Additionally, the schema filter configuration may not be applied correctly, causing the connector to discover tables from schemas other than the one specified.

## Environment

- Connector: source-snowflake v1.0.3
- Configuration: Schema filter set to ``, Database set to ``, Role set to ``

## Issues Identified

### Issue 1: Discovery crashes when privilege check encounters views with session variables

When "Check table and column access privileges" is enabled, the connector runs `SELECT ... LIMIT 0` queries against each table/view to verify access. For views that depend on Snowflake session variables (e.g., `$ENGAGEMENT_LEVEL`), this query fails because Snowflake tries to expand the view definition but the session variable is not set.

**Error from logs:**
```
Caused by: net.snowflake.client.jdbc.SnowflakeSQLException: SQL compilation error:
Failure during expansion of view '': SQL compilation error: error line 33 at position 19
Session variable '$ENGAGEMENT_LEVEL' does not exist
```

**Root cause:** In `SnowflakeSourceMetadataQuerier.kt` lines 196-198, when the privilege check query fails, it throws a `RuntimeException` that crashes the entire discovery instead of gracefully handling the error:

```kotlin
} catch (e: SQLException) {
throw RuntimeException("Column name discovery query failed: ${e.message}", e)
}
```

**Expected behavior:** The connector should log a warning and skip the problematic view, allowing discovery to continue for all other tables.

### Issue 2: Schema filter may not be applied correctly

Despite configuring the schema filter to ``, the connector discovered 1734 tables from `` instead. The code at `SnowflakeSourceMetadataQuerier.kt` lines 232-235 handles case-sensitivity for the database name (tries both original and uppercase), but NOT for the schema parameter:

```kotlin
for (namespace in
base.config.namespaces + base.config.namespaces.map { it.uppercase() }) {
// Query all schemas in the current database
dbmd.getTables(namespace, schema, null, arrayOf("TABLE", "VIEW")).use {
```

This may cause schema filtering to fail if there is a case mismatch or if the Snowflake JDBC driver has specific behavior with roles that have access to multiple schemas.

## Proposed Fixes

1. **Fix error handling in `queryColumnMetadata()`**: Return `null` on `SQLException` instead of throwing `RuntimeException`, allowing discovery to continue with a warning logged for problematic views.

2. **Add case-insensitive schema matching**: Similar to how database names are handled, try both the original schema value and its uppercase variant when calling `getTables()`.

## Relevant Code

- `airbyte-integrations/connectors/source-snowflake/src/main/kotlin/io/airbyte/integrations/source/snowflake/SnowflakeSourceMetadataQuerier.kt`

---
**Internal Tracking:** https://github.com/airbytehq/oncall/issues/11118

コントリビューションガイド

コントリビューションガイドを開く

評価

この issue はまだ評価されていません。

新しい issue をメールで受け取る

初心者向けの GitHub issue を短くまとめたダイジェスト。