dbt integration

Import your dbt models into Count

Introduction

dbt is an open-source tool for transforming data, helping data engineers build and define a DAG of SQL queries called models.

Sound familiar? By adding dbt models as cells, the Count canvas becomes a natural place to collaboratively explore, visualise, and develop your dbt models.

Count supports dbt Cloud and dbt Core integrations. Learn how to connect to each in the following pages:

☁️dbt Cloud integration👩‍💻dbt Core integration

Video tutorials

Learn more about how the dbt integration in Count works in the following videos:

• Feature overview

• Connecting dbt

• Adding models to the canvas

• Committing changes to GitHub

Why import dbt metadata?

With a dbt integration your database tables in Count are annotated with dbt model and test metadata, allowing you to import raw and compiled code with lineage intact. Count compiles your raw code live, allowing you to swap model references for cells and vice-versa.

This simple but powerful feature enables lots of helpful workflows. For example, you may:

• Import a database table with all upstream models in one click.

• Explode a model into its component CTEs for better debugging and comprehension.

• Execute models against other databases - for example, staging and production environments.

• Prototype and iterate models live with the data team, then export dbt-ready model files.

Read more about how the canvas can help improve your dbt workflow here:

💡Tips for using dbt in Count

Viewing dbt metadata

Once a dbt integration is created, a dbt icon appears in the table pane when hovering over any database table or view that is associated with a source or model (plus any ephemeral models). Clicking this icon opens a metadata pane that exposes additional information from your dbt project, including documentation and test results.

Hover over a table or column to access dbt metadata.

Importing models

The Add to canvas button exposes options for adding models and sources to the canvas:

The different options here determine how the model is added and subsequently executed:

Jinja / Compiled

The Jinja option adds the model using the raw code from the original .sql file that defines it. The Compiled option adds the model as plain SQL as compiled by dbt. This option is available if your dbt artifacts contain compiled SQL.

Compiled SQL may be preferable if your model compilation requires information that is not available in Count, for example any private or local environment variables passed to dbt in another environment.

Explode cells

If enabled, models are exploded into CTEs before being added to the canvas. When a model has been exploded, the exploded cells are wrapped in a frame.

The cell containing the base SELECT statement from the model will be annotated with a dbt icon, while the other cells formed from the CTEs will not.

An example of a dbt model exploded into 5 cells. The cell on the right with the dbt icon represents the original SELECT statement of the model.

Upstream / downstream levels

By increasing the values here, it is possible to import additional upstream and downstream models at the same time as this one - the number of models that will be added is shown in the button below. Leaving both values at zero will import only the current model.

For example, selecting 1 upstream level and 2 downstream levels for model model is equivalent to the dbt select command:

--select 1+model+2

Model cells

When a cell is connected to a source with dbt enabled, then it will be possible to mark that cell as a dbt model using the controls in the right sidebar:

The cell model controls

If a cell is marked as a model:

• A dbt icon appears in the header of the cell.

• When exporting SQL + Jinja, references to that cell default to ref() rather than SQL, with cells further upstream not included.

• If the dbt Core integration is connected to GitHub, then changes to these cells are interpreted as changes to dbt model files.

How the ref() macro works

The ref() macro is one of several macros that are built-in to dbt and available in Count with some additional functionality.

When using this macro, any references to upstream models will be compiled to:

• A Count cell reference if a cell with that name exists

• A database identifier if a dbt model with that name exists, and is present in the database

• A CTE if an ephemeral dbt model with that name exists

This is a very helpful feature when developing models, as Count cells can act as temporary scratchpads that can override your existing model definitions. To switch between referring to a Count cell or a database table, just rename or delete the cell.

To quickly view how a reference is compiled, hover over it to view either the compiled SQL or a link to the cell:

Hovering over Jinja blocks will show either compiled SQL or a link to the referenced cell.

If a cell is deleted, all cells containing a ref() that reference that cell will be recompiled, and revert back to referencing the full database identifier.

How Jinja compilation works

Count reads your dbt artifacts and uses that information to compile your Jinja-SQL code directly in your browser. This ensures that compilation is quick, helping you to explore and iterate your models rapidly.

Because some dbt macros require executing arbitrary SQL, compilation involving complex dbt functionality is currently disabled for canvas viewers.

When opening a canvas, if you're a canvas editor the relevant dbt artifacts will be downloaded by your browser.

Once the artifacts have been downloaded and processed in the background, your models and macros will become available in autocompletions:

Start typing a ref() call, and available models will appear in autocompletions

Your project macros are also available.

In addition, Count makes available a subset of common dbt functionality, including access to cell results and formatting using native dbt macros:

These macros are from the dbt package, and so in the global scope.

dbt macros that access cell results will error if that cell is currently being executed or compiled. In this case, re-compile the cell once results are available using the Run cell button.

For compatibility with existing dbt models and macros, it is also possible to execute queries during the compilation phase of cell execution. We would suggest avoiding this approach where possible, as it is not yet (and may never be) fully integrated with Count's reactive cell execution framework.

Any queries executed during compilation are authorised and validated in the same way as any other cell query in Count, so are limited to SELECT statements.

If any queries are executed during compilation, their duration will be displayed in the cell footer.

Troubleshooting

Count fails to compile my Jinja-SQL

The Count dbt integration is designed to complement rather than replace your existing dbt workflows, so does not support the full range of dbt functionality. For example, any macros that involve executing queries that write to your database will fail, and any supporting introspective macros may also fail.

If you encounter a failure to compile a macro that you believe should be supported, please contact support using the in-app chat. You can always force a re-compilation by clicking the Run cell button.

Compilation fails for another member of my team

Currently, canvas viewers can only compile cells which contain:

• Generic Jinja functionality - for example, for-loops, set expressions, inline macros

• Uses of the ref() macro which refer to other cells

• Uses of the Count cells variable

If more complex dbt functionality is encountered, compilation will fail and a warning will be displayed in the corner of the cell:

Canvas viewers may see this warning when cells fail to compile.

This is a temporary security measure which will be lifted soon once dbt support in Count is expanded more generally.

Count failed to import my metadata

This may occur if the version of dbt in your project produces artifacts which are not yet understood by Count - for example, if the dbt version is too new or too old.

While these errors will be reported automatically, please contact support using the in-app chat if you think that your metadata should have been imported correctly.