Skip to main content

​
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
Using parameterized semantics in a dynamic dataset deployed as a VIEW allows to create parameterized views

​
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

Each parameter is defined by a YAML file in Git, which also tracks and preserves the full 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
⌘I