Missing column `_SDC_TABLE_VERSION` is added twice, resulting in duplication

[ralichkov]

In the situation where the target table exists, but does not yet have the _SDC_TABLE_VERSION column, target-snowflake does the following:

1. Table metadata is fetched and immediately cached

• SnowflakeSink.setup() calls
• self.connector.prepare_table (from the SDK) which calls
• self.get_table_columns(full_table_name) (from SnowflakeConnector) which caches the initial table schema

2. Since _SDC_TABLE_VERSION is not yet present:

• self.prepare_column (connector class in the SDK) adds the column, but cache is not invalidated

3. Later, activate_version checks if _SDC_TABLE_VERSION is present

• self.connector.column_exists calls SnowflakeConector's implementation of get_table_columns, which in our case returns the initial version that was cached

4. activate_version calls into prepare_column again to add the column for a second time, resulting in an error

Full Traceback

target-snowflake (snowflake.connector.errors.ProgrammingError) 001430 (42601): SQL compilation error:
target-snowflake column '_SDC_TABLE_VERSION' already exists
target-snowflake Traceback (most recent call last):
target-snowflake   File "/Users/user/.meltano/loaders/target-snowflake/venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1967, in _exec_single_context
target-snowflake     self.dialect.do_execute(
target-snowflake   File "/Users/user/.meltano/loaders/target-snowflake/venv/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 952, in do_execute
target-snowflake     cursor.execute(statement, parameters)
target-snowflake   File "/Users/user/.meltano/loaders/target-snowflake/venv/lib/python3.11/site-packages/snowflake/connector/cursor.py", line 1144, in execute
target-snowflake     Error.errorhandler_wrapper(self.connection, self, error_class, errvalue)
target-snowflake   File "/Users/user/.meltano/loaders/target-snowflake/venv/lib/python3.11/site-packages/snowflake/connector/errors.py", line 298, in errorhandler_wrapper
target-snowflake     handed_over = Error.hand_to_other_handler(
target-snowflake                   ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
target-snowflake   File "/Users/user/.meltano/loaders/target-snowflake/venv/lib/python3.11/site-packages/snowflake/connector/errors.py", line 354, in hand_to_other_handler
target-snowflake     cursor.errorhandler(connection, cursor, error_class, error_value)
target-snowflake   File "/Users/user/.meltano/loaders/target-snowflake/venv/lib/python3.11/site-packages/snowflake/connector/errors.py", line 229, in default_errorhandler
target-snowflake     raise error_class(
target-snowflake snowflake.connector.errors.ProgrammingError: 001430 (42601): SQL compilation error:
target-snowflake column '_SDC_TABLE_VERSION' already exists
target-snowflake The above exception was the direct cause of the following exception:
target-snowflake Traceback (most recent call last):
target-snowflake   File "/Users/user/.meltano/loaders/target-snowflake/venv/bin/target-snowflake", line 10, in <module>
target-snowflake     sys.exit(TargetSnowflake.cli())
target-snowflake              ^^^^^^^^^^^^^^^^^^^^^
target-snowflake   File "/Users/user/.meltano/loaders/target-snowflake/venv/lib/python3.11/site-packages/click/core.py", line 1485, in __call__
target-snowflake     return self.main(*args, **kwargs)
target-snowflake            ^^^^^^^^^^^^^^^^^^^^^^^^^^
target-snowflake   File "/Users/user/.meltano/loaders/target-snowflake/venv/lib/python3.11/site-packages/click/core.py", line 1406, in main
target-snowflake     rv = self.invoke(ctx)
target-snowflake          ^^^^^^^^^^^^^^^^
target-snowflake   File "/Users/user/.meltano/loaders/target-snowflake/venv/lib/python3.11/site-packages/singer_sdk/plugin_base.py", line 150, in invoke
target-snowflake     return super().invoke(ctx)
target-snowflake            ^^^^^^^^^^^^^^^^^^^
target-snowflake   File "/Users/user/.meltano/loaders/target-snowflake/venv/lib/python3.11/site-packages/click/core.py", line 1269, in invoke
target-snowflake     return ctx.invoke(self.callback, **ctx.params)
target-snowflake            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
target-snowflake   File "/Users/user/.meltano/loaders/target-snowflake/venv/lib/python3.11/site-packages/click/core.py", line 824, in invoke
target-snowflake     return callback(*args, **kwargs)
target-snowflake            ^^^^^^^^^^^^^^^^^^^^^^^^^
target-snowflake   File "/Users/user/.meltano/loaders/target-snowflake/venv/lib/python3.11/site-packages/singer_sdk/target_base.py", line 582, in invoke
target-snowflake     target.listen(file_input)
target-snowflake   File "/Users/user/.meltano/loaders/target-snowflake/venv/lib/python3.11/site-packages/singer_sdk/plugin_base.py", line 816, in listen
target-snowflake     counter = self.process_lines(file_input)
target-snowflake               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
target-snowflake   File "/Users/user/.meltano/loaders/target-snowflake/venv/lib/python3.11/site-packages/singer_sdk/plugin_base.py", line 842, in process_lines
target-snowflake     return self.message_reader.process_lines(
target-snowflake            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
target-snowflake   File "/Users/user/.meltano/loaders/target-snowflake/venv/lib/python3.11/site-packages/singer_sdk/singerlib/encoding/base.py", line 70, in process_lines
target-snowflake     callback(line_dict)
target-snowflake   File "/Users/user/.meltano/loaders/target-snowflake/venv/lib/python3.11/site-packages/singer_sdk/target_base.py", line 447, in _process_activate_version_message
target-snowflake     sink.activate_version(message_dict["version"])
target-snowflake   File "/Users/user/.meltano/loaders/target-snowflake/venv/lib/python3.11/site-packages/singer_sdk/sql/sink.py", line 406, in activate_version
target-snowflake     self.connector.prepare_column(
target-snowflake   File "/Users/user/load/target-snowflake/target_snowflake/connector.py", line 319, in prepare_column
target-snowflake     super().prepare_column(
target-snowflake   File "/Users/user/.meltano/loaders/target-snowflake/venv/lib/python3.11/site-packages/singer_sdk/sql/connector.py", line 1473, in prepare_column
target-snowflake     self._create_empty_column(
target-snowflake   File "/Users/user/.meltano/loaders/target-snowflake/venv/lib/python3.11/site-packages/singer_sdk/sql/connector.py", line 1370, in _create_empty_column
target-snowflake     conn.execute(column_add_ddl)
target-snowflake   File "/Users/user/.meltano/loaders/target-snowflake/venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1419, in execute
target-snowflake     return meth(
target-snowflake            ^^^^^
target-snowflake   File "/Users/user/.meltano/loaders/target-snowflake/venv/lib/python3.11/site-packages/sqlalchemy/sql/ddl.py", line 187, in _execute_on_connection
target-snowflake     return connection._execute_ddl(
target-snowflake            ^^^^^^^^^^^^^^^^^^^^^^^^
target-snowflake   File "/Users/user/.meltano/loaders/target-snowflake/venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1530, in _execute_ddl
target-snowflake     ret = self._execute_context(
target-snowflake           ^^^^^^^^^^^^^^^^^^^^^^
target-snowflake   File "/Users/user/.meltano/loaders/target-snowflake/venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1846, in _execute_context
target-snowflake     return self._exec_single_context(
target-snowflake            ^^^^^^^^^^^^^^^^^^^^^^^^^^
target-snowflake   File "/Users/user/.meltano/loaders/target-snowflake/venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1986, in _exec_single_context
target-snowflake     self._handle_dbapi_exception(
target-snowflake   File "/Users/user/.meltano/loaders/target-snowflake/venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 2363, in _handle_dbapi_exception
target-snowflake     raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
target-snowflake   File "/Users/user/.meltano/loaders/target-snowflake/venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1967, in _exec_single_context
target-snowflake     self.dialect.do_execute(
target-snowflake   File "/Users/user/.meltano/loaders/target-snowflake/venv/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 952, in do_execute
target-snowflake     cursor.execute(statement, parameters)
target-snowflake   File "/Users/user/.meltano/loaders/target-snowflake/venv/lib/python3.11/site-packages/snowflake/connector/cursor.py", line 1144, in execute
target-snowflake     Error.errorhandler_wrapper(self.connection, self, error_class, errvalue)
target-snowflake   File "/Users/user/.meltano/loaders/target-snowflake/venv/lib/python3.11/site-packages/snowflake/connector/errors.py", line 298, in errorhandler_wrapper
target-snowflake     handed_over = Error.hand_to_other_handler(
target-snowflake                   ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
target-snowflake   File "/Users/user/.meltano/loaders/target-snowflake/venv/lib/python3.11/site-packages/snowflake/connector/errors.py", line 354, in hand_to_other_handler
target-snowflake     cursor.errorhandler(connection, cursor, error_class, error_value)
target-snowflake   File "/Users/user/.meltano/loaders/target-snowflake/venv/lib/python3.11/site-packages/snowflake/connector/errors.py", line 229, in default_errorhandler
target-snowflake     raise error_class(
target-snowflake sqlalchemy.exc.ProgrammingError: (snowflake.connector.errors.ProgrammingError) 001430 (42601): SQL compilation error:
target-snowflake column '_SDC_TABLE_VERSION' already exists
target-snowflake [SQL: ALTER TABLE "MELTANO"."MY_TAP"."MY_TABLE" ADD COLUMN _sdc_table_version INTEGER]

The way I got around this is by overriding prepare_table in connector.py

def prepare_table(
    self,
    full_table_name: str,
    schema: dict,
    primary_keys: list[str],
    partition_keys: list[str] | None = None,
    as_temp_table: bool = False,  # noqa: FBT002, FBT001
) -> None:
    super().prepare_table(
        full_table_name=full_table_name,
        schema=schema,
        primary_keys=primary_keys,
        partition_keys=partition_keys,
        as_temp_table=as_temp_table,
    )
    self.table_cache.pop(full_table_name)

If you think this is the best approach, I'm happy to open a PR.

[ralichkov]

There is this a draft PR #389 but it seems unfinished