Substituting Variables and Secrets at Runtime

Magpie offers both variables and secrets to substitute values into commands and scripts when they are executed.

Variables vs. Secrets

Variables and Secrets both offer a way to insert values into a script at runtime, but have differences that are summarized in the table below:



Substitution Syntax




Exist only for the current Magpie session; erased when you logout.

Exist until deleted


Can be created by anyone

Can be created by Organization Admins


Can be updated by the person who created the variable

Can be updated by Organization Admins or delegated to other users with the write command


Can only be used by the user who set the variable

Can be shared with others by granting read access to the user for a specific Secret

Data Types

String, Integer, Long, Float, Double, Decimal, Boolean, date, timestamp






Can be substituted into Magpie and SQL commands

Can be substituted into Magpie and SQL commands

Access in Scripting Languages

Can be set and accessed in Scala, Python, and R scripts via MagpieContext object

Can be accessed in Scala, Python, and R scripts via MagpieContext object

Suggested Uses

Storing a value for temporary use in multiple places in a Notebook or Script

Storing and distributing API Keys or Shared Passwords

Setting and Using Variables

In the first example, we set a variable that is used as a table name. The table is called property_use_codes.

set table_var = 'property_use_codes'

The variable can then be called in any interpreter with the general substitution syntax of $#{variable:table_var} or using the variable shorthand notation, which consists of the variable name preceded by the $# prefix.

The variable could be used to create a table by substituting the key value string using the full substitution syntax:

create table {
  "name": "$#{variable:table_var}"

Or the exact same command can be run with the variable shorthand notation:

create table {
  "name": "$#{table_var}"

Next, you can substitute variables in a show statement with the variable:

show 8 from $#{table_var}

Which results in:

Other String Variables

When declaring string variables, different types of quotes can be used depending on your use case. For instance, if you need a multiline variable, you can declare your variable with """...""" quotes. Or, if your variable contains double quotes, you may contain the variable with a single quote.

Forced Type

By default, Magpie infers the type of the variable based on the content. However, variable types can be forced by adding as <variable type>. For example:

set date_cutoff = '2018-09-23' as date

Listing Variables

In order to see what variables are already set, Magpie allows users to list variables. This will show the name, type, and value of each variable in the Magpie context.

list variables

Which results in:

SQL Parameter Substitution

There is a second way that variables can be used in Magpie. The usage is determined by the variables prefix. The variable prefix $ can be used to substitute SQL parameters as opposed the variable prefix $#which performs a text substitution.

Say some variable is set date_cutoff = '2018-09-23'. One might use a SQL parameter substitution rather than a text substitution to run the query:

FROM transactions
WHERE timestamp >= $date_cutoff

Note that in this case, we did not force the type because Magpie’s SQL interpreter is able to cast the string value in the background. Using a $# prefix, in contrast, requires enclosing the variable in quotes, i.e., ... WHERE timestamp >= '$#date_cutoff'.

Setting Variables in Scripting Interpreters

Magpie variables can also be set within Python and Scala scripts. This can be useful when running Magpie job flows. Variables are set in the Magpie context and are associated with the session in which the variable is set. For example, let’s say that we want to substitute a file path to use later when saving a table:

mc.variables.set('staging_path', '/staging/2dyNWv/')
mc.variables.get('staging_path') # returns "/staging/2dyNWv/"

In a separate task, we can then use the variable, staging_path, to set a path in a command. For example:

create table transactions from data source my_data_source
  at path '$#{variable:staging_path}';

Creating and Using Secrets

In this example, we demonstrate creating two Secrets to store the credentials to create a Snowflake Data Source.

First, an Organization Admin can create the Secrets. It is important to note that the name of your secret must be unique across your Organization.

Store the username using the shorthand syntax for creating secrets:

create secret snowflake_user with value "magpie"

If your secret contains special characters or quotes, you can enclose your secret’s value in “““triple quotes”””.

Next store the password as a secret. Since we are including a description, it is necessary to use the longer-form JSON-Spec syntax:

create secret {
  "name": "snowflake_password",
  "description": "The Snowflake system account password for magpie",
  "value": "PL7cvi6ImTfGH22vUW9AM8oC"

Q: Why did we store the Snowflake username as a secret and not as a variable?
A: Secrets can be shared with other users and remain until they are explicitly deleted, unlike variables, which cannot be shared and are erased when you log out.

Next, the Organization Admin can grant read access to users who have the developer role:

grant read on secret api_key to role developer;

Any users with read permissions on a Secret may be able to expose the Secret’s value via scripts or commands. Be sure to only grant read permissions to individuals who are authorized to know the secret.

The secret can now be substituted into any interpreter with the general substitution syntax of $#{secret:<secret_name>}. Below is a create data source example that uses the username and password secrets set above:

create data source {
  "name": "sample_snowflake_source",
  "sourceType": "snowflake",
  "hostname": "",
  "user": "$#{secret:snowflake_user}",
  "password": "$#{secret:snowflake_password}",
  "database": "ANALYTICS",
  "defaultSchema": "PUBLIC"

Listing Secrets

All Secrets in your organization can be enumerated by all users of your organization, but they cannot see more than the Secret’s name unless the user is an Organization Admin or Read/Write permissions have been granted to the user or a role attached to a user. Therefore, the result of the list secrets command does not imply the ability to read or write the value of a Secret.

list secrets;

Which results in:

Reading Secrets in Scripting Interpreters

Magpie secrets can also be set within Python and Scala scripts. This can be useful when running Magpie job flows. Secrets are defined in advance using the Magpie DSL and then can be read via the Magpie Context, provided that the user running the script has permissions to read the secret.


create secret api_key with value "my_secret_key";

mc.secrets.get('api_key') # returns "my_secret_key"

Was this article helpful?
0 out of 0 found this helpful