SNOW-835006: Schema-qualified metadata reflection does not include schemas for tables referenced via foreign keys

[fordhoka]

Please answer these questions before submitting your issue. Thanks!

1. What version of Python are you using?

3.10.10

2. What operating system and processor architecture are you using?

macOS-13.4-x86_64-i386-64bit

3. What are the component versions in the environment (pip freeze)?

Relevant modules:

snowflake-connector-python==3.0.4
snowflake-sqlalchemy==1.4.7
SQLAlchemy==1.4.41

4. What did you do?

• Created database, schema test_schema, and tables test_table_1 and test_table_2. test_table_1 contains a foreign key referencing test_table_2
• Called:

  metadata = MetaData(schema=schema)
  metadata.reflect(bind=engine, only=['test_table_1'])

5. What did you expect to see?

I expected:

• metadata.tables to contain 2 keys, test_schema.test_table_1 and test_schema.test_table_2,
• both corresponding Table objects to be associated with test_schema, and
• metadata.tables['test_schema.test_table_1'].foreign_key_constraints.pop().referred_table should resolve to the Table object for test_schema.test_table_2.

Instead, it contained test_schema.test_table_1 and test_table_2. The Table object returned for test_table_2 was not associated with a schema. metadata.tables['test_schema.test_table_1'].foreign_key_constraints.pop().referred_table raises the following error:

sqlalchemy.exc.NoReferencedTableError: Foreign key associated with column 'test_table_1.foreign_key_id' could not find table 'test_schema.test_table_2' with which to generate a foreign key to target column 'primary_key_id'

I have a workaround for this issue: list all of the foreign keys using SHOW IMPORTED KEYS, pull out all of the fk-referenced table names, and include them explicitly along with the requested tables in only=; and then filter the non-schema-qualified tables out of metadata.tables. This is unwieldy, and made even more so by https://github.com/snowflakedb/snowflake-sqlalchemy/issues/388.

6. Can you set logging to DEBUG and collect the logs?

snowflake_sqlalchemy_logs.txt

[sfc-gh-aling]

thanks for reaching out.

This might be an issue around looking for tables, I tried to reproduce and find that metadata.tables (internally it's maintained by a dict) actually contains two tables, but the keys are one with schema name "schema.table_name1while the other doesn't have the schematable_name2`.

but when foreign_key_constraints.pop().referred_table is called, it's looking for the fully qualified schema.table_name2 which is not available in the

probably the code of getting foreign keys needs more logic: https://github.com/snowflakedb/snowflake-sqlalchemy/blob/main/src/snowflake/sqlalchemy/snowdialect.py#L400.

if you are interested and have time, could you take a look into that function?

[sfc-gh-dszmolka]

hi and thank you for drawing our attention to this gap; we'll take a look