Nested dynamic content allows Matik users to reference the results of one piece of dynamic content within another.
When nesting is used, Matik will first evaluate the upstream (nested) dynamic content and then insert its results into the downstream dynamic content before executing the final query or generation. This enables powerful and flexible automation across your content templates.
This article focuses on nesting dynamic content in SQL based dynamic content. See these support articles for more info on nesting in Google Sheets, Excel, and in the Matik Logos.
What is Nested Dynamic Content?
Nested dynamic content allows you to:
- Treat the results of one piece of dynamic content as input for another
- Insert dynamic content results into query parameters and
WHEREclauses - Use a result table from upstream dynamic content in your queries
Matik will always evaluates the nested (upstream) dynamic content first and then inject the result(s) into the parent (downstream) dynamic content.
Use Cases
-
Transform and Join Data Across Sources
Example: Nest a dynamic content piece that pulls Salesforce data into a parent dynamic content piece that joins it with Redshift data. -
Add Dynamic parameters in WHERE clauses
Insert a text‑based dynamic content output into an API request body orWHEREclause of a SQL query.
Where Can You Nest Dynamic Content?
You can nest these types of dynamic content:
- Text dynamic content (including subcontent)
- Table dynamic content
You can use nesting in these parent dynamic content types:
- SQL‑based dynamic content (Redshift, Databricks, Snowflake, BigQuery, Athena, MySQL, etc.)
- Google Sheets & Excel dynamic content
Not yet supported:
- CRMs (Salesforce (including SOQL), Gainsight, Hubspot...), BI Tools (Tableau, Looker, PowerBI...)
- Conditions
- Image or chart dynamic content
Matik supports up to 10 levels of nested dynamic content. However, deeper levels of nesting can lead to slower performance and longer generation times. To ensure optimal performance, we recommend keeping nesting to a minimum whenever possible.
Nesting Text Dynamic Content
To nest text dynamic content in your queries, write the name of the nested dynamic content in your query surrounded in double curly brackets, e.g {{nested_dynamic_content}}.
When nested text dynamic content is evaluated, its result is inserted into the parent as a quoted string. If you need to use the nested Text Dynamic Content in the context of another data type, you will need to explicitly cast the value. This can be done via SQL code using a CAST() function.
Nesting Text Process Flow
-
Evaluate the nested dynamic content (e.g.,
region_text)SELECT region FROM customers WHERE customer = &:customer_input; --Result: 'west'
-
Insert its value into the parent dynamic content,
region_totalSELECT SUM(sales) FROM customers WHERE region = {{region_text}}; -
Run the parent dynamic content with the injected value
SELECT SUM(sales) FROM customers WHERE region = 'west' ;
Example: Filter query by text dynamic content
SELECT * FROM customers
WHERE region = {{region_text}};Example: Cast nested text dynamic content
In the below example we want to compare a integer datatype to our nested dynamic content. Since quantity > '34' is an invalid statement. We will cast {{threshold}} to an integer:
SELECT * FROM sales
WHERE quantity > CAST({{threshold}} AS INTEGER);Example: Use sub-content from text dynamic content
SELECT {{product_details.price|commaformat(2)}} AS formatted_price;Nesting Table Dynamic Content
When you nest a table dynamic content piece in your query dynamic content, Matik creates a temporary table on your data warehouse or database containing the output of the nested dynamic content. To setup, reference the table dynamic content in double curly brackets where you would insert the table in your query.
For example, if there is a nested dynamic content piece called customer_data, its results can be queried using the following SQL in the parent dynamic content:
SELECT * FROM {{customer_data}}
WHERE customer_data.status = 'active';The customer_data dynamic content is treated as a table and can be referenced by name directly within the SQL query.
Nested Table Process Flow
To provide some clarity on what Matik is doing to achieve this here is what is happening under the hood in the above example:
- Matik runs and evaluates the
customer_datadynamic content and saves the result table - Prior to running the query above, Matik makes a call to your datasource and creates a temporary table called "customer_data" with the result table
- Matik runs the parent dynamic content query with the
{{customer_data}}tag replaced with a reference to thecustomer_datatemporary table. - The temporary table is deleted once the dynamic content has finished running (or as configured in your database settings)
Limitations
- Subcontent nesting is supported only for text dynamic content
Since SQL systems vary across data sources, certain SQL data sources might have their own nuances or limitations. See below for more details.
Redshift
- Case‑insensitive for table and column names. We recommend using unique names for Dynamic Content that do not rely on letter casing. This best practice helps avoid name collision.
- 16MB query size limit
- Use unique table/dynamic content names to avoid naming collisions
Databricks
- Initial query may be delayed if the cluster is dormant
- 16MB query size limit
Athena
- 256KB query text limit
FAQ
Can I use dynamic content in conditions?
This is not supported at this time.
Can I cast the output of nested dynamic content?
Yes! Use SQL casting or spreadsheet transformations to convert it into the desired format.
Comments
0 comments
Please sign in to leave a comment.