Snowflake Asset description reverse sync¶

Asset description reverse sync Package enables users to reverse-sync updated descriptions of assets from Atlan back to Snowflake by adding them as comments on the corresponding objects within Snowflake. This package provides a stored procedure that users can execute to update the comments on Snowflake.

In-Scope: Snowflake

Mode: Offline

Prerequisites for Execution.¶

To successfully execute this package on Snowflake, ensure that the snowflake user performing the operation has the necessary roles and permissions required to update comments on Snowflake objects.
User can use Dry Run option to get stored procedure which they can run to get all this information regarding your current roles and updated that will be made inside the logs of the snowflake. To execute the stored procedure in Dryrun state pass YES in parameter while calling the stored procedure ex:- call Asset_Description_reverse_sync('YES');.
The object_owner column should not be empty if you want the comment to be updated for the object in snowflake.account_usages.object where object can be table, schemata, database, functions etc. For example if you use below query for checking details of table in account_usage.
```
select table_name, table_owner from snowflake.account_usage.views  where table_catalog = '<yourDatabaseName>';
```
Here If both the tables are included in the stored procedure for update of comments table NRR_COHOT comment will not be updated and it has no owner. Plus if the user does not have role which is in object owner this also will result in comment not being updated.
In similar way you can check for owners of other assets types.

To check all roles a current user have run below command or run the stored procedure by making dryrun parameter as Yes.

SELECT REPLACE(VALUE, '"') as CURRENT_ROLE FROM TABLE(FLATTEN(input => PARSE_JSON(CURRENT_AVAILABLE_ROLES())))

If any roles found out to missing then to grant ownership of object to a certain role you can use below query.
```
GRANT OWNERSHIP ON '<object_type>' '<object_name>' TO ROLE '<role_name>' COPY CURRENT GRANTS;
```

Credentials¶

In this section, you will configure the output destination where the assets are extracted.

PreviewS3 Bucket

Image title

Atlan API Token: this is required to generate the dynamic dropdowns (see the Asset types in scope screens)

Warning

The CSV file can be downloaded from the Argo UI.

Image title

The Bucket which will be used to store the output value is default S3 bucket attached to the tenant.
Atlan API Token: this is required to generate the dynamic dropdowns (see the Asset types in scope screens)
S3 Bucket Name
S3 Folder Path: folder path with trailing slash - atlan/output/
Region: s3 bucket region

Configuration¶

Image title

Extraction mode Set as Offline.
Connection Select the Snowflake connection you want to use for syncing data from Atlan to Snowflake.
Asset types in scope Choose the asset types you want to include in the reverse-sync operation, such as ['Table', 'Column']. This selection will ensure that only Tables and Columns with updated user descriptions are included in the process.
Recompile Views? If selected as YES Updating descriptions for Columns in Views will require a recompilation of the updated View definition SQL, that could result in a momentary unavailability of the impacted Views. If NO is selected no changes will be made.
Interval for Run Select the time interval in which the user-description was updated on Atlan for the selected assets.

Audit Trail¶

Image title

Database Name and Schema Name are used to create two types of tables during the execution of the stored procedure: tbl_atlan_reverse_sync_inventory (Temporary Table) This table is a temporary table that exists only for the duration of the current database session. It is used to store temporary data that is required for updating comments. Once the session ends or the user logs out, this table is automatically dropped and no longer exists. It helps manage intermediate data efficiently without leaving any persistent footprint.

tbl_atlan_reverse_sync_inventory (Persistent Table) This table is created only if it does not already exist in the environment. It is a persistent table that remains in the database across sessions. If it is the first time the stored procedure is executed or if the table is not present in the current environment, this table will be created. It is used to store data that needs to persist beyond the current session, ensuring that essential information is retained for future operations. By using these tables appropriately, the stored procedure manages both temporary and persistent data effectively, facilitating accurate updates and maintaining necessary records.

Downloading the stored procedure¶

To Download the Stored Procedure Follow below steps.
Download Preview File
- From workflow centre: Go to the run and select the node run(0) from the graph. On the right sidebar that appears, click on Inputs/Outputs and download asset-description-reverse-sync.
- From tenant S3: Go to the tenant S3 bucket and navigate to this prefix supplied in input to download asset-description-reverse-sync.sql.

What it does¶

Get assets whose user-description was updated in selected time range and updated that as a comment on Snowflake.

How it works

Extracts assets from the selected Snowflake connection.
Transform that data and convert them into a single python dictionary.
Create Stored Procedure using this data and jinja template predefined.
Based on option selected output method the file is either uploaded to s3 bucket or can be downloaded from argo artifact.

Image title