Count
count.co
  • Getting Started
    • What is Count?
    • Count FAQ
    • Intro to your workspace
    • Example canvases
    • Getting started guides
      • Set up your workspace and projects
        • 1. Review workspace settings
        • 2. Create and organise your projects
      • Canvas orientation
      • Your first ad hoc analysis
        • 1. Examples and templates
        • 2. Build your first queries
        • 3. Create visuals
        • 4. Caching, local cells and scheduling
        • 5. Collaborating with a stakeholder
      • Your first report
        • 1. Examples and templates
        • 2. Filters and control cells
        • 3. Sharing your report
        • 4. Alerts
  • Connect your data
    • Database connection overview
      • Athena
      • Azure Synapse
      • BigQuery
      • Databricks
      • Microsoft SQL Server
      • MySQL
      • PostgreSQL
      • Redshift
      • Snowflake
    • Refresh database schema
    • Upload CSV files
    • dbt integration
      • ☁️dbt Cloud integration
      • 👩‍💻dbt Core integration
  • Import & Export
    • Import from other tools
      • Import Miro files
      • Import SQL files
      • Import Google Sheets
      • Import Jupyter notebooks
    • Export code and results
      • Export compiled SQL and Jinja-SQL
      • Export CSV files
      • Export images and PDF files
  • THE CANVAS
    • Navigating the canvas
      • Canvas tool bar
      • Data sidebar
      • Customizing the canvas
    • Canvas objects
      • Cells
      • Text and markdown
      • Shapes and tools
      • Sticky notes
      • Frames
      • Images
      • Embeds
      • Stamps
      • Grouping objects
      • Object order and alignment
      • Locking objects
      • Scaling objects
      • Shared styles
    • Overviews
    • Templates
    • Count AI
    • Alerts and subscriptions
      • Slack integration
    • Keyboard shortcuts
  • Querying data
    • Cells overview
      • Dynamic query compilation engine
    • SQL cells
      • Referencing other cells
      • Jinja templating
      • SQL formatting
    • Python cells
    • Visual and low-code cells
      • Calculations in visuals and low-code cells
      • Joins in visuals and low-code cells
    • Control cells
      • Single and multiple selects
      • Date controls
      • Text, number, and boolean controls
      • Custom control cells
    • Local DuckDB cells
      • DuckDB on the server
    • Query caching and scheduling
    • Manage queries and results
    • Troubleshooting
  • Visualizing data
    • Visualization overview
    • Templated visuals
    • Custom visuals
      • Marks
      • Facet
      • Subplots
      • Style
      • Filters
    • Formatting a visual
      • Axes
        • Secondary Axis
      • Colors and labels
      • Legends
      • Tooltips
    • Column summaries
    • Dynamic text
    • Every Visual Under the Sun
  • Presenting and Reporting
    • Reports and Slides
  • Count Metrics
    • Intro to Count Metrics
    • Build and edit a catalog
    • Views
      • Creating views
      • Customizing views
    • Datasets
      • Creating datasets
    • Save changes to the catalog
      • Catalog validation
      • Version control
    • Exposing catalogs to the workspace
    • Caching in Count Metrics
    • Using the catalog
      • Explore from cell
  • Sharing and Permissions
    • Real-time collaboration
    • Comments
    • Sharing permissions
    • Shared links
    • Embedding canvases
  • History and Versions
    • Version control
    • Duplicating and merging
    • Data snapshots
  • Settings and administration
    • Workspace settings
      • Workspace members
      • Groups
      • Tags
      • Billing
      • Single sign-on (SSO)
        • Okta
        • Entra ID
        • JumpCloud
        • Google
        • Generic OIDC
      • Brand
    • Connection settings
    • Project settings
    • User settings
    • Roles and permissions
  • Quick guides
    • Interactive control guides
      • Date ranges
      • Date groupings
      • Search
      • Select All
  • Resources
    • Join the Slack community
  • Blog
  • Security overview
  • Terms of use
  • Pricing FAQ
Powered by GitBook
On this page
  • Introduction
  • How jinja templating works
  • Additional functionality provided by Count
  • Additional functionality for dbt
  • When to use Jinja
  1. Querying data
  2. SQL cells

Jinja templating

PreviousReferencing other cellsNextSQL formatting

Last updated 15 days ago

Introduction

Jinja is a templating library written in Python, traditionally used for rendering HTML web pages. However, the jinja library can be used to generate any type of text, and in Count you can use it in SQL cells to write queries.

Jinja expressions are enclosed in curly braces and support a subset of Python syntax, so compiling {{ 1 + 1 }} (for example) would generate 2.

How jinja templating works

When you include Jinja delimiter tags in a SQL cell (i.e. any of {{, }}, {%, %}, {#, #}), Count will attempt to compile the cell text as a jinja template before executing the cell. To view the compiled SQL that is actually executed, click the Show compiled SQL button in the design bar:

Jinja templates are local to each cell, so variable and macro definitions are not shared between cells.

When writing templates in SQL cells, all jinja functionality is available including:

For loops

Loop over any Python iterable and execute the expression in the body of the loop:

If expressions

Return different text depending on the result of some boolean value:

Set expressions

Declare a local variable and use it in subsequent expressions:

Macros

Define a function that can be called from subsequent expressions:

Additional functionality provided by Count

Count provides several helper functions in addition to the standard Jinja functionality. Read more about these helper functions from the documentation available through the cell autocomplete:

ref

The ref macro formats strings as SQL identifiers, adding delimiter characters and escaping as required by your database:

If the source for this cell has a dbt connection, then the ref() macro can also reference models.

cells

The cells variable can be used to access the value of control cells without the requirement to use subqueries or joins - the control cell value is compiled directly into the SQL query:

Control cell values are formatted as literals by default.

To access the string value of a control cell more generally, use the Jinja string() filter, for example:

{% if cells.b | raw | string() == 'some text' %}
...

literal / identifier / raw filters

These filters are provided by Count to format control cell values appropriately for your database:

It is currently only possible to format the values of control cells using these filters.

Additional functionality for dbt

If the current source has a dbt integration connected, then the available functionality in jinja is increased to include:

  • All built-in dbt APIs and macros

  • Any macros defined in your dbt project

Explore these additional options using autocomplete while focussed within a Jinja block.

When to use Jinja

When writing queries, it is recommended to use plain SQL as much as possible for maximum portability and clarity. However, there are some cases where using Jinja is preferable or required.

Avoiding subqueries

Some databases restrict the use of too many subqueries, which can make it difficult to use control cells effectively. In this case it is possible to remove subqueries by switching to Jinja. For example, if your SQL looks like:

select * from table where column = (select value from control_cell)

then change it to:

select * from table where column = {{ cells.control_cell }}

If your control cell is a multiple select, note that the compiled SQL does not contain any parentheses, so the SQL you write may look like:

select * from table where column in ( {{ cells.control_cell }} )

Dynamic query structure

Some manipulations are impossible in SQL, such as dynamically changing the number of selected columns or the tables in the FROM clause. In these cases it is possible to use jinja templating instead:

An example of referencing a control cell using a jinja template
Note the use of the special loop.last variable to strip the trailing comma.
When typing within a jinja expression, the cell autocomplete will switch to jinja mode
Allow selecting from different cells depending on the value of a control cell