.. _dbt_templater:

:code:`dbt` templater
^^^^^^^^^^^^^^^^^^^^^

.. note::
    From sqlfluff version 0.7.0 onwards, the dbt templater has been moved
    to a separate plugin and python package. Projects that were already using
    the dbt templater may initially fail after an upgrade to 0.7.0+. See the
    installation instructions below to install the dbt templater.

    dbt templating is still a relatively new feature added in 0.4.0 and
    is still in very active development! If you encounter an issue, please
    let us know in a GitHub issue or on the SQLFluff slack workspace.

:code:`dbt` is not the default templater for *SQLFluff* (it is :code:`jinja`).
:code:`dbt` is a complex tool, so using the default :code:`jinja` templater
will be simpler. You should be aware when using the :code:`dbt` templater that
you will be exposed to some of the complexity of :code:`dbt`. Users may wish to
try both templaters and choose according to how they intend to use *SQLFluff*.

A simple rule of thumb might be:

- If you are using *SQLFluff* in a CI/CD context, where speed is not
  critical but accuracy in rendering sql is, then the `dbt` templater
  may be more appropriate.
- If you are using *SQLFluff* in an IDE or on a git hook, where speed
  of response may be more important, then the `jinja` templater may
  be more appropriate.

Pros:

* Most (potentially all) macros will work

Cons:

* More complex, e.g. using it successfully may require deeper
  understanding of your models and/or macros (including third-party macros)

  * More configuration decisions to make
  * Best practices are not yet established or documented

* If your :code:`dbt` model files access a database at compile time, using
  SQLFluff with the :code:`dbt` templater will **also** require access to a
  database.

  * Note that you can often point SQLFluff and the :code:`dbt` templater at a
    test database (i.e. it doesn't have to be the production database).

* Runs slower

Installation & Configuration
""""""""""""""""""""""""""""

In order to get started using *SQLFluff* with a dbt project you will
first need to install the relevant `dbt adapter`_ for your dialect
and the :code:`sqlfluff-templater-dbt` package using
your package manager of choice (e.g.
:code:`pip install dbt-postgres sqlfluff-templater-dbt`) and then will need the
following configuration:

.. _`dbt adapter`: https://docs.getdbt.com/docs/available-adapters

In *.sqlfluff*:

.. code-block:: cfg

    [sqlfluff]
    templater = dbt

In *.sqlfluffignore*:

.. code-block:: text

    target/
    # dbt <1.0.0
    dbt_modules/
    # dbt >=1.0.0
    dbt_packages/
    macros/

You can set the dbt project directory, profiles directory and profile with:

.. code-block:: cfg

    [sqlfluff:templater:dbt]
    project_dir = <relative or absolute path to dbt_project directory>
    profiles_dir = <relative or absolute path to the directory that contains the profiles.yml file>
    profile = <dbt profile>
    target = <dbt target>

.. note::

    If the `profiles_dir` setting is omitted, SQLFluff will look for the profile
    in the default location, which varies by operating system. On Unix-like
    operating systems (e.g. Linux or macOS), the default profile directory is
    `~/.dbt/`. On Windows, you can determine your default profile directory by
    running `dbt debug --config-dir`.

To use builtin dbt Jinja functions SQLFluff provides a configuration option
that enables usage within templates.

.. code-block:: cfg

    [sqlfluff:templater:jinja]
    apply_dbt_builtins = True

This will provide dbt macros like `ref`, `var`, `is_incremental()`. If the need
arises builtin dbt macros can be customised via Jinja macros in `.sqlfluff`
configuration file.

.. code-block:: cfg

    [sqlfluff:templater:jinja:macros]
    # Macros provided as builtins for dbt projects
    dbt_ref = {% macro ref(model_ref) %}{{model_ref}}{% endmacro %}
    dbt_source = {% macro source(source_name, table) %}{{source_name}}_{{table}}{% endmacro %}
    dbt_config = {% macro config() %}{% for k in kwargs %}{% endfor %}{% endmacro %}
    dbt_var = {% macro var(variable, default='') %}item{% endmacro %}
    dbt_is_incremental = {% macro is_incremental() %}True{% endmacro %}

If your project requires that you pass variables to dbt through command line,
you can specify them in `template:dbt:context` section of `.sqlfluff`.
See below configuration and its equivalent dbt command:

.. code-block:: cfg

    [sqlfluff:templater:dbt:context]
    my_variable = 1

.. code-block:: text

    dbt run --vars '{"my_variable": 1}'

Known Caveats
"""""""""""""

- To use the dbt templater, you must set `templater = dbt` in the `.sqlfluff`
  config file in the directory where sqlfluff is run. The templater cannot
  be changed in `.sqlfluff` files in subdirectories.
- In SQLFluff 0.4.0 using the dbt templater requires that all files
  within the root and child directories of the dbt project must be part
  of the project. If there are deployment scripts which refer to SQL files
  not part of the project for instance, this will result in an error.
  You can overcome this by adding any non-dbt project SQL files to
  .sqlfluffignore.