bug: SQLAlchemy reflection cache is defeated by creating new Inspector instances per call
kind/BugSQLType/Target
### Singer SDK Version
0.53.5
### Is this a regression?
- [ ] Yes
### Python Version
NA
### Bug scope
Targets (data type handling, batching, SQL object generation, etc.)
### Operating System
_No response_
### Description
Three methods in `SQLConnector` create a new `sa.inspect(self._engine)` on every call. Since SQLAlchemy's reflection cache (`info_cache`) is an instance-level dictionary on `Inspector`, each new instance starts with an empty cache and the previous one is garbage collected — completely defeating dialect-level caching optimizations.
#### Affected methods
- [`table_exists()`](https://github.com/meltano/sdk/blob/c3176a4ec22872993c5542c5b7443f62012650c1/singer_sdk/sql/connector.py#L1206)
- [`schema_exists()`](https://github.com/meltano/sdk/blob/c3176a4ec22872993c5542c5b7443f62012650c1/singer_sdk/sql/connector.py#L1217)
- [`get_table_columns()`](https://github.com/meltano/sdk/blob/c3176a4ec22872993c5542c5b7443f62012650c1/singer_sdk/sql/connector.py#L1235) — most critical, called frequently during data loading
For contrast, [`discover_catalog_entries()`](https://github.com/meltano/sdk/blob/c3176a4ec22872993c5542c5b7443f62012650c1/singer_sdk/sql/connector.py#L1116) already correctly creates a single Inspector and reuses it throughout the method.
#### How SQLAlchemy's reflection cache works
SQLAlchemy stores reflection results in `Inspector.info_cache`, a plain dictionary. The `@reflection.cache` decorator on dialect methods (e.g., Snowflake's `_get_schema_columns()`) uses this dict to avoid redundant queries. **New Inspector = new empty dict = all cached results lost.**
#### Real-world impact (Snowflake)
The Snowflake SQLAlchemy dialect optimizes by querying **all columns in a schema** at once (`SELECT ... FROM information_schema.columns WHERE table_schema = :schema`) and caching the result. Subsequent `get_columns()` calls for other tables in the same schema should return from cache with zero queries.
Because the SDK creates a throwaway Inspector each time, this optimization is completely negated.
This affects any SQL dialect that uses `@reflection.cache` for optimization, not just Snowflake.
#### Suggested fix
Cache the Inspector instance at the connector level (e.g., a lazy `_inspector` property) so the `info_cache` persists across calls. A `clear_cache()` escape hatch can be provided for edge cases where schema changes during a sync.
### Link to Slack/Linen
_No response_
0 条评论