bug: TypeError: Invalid argument(s) 'json_serializer','json_deserializer' sent to create_engine()...

[pnadolny13]

I'm getting the following error with the tap pinned to version v0.0.22. It looks like the SDK dependency is unconstrained and meltano/sdk#1898 made a change that might only work for SQLAlchemy 2.0? I'm not totally sure though. I see these arguments in the sqlalchemy docs but they could be new in >2.0.

TypeError: Invalid argument(s) 'json_serializer','json_deserializer' sent to create_engine(), using configuration SnowflakeDialect/QueuePool/Engine

This is especially concerning because I pinned my package version but this still got through.

• Should we pin the SDK version and let dependabot update it periodically?
• It looks like we're blocked by Use SQLAlchemy 2 #28 which is blocked by the snowflake library. Should we override the method and exclude the serializers to solve this?

cc @edgarrmondragon

2023-08-08T13:00:02.433014Z [info     ] Traceback (most recent call last): cmd_type=elb consumer=False name=tap-snowflake-metrics producer=True stdio=stderr string_id=tap-snowflake-metrics
2023-08-08T13:00:02.433295Z [info     ]   File "/project/data/.meltano/extractors/tap-snowflake/venv/lib/python3.9/site-packages/singer_sdk/streams/core.py", line 1179, in sync cmd_type=elb consumer=False name=tap-snowflake-metrics producer=True stdio=stderr string_id=tap-snowflake-metrics
2023-08-08T13:00:02.433518Z [info     ]     for _ in self._sync_records(context=context): cmd_type=elb consumer=False name=tap-snowflake-metrics producer=True stdio=stderr string_id=tap-snowflake-metrics
2023-08-08T13:00:02.433739Z [info     ]   File "/project/data/.meltano/extractors/tap-snowflake/venv/lib/python3.9/site-packages/singer_sdk/streams/core.py", line 1073, in _sync_records cmd_type=elb consumer=False name=tap-snowflake-metrics producer=True stdio=stderr string_id=tap-snowflake-metrics
2023-08-08T13:00:02.433959Z [info     ]     for record_result in self.get_records(current_context): cmd_type=elb consumer=False name=tap-snowflake-metrics producer=True stdio=stderr string_id=tap-snowflake-metrics
2023-08-08T13:00:02.434191Z [info     ]   File "/project/data/.meltano/extractors/tap-snowflake/venv/lib/python3.9/site-packages/tap_snowflake/client.py", line 431, in get_records cmd_type=elb consumer=False name=tap-snowflake-metrics producer=True stdio=stderr string_id=tap-snowflake-metrics
2023-08-08T13:00:02.434442Z [info     ]     table = self.connector.get_table( cmd_type=elb consumer=False name=tap-snowflake-metrics producer=True stdio=stderr string_id=tap-snowflake-metrics
2023-08-08T13:00:02.434693Z [info     ]   File "/project/data/.meltano/extractors/tap-snowflake/venv/lib/python3.9/site-packages/singer_sdk/connectors/sql.py", line 622, in get_table cmd_type=elb consumer=False name=tap-snowflake-metrics producer=True stdio=stderr string_id=tap-snowflake-metrics
2023-08-08T13:00:02.434939Z [info     ]     columns = self.get_table_columns( cmd_type=elb consumer=False name=tap-snowflake-metrics producer=True stdio=stderr string_id=tap-snowflake-metrics
2023-08-08T13:00:02.435199Z [info     ]   File "/project/data/.meltano/extractors/tap-snowflake/venv/lib/python3.9/site-packages/singer_sdk/connectors/sql.py", line 594, in get_table_columns cmd_type=elb consumer=False name=tap-snowflake-metrics producer=True stdio=stderr string_id=tap-snowflake-metrics
2023-08-08T13:00:02.435454Z [info     ]     inspector = sqlalchemy.inspect(self._engine) cmd_type=elb consumer=False name=tap-snowflake-metrics producer=True stdio=stderr string_id=tap-snowflake-metrics
2023-08-08T13:00:02.435703Z [info     ]   File "/project/data/.meltano/extractors/tap-snowflake/venv/lib/python3.9/site-packages/singer_sdk/connectors/sql.py", line 305, in _engine cmd_type=elb consumer=False name=tap-snowflake-metrics producer=True stdio=stderr string_id=tap-snowflake-metrics
2023-08-08T13:00:02.435950Z [info     ]     self._cached_engine = self.create_engine() cmd_type=elb consumer=False name=tap-snowflake-metrics producer=True stdio=stderr string_id=tap-snowflake-metrics
2023-08-08T13:00:02.436193Z [info     ]   File "/project/data/.meltano/extractors/tap-snowflake/venv/lib/python3.9/site-packages/singer_sdk/connectors/sql.py", line 322, in create_engine cmd_type=elb consumer=False name=tap-snowflake-metrics producer=True stdio=stderr string_id=tap-snowflake-metrics
2023-08-08T13:00:02.436447Z [info     ]     return sqlalchemy.create_engine( cmd_type=elb consumer=False name=tap-snowflake-metrics producer=True stdio=stderr string_id=tap-snowflake-metrics
2023-08-08T13:00:02.436695Z [info     ]   File "<string>", line 2, in create_engine cmd_type=elb consumer=False name=tap-snowflake-metrics producer=True stdio=stderr string_id=tap-snowflake-metrics
2023-08-08T13:00:02.436945Z [info     ]   File "/project/data/.meltano/extractors/tap-snowflake/venv/lib/python3.9/site-packages/sqlalchemy/util/deprecations.py", line 375, in warned cmd_type=elb consumer=False name=tap-snowflake-metrics producer=True stdio=stderr string_id=tap-snowflake-metrics
2023-08-08T13:00:02.437229Z [info     ]     return fn(*args, **kwargs) cmd_type=elb consumer=False name=tap-snowflake-metrics producer=True stdio=stderr string_id=tap-snowflake-metrics
2023-08-08T13:00:02.437479Z [info     ]   File "/project/data/.meltano/extractors/tap-snowflake/venv/lib/python3.9/site-packages/sqlalchemy/engine/create.py", line 632, in create_engine cmd_type=elb consumer=False name=tap-snowflake-metrics producer=True stdio=stderr string_id=tap-snowflake-metrics
2023-08-08T13:00:02.437731Z [info     ]     raise TypeError(           cmd_type=elb consumer=False name=tap-snowflake-metrics producer=True stdio=stderr string_id=tap-snowflake-metrics
2023-08-08T13:00:02.437975Z [info     ] TypeError: Invalid argument(s) 'json_serializer','json_deserializer' sent to create_engine(), using configuration SnowflakeDialect/QueuePool/Engine.  Please check that the keyword arguments are appropriate for this combination of components. cmd_type=elb consumer=False name=tap-snowflake-metrics producer=True stdio=stderr string_id=tap-snowflake-metrics
2023-08-08T13:00:02.438218Z [info     ] Traceback (most recent call last): cmd_type=elb consumer=False name=tap-snowflake-metrics producer=True stdio=stderr string_id=tap-snowflake-metrics
2023-08-08T13:00:02.438457Z [info     ]   File "/project/data/.meltano/extractors/tap-snowflake/venv/bin/tap-snowflake", line 8, in <module> cmd_type=elb consumer=False name=tap-snowflake-metrics producer=True stdio=stderr string_id=tap-snowflake-metrics
2023-08-08T13:00:02.438702Z [info     ]     sys.exit(TapSnowflake.cli()) cmd_type=elb consumer=False name=tap-snowflake-metrics producer=True stdio=stderr string_id=tap-snowflake-metrics
2023-08-08T13:00:02.438949Z [info     ]   File "/project/data/.meltano/extractors/tap-snowflake/venv/lib/python3.9/site-packages/click/core.py", line 1157, in __call__ cmd_type=elb consumer=False name=tap-snowflake-metrics producer=True stdio=stderr string_id=tap-snowflake-metrics
2023-08-08T13:00:02.439196Z [info     ]     return self.main(*args, **kwargs) cmd_type=elb consumer=False name=tap-snowflake-metrics producer=True stdio=stderr string_id=tap-snowflake-metrics
2023-08-08T13:00:02.439450Z [info     ]   File "/project/data/.meltano/extractors/tap-snowflake/venv/lib/python3.9/site-packages/click/core.py", line 1078, in main cmd_type=elb consumer=False name=tap-snowflake-metrics producer=True stdio=stderr string_id=tap-snowflake-metrics
2023-08-08T13:00:02.439696Z [info     ]     rv = self.invoke(ctx)      cmd_type=elb consumer=False name=tap-snowflake-metrics producer=True stdio=stderr string_id=tap-snowflake-metrics
2023-08-08T13:00:02.439949Z [info     ]   File "/project/data/.meltano/extractors/tap-snowflake/venv/lib/python3.9/site-packages/click/core.py", line 1434, in invoke cmd_type=elb consumer=False name=tap-snowflake-metrics producer=True stdio=stderr string_id=tap-snowflake-metrics
2023-08-08T13:00:02.440195Z [info     ]     return ctx.invoke(self.callback, **ctx.params) cmd_type=elb consumer=False name=tap-snowflake-metrics producer=True stdio=stderr string_id=tap-snowflake-metrics
2023-08-08T13:00:02.440439Z [info     ]   File "/project/data/.meltano/extractors/tap-snowflake/venv/lib/python3.9/site-packages/click/core.py", line 783, in invoke cmd_type=elb consumer=False name=tap-snowflake-metrics producer=True stdio=stderr string_id=tap-snowflake-metrics
2023-08-08T13:00:02.440683Z [info     ]     return __callback(*args, **kwargs) cmd_type=elb consumer=False name=tap-snowflake-metrics producer=True stdio=stderr string_id=tap-snowflake-metrics
2023-08-08T13:00:02.440929Z [info     ]   File "/project/data/.meltano/extractors/tap-snowflake/venv/lib/python3.9/site-packages/singer_sdk/tap_base.py", line 500, in invoke cmd_type=elb consumer=False name=tap-snowflake-metrics producer=True stdio=stderr string_id=tap-snowflake-metrics
2023-08-08T13:00:02.441208Z [info     ]     tap.sync_all()             cmd_type=elb consumer=False name=tap-snowflake-metrics producer=True stdio=stderr string_id=tap-snowflake-metrics
2023-08-08T13:00:02.441462Z [info     ]   File "/project/data/.meltano/extractors/tap-snowflake/venv/lib/python3.9/site-packages/singer_sdk/tap_base.py", line 459, in sync_all cmd_type=elb consumer=False name=tap-snowflake-metrics producer=True stdio=stderr string_id=tap-snowflake-metrics
2023-08-08T13:00:02.441707Z [info     ]     stream.sync()              cmd_type=elb consumer=False name=tap-snowflake-metrics producer=True stdio=stderr string_id=tap-snowflake-metrics
2023-08-08T13:00:02.441948Z [info     ]   File "/project/data/.meltano/extractors/tap-snowflake/venv/lib/python3.9/site-packages/singer_sdk/streams/core.py", line 1186, in sync cmd_type=elb consumer=False name=tap-snowflake-metrics producer=True stdio=stderr string_id=tap-snowflake-metrics
2023-08-08T13:00:02.442193Z [info     ]     raise ex                   cmd_type=elb consumer=False name=tap-snowflake-metrics producer=True stdio=stderr string_id=tap-snowflake-metrics
2023-08-08T13:00:02.442438Z [info     ]   File "/project/data/.meltano/extractors/tap-snowflake/venv/lib/python3.9/site-packages/singer_sdk/streams/core.py", line 1179, in sync cmd_type=elb consumer=False name=tap-snowflake-metrics producer=True stdio=stderr string_id=tap-snowflake-metrics
2023-08-08T13:00:02.442685Z [info     ]     for _ in self._sync_records(context=context): cmd_type=elb consumer=False name=tap-snowflake-metrics producer=True stdio=stderr string_id=tap-snowflake-metrics
2023-08-08T13:00:02.442924Z [info     ]   File "/project/data/.meltano/extractors/tap-snowflake/venv/lib/python3.9/site-packages/singer_sdk/streams/core.py", line 1073, in _sync_records cmd_type=elb consumer=False name=tap-snowflake-metrics producer=True stdio=stderr string_id=tap-snowflake-metrics
2023-08-08T13:00:02.443167Z [info     ]     for record_result in self.get_records(current_context): cmd_type=elb consumer=False name=tap-snowflake-metrics producer=True stdio=stderr string_id=tap-snowflake-metrics
2023-08-08T13:00:02.443420Z [info     ]   File "/project/data/.meltano/extractors/tap-snowflake/venv/lib/python3.9/site-packages/tap_snowflake/client.py", line 431, in get_records cmd_type=elb consumer=False name=tap-snowflake-metrics producer=True stdio=stderr string_id=tap-snowflake-metrics
2023-08-08T13:00:02.443675Z [info     ]     table = self.connector.get_table( cmd_type=elb consumer=False name=tap-snowflake-metrics producer=True stdio=stderr string_id=tap-snowflake-metrics
2023-08-08T13:00:02.443925Z [info     ]   File "/project/data/.meltano/extractors/tap-snowflake/venv/lib/python3.9/site-packages/singer_sdk/connectors/sql.py", line 622, in get_table cmd_type=elb consumer=False name=tap-snowflake-metrics producer=True stdio=stderr string_id=tap-snowflake-metrics
2023-08-08T13:00:02.444168Z [info     ]     columns = self.get_table_columns( cmd_type=elb consumer=False name=tap-snowflake-metrics producer=True stdio=stderr string_id=tap-snowflake-metrics
2023-08-08T13:00:02.444409Z [info     ]   File "/project/data/.meltano/extractors/tap-snowflake/venv/lib/python3.9/site-packages/singer_sdk/connectors/sql.py", line 594, in get_table_columns cmd_type=elb consumer=False name=tap-snowflake-metrics producer=True stdio=stderr string_id=tap-snowflake-metrics
2023-08-08T13:00:02.444652Z [info     ]     inspector = sqlalchemy.inspect(self._engine) cmd_type=elb consumer=False name=tap-snowflake-metrics producer=True stdio=stderr string_id=tap-snowflake-metrics
2023-08-08T13:00:02.444898Z [info     ]   File "/project/data/.meltano/extractors/tap-snowflake/venv/lib/python3.9/site-packages/singer_sdk/connectors/sql.py", line 305, in _engine cmd_type=elb consumer=False name=tap-snowflake-metrics producer=True stdio=stderr string_id=tap-snowflake-metrics
2023-08-08T13:00:02.445179Z [info     ]     self._cached_engine = self.create_engine() cmd_type=elb consumer=False name=tap-snowflake-metrics producer=True stdio=stderr string_id=tap-snowflake-metrics
2023-08-08T13:00:02.445433Z [info     ]   File "/project/data/.meltano/extractors/tap-snowflake/venv/lib/python3.9/site-packages/singer_sdk/connectors/sql.py", line 322, in create_engine cmd_type=elb consumer=False name=tap-snowflake-metrics producer=True stdio=stderr string_id=tap-snowflake-metrics
2023-08-08T13:00:02.445677Z [info     ]     return sqlalchemy.create_engine( cmd_type=elb consumer=False name=tap-snowflake-metrics producer=True stdio=stderr string_id=tap-snowflake-metrics
2023-08-08T13:00:02.445919Z [info     ]   File "<string>", line 2, in create_engine cmd_type=elb consumer=False name=tap-snowflake-metrics producer=True stdio=stderr string_id=tap-snowflake-metrics
2023-08-08T13:00:02.446161Z [info     ]   File "/project/data/.meltano/extractors/tap-snowflake/venv/lib/python3.9/site-packages/sqlalchemy/util/deprecations.py", line 375, in warned cmd_type=elb consumer=False name=tap-snowflake-metrics producer=True stdio=stderr string_id=tap-snowflake-metrics
2023-08-08T13:00:02.446390Z [info     ]     return fn(*args, **kwargs) cmd_type=elb consumer=False name=tap-snowflake-metrics producer=True stdio=stderr string_id=tap-snowflake-metrics
2023-08-08T13:00:02.446631Z [info     ]   File "/project/data/.meltano/extractors/tap-snowflake/venv/lib/python3.9/site-packages/sqlalchemy/engine/create.py", line 632, in create_engine cmd_type=elb consumer=False name=tap-snowflake-metrics producer=True stdio=stderr string_id=tap-snowflake-metrics
2023-08-08T13:00:02.446875Z [info     ]     raise TypeError(           cmd_type=elb consumer=False name=tap-snowflake-metrics producer=True stdio=stderr string_id=tap-snowflake-metrics
2023-08-08T13:00:02.447299Z [info     ] TypeError: Invalid argument(s) 'json_serializer','json_deserializer' sent to create_engine(), using configuration SnowflakeDialect/QueuePool/Engine.  Please check that the keyword arguments are appropriate for this combination of components. cmd_type=elb consumer=False name=tap-snowflake-metrics producer=True stdio=stderr string_id=tap-snowflake-metrics

[edgarrmondragon]

I see these arguments in the sqlalchemy docs but they could be new in >2.0.

https://docs.sqlalchemy.org/en/14/core/engines.html#sqlalchemy.create_engine.params.json_deserializer

I can confirm that these should pass in SQLAlchemy 1.4 since the tests passed there: https://github.com/meltano/sdk/actions/runs/5789625613/job/15691059238#step:7:118

• Should we pin the SDK version and let dependabot update it periodically?

I think so, at least to the minor version: singer-sdk = "~=0.30.0"

---

TypeError: Invalid argument(s) 'json_serializer','json_deserializer' sent to create_engine(), using configuration SnowflakeDialect/QueuePool/Engine

Is there more context on this error? Like filename and line.

[pnadolny13]

@edgarrmondragon I updated the description to include the full stack trace.

[edgarrmondragon]

Thanks @pnadolny13! Ok, so I found the underlying issue and logged it in the SF driver repo: snowflakedb/snowflake-sqlalchemy#433.

I think we can fix this in 3 steps in the coming days:

• Constrain the SDK to the latest working minor version, i.e. singer-sdk = "~=0.30.0" and publish a release to PyPI
• Update the connector in this tap to override create_engine so it doesn't use those parameters, and also publish a release to PyPI.
• (Probably a bit more into the future) Bump snowflake-sqlalchemy and remove the custom create_engine implementation.