Best practices for Snowflake

This article extends our recommended best practices for Snowflake.

Snowflake account management

We recommend using a single Snowflake account for working with Ascend. For additional security, you can create and use separate Snowflake accounts for each Ascend Environment.

Snowflake resource management

For each Ascend Environment, create a separate:

• Snowflake user
• Snowflake role
• Snowflake database
• Snowflake warehouse

You can name these ASCEND_[ENVIRONMENT]:

ASCEND_DEVELOPMENT
ASCEND_STAGING
ASCEND_PRODUCTION

Then configure these for each Profile. To start, you can set defaults in your Project Parameters. For instance, in Otto's Expeditions we set the following Project-level Parameters:

  parameters:
    data_planes:
      snowflake:
        account: <your-snowflake-account>
        user: ASCEND_DEVELOPMENT
        role: ASCEND_DEVELOPMENT
        warehouse: ASCEND_DEVELOPMENT
        database: OTTOS_EXPEDITIONS_DEVELOPMENT
        schema: DEFAULT

Then we have a workspace_template.yaml Profile:

profile:
  parameters:
    snowflake:
      $<: $parameters.data_planes.snowflake
      schema: DEFAULT

Each Deployment Profile will similarly override Parameters to isolate its resources.

We further override the Snowflake schema for each Flow in the [flow].yaml (transform.yaml in this example) file:

flow:
  version: 0.1.0
  parameters:
    snowflake:
      schema: ${parameters.snowflake.schema}_TRANSFORM

tip

Need help with YAML syntax? Ask Otto!

This results in isolated Snowflake resources for each Ascend Environment. Schemas are additionally namespaced by Flow for better organization. If resource conflicts between developers are a concern, you can create a separate warehouse for each developer and configure it in their Workspace Profile.