.. _dbt_templater:

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

: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*.

Because the dbt templater subclasses the Jinja templater, it can also benefit
from :ref:`templater_variant_rendering` when SQLFluff is linting branched
templated SQL.

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

    dbt_packages/
    macros/
    target/

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, can be overridden by env var DBT_PROJECT_DIR>
    profiles_dir = <relative or absolute path to the directory that contains the profiles.yml file, can be overridden by env var DBT_PROFILES_DIR>
    profile = <dbt profile>
    target = <dbt target>
    dbt_skip_compilation_error = <True or False, default is True>

.. 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`.

.. note::

    A fatal error can be raised at compile time. That can sometimes happen for SQLFluff related reasons (it used
    to happen if we tried to compile ephemeral models in the wrong order), but more often because a macro tries to query
    a table at compile time which doesn't exist.
    By default, `dbt_skip_compilation_error` parameter is set to `True`, that's why such errors will be ignored.
    However if you want to see them, you can set it to `False` and SQLFluff will raise a fatal error.

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.