Configure OAuth with write access (Beta)

🚩
This is a public beta feature that’s subject to quick, iterative changes. As a result, the latest product version may differ from the contents of this document.

Sigma allows you to leverage the benefits of OAuth permission management with write-access features like input tables, warehouse views, workbook materializations, and CSV uploads.

This document explains how to configure an OAuth connection to enable secure and efficient writeback workflows for Sigma objects (created or edited using write-access features). For more information about OAuth and general usage, see OAuth with Snowflake.

System and user requirements

The ability to configure an OAuth connection with write access requires the following:

OAuth must be configured as an authentication method for your organization.
You must be assigned the Admin account type.
You must be able to provide credentials for a Snowflake service account.
You must be able to provide the schema paths of all write destinations to be used by Sigma’s write-access features.
- If your organization utilizes input tables, this includes the schema path for the input table write-ahead log (WAL) destination.

Understanding OAuth with write-access features

To use an OAuth connection with write-access features, Sigma requires you to designate one or more Snowflake schemas as write destinations for Sigma object data.

Since OAuth enables Sigma to inherit data permissions defined in Snowflake, users can only create and edit Sigma objects written to authorized write destinations. When a user is granted write permission to only one Snowflake schema designated as a write destination for the OAuth connection, the Sigma objects they create are written to that destination by default. Otherwise, Sigma allows the user to choose the destination when creating the object.

💡
Ensure relevant write permissions are granted to users through their primary roles in Snowflake. Sigma doesn't support or inherit permissions granted to secondary roles.

Sigma also requires you to provide credentials for a service account granted permission to write to all configured destination schemas. This service account creates tables in Snowflake used specifically for managing individual users' writeback behavior for each schema.

🚧
Tables created by the service account to manage writeback behavior can be easily identified by object names prepended with SIGDS_. To ensure proper functionality of write-access features, avoid modifying any Snowflake table with the SIGDS_ prefix.

For more information about enabling and using a service account with an OAuth connection, see OAuth with Snowflake.

Writeback architecture for write-access features

The following steps explain how an OAuth connection enables Sigma to write warehouse views, workbook materializations, and CSV uploads to Snowflake (for information about writing input tables, see Understanding OAuth with input tables in this document):

In the Sigma UI, a user creates or edits a Sigma object with a specified write destination.
The Sigma UI sends the object data to Sigma’s web service.
Sigma’s web service retrieves the user’s OAuth credentials from the Sigma database.
Sigma’s web service applies the user’s OAuth credentials to authorize Snowflake access and write the object data to the specified write destination schema.

Understanding OAuth with input tables

When your organization utilizes input tables, enabling OAuth with write access requires you to designate a Snowflake schema as the input table write-ahead log (WAL) destination.

📘
The WAL is a sequential record of input table changes that stores information related to user activity and resulting system operations (including input table data stored as edit records). As an internal database mechanism, the WAL ensures data durability, consistency, and recovery.

The configured service account must have permission to write to the WAL destination, and it must be the only Snowflake user account with this access.

🚧
The WAL's corresponding Snowflake table can be easily identified by an object name prepended with SIGDS_. To ensure proper input table functionality, avoid modifying any table with the SIGDS_ prefix.

Writeback architecture for input tables

The following steps explain how an OAuth connection enables Sigma to write input tables to Snowflake:

In the Sigma UI, a user creates or edits an input table with a specified write destination.
The Sigma UI sends the object data to Sigma’s web service.
Sigma’s web service retrieves the connection’s service account credentials from the Sigma database.
Sigma’s web service applies the service account credentials to authorize Snowflake access and write input table change information to the WAL destination schema.
Sigma’s web service retrieves the user OAuth credentials from the Sigma database.
Sigma’s web service applies the user OAuth credentials to authorize Snowflake access and write the object data to the specified write destination schema.

Best practices when upgrading to OAuth with input tables

If your organization utilizes input tables, it's important to note the following information and best practices when you upgrade a non-OAuth connection to use OAuth.

Input tables written to the connection before the upgrade can still be viewed in Sigma, regardless of whether the previous destination schema is configured as a new write destination. However, users can only continue writing to existing input tables if they're granted write permission to the previous destination schema in Snowflake.
For a seamless transition using OAuth, the WAL destination must be the same schema path that was configured as the connection's writeback destination before the upgrade. If you need to configure a different schema path as the WAL destination, you must also move the WAL's corresponding Snowflake table to the new destination.
- To move the WAL table to a new destination, use the following SnowSQL:
```
ALTER TABLE {original_db}.{original_schema}.{original_table} RENAME TO {new_db}.{new_schema}.{new_table};
```
- Each connection has a single WAL table, named as follows: SIGDS_WAL_{connection_id}. If multiple connections use the same WAL destination, that destination schema can contain multiple tables with the SIGDS_WAL prefix. Ensure you move the correct WAL table for the specific connection.