Sync Back to Snowflake

Automatically push documentation from Catalog to your Snowflake instance. You can sync descriptions, also called comments, for tables, views, dynamic tables, external tables, and columns so they appear natively in Snowflake.

Prerequisites

You need a Catalog managed Snowflake integration to push back information to Snowflake. Configure it first if you haven't already.

How It Works

What Is Synchronized?

Catalog synchronizes descriptions for:

• Tables
• Views
• Dynamic tables
• External tables
• Columns

When Does It Run?

Once Sync Back is enabled, Catalog automatically checks for new or updated descriptions every 24 hours.

How Are Descriptions Updated?

Catalog only writes descriptions when the corresponding object has no description in Snowflake. It never overwrites an existing Snowflake comment.

Setup

Complete the Snowflake procedure, role, and credential configuration below to enable Sync Back.

Two Separate Setups

Activating the Snowflake integration data source and setting up Sync Back are two distinct configurations. Completing the integration setup does not enable Sync Back. Follow the steps below to configure it.

Technical Details

Catalog leverages Snowflake Procedures in order to safely update the descriptions in your Snowflake instance. As Snowflake states, procedures can run user-defined code with privileges of the role that owns the procedure, rather than with the privileges of the role that runs the procedure.

One of the top-level roles on Snowflake will create and own a secure procedure specifically made for updating comments in Snowflake and will delegate to a specific user and role only the ability to call said procedure.

Finally, enter the credentials for the user in Catalog. Use this user for Sync Back only.

For more information on how Stored Procedures work, review the Snowflake documentation:

• Snowflake Procedures

Before You Start

Complete the steps in the Technical Details and procedure sections below.

You'll need one of the following top-level roles on Snowflake to create the Catalog user:

• SECURITYADMIN
• ACCOUNTADMIN
• SYSADMIN

If your configuration allows, you can use different roles.

The role used for this setup needs rights to update comments on all objects in the Snowflake instance. If the top-level role cannot update a specific object, Catalog Sync Back cannot update it either.

Step 1: Create the Sync Back Catalog User

1.1 Create a Dedicated Role

Create a role called CATALOG_SYNC_BACK_ROLE. This role inherits the public role from your instance and won't have any rights initially. Assign this role to the Catalog Sync Back user.

USE ROLE SECURITYADMIN;

CREATE ROLE CATALOG_SYNC_BACK_ROLE;
GRANT ROLE CATALOG_SYNC_BACK_ROLE TO ROLE SYSADMIN;

1.2 Create a Dedicated Warehouse

Catalog only updates your table and column descriptions once per day and the auto-suspend setup is on.

USE ROLE SYSADMIN;

CREATE WAREHOUSE CATALOG_SYNC_BACK_WH
WITH WAREHOUSE_SIZE = XSMALL
  AUTO_SUSPEND = 59
  AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED = TRUE
  COMMENT = 'warehouse used for catalog sync-back';

1.3 Grant Usage on the New Warehouse to New Role

Add rights to the role you created in Step 1.1. This role is granted usage rights to the new warehouse you created in Step 1.2.

GRANT USAGE ON WAREHOUSE CATALOG_SYNC_BACK_WH TO ROLE CATALOG_SYNC_BACK_ROLE;

1.4 Create the Catalog User on Snowflake

Create a user called CATALOG_SYNC_BACK.

USE ROLE SECURITYADMIN;
-- You may need to use ACCOUNTADMIN
CREATE OR REPLACE USER CATALOG_SYNC_BACK
    LOGIN_NAME = CATALOG_SYNC_BACK
    DEFAULT_ROLE = CATALOG_SYNC_BACK_ROLE
    DEFAULT_WAREHOUSE = CATALOG_SYNC_BACK_WH;

-- Setting DEFAULT_ROLE does not grant the role. Grant it explicitly, otherwise
-- the user falls back to PUBLIC and cannot use the warehouse or the procedure.
GRANT ROLE CATALOG_SYNC_BACK_ROLE TO USER CATALOG_SYNC_BACK;

Step 2: Create the Snowflake Procedure

Create a procedure called CATALOG_SYNC_BACK_DESCRIPTION.

Step 2.1: Create the Catalog Utility Database

Before creating the procedure, you need a database and schema for it. You can use any database and schema you like. Note its full path, DATABASE.SCHEMA.PROCEDURE, because you'll enter it in the Catalog settings later. We recommend a dedicated one:

USE ROLE SECURITYADMIN;
-- You may need to use ACCOUNTADMIN

CREATE DATABASE IF NOT EXISTS CATALOG_UTILS;
CREATE SCHEMA IF NOT EXISTS CATALOG_UTILS.PUBLIC;

Step 2.2: Create the Sync Back Procedure

Create the Snowflake procedure that will be used by the CATALOG_SYNC_BACK user. This procedure properly treats the inputs in order to prevent the possibility of SQL injections.

USE ROLE SECURITYADMIN;
-- You may need to use ACCOUNTADMIN

CREATE OR REPLACE PROCEDURE CATALOG_UTILS.PUBLIC.CATALOG_SYNC_BACK_DESCRIPTION(
    OBJECT_TYPE  STRING,   -- 'TABLE' | 'VIEW' | 'DYNAMIC TABLE'
    OBJECT_NAME  STRING,   -- fully qualified: DB.SCHEMA.OBJECT
    COLUMN_NAME  STRING,   -- NULL/'' for object-level comment
    NEW_COMMENT  STRING    -- the description ('' clears it)
)
COPY GRANTS   -- keep existing grants when re-running this CREATE OR REPLACE
RETURNS STRING
LANGUAGE PYTHON
RUNTIME_VERSION = '3.11'
PACKAGES = ('snowflake-snowpark-python')
HANDLER = 'run'
EXECUTE AS OWNER
AS
$$
def run(session, object_type, object_name, column_name, new_comment):
    # 1) OBJECT_TYPE is a keyword, not an identifier -> whitelist it.
    otype = (object_type or '').strip().upper()
    if otype not in ('TABLE', 'VIEW', 'DYNAMIC TABLE'):
        raise ValueError('object_type must be TABLE, VIEW, or DYNAMIC TABLE.')

    if not (object_name and object_name.strip()):
        raise ValueError('object_name is required.')
    obj = object_name.strip()

    # 2) Comment literal: cannot be bound in DDL -> escape single quotes.
    safe_cmt = (new_comment or '').replace("'", "''")

    # 3) Asset name via IDENTIFIER(?) with a BIND -> the value is passed as data,
    #    never concatenated, and IDENTIFIER interprets it as a name only.
    #    Column name: IDENTIFIER() is not accepted in ALTER COLUMN, so we
    #    double-quote it (escaping any embedded ") to contain it safely.
    col = (column_name or '').strip()
    if col:
        col_q = '"' + col.replace('"', '""') + '"'
        stmt = f"ALTER {otype} IDENTIFIER(?) ALTER COLUMN {col_q} COMMENT '{safe_cmt}'"
    else:
        stmt = f"ALTER {otype} IDENTIFIER(?) SET COMMENT = '{safe_cmt}'"

    session.sql(stmt, params=[obj]).collect()
    return 'OK: ' + stmt.replace('?', obj)
$$;

Signal Failures by Raising

If you customize this procedure, make sure it raises an exception on any failure. Never return an error string instead: Catalog treats a call that returns without raising as a success, and would mark the description as synced even though Snowflake was not updated.

Step 2.3: Grant Usage on the Database, Schema, and Procedure to the Sync Back Role

USE ROLE SECURITYADMIN;
-- You may need to use ACCOUNTADMIN

-- The role must be able to traverse the database and schema to reach the procedure.
-- Without these, the procedure call fails with a misleading "does not exist" error.
GRANT USAGE ON DATABASE CATALOG_UTILS TO ROLE CATALOG_SYNC_BACK_ROLE;
GRANT USAGE ON SCHEMA CATALOG_UTILS.PUBLIC TO ROLE CATALOG_SYNC_BACK_ROLE;

GRANT USAGE ON PROCEDURE CATALOG_UTILS.PUBLIC.CATALOG_SYNC_BACK_DESCRIPTION(STRING, STRING, STRING, STRING)
TO ROLE CATALOG_SYNC_BACK_ROLE;

Step 2.4: Test the Sync Back User

The Catalog Sync Back user should now be correctly set up. Before you give Catalog the credentials, run a test to make sure everything works. Use the created role and run the following statements.

Check Warehouse

You must see the warehouse CATALOG_SYNC_BACK_WH as the default one.

USE ROLE CATALOG_SYNC_BACK_ROLE;

SHOW WAREHOUSES;

Check Role

You must see the role CATALOG_SYNC_BACK_ROLE as the current and default one.

SHOW ROLES;

Check Procedure

You must see the procedure CATALOG_UTILS.PUBLIC.CATALOG_SYNC_BACK_DESCRIPTION in the list of returned values.

USE ROLE CATALOG_SYNC_BACK_ROLE;

SHOW PROCEDURES IN ACCOUNT;

Step 3: Create a Key Pair

Review the Snowflake documentation about Key Pair Authentication for more details.

3.1 Create Private Key

Open the terminal on your computer and generate a private key without a passphrase using the following command:

openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt

3.2 Create Public Key

Generate the public key using the private key you created above with the following command:

openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub

The key generated will be in the following format:

-----BEGIN PUBLIC KEY-----
MIIBIj...
-----END PUBLIC KEY-----

3.3 Assign Public Key to the Catalog User

Execute an ALTER USER command to add the public key to the Catalog user. Insert the public key without its delimiters in the command. Here is an example:

ALTER USER CATALOG_SYNC_BACK SET RSA_PUBLIC_KEY='MIIBIjANBgkqh...';

Step 4: Add New Credentials in Catalog App

Admin Access Required

You must be a Catalog admin to complete this step.

You can now enter the newly created credentials in the Catalog App.

1. Go to Settings > Integrations.

2. Click the Sync Back button for your Snowflake integration.

   

3. Click the Edit Sync Back Credentials button in the Snowflake Sync Back section.

   

4. Add the following credentials.

   1. Account field: enter the Account Locator of your Snowflake instance. You can find it in either of these ways:

      • From the hostname, AWS Private Link, or Azure Private Link endpoint of your instance (for example example.us-west-1).

      • By running the following query in a Snowflake worksheet:

        SELECT t.value:host::string AS host
        FROM TABLE(FLATTEN(input => PARSE_JSON(SYSTEM$ALLOWLIST()))) t
        WHERE t.value:type = 'SNOWFLAKE_DEPLOYMENT';

        This returns your account hostname, for example example.eu-west-2.aws.snowflakecomputing.com. Enter only the part before .snowflakecomputing.com, for example example.eu-west-2.aws.

   2. Username field: enter the LOGIN_NAME of the user you created.

   3. Procedure Path field: paste the path of the procedure you created, for example CATALOG_UTILS.PUBLIC.CATALOG_SYNC_BACK_DESCRIPTION if you did not rename it.

   4. Private Key field: paste the private key using the following format:

      -----BEGIN PRIVATE KEY-----
      <PRIVATE_KEY>
      -----END PRIVATE KEY-----

Account Format

Don't add .snowflakecomputing.com in the Account form. Only include the account and region.

What's Next?

• Sync Back to Coalesce
• Sync Back to dbt
• Snowflake integration