dbt integration
Import your dbt models into Count
Last updated
Import your dbt models into Count
Last updated
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:
Learn more about how the dbt integration in Count works in the following videos:
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:
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.
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.
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:
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:
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.
ref() macro worksThe 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:
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.
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:
In addition, Count makes available a subset of common dbt functionality, including access to cell results and formatting using native dbt macros:
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.
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:
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.
SELECT statement of the model.ref() call, and available models will appear in autocompletionsdbt package, and so in the global scope.
