Snowflake - Target environment

Applies to
Platform: Snowflake

A Snowflake target environment needs the target databases, a warehouse, a role that creates and runs the objects, and SnowSQL. Prepare it before you replace the placeholders and deploy the artifacts of a Snowflake generator. Afterwards deploy.ps1 can connect and create the schemas, tables, views, stored procedures and tasks.

The steps apply to all Snowflake generator configurations; Stage Files needs the extra preparation in Provide the source data. biGENIUS support does not cover the installation and configuration of Snowflake beyond the example in this article.

Prerequisites

A Snowflake account and a user who signs in through single sign-on in a browser or with key-pair authentication. Password-only sign-ins are blocked by the Snowflake strong-authentication rules.
PowerShell 7 on the deploying machine. deploy.ps1 requires it.

Prepare Snowflake

Create the target databases, for example one database DWH for all layers. The deployment creates the schemas but not the databases.
Choose a warehouse for the deployment and the tasks, for example the default warehouse COMPUTE_WH, or create one. A warehouse provides the compute; databases are not tied to a warehouse.
Give a role the privileges to create and run the objects. For example, the owner of the target databases can create the schemas and objects; the account privilege EXECUTE TASK lets the role run tasks:
```
GRANT OWNERSHIP ON DATABASE DWH TO ROLE <role>;
GRANT USAGE ON WAREHOUSE COMPUTE_WH TO ROLE <role>;
GRANT EXECUTE TASK ON ACCOUNT TO ROLE <role>;
```
Here <role> is the role of the deploying user. The role also needs read access to the source databases.
Grant the role to the deploying user.

The load control helper scripts set the log level of the tasks and read their log events. Their logging query reads the event table LOGGING.AUDIT.EVENTS.

Install SnowSQL

Install SnowSQL, the Snowflake command-line client that deploy.ps1 runs, for example with winget install Snowflake.SnowSQL. SnowSQL is a legacy client of Snowflake; version 1.5 is supported until 16 April 2028.
Open a new PowerShell 7 window and run snowsql -v. SnowSQL prints its version.
Add a named connection to the SnowSQL configuration file, as described in Snowflake - Deployment.

Provide the source data

Which source system types a generator configuration accepts is listed in Source systems per generator configuration. To create source model objects from tables of a Snowflake database, see Discover a Snowflake database. A Data Mart project has no discovery; it uses model objects of linked projects, see Manage Linked Projects.

Parquet files with Stage Files

Parquet files are loaded by a separate Snowflake Stage Files project. A second project, for example a Data Vault project, then uses its Stage model objects as a linked project. The Stage Files loaders read the files through an external stage and a file format:

Store the Parquet files in Azure Blob Storage or an Amazon S3 bucket. The loader of each Stage model object reads the files of one folder below the stage, for example @<stage name>/customer/.
Create an external stage on that location, for example with a storage integration.
Create a file format of type PARQUET.
Enter the names of the stage and the file format in the placeholder values.

Check the result

Connect with SnowSQL as the deployment will: snowsql -c <connection name> -q "SELECT CURRENT_ROLE(), CURRENT_WAREHOUSE()". SnowSQL prints the role and the warehouse of the connection. For Stage Files, LIST @<stage name>; lists the Parquet files.

Failure symptoms and recovery

The deployment script does not find SnowSQL

Message: SnowSQL is not installed, not recognized or the PATH environment variable is not properly configured.
Cause: SnowSQL is not installed, or its folder is not in PATH of the PowerShell window.
Fix: Install SnowSQL, open a new PowerShell 7 window and check snowsql -v.

For failures in other steps, see Troubleshoot generation, deployment and load failures.