Snowflake Data Clean Rooms developer’s guide¶

Development tools¶

Here are the main developer tools for clean rooms:

• Coding environment: Any coding environment that can run stored procedures in your Snowflake account will work. Most developers use worksheets in Snowsight (the browser-based tool) or the Snowflake CLI.

• The clean rooms UI: Use the clean rooms UI to configure, manage, or create clean rooms. Most clean room analysts use the UI rather than code, so it can be useful to see and test the experience of your clean rooms in the UI. Additionally, there are a handful of features that are available only in the clean rooms UI.

• Snowsight is useful to explore databases and other objects and search for objects.

Coding setup¶

-- Set up environment.
USE ROLE samooha_app_role;
USE WAREHOUSE app_wh;

-- Call your clean rooms API functions.
...

GRANT USAGE ON WAREHOUSE <your warehouse> TO SAMOOHA_APP_ROLE;`

Setting up accounts, users, and roles¶

Technically, you don’t need to use the clean rooms UI to develop clean rooms: most clean room functionality is available using stored procedures. However, a few features are available only in the UI, and some are faster to perform in the UI. And because many users use the UI exclusively, it’s important to see how your clean room behaves in the UI, especially if you create a user configuration web form for a clean room that you create. Therefore, you should ask a clean room administrator to add you as a clean room manager or higher in the appropriate clean room accounts.

At minimum we recommend having separate provider and consumer Snowflake accounts to test both sides of a clean room. Depending on your use case, you might also want to set up an additional Snowflake account in different web hosting regions to test cross-cloud behavior.

Name your test Snowflake accounts meaningfully to indicate their typical usage: for example, “Consumer account,” “Provider account,” and “Cross-cloud account.” This can help when you have multiple test accounts and need to know which one to sign in to.

Confirm that you are using the same account in the clean rooms UI and in code¶

You often need to open a coding environment and the clean rooms UI for the same Snowflake account, for example, when creating a clean room in code, then checking its appearance in the clean rooms UI. It’s important to confirm that you’re using the same Snowflake account in each.

Snowsight does not have a shortcut to open the clean rooms UI for the same account, or the reverse, so you must be sure that both the clean rooms UI and Snowsight are open in the same account.

Clean room names vs clean room ID¶

When using the API, for procedures that need a cleanroom_name argument, determine whether to use the clean room name or the clean room ID as follows:

• If the clean room was created using the API, use the clean room name.

• If the clean room was created in the web app, use the clean room ID. You can see both the clean room name and ID by calling provider.view_cleanrooms or provider.describe_cleanroom.

Update your clean room whenever you make changes¶

To change any clean room settings, including templates, permissions, or policies, call provider.create_or_update_cleanroom_listing to enable the changes. In some cases, changes might be visible before calling this procedure, but the changes won’t properly apply until after you call provider.create_or_update_cleanroom_listing.

Interoperability between clean rooms created in code or the UI¶

When you create a clean room in the API, some features are not modifiable in the clean rooms UI. For example, you cannot add additional templates, even stock Snowflake templates, in code for a UI-created clean room. You also cannot change the differential privacy settings.

(Consumer) Can’t set join policies or perform other basic actions on a joined clean room¶

Confirm that you installed your clean room with the proper role (SAMOOHA_APP_ROLE). If you didn’t use SAMOOHA_APP_ROLE when installing the clean room, you’ll encounter many problems, typically permission errors. If this is the case, even consumer.uninstall_cleanroom will fail and you must take extra steps to uninstall then reinstall the clean room with the correct role.

-- Who owns the clean room?
SHOW SHARES LIKE 'SAMOOHA_CLEANROOM_REQUESTS_<cleanroom_name>';

-- If the owner role is not SAMOOHA_APP_ROLE, you must drop the share, then
-- uninstall the clean room.
DROP SHARE SAMOOHA_CLEANROOM_REQUESTS_<cleanroom_name>;
CALL samooha_by_snowflake_local_db.consumer.uninstall_cleanroom($cleanroom_name);
USE ROLE samooha_app_role;
CALL samooha_by_snowflake_local_db.consumer.install_cleanroom($cleanroom_name, '<provider_locator>');

General unexpected results¶

Be sure to call the proper consumer or provider version of your procedure. Many procedures have both provider and consumer versions.

Can’t find a clean room that you created¶

If you created a clean room in one account but can’t see it in the collaborator’s account, here are some possible reasons:

• The clean room was created in a different cloud hosting region and you haven’t enabled cross-cloud auto-fulfillment.

• You didn’t publish your clean room by calling provider.create_or_update_cleanroom_listing.

• You are calling consumer.view_cleanrooms() instead of provider.view_cleanrooms() (or the reverse).

• You didn’t share the clean room, you shared the clean room with the wrong account, or you opened the wrong collaborator account in the Snowsight/Clean rooms UI/CLI. Confirm that the account where you expect to see your clean room is the one that you shared the clean room with, and that you’re signed in to that shared account.

• There is a small delay between publishing a clean room and when it becomes visible to the collaborator.

Unknown function¶

If you call a procedure and get an error something like this: .. code-block:: sqlexample

Unknown user-defined function SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.CONSUMER.<procedure name>

You either don’t have the SAMOOHA_APP_ROLE or you’ve mistyped the name of the function.

It can also mean that you have been granted a limited-access run role, and the function you are calling isn’t allowed by your role. You can test this by trying to run one of the procedures permitted to consumer.grant_run_on_cleanrooms_to_role. If the procedure succeeds, you might be using a limited role. If it fails, you likely don’t have permission to run the clean rooms API in this account.

To see if you have SAMOOHA_APP_ROLE, run the following command:

SELECT IS_ROLE_IN_SESSION( 'SAMOOHA_APP_ROLE' );

If you get the following error:

Database <cleanroom name> does not exist or not authorized. in RUN_ANALYSIS_STRING

You were granted a temporary run role that has been revoked. Ask a clean rooms administrator for a new run role or full SAMOOHA_APP_ROLE access.

Check your query history¶

You can see your query history for queries run in the UI or in code. These histories are stored and checked separately.