Problem with multiple backends - possible to select one?

[a-hartens]

Hello,

I am having a problem with multiple backends.

IbisError: Multiple backends found for this expression

I am using a Snowflake as a backend. Source data is in one schema, and tables are written in another, which I will call the 'destination' schema. When using tables in the destination schema, i get the above error.

Is this a known/required behavior? Is it possible to select one of the backends? I am able to list the backends using table._find_backends() source code here

Any information is greatly appreciated.

[Sara25776]

Could you please share more details about what you're trying to achieve? Including a sample code snippet would help clarify your intent

[sprivite]

Basically this is what we're trying to do. We have source data in SNOWFLAKE_SOURCE_DATABASE and we want to process that data and then store the result somewhere else SNOWFLAKE_DEST_DATABASE. The analysis pipeline is quite complex and sometimes intermediate tables already written to SNOWFLAKE_DEST_DATABASE need to be joined on tables in the SNOWFLAKE_SOURCE_DATABASE.

See this code snippet:

DATABASE1, SCHEMA1 = SNOWFLAKE_SOURCE_DATABASE.split('.')
DATABASE2, SCHEMA2 = SNOWFLAKE_DEST_DATABASE.split('.')

con1 = ibis.snowflake.connect(
    user=SNOWFLAKE_USER,
    authenticator="externalbrowser",
    account=SNOWFLAKE_ACCOUNT,
    warehouse=SNOWFLAKE_WAREHOUSE,
    role=SNOWFLAKE_ROLE,
    database=DATABASE1,
    schema=SCHEMA1,
)
con2 = ibis.snowflake.connect(
    user=SNOWFLAKE_USER,
    authenticator="externalbrowser",
    account=SNOWFLAKE_ACCOUNT,
    warehouse=SNOWFLAKE_WAREHOUSE,
    role=SNOWFLAKE_ROLE,
    database=DATABASE2,
    schema=SCHEMA2
)

tbl1 = con1.table("PERSON", database=SNOWFLAKE_SOURCE_DATABASE)
tbl2 = con2.table("PERSON", database=SNOWFLAKE_DEST_DATABASE)
tbl1.join(tbl2, "PERSON_ID")

This code throws the error posted by Alex.

Since then I've learned that although it seems that database / schema are required arguments to snowflake.connect, the connector is not actually bound to that database. So for us, a simple fix is just to write:

DATABASE1, SCHEMA1 = SNOWFLAKE_SOURCE_DATABASE.split('.')
DATABASE2, SCHEMA2 = SNOWFLAKE_DEST_DATABASE.split('.')

con = ibis.snowflake.connect(
    user=SNOWFLAKE_USER,
    authenticator="externalbrowser",
    account=SNOWFLAKE_ACCOUNT,
    warehouse=SNOWFLAKE_WAREHOUSE,
    role=SNOWFLAKE_ROLE,
    database=DATABASE1,
    schema=SCHEMA1,
)

tbl1 = con.table("PERSON", database=SNOWFLAKE_SOURCE_DATABASE)
tbl2 = con.table("PERSON", database=SNOWFLAKE_DEST_DATABASE)
tbl1.join(tbl2, "PERSON_ID")

This code works. It's a little weird to me because the connector is initialized with the SOURCE database, but it works.

Is this clearer what we're trying to do? Is the solution we came up with the recommended approach?