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 個 reaction 已指派 1 人 已被 @jrbarkin 認領 在 GitHub 檢視
area/connectors autoteam connectors/source/snowflake hyd-review team/extensibility type/bug
主要語言
Python
星號
22.1k
分支
5.4k
平均合併
5 小時
30 天內合併 PR
671

描述

## 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 摘要。