Parameters

​

Introduction to Parameters

Parameters can be used to create global constants.

A parameter can be used with a $ sign, like $status in this parameterized filtered metric:

orders.count FILTER (WHERE orders.status = $status)

Parameters are defined either per workspace, per domain, or per dynamic dataset. The definition includes parameter name and default value.

Parameter values can be overridden based on the following hierarchy:

1. Global value (per workspace)
2. In a domain
3. In a query (when using the SQL interface) or in a dynamic dataset

​

Parameterized Dynamic Datasets

Dynamic datasets can include calculations with parameters.

The value of the parameter when in a user query is by this order:

1. Value set in real-time by user with a SET command.
2. Default value set in the dynamic dataset
3. Default value set in the domain
4. Default value set in the workspace

A dynamic dataset parameter default value can override a domain parameter or a workspace parameter of the same name.

​

Deploying to VIEWs

When a dynamic dataset is deployed as a Snowflake VIEW, parameters can be set using the Snowflake SET statement:

-- run in Snowflake, on a view that uses the $status parameter

SET status='O';
SELECT * FROM [Honeydew Dataset Deployed View];

​

Deploying to TABLEs

When a dynamic dataset is deployed as a Snowflake TABLE (regular or dynamic) the default values of parameters are used, as set in the dynamic dataset configuration.

​

Using parameters within a SQL interface connection

If there is a SQL interface table or domain called world, and a parameter called param, set it as:

SELECT ...
FROM (
  SELECT *, 12 as "$param" from world
)

The statement above selects from world with 12 as the value of parameter param.

​

Dynamic parameters using UDFs

Parameter values can be computed using user defined functions (UDF) in Snowflake, by using the UDF in the parameter expression:

SELECT ...
FROM (
  SELECT *, my_udf(12) as "$param" from world
)

The statement above selects from world with parameter param set to the result of UDF my_udf(12).

This approach allows complex computation of parameters, using other functions or data in Snowflake.

For example, a user parameter can be used to extract value from a lookup table:

-- A user defined function that maps keys to value
CREATE OR REPLACE FUNCTION my_udf (key number)
RETURNS varchar
AS 'select value from lookup_table where key = lookup_table.key limit 1';

​

YAML Schema

Every parameter is backed by a text file in git that defines it, and keeps history of every change.

The schema for parameter is:

type: parameter
name: [name]
display_name: [display name]
owner: [owner]
description: |-
  [description]
labels: [...]
folder: [folder]
hidden: [True/False/Yes/No]
value: |-
  [value statement]

Fields:

• name: Name of parameter (global)
• value: SQL constant expression (i.e. a number or a string)
• display_name, owner, description, labels, folder, hidden: Metadata