Ref Functions

These are used to generate database object names specific to the current environment and to establish relationships in the DAG for orchestrating node execution.

ref ()

ref is used to resolve the fully qualified name of a node from its logical storage location and node name. Using ref() will also add reference to your graph as a dependency.

This is most commonly used in join conditions to render the fully qualified node name.

Syntax

ref('<location_name>','<node_name>')

Resolves To

{{ ref('STG','CUSTOMER') }} = 'myDB'.'mySCHEMA'

ref () Example

The most basic example is adding a node to your graph. In the following example, STG_CUSTOMER was from the source CUSTOMER node, FROM {{ ref('WORK', 'CUSTOMER') }}.

'WORK' points to the Storage Mapping WORK. The Storage Mapping WORKin the workspace points to the fully qualified database and schema,SNOWFLAKE_SAMPLE_DATA.TPCH_SF10.

Because WORK is a dynamic reference that points to a fully qualified database name and schema in the Storage Mapping, if you want to deploy to a new environment, then the reference will point to the fully qualified name of the database or schema in the Storage location of the environment deployed to. In the QA Environment below, WORK is pointing to a different database and schema.

ref() Summary

• Uses Storage Locations to dynamically point to Snowflake database and schema.
• To use in multiple environments, the ref location name should be the same. For example, use location WORK in both QA and Testing environments.

ref_link()

Use ref_link() to add a reference to the graph as a dependency. ref_link() doesn’t create a string in the template. This means it will link between the nodes, but not refer to any object.

This is most commonly used to create a dependency on the execution of a node, without directly referencing that node in the user's SQL. For example, to force a node to run before the current node, the user would reference the node with ref_link in the JOIN tab of the downstream node.

Syntax

ref_link('<location_name>','<node_name>')

Resolves To

{{ ref_link('STG','CUSTOMER') }} resolves to → (empty string)

Example

In the graph, you can see several nodes. If you want to refer to nodes and create a link between them, use ref_link().

In the graph below, you can see lines linking different nodes to each other. If using ref_link(), you’ll create a connection between the nodes.

ref_no_link()

ref_no_link() is used to resolve the fully qualified name of a node from its logical storage location and node name. Refer to any object in the DAG without creating a connection. This is most commonly used when referencing the fully qualified name of the current node within its own template. Without ref_no_link, a cyclical dependency would be created, which is not allowed. ref_no_link() is good for when you need to reference something once, but don’t need to track its lineage.

Syntax

ref_no_link('<location_name>','<node_name>')

Resolves To

{{ ref_no_link('STG','CUSTOMER') }} resolves to → ”myDB”.”mySCHEMA”.”CUSTOMER”

Example

In this graph, you can see different nodes and the links between them. If you want to refer to a node without creating a link use ref_no_link().

For example, you want to refer to a set of values derived from the DIM_CUSTOMER_REF_LINK node in the join query in STG_NATION without creating a link between the nodes in the graph. You could write this in WHERE statement:

FROM {{ ref('WORK', 'NATION') }} "NATION"
WHERE "NATION"."CUSTOMER_ID" IN (SELECT "TABLE2"."CUSTOMER_ID" FROM
 {{ ref_no_link('WORK',DIM_CUSTOMER_REF_LINK )}} "TABLE2")

WHERE table2.id {{ ref_no_link('WORK',DIM_CUSTOMER_REF_LINK )}} TABLE2

Now you can use the data in DIM_CUSTOMER_REF_LINK without creating a link between them.

You can also find example usage in the Stage-Base Templates section of our User-defined Nodes (UDNs) article.

Summary

• It doesn’t add a relationship in your DAG.
• You can use it with filter statements like WHERE or SELECT. Good for infrequent use cases and incremental loading.

{{this}}

This used to dynamically point to the current object so it can be deployed and used across different environments.

Example

There are multiple ways to use {{this}}. The following example uses data quality testing with Snowflake sample data to test for the existence of a value.

In this example, there is the Customer table, and in that table we only want to return rows where the C_MKTSEGMENT = 'BUILDING'. To be sure, create a test that looks for the value you don’t want, FURNITURE.

SELECT *
FROM {{this}}
WHERE  C_MKTSEGMENT = 'FURNITURE'

The test uses {{this}} to refer to the current fully qualified database path. When you run the tests, it evaluates to the current database.

In this example results, you can see it returns:

SELECT 1 WHERE EXISTS (
           SELECT *
FROM "TESTING"."DEV"."STG_CUSTOMER_THIS"
WHERE  C_MKTSEGMENT = 'FURNITURE'

It has been evaluated to database.schema.table or "TESTING"."DEV"."STG_CUSTOMER_THIS" . Using {{this}} allows you to reuse your SQL across Environments and Workspaces.