Snowflake Connector

SHOW PRIMARY KEYS IN DATABASE "DEMO_DB";
SHOW IMPORTED KEYS IN DATABASE "DEMO_DB";

For these elements to appear in the "Worksheets" space of Snowflake, the role used must have the necessary permissions. This role is visible at the top right of the window. It is also a good way to test if the custom role you will create for the use of the connector has the necessary permissions (exemple with the role "DATAGALAXY_CONNECTOR_ROLE" here)

    Output

• Source, schema, table, view and column 

• Primary key, foreign key

Connexion configuration

    On the Snowflake side

In order to extract the first level of metadata, a database role defined by the Snowflake platform called OBJECT_VIEWER is sufficient. We recommand you to associate this default role to a custom role like this:

• Creation of a dedicated role, assigning to this role the default role OBJECT_VIEWER, assigning the necessary permissions on a warehouse to this role, creating a dedicated user and assigning the role as well as a default warehouse to this user
  To perform these actions, please execute the following commands in the “Worksheets” tab of Snowflake, replacing values between <> (example below):

// Creation of a dedicated role
create or replace role <datagalaxy_role_name>;

// Assigning default Snowflake database role to this role
grant database role SNOWFLAKE.OBJECT_VIEWER to role <datagalaxy_role_name>;

// Assigning necessary permissions on a warehouse to this role
grant usage on warehouse "<warehouse_name>" to role <datagalaxy_role_name>;

// Creation of a dedicated user and assigning the role as well as a default warehouse to this user (do not put quotes around the default role!)
create user <datagalaxy_user_name> password="<datagalaxy_user_password>" default_role = <datagalaxy_role_name> default_warehouse = "<warehouse_name>";

// Assigning the role to the user
grant role <datagalaxy_role_name> to user <datagalaxy_user_name>;

Do not forget to check "All queries" next to the "Run" button to execute all queries at once, nor to have sufficient rights to perform these actions (via the ACCOUNTADMIN role in general). Once these are performed the statement "Statement executed successfully" must appear in the result window.

• Level 1: Database, schema, table, view, dynamic table and column

        Once the role was created there is no need for extra access in order to retrieve your level 1 metadata, whether is is a single or multi database import, as long as none of them is a shared database.

• Option 1: Shared database, schema, table, view, dynamic table and column

    To retrieve metadata from shared databases, you must give explicit access to them to the role via the following command:

grant imported privileges on all tables IN DATABASE "<shared_database_name>" to role <datagalaxy_role_name>;

It is currently not possible on Snowflake to more finely manage rights on shared databases, for example by giving usage rights only on the necessary views for the execution of the connector contained in the "ACCOUNT_USAGE" schema of the shared database "SNOWFLAKE". However, a workaround is available here for those who wish

• Option 2: Primary key, foreign key

        In addition to these rights, when you wish to send primary and foreign keys, it is necessary to have certain rights directly on the schemas and tables of the databases using the following commands:

grant MONITOR on all schemas in DATABASE "<database_name>" to role <datagalaxy_role_name>; 
grant SELECT on all tables in DATABASE "<database_name>" to role <datagalaxy_role_name>;

• Option 3: Dynamic Table creation requests

       In addition to these rights, when you wish to retrieve dynamic tables creation requests it is necessary to have certain rights directly on the schemas and dynamic tables of the databases using the following commands:

grant MONITOR on all schemas in DATABASE "<database_name>" to role <datagalaxy_role_name>; 
grant MONITOR on all dynamic tables in DATABASE "<database_name>" to role <datagalaxy_role_name>;

• Option 4: Governance features

       In addition to these rights, when you wish to retrieve Snowflake tag references in non shared databases, the connector will need the SNOWFLAKE.GOVERNANCE_VIEWER database role. This can be done with the following query: 

GRANT DATABASE ROLE SNOWFLAKE.GOVERNANCE_VIEWER TO ROLE <ACCOUNT_ROLE>;

- VIEW LINEAGE on the account.

- Any privilege on the objects for which you want to retrieve the lineage for (e.g. SELECT on a table).

- USAGE on the databases and schemas that contain the objects for which you would like to retrieve the lineage for.

You can find more information about the necessary priviliges for lineage retrieval in Snowflake's documentation here.

Any trouble configuring your connexion? Please have a look at our troubleshooting page for Snowflake!

    Summary

    On the DataGalaxy side

The following information is required to set up a connection:

⚠ Retrieving the lineage can increase the duration of the connector and generates more compute on Snowflake size, which will therefore have impacts on the costs of the associated warehouse. 

* Only in URN mode

Push DataGalaxy tags to Snowflake

If you wish to push DataGalaxy tags to Snowflake, here are the necessary prerequisites:

• In Snowflake:
  • Provide the "APPLY TAG" grant on the account level to the role used by the connector. This can be achieved with the following query:

grant apply tag on account to role <connector_role>;

• Create a schema that the connector will store the DataGalaxy tags. This can be achived with the CREATE SCHEMA query.
• Provide the "USAGE" grant on the parent database of the schema that will store the DataGalaxy tags. This can be achieved with the following query:

grant usage on database <dg_database>;

• Provide the "CREATE TAG" grant on the schema that will store the DataGalaxy tags. This can be achieved with the following query:

grant create tag on schema <dg_database>.<dg_schema> to role <connector_role>;

• In DataGalaxy:
  1. Add the attribute "Tags" on the following screens: "Dictionary: Model", "Dictionary: Table", "Dictionary: View" and "Dictionary: Column"  
  2. Add values to the "Tags" attribute on objects representing Snowflake entities
• In the Snowflake connector: 
  1. Tick the "Enable Governance features" checkbox
  2. Tick the "Push DataGalaxy tags to Snowflake" checkbox
  3. Provide in the "Push DataGalaxy tags to Snowflake" text field the path of the schema where the DataGalaxy tags will be stored 

Once the prerequisites are done, all you need to do is execute the Snowflake connector that you configured.

From Standard to URN mode

Differences

1. In Standard mode, the name of your root object will be the one you give it when you create the connection (or the root object of the Dictionary module you target). In URN mode, the name of the root object will be the Snowflake account name used when setting up the connection.
   • Standard mode
   • URN mode
2. In Standard mode, in the case of a "Single import" mode (that retrieves only one database), your database will be considered as the root object of the hierarchy. In URN mode this database will be one level lower, der your Snowflake account name which will become the new root object. 
   • Standard mode ("Single import" mode)
   • URN mode

Migration guide

The aim of this guide is to show you how to switch your root object and all the Snowflake objects it contains from Standard mode to URN mode. Once you've completed these steps, you'll be able to perform all your future imports in URN mode and take advantage of the new features associated with this mode.

1. Bring one level below the schematas contained in your database (only in case of a previous import in "Single import" mode):
   • Open the menu associated with your root object (”Snowflake” here) and pick the option “+ Create a container”. It will be of type "Model" and you will name it after the name of your Database as it exists in Snowflake
   • Once it is done you will have to move all your schematas (”BURST_BANK” here) by opening their associated menus and choosing the "Move" option. You will target the previously created object, "DEMO_DB" for this example
   • From now on the steps will be the same to switch to URN mode, no matter the import mode initially chosen in Standard mode
   • If you do not do this, when you do the final URN import on your root object you will end up with duplicates of all the schemata objects of your database retrieved from Snowflake
2. If is is not already the case, associate to "Database" sources from the "Dictionary" module the "URN" attribute
3. Associate with your root object the right URN
   • Regarding this, we advise you to follow these steps in order to avoid any error:
     • Perform a new import in URN mode, which will create a new root object for which the URN attribute will be filled
     • Copy the URN attribute
     • Delete the root object and all its children that you just imported in URN mode (since a URN must be unique, if you do not delete this root object before trying to assign its URN to another object, the platform will return an error)
     • Paste the URN in order to fill the URN attribute field from your root object that is still in Standard mode
4. Do a final import in URN mode
   • This time all the URN attributes from the child objects under your root object should be filled

Congratulations, you switched from the Standard mode to the URN mode and can now enjoy all the new features it offers!

Features

Only in URN mode

• Lineage retrieval

Warnings

Account name format

Snowflake supports multiple formats for account names. When you use different formats for the same Snowflake account across connections, DataGalaxy creates separate source objects for each format rather than grouping the objects together. Furthermore, to properly utilize the cross-technology lineage feature with URN imports, you must maintain consistency by using the same account format across all technologies. Inconsistent formatting will result in duplicate representations of the same Snowflake objects in DataGalaxy.

Execution of the connector

    Step 1: Installation

• Download DataGalaxy connector from the portal (see here)
• Extract the connector archive in the directory of your choice
• Download the Snowflake plug-in from the portal and copy it into the /lib directory of the connector

   Step 2: Run connector

• After starting the connector, access the connectors of the Dictionary category

• If it was correctly installed, the Snowflake plug-in will appear

• Fill the corresponding fields using the connection information from above

• Click on "Test" to test the connection
• Once the connection test passed follow the steps to finalize your import

This connector is also available in online mode, more information on this page: [How to] Online Connector operating mode

Frequently asked questions

Does the connector support Iceberg tables?

Yes, the connector has been validated with Iceberg tables managed by the Snowflake catalog.

Releases