dbt integration
Import your dbt models into Count
Last updated
Import your dbt models into Count
Last updated
is an open-source tool for transforming data, helping data engineers build and define a of SQL queries called .
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.
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.
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.
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:
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.
Once a dbt integration is created, a dbt icon appears in the 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.
When exporting , references to that cell default to ref() rather than SQL, with cells further upstream not included.
If the dbt Core integration is , then changes to these cells are interpreted as changes to dbt model files.
Count reads your dbt 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.
See our for frequently asked questions & how to guides on our dbt integrations.
SELECT statement of the model.ref() call, and available models will appear in autocompletionsdbt package, and so in the global scope.
