#3627 perf: Fetch table columns once in prepare_table instead of per column Pull request opened by edgarrmondragon Closes #2353. Problem

SQLConnector.prepare_table

previously made 2N database round-trips per schema sync, where N is the number of columns: 1.

prepare_column

→

column_exists

→

get_table_columns

(one query per column to check existence) 2.

_adapt_column_type

→

_get_column_type

→

get_table_columns

(another query per column to read the current type before deciding whether to alter it) For a table with 100 columns this means 200 queries just to prepare the schema. This shows up noticeably on high-latency connections. Solution

prepare_table

now calls

get_table_columns

once before the column loop and passes the result through the call chain via two new optional keyword-only parameters: •

prepare_column(..., *, existing_columns: dict[str, sa.Column] | None = None)

— when provided, uses the pre-fetched mapping to decide whether the column exists without an extra query. •

_adapt_column_type(..., *, current_type: sqlalchemy.types.TypeEngine | None = None)

— when provided, skips the

_get_column_type

lookup entirely. Both parameters default to

None

, so the old per-query behaviour is preserved when calling these methods directly (full backward compatibility — no changes needed in subclasses that only override

prepare_column

or

_adapt_column_type

). Migration guide No action required for most targets. The optimization is applied automatically by

prepare_table

. If your target overrides

prepare_column

and performs its own column-existence check that hits the database, you can opt in to the batch result to avoid the redundant queries: # Before — still works, but makes one DB query per column class MyConnector(SQLConnector): def prepare_column(self, full_table_name, column_name, sql_type): if not self.column_exists(full_table_name, column_name): self._create_empty_column(full_table_name, column_name, sql_type) return self._adapt_column_type(full_table_name, column_name=column_name, sql_type=sql_type) # After — accepts the pre-fetched columns dict to avoid the extra query class MyConnector(SQLConnector): def prepare_column( self, full_table_name, column_name, sql_type, *, existing_columns: dict | None = None, ): if existing_columns is None: existing_columns = self.get_table_columns(full_table_name) if column_name not in existing_columns: self._create_empty_column(full_table_name, column_name, sql_type) return self._adapt_column_type( full_table_name, column_name=column_name, sql_type=sql_type, current_type=existing_columns[column_name].type, ) Similarly, if you override

_adapt_column_type

and call

_get_column_type

inside, you can accept the optional

current_type

parameter to skip the lookup when the caller already has the type: class MyConnector(SQLConnector): def _adapt_column_type( self, full_table_name, column_name, sql_type, *, current_type=None, ): if current_type is None: current_type = self._get_column_type(full_table_name, column_name) # ... rest of your logic Test plan •

test_adapt_column_type

— existing test, unmodified, still passes (old call-site without

current_type

) •

test_adapt_column_type_skips_lookup_when_current_type_provided

— new: asserts

_get_column_type

is not called when

current_type

is supplied •

test_prepare_table_fetches_columns_once

— new: asserts

get_table_columns

is called exactly once across a multi-column

prepare_table

invocation • Full

tests/sql/

suite — 132 passed, no regressions 🤖 Generated with Claude Code Summary by Sourcery Reduce database round-trips during SQL table preparation by reusing pre-fetched column metadata across column operations. Enhancements: • Prefetch table columns once in prepare_table and reuse them across prepare_column calls to avoid redundant existence checks. • Extend prepare_column and _adapt_column_type to accept optional existing_columns and current_type parameters while preserving backward-compatible behavior. Tests: • Add tests to verify _adapt_column_type skips type lookups when current_type is provided and that prepare_table fetches columns only once. • Add tests to ensure prepare_column creates missing columns and adapts existing ones correctly under the new behavior. meltano/sdk