How-to: develop templates

Template generic building blocks

Let’s see what the building blocks are for templates.

Name Description

Information details template

can contain data for describing the template (with for example used config variables)

Variables

a set of variables that will be used in your template

Prerequiresites

check if you have rights to use used object

Action

contains the actions to create or update the object

Validation

check if the actions are correct executed

Information

Information in a template always has a start and an end:

start: /**
end:   **/

And is used for describing a couple off things:

  • Description of template

  • Describing building block, for example prerequiresites

  • Describing usefull information for the users of the template

Listing 1. information top
/**
 * Derived from template: {{ template }} (1)
 *
 * This script solely creates admin user (2)
 *
 * The default role will be set where the role is created (3)
 *
 * Jinja variables to set: (4)
 *  - var_user_name: STRING e.g. "adm_user"
 *  - var_user_email: STRING e.g. "info@acheron.cloud"
 *  - var_wh_default: STRING e.g. "WH_DEMO"
 **/
1 Is used for setting the template name dynamically, this will be filled when the SQL file is generated
2 Short description of the use of the template
3 Extended information about the template
4 A block of variables that will be used in the config file, for generating SQL with this template

Variables

The power of a templates are the use of variables, so that you can generate SQL with only a few lines of code.

Listing 2. variables
{% for var_user_name, var_user_email, var_wh_default in j2_vars %} (1)
UNSET (var_rl_sec, var_user_name, var_user_email, var_wh_default); (2)
SET var_rl_sec          = 'SECURITYADMIN'; (3)
SET var_user_name       = '{{ var_user_name  | resolve() }}'; (4)
SET var_user_email      = '{{ var_user_email | resolve() }}';
SET var_wh_default      = '{{ var_wh_default | resolve() }}';
1 A block used for looping over a variable list, read more …​…​
2 "UNSET" is used for clearing variables before use, so that you always use the rights variables and not using old variables that are set
3 Hardcoded variables that will never change for a template can be used as "SET <variable name template> = 'hardcoded value'"
4 Configurable variables will be set with the following structure "SET <variable name template> = '{{ config file variable | resolve() }} '

Prerequiresites

Within prerequiresites we will check if your setup is ready for executing the requested action.

Listing 1. A couple of regular used prerequiresites are:
/**
 * Prerequisites
 **/
USE ROLE IDENTIFIER (1)
USE WAREHOUSE IDENTIFIER (2)
USE DATABASE IDENTIFIER (3)
1 For checking if you can use the selected Role
2 For checking if you can use the selected Warehouse
3 For checking if you can use the selected DATABASE

Action

Within action we setup the actions to create or update the object

Listing 3. action
/**
 * Create user
 **/
USE ROLE IDENTIFIER($var_rl_sec);
  CREATE USER IF NOT EXISTS IDENTIFIER($var_user_name)
    PASSWORD             = '*****'
    COMMENT              = 'ADM account'
    LOGIN_NAME           = $var_user_name
    DISPLAY_NAME         = $var_user_name
    MUST_CHANGE_PASSWORD = FALSE
    DEFAULT_ROLE         = $var_rl_sec
    DEFAULT_WAREHOUSE    = $var_wh_default
    EMAIL                = $var_user_email
  ;
GRANT ROLE IDENTIFIER($var_rl_sec) TO USER IDENTIFIER($var_user_name);

Validation

Within validation we check if the actions are correct executed

Listing 4. config
{
  "template": "create_named_user.sql.j2",
  "vars": [
    {
      "var_user_name": "SNOWFLAKE_USER",
      "var_user_email": "info@acheron.cloud",
      "var_wh_default": "WH_DWH_P_ELT"
    }
  ]
}