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’s important 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.

• Clean rooms API: API documentation is divided into provider and consumer topic pages.

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¶

You aren’t required to use the clean rooms UI to develop clean rooms: most clean room functionality is available by calling the API. 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. Therefore, you should ask a clean room administrator to add you as a clean room manager or higher in the appropriate clean room accounts.

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 must choose an account on the clean rooms login page.

See what’s installed with the clean rooms environment¶

Snowflake Data Clean Rooms creates many local databases upon installation. You can find details about tasks and objects that are run or installed with a clean room package in Snowflake Data Clean Rooms: Installed objects.

Sample data¶

The clean rooms environment installs a few sample datasets you can use.

You can also generate synthetic test data using Snowflake.

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 to log in to the same account in each environment.

Clean room names vs clean room ID¶

When using the API, for procedures that take a clean room 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 clean rooms UI, 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 UI changes¶

When you change any clean room properties that affect the UI, call provider.create_or_update_cleanroom_listing to propagate the changes.

Interoperability between clean rooms created in code or the UI¶

When you create a clean room using 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>');

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 the following snippet:

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

Here are a few possible causes:

You typed the wrong namespace.

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

You mistyped the name of the function.

Check the reference guide for the proper naming.

You have been granted a limited-access run-role, and the function you called isn’t allowed by your role.

Test this by running the following SQL code:

USE DATABASE samooha_by_snowflake_local_db;
CALL IS_DATABASE_ROLE_IN_SESSION('samooha_run_role');

Copy

If the code snippet returns TRUE, you have limited-access run-role permissions on the clean room API. If you need greater access, ask a clean room administrator for full access. See the list of permitted run-role procedures in the consumer.grant_run_on_cleanrooms_to_role documentation.

You don’t have SAMOOHA_APP_ROLE

To see if you can use the SAMOOHA_APP_ROLE, run the following command:

-- Get current user name.
SELECT current_user();

-- Add current user name in place as indicated.
SHOW GRANTS TO USER <current_user_name> ->> select * from $1 where "role" = 'SAMOOHA_APP_ROLE';

Copy

If you don’t get any results, ask an administrator to give you API access to the clean room.

See if a user has installed a clean room¶

You can check if a given user has installed a given clean room by running the following SQL code. Replace $consumer_locator and $cleanroom_name with the consumer locator and clean room name.

SELECT * FROM snowflake.data_sharing_usage.application_state
  WHERE consumer_account_locator = $consumer_locator
    AND CONTAINS(package_name, UPPER(REPLACE($cleanroom_name, ' ', '_')));

Check your query or analysis history¶

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