Support
How can we help you today?

Snowflake

Snowflake is a cloud-based data warehousing platform known for its scalability and flexibility. Its cloud-native architecture leverages the benefits of providers like Google Cloud, AWS and Azure. With a focus on ease of use, it facilitates secure data sharing and collaboration. The platform supports standard SQL enabling all common operations. Standard Data Definition Language (DDL) statements like CREATE, ALTER and DROP allow database managers to create, modify, and remove database objects such as tables, columns, views, sequences, functions and procedures.

DeZign for Databases has been adapted to enable data modeling for Snowflake databases. Our software connects to your Snowflake databases and reads out the structure of the database. After the connection with the Snowflake database has been set up, the structure of tables, views, primary key constraints, foreign key constraints, sequences, procedures and functions are loaded into DeZign for Databases. The tool automatically creates a data model from this metadata that you can adjust. After you have adjusted the data model, you can compare your data model within the tool with your original Snowflake database in the cloud. The tool can then generate intelligent Snowflake specific alteration code to modify the database in the cloud.

In summary:

  • Reverse engineering: Connect to a Snowflake database and import your database structure (a diagram is automatically created for each schema).
  • Forward engineering: Generate a complete (new) Snowflake database.
  • Bi-directional synchronization: Intelligently generate Snowflake specific code to modify/alter your database in the cloud. Connect to the Snowflake database to keep your data model up to date if it has changed outside of our software (update model).

Connect to a Snowflake database

To access a Snowflake database, DeZign for Databases uses ODBC. DeZign for Databases requires that the Snowflake ODBC driver (32-bit) is installed on the machine from which DeZign for Databases connects to Snowflake. For more information on downloading and installing the Snowflake ODBC driver, see the Snowflake Documentation.

Prerequisites

Prerequisite steps for establishing a successful connection to a Snowflake database:

  • Snowflake Database ports 443 and 80 should be opened in your firewall to accept connections to your Snowflake database.
  • Creation of an account with metadata read-only privileges in your Snowflake database.
    • SELECT privilege for tables and views
    • USAGE privilege for database and schema (Example: GRANT USAGE ON WAREHOUSE mywarehoude TO ROLE PUBLIC)

    By default, each Snowflake account has one user who has been designated as an account administrator (i.e. user granted the system-defined ACCOUNTADMIN role). You might need to set up am account with metadata read-only privileges. DeZign for Databases executes the following queries: SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE UPPER(CATALOG_NAME)= UPPER('%database_name%') and then for each schema found in this query SELECT GET_DDL('SCHEMA', 'schema_from_query_above');

    Read more about roles in the Snowflake documentation:

Connection details

Connection method: Select one of the following options:

  • SnowflakeDSIIDriver - Snowflake ODBC Driver
    Use this connection method when you have installed the Snowflake ODBC driver (SnowflakeDSIIDriver from the Snowflake website).
  • ODBC connection string
    Use this connection method when you want to connect using a DSN (data source name), a File DSN, or a DSN-less connection using a connection string.

Snowflake account id:An account identifier uniquely identifies a Snowflake account within your organization, as well as throughout the global network of Snowflake-supported cloud platforms. Your account id has the following format: orgname-account_name. Read the Snowflake documentation for more information.

Port: Port number for the connection (default 443).

Database name: Enter the name of the Snowflake database to load.

Schema: Enter the name of the schema you wish to load. If you leave this field empty then all schemas in the database will be loaded (a diagram is created for each individual schema).

User name: Username to connect to Snowflake. If you use a connection string to connect then fill out this field when you have used the place holder %username% in your connection string.

Password: Password to connect to Snowflake. If you use a connection string to connect then fill out this field when you have used the place holder %password% in your connection string.

Connection string: If you want to use a connection string for connecting to a database then you must specify either a DSN (data source name), a File DSN, or a DSN-less connection in the string. The difference is whether you use the DSN=, FILEDSN=, or the DRIVER= keyword in the connection string, as described in the ODBC specification.

A DSN or FILEDSN connection string tells the driver where to find the default connection information. Optionally, you may specify attribute=value pairs in the connection string to override the default values stored in the data source.

The DSN connection string has the following form: DSN=data_source_name[;attribute=value[;attribute=value]...]

The FILEDSN connection string has the following form: FILEDSN=filename.dsn[;attribute=value[;attribute=value]...]

The DSN-less connection string specifies a driver instead of a data source. All connection information must be entered in the connection string. The information is not stored in a data source.

The DSN-less connection string has the following form: DRIVER=[{]driver_name[}][;attribute=value[;attribute=value]...]

The connection string is stored in the project. If you don't want to store the user name and/or password in the connection string then you can use the place holders %username% and/or %password% in the connection string. In that case fill out the user name and password in the entry fields.

Example: DRIVER=MyDriver;UID=%username%;PWD=%password%;HOST=myserver;PORT=27017;DB=db1;

Specifications

Metadata

DeZign for Databases reads the following metadata from your Snowflake database:

  • Tables
    • Table name
    • Table comments (filtered out of table options)
    • Table options (description is deleted from the table options and added to the description property)
    • Columns
      • Column name
      • Data type
      • Nullability
      • Character set
      • Collation
      • Column comment
    • Primary key constraints
      • Primary key constraint name
      • Constraint columns
    • Foreign key constraints
      • Foreign key constraint name
      • Constraint columns
  • Views
    • View name
    • Code/script
    • View comments
  • Procedure/Functions
    • Procedure/function name
    • Code/script
    • Procedure/function comments
  • Sequences
    • Sequence name
    • Start
    • Increment
    • Order
    • Sequence comments

Supported features

Reverse engineering a Snowflake database using a ODBC connectionYes
Reverse engineering by parsing a Snowflake SQL DDL fileYes
Forward engeneering (generating new Snowflake databases)Yes
Update model after comparing the model to the databaseYes
Update database (generate alter database scripts) after comparing the model to the databaseYes