dbt_coves 1.1.1a9

CLI tool for dbt users adopting analytics engineering best practices.

dbt-coves

What is dbt-coves?

dbt-coves is a complimentary CLI tool for dbt that allows users to quickly apply Analytics Engineering best practices.

dbt-coves helps with the generation of scaffold for dbt by analyzing your data warehouse schema in Redshift, Snowflake, or Big Query and creating the necessary configuration files (sql and yml).

⚠️ dbt-coves is in alpha, make sure to test it for your dbt project version and DW before using in production

Here's the tool in action

Supported dbt versions

Version	Status
< 1.0	❌ Not supported
>= 1.0	✅ Tested

Supported adapters

Feature	Snowflake	Redshift	BigQuery
dbt project setup	✅ Tested	🕥 In progress	❌ Not tested
source model (sql) generation	✅ Tested	🕥 In progress	❌ Not tested
model properties (yml) generation	✅ Tested	🕥 In progress	❌ Not tested

Installation

pip install dbt-coves

We recommend using python virtualenvs and create one separate environment per project.

Main Features

For a complete detail of usage, please run:

dbt-coves -h
dbt-coves <command> -h

Project initialization

dbt-coves init

Initializes a new ready-to-use dbt project that includes recommended integrations such as sqlfluff, pre-commit, dbt packages, among others.

Uses a cookiecutter template to make it easier to maintain.

Models generation

dbt-coves generate <resource>

Where <resource> could be sources or properties.

Code generation tool to easily generate models and model properties based on configuration and existing data.

Supports Jinja templates to adjust how the resources are generated.

Arguments

--sources-destination
# Where sources yml files will be generated, i.e. 'models/sources/{{schema}}/{{relation}}.yml'

--models-destination
# Where models sql files will be generated, i.e 'models/staging/{{schema}}/{{relation}}.sql'

--model-props-destination
# Where models yml files will be generated, i.e. 'models/staging/{{schema}}/{{relation}}.yml'

--update-strategy
# Action to perform when a property file already exists: 'update', 'recreate', 'fail', 'ask' (per file)

Metadata

Supports the argument --metadata which allows to specify a csv file containing field types and descriptions to be inserted into the model property files.

dbt-coves generate sources --metadata metadata.csv

Metadata format:

database	schema	relation	column	key	type	description
raw	master	person	name	(empty)	varchar	The full name
raw	master	person	name	groupName	varchar	The group name

Environment setup

Setting up your environment can be done in two different ways:

dbt-coves setup all

Runs a set of checks in your local environment and helps you configure every project component properly: ssh key, git, dbt profiles.yml, vscode extensions, sqlfluff and precommit.

You can also configure individual components:

dbt-coves setup git

Set up Git repository of dbt-coves project

dbt-coves setup dbt

Setup dbt within the project (delegates to dbt init)

dbt-coves setup ssh

Set up SSH Keys for dbt-coves project. Supports the argument --open_ssl_public_key which generates an extra Public Key in Open SSL format, useful for configuring certain providers (i.e. Snowflake authentication)

dbt-coves setup vscode

Setup of predefined settings.json for vscode, settings.json may be added to .dbt_coves/templates/ folder

dbt-coves setup sqlfluff

Set up sqlfluff of dbt-coves project. Supports --templates argument for using your custom .sqlfluff configuration file

dbt-coves setup precommit

Setup of default pre-commit template of dbt-coves project. Supports --templates argument for using your custom .pre-commit-config.yaml configuration file

Extract configuration from Airbyte

dbt-coves extract airbyte

Extracts the configuration from your Airbyte sources, connections and destinations (excluding credentials) and stores it in the specified folder. The main goal of this feature is to keep track of the configuration changes in your git repo, and rollback to a specific version when needed.

Load configuration to Airbyte

dbt-coves load airbyte

Loads the Airbyte configuration generated with dbt-coves extract airbyte on an Airbyte server. Secrets folder needs to be specified separatedly. You can use git-secret to encrypt them and make them part of your git repo.

Settings

Dbt-coves could optionally read settings from .dbt_coves.yml or .dbt_coves/config.yml. A standard settings files could looke like this:

generate:
  sources:
    schemas:
      - RAW
    destination: "models/sources/{{ schema }}/{{ relation }}.sql"
    model_props_strategy: one_file_per_model
    templates_folder: ".dbt_coves/templates"

In this example options for the generate command are provided:

schemas: List of schema names where to look for source tables

destination: Path to generated model, where schema represents the lowercased schema and relation the lowercased table name.

model_props_strategy: Defines how dbt-coves generates model properties files, currently just one_file_per_model is available, creates one yaml file per model.

templates_folder: Folder where source generation jinja templates are located.

Override source generation templates

Customizing generated models and model properties requires placing specific files under the templates_folder folder like these:

source_model.sql

with raw_source as (

    select
        *
    from {% raw %}{{{% endraw %} source('{{ relation.schema.lower() }}', '{{ relation.name.lower() }}') {% raw %}}}{% endraw %}

),

final as (

    select
{%- if adapter_name == 'SnowflakeAdapter' %}
{%- for key, cols in nested.items() %}
  {%- for col in cols %}
        {{ key }}:{{ '"' + col + '"' }}::{{ cols[col]["type"] }} as {{ cols[col]["id"] }}{% if not loop.last or columns %},{% endif %}
  {%- endfor %}
{%- endfor %}
{%- elif adapter_name == 'BigQueryAdapter' %}
{%- for key, cols in nested.items() %}
  {%- for col in cols %}
        cast({{ key }}.{{ col }} as {{ cols[col]["type"].replace("varchar", "string") }}) as {{ cols[col]["id"] }}{% if not loop.last or columns %},{% endif %}
  {%- endfor %}
{%- endfor %}
{%- elif adapter_name == 'RedshiftAdapter' %}
{%- for key, cols in nested.items() %}
  {%- for col in cols %}
        {{ key }}.{{ col }}::{{ cols[col]["type"] }} as {{ cols[col]["id"] }}{% if not loop.last or columns %},{% endif %}
  {%- endfor %}
{%- endfor %}
{%- endif %}
{%- for col in columns %}
        {{ '"' + col['name'] + '"' }} as {{ col['id'] }}{% if not loop.last %},{% endif %}
{%- endfor %}

    from raw_source

)

select * from final

source_model_props.yml

version: 2

sources:
  - name: {{ relation.schema.lower() }}
{%- if source_database %}
    database: {{ source_database }}
{%- endif %}
    schema: {{ relation.schema.lower() }}
    tables:
      - name: {{ relation.name.lower() }}
        identifier: {{ relation.name }}

models:
  - name: {{ model.lower() }}
    columns:
{%- for cols in nested.values() %}
  {%- for col in cols %}
      - name: {{ cols[col]["id"] }}
      {%- if cols[col]["description"] %}
        description: "{{ cols[col]['description'] }}"
      {%- endif %}
  {%- endfor %}
{%- endfor %}
{%- for col in columns %}
      - name: {{ col['id'] }}
      {%- if col['description'] %}
        description: "{{ col['description'] }}"
      {%- endif %}
{%- endfor %}

model_props.yml

version: 2

models:
  - name: {{ model.lower() }}
    columns:
{%- for col in columns %}
      - name: {{ col['id'] }}
      {%- if col['description'] %}
        description: "{{ col['description'] }}"
      {%- endif %}
{%- endfor %}

model.yml

version: 2

models:
  - name: {{ model.lower() }}
    columns:
{%- for cols in nested.values() %}
  {%- for col in cols %}
      - name: {{ cols[col]["id"] }}
      {%- if cols[col]["description"] %}
        description: "{{ cols[col]['description'] }}"
      {%- endif %}
  {%- endfor %}
{%- endfor %}
{%- for col in columns %}
      - name: {{ col.name.lower() }}
{%- endfor %}

Thanks

The project main structure was inspired by dbt-sugar. Special thanks to Bastien Boutonnet for the great work done.

Authors

• Sebastian Sassi @sebasuy -- Datacoves
• Noel Gomez @noel_g -- Datacoves
• Bruno Antonellini -- Datacoves

About

Learn more about Datacoves.