Tech Spec for Allow checking access to destination schema in replication config UI (check-with-catalog)

[grishick]

Problem Statement

Currently, we allow users to test their destination configuration only when they are setting up the destination. However, replication configuration can cause destinations to fail as well. The specific problem that we have observed so far is that Snowflake destination is configured with some default schema and we check for access to that schema as well as for ability to create new schemas during connector setup. If the user then sets up replication and selects the "Mirror source structure" or "Namespace custom format" in "Destination Namespace" options, Snowflake destination can end up trying to write to a schema that it does not have access to. This was observed in several customer accounts when syncing data from Postgres source where tables are in schema "public". The problem is reported as

Schema 'PUBLIC' already exists, but current role has no privileges on it. If this is unexpected and you cannot resolve this problem, contact your system administrator. ACCOUNTADMIN role may be required to manage the privileges on the object.

Furthermore, this problem cannot currently be caught in CHECK method, because CHECK does not have access to source schema or replication configuration. We already do CHECK for ability to create new schema, so the problem happens only when the target schema already exists and we don’t have permissions to write to it and we don’t know the name of the target schema until the sync starts

Desired solution

For better UX, I suggest that we add a "check" button to Replication page and add a new call to the protocol, which will allow the connectors (or at least the destination connector) to check if the replication configuration is correct, not just if the connector configuration is correct.

Doing so will also require having a new type of check method (checkReplication?) that takes replication options and source schema as arguments in addition to destination configuration.

Current workaround

Currently, the problem is only detected after the failure of the first sync, which has a negative affect on first experience and activation.

[octavia-squidington-iv]

cc @airbytehq/frontend

[evantahler]

Alternative Solution

For CHECK commands which run as part of a sync (because the previous sync attempt has failed - #13459), we provide the configured-catalog.json as an argument to the CHECK command in addition to the config.json. This way, if the ConfiguredCatalog is present the CHECK command can opt to use this additional information to do a more thorough check, knowing the schema and table names it will need.

Pros:

• No need for a new Airbyte protocol command

Cons:

• Change to inputs of CHECK command which are only available sometimes
• Users still won't see the failure until after that first failing sync (but it is assumed that the sync will fail quickly)

[evantahler]

As a note, I find this problem really interesting because this is the first instance I've seen where the configuration of the source can materially effect the success of the destination. I think to-date we've generally assumed that Source and Destination configurations are fully independent of each other, and the sync configuration independent of both. The protocol lacks any sort of "compatibility ACK" between source, destination, sync.

[evantahler]

Also, @grishick - asking the lazy product question: Do we see users who replicate multiple schemas, and if we do, are there regularly conflicting table names? Perhaps "mirror source" doesn't need to imply the schemas as well?

[evantahler]

Alternative Solution

... maybe in the docs we say we need an "admin"-level user who can write to all schemas.

[grishick]

A common pattern is for a workspace to have multiple sources configured to write to a single destination with schema name specified in the replication config.

We can say "admin" level in the docs, but technically we don't need that, so I hesitate to make this a requirement.

I am OK with changing the signature of the existing CHECK method. I don't think it is very important whether we add a new checkWithCatalog method or add parameters to existing CHECK method. I suspect that it will be easier to add a new method then updating the existing one, because:

• fewer connectors to change
• fewer if-else forks to add inside CHECK code paths in platform and connectors
• maybe cleaner - that's a taste preference, so not sure about it

I want to run checkWithCatalog from the Replication Configuration screen before starting the first sync job. I think this is a better UX than finding out that your sync does not work after you've left that screen. If we add this new method (or add a new parameter to CHECK), we can also add a flag to connector definition saying "supports check with catalog" and show this button only if the destination supports check with catalog

[grishick]

Also, @grishick - asking the lazy product question: Do we see users who replicate multiple schemas
yes (I sent some examples on Slack, but generally, yes - this is a common pattern)

and if we do, are there regularly conflicting table names? Perhaps "mirror source" doesn't need to imply the schemas as well?

Conflicting names sometimes come up on OC, but I cannot say that that's a regular occasion. However, it is usual for users to specify target schema name in replication configuration. If that schema already exists and the destination connector does not have permissions to write to it, we will detect that only during the second attempt of the first sync.