dbt_coves 0.21.1a20

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 version. Don’t use on your prod models unless you have tested it before.

Here’s the tool in action

Supported dbt versions

Version	Status
0.17.0	❌ Not supported
0.18.x	✅ Tested
0.19.x	✅ Tested
0.20.x	✅ Tested
0.21.x	🕥 In progress

Supported adapters

Feature	Snowflake	Redshift	BigQuery	Postgres
profile.yml generation	✅ Tested	🕥 In progress	❌ Not tested	❌ Not tested
sources generation	✅ Tested	🕥 In progress	❌ Not tested	❌ Not tested

Installation

pip install dbt-coves

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

⚠️ if you have dbt < 0.18.0 installed, dbt-coves will automatically upgrade dbt to the latest version

Main Features

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.

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.

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

Quality Assurance

dbt-coves check

Runs a set of checks in your local environment to ensure high code quality.

Checks can be extended by implementing pre-commit hooks.

Environment setup

dbt-coves setup

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

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 + '"' }}::varchar as {{ col.lower().replace(" ","_").replace(":","_").replace("(","_").replace(")","_") }}{% 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.lower() }} as string) as {{ col.lower().replace(" ","_").replace(":","_").replace("(","_").replace(")","_") }}{% 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.lower() }}::varchar as {{ col.lower().replace(" ","_").replace(":","_").replace("(","_").replace(")","_") }}{% if not loop.last or columns %},{% endif %}
  {%- endfor %}
{%- endfor %}
{%- endif %}
{%- for col in columns %}
        {{ '"' + col.name.lower() + '"' }} as {{ col.name.lower() }}{% 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: {{ col.lower().replace(" ","_").replace(":","_").replace("(","_").replace(")","_") }}
  {%- endfor %}
{%- endfor %}
{%- for col in columns %}
      - name: {{ col.name.lower() }}
{%- endfor %}

CLI Detailed Reference

CLI tool for dbt users applying analytics engineering best practices.

usage: dbt_coves [-h] [-v] {init,generate,check,fix,setup,extract,load} ...

Named Arguments

-v, –version

show program’s version number and exit

dbt-coves commands

task

Possible choices: init, generate, check, fix, setup, extract, load

Sub-commands:

init

Initializes a new dbt project using predefined conventions.

dbt_coves init [-h] [--log-level LOG_LEVEL] [-vv] [--config-path CONFIG_PATH] [--project-dir PROJECT_DIR] [--profiles-dir PROFILES_DIR] [--profile PROFILE] [-t TARGET] [--vars VARS] [--template TEMPLATE] [--current-dir]

Named Arguments

–log-level

overrides default log level

Default: “”

-vv, –verbose

When provided the length of the tracebacks will not be truncated.

Default: False

–config-path

Full path to .dbt_coves.yml file if not using default. Default is current working directory.

–project-dir

Which directory to look in for the dbt_project.yml file. Default is the current working directory and its parents.

–profiles-dir

Which directory to look in for the profiles.yml file.

Default: “~/.dbt”

–profile

Which profile to load. Overrides setting in dbt_project.yml.

-t, –target

Which target to load for the given profile

–vars

Supply variables to your dbt_project.yml file. This argument should be a YAML string, eg. ‘{my_variable: my_value}’

Default: “{}”

–template

Cookiecutter template github url, i.e. ‘https://github.com/datacoves/cookiecutter-dbt-coves.git’

–current-dir

Generate the dbt project in the current directory.

Default: False

generate

Generates sources and models with defaults.

dbt_coves generate [-h] [--log-level LOG_LEVEL] [-vv] [--config-path CONFIG_PATH] [--project-dir PROJECT_DIR] [--profiles-dir PROFILES_DIR] [--profile PROFILE] [-t TARGET] [--vars VARS] {sources} ...

Named Arguments

–log-level

overrides default log level

Default: “”

-vv, –verbose

When provided the length of the tracebacks will not be truncated.

Default: False

–config-path

Full path to .dbt_coves.yml file if not using default. Default is current working directory.

–project-dir

Which directory to look in for the dbt_project.yml file. Default is the current working directory and its parents.

–profiles-dir

Which directory to look in for the profiles.yml file.

Default: “~/.dbt”

–profile

Which profile to load. Overrides setting in dbt_project.yml.

-t, –target

Which target to load for the given profile

–vars

Supply variables to your dbt_project.yml file. This argument should be a YAML string, eg. ‘{my_variable: my_value}’

Default: “{}”

dbt-coves generate commands

task

Possible choices: sources

Sub-commands:

sources

Generate source dbt models by inspecting the database schemas and relations.

dbt_coves generate sources [-h] [--log-level LOG_LEVEL] [-vv] [--config-path CONFIG_PATH] [--project-dir PROJECT_DIR] [--profiles-dir PROFILES_DIR] [--profile PROFILE] [-t TARGET] [--vars VARS] [--database DATABASE] [--schemas SCHEMAS]
                           [--relations RELATIONS] [--destination DESTINATION] [--model_props_strategy MODEL_PROPS_STRATEGY] [--templates_folder TEMPLATES_FOLDER] [--metadata METADATA]

Named Arguments

–log-level

overrides default log level

Default: “”

-vv, –verbose

When provided the length of the tracebacks will not be truncated.

Default: False

–config-path

Full path to .dbt_coves.yml file if not using default. Default is current working directory.

–project-dir

Which directory to look in for the dbt_project.yml file. Default is the current working directory and its parents.

–profiles-dir

Which directory to look in for the profiles.yml file.

Default: “~/.dbt”

–profile

Which profile to load. Overrides setting in dbt_project.yml.

-t, –target

Which target to load for the given profile

–vars

Supply variables to your dbt_project.yml file. This argument should be a YAML string, eg. ‘{my_variable: my_value}’

Default: “{}”

–database

Database where source relations live, if different than target

–schemas

Comma separated list of schemas where raw data resides, i.e. ‘RAW_SALESFORCE,RAW_HUBSPOT’

–relations

Comma separated list of relations where raw data resides, i.e. ‘RAW_HUBSPOT_PRODUCTS,RAW_SALESFORCE_USERS’

–destination

Where models sql files will be generated, i.e. ‘models/{schema_name}/{relation_name}.sql’

–model_props_strategy

Strategy for model properties files generation, i.e. ‘one_file_per_model’

–templates_folder

Folder with jinja templates that override default sources generation templates, i.e. ‘templates’

–metadata

Path to csv file containing metadata, i.e. ‘metadata.csv’

check

Runs pre-commit hooks and linters.

dbt_coves check [-h] [--log-level LOG_LEVEL] [-vv] [--config-path CONFIG_PATH] [--project-dir PROJECT_DIR] [--profiles-dir PROFILES_DIR] [--profile PROFILE] [-t TARGET] [--vars VARS] [--no-fix]

Named Arguments

–log-level

overrides default log level

Default: “”

-vv, –verbose

When provided the length of the tracebacks will not be truncated.

Default: False

–config-path

Full path to .dbt_coves.yml file if not using default. Default is current working directory.

–project-dir

Which directory to look in for the dbt_project.yml file. Default is the current working directory and its parents.

–profiles-dir

Which directory to look in for the profiles.yml file.

Default: “~/.dbt”

–profile

Which profile to load. Overrides setting in dbt_project.yml.

-t, –target

Which target to load for the given profile

–vars

Supply variables to your dbt_project.yml file. This argument should be a YAML string, eg. ‘{my_variable: my_value}’

Default: “{}”

–no-fix

Do not suggest auto-fixing linting errors. Useful when running this command on CI jobs.

Default: False

fix

Runs linter fixes.

dbt_coves fix [-h] [--log-level LOG_LEVEL] [-vv] [--config-path CONFIG_PATH] [--project-dir PROJECT_DIR] [--profiles-dir PROFILES_DIR] [--profile PROFILE] [-t TARGET] [--vars VARS]

Named Arguments

–log-level

overrides default log level

Default: “”

-vv, –verbose

When provided the length of the tracebacks will not be truncated.

Default: False

–config-path

Full path to .dbt_coves.yml file if not using default. Default is current working directory.

–project-dir

Which directory to look in for the dbt_project.yml file. Default is the current working directory and its parents.

–profiles-dir

Which directory to look in for the profiles.yml file.

Default: “~/.dbt”

–profile

Which profile to load. Overrides setting in dbt_project.yml.

-t, –target

Which target to load for the given profile

–vars

Supply variables to your dbt_project.yml file. This argument should be a YAML string, eg. ‘{my_variable: my_value}’

Default: “{}”

setup

Sets up SSH keys, git repo, and db connections.

dbt_coves setup [-h] [--log-level LOG_LEVEL] [-vv] [--config-path CONFIG_PATH] [--project-dir PROJECT_DIR] [--profiles-dir PROFILES_DIR] [--profile PROFILE] [-t TARGET] [--vars VARS]

Named Arguments

–log-level

overrides default log level

Default: “”

-vv, –verbose

When provided the length of the tracebacks will not be truncated.

Default: False

–config-path

Full path to .dbt_coves.yml file if not using default. Default is current working directory.

–project-dir

Which directory to look in for the dbt_project.yml file. Default is the current working directory and its parents.

–profiles-dir

Which directory to look in for the profiles.yml file.

Default: “~/.dbt”

–profile

Which profile to load. Overrides setting in dbt_project.yml.

-t, –target

Which target to load for the given profile

–vars

Supply variables to your dbt_project.yml file. This argument should be a YAML string, eg. ‘{my_variable: my_value}’

Default: “{}”

extract

Extracts data from different systems.

dbt_coves extract [-h] [--log-level LOG_LEVEL] [-vv] [--config-path CONFIG_PATH] [--project-dir PROJECT_DIR] [--profiles-dir PROFILES_DIR] [--profile PROFILE] [-t TARGET] [--vars VARS] {airbyte} ...

Named Arguments

–log-level

overrides default log level

Default: “”

-vv, –verbose

When provided the length of the tracebacks will not be truncated.

Default: False

–config-path

Full path to .dbt_coves.yml file if not using default. Default is current working directory.

–project-dir

Which directory to look in for the dbt_project.yml file. Default is the current working directory and its parents.

–profiles-dir

Which directory to look in for the profiles.yml file.

Default: “~/.dbt”

–profile

Which profile to load. Overrides setting in dbt_project.yml.

-t, –target

Which target to load for the given profile

–vars

Supply variables to your dbt_project.yml file. This argument should be a YAML string, eg. ‘{my_variable: my_value}’

Default: “{}”

dbt-coves extract commands

task

Possible choices: airbyte

Sub-commands:

airbyte

Extracts airbyte sources, connections and destinations and stores them as json files

dbt_coves extract airbyte [-h] [--log-level LOG_LEVEL] [-vv] [--config-path CONFIG_PATH] [--project-dir PROJECT_DIR] [--profiles-dir PROFILES_DIR] [--profile PROFILE] [-t TARGET] [--vars VARS] [--path PATH] [--host HOST] [--port PORT]
                          [--dbt_list_args DBT_LIST_ARGS]

Named Arguments

–log-level

overrides default log level

Default: “”

-vv, –verbose

When provided the length of the tracebacks will not be truncated.

Default: False

–config-path

Full path to .dbt_coves.yml file if not using default. Default is current working directory.

–project-dir

Which directory to look in for the dbt_project.yml file. Default is the current working directory and its parents.

–profiles-dir

Which directory to look in for the profiles.yml file.

Default: “~/.dbt”

–profile

Which profile to load. Overrides setting in dbt_project.yml.

-t, –target

Which target to load for the given profile

–vars

Supply variables to your dbt_project.yml file. This argument should be a YAML string, eg. ‘{my_variable: my_value}’

Default: “{}”

–path

Where json files will be generated, i.e. ‘airbyte’

–host

Airbyte’s API hostname, i.e. ‘http://airbyte-server’

–port

Airbyte’s API port, i.e. ‘8001’

–dbt_list_args

Extra dbt arguments, selectors or modifiers

load

Loads data from different systems.

dbt_coves load [-h] [--log-level LOG_LEVEL] [-vv] [--config-path CONFIG_PATH] [--project-dir PROJECT_DIR] [--profiles-dir PROFILES_DIR] [--profile PROFILE] [-t TARGET] [--vars VARS] {airbyte} ...

Named Arguments

–log-level

overrides default log level

Default: “”

-vv, –verbose

When provided the length of the tracebacks will not be truncated.

Default: False

–config-path

Full path to .dbt_coves.yml file if not using default. Default is current working directory.

–project-dir

Which directory to look in for the dbt_project.yml file. Default is the current working directory and its parents.

–profiles-dir

Which directory to look in for the profiles.yml file.

Default: “~/.dbt”

–profile

Which profile to load. Overrides setting in dbt_project.yml.

-t, –target

Which target to load for the given profile

–vars

Supply variables to your dbt_project.yml file. This argument should be a YAML string, eg. ‘{my_variable: my_value}’

Default: “{}”

dbt-coves load commands

task

Possible choices: airbyte

Sub-commands:

airbyte

Extracts airbyte sources, connections and destinations and stores them as json files

dbt_coves load airbyte [-h] [--log-level LOG_LEVEL] [-vv] [--config-path CONFIG_PATH] [--project-dir PROJECT_DIR] [--profiles-dir PROFILES_DIR] [--profile PROFILE] [-t TARGET] [--vars VARS] [--path PATH] [--host HOST] [--port PORT]
                       [--secrets SECRETS]

Named Arguments

–log-level

overrides default log level

Default: “”

-vv, –verbose

When provided the length of the tracebacks will not be truncated.

Default: False

–config-path

Full path to .dbt_coves.yml file if not using default. Default is current working directory.

–project-dir

Which directory to look in for the dbt_project.yml file. Default is the current working directory and its parents.

–profiles-dir

Which directory to look in for the profiles.yml file.

Default: “~/.dbt”

–profile

Which profile to load. Overrides setting in dbt_project.yml.

-t, –target

Which target to load for the given profile

–vars

Supply variables to your dbt_project.yml file. This argument should be a YAML string, eg. ‘{my_variable: my_value}’

Default: “{}”

–path

Where json files will be loaded from, i.e. ‘/var/data’

–host

Airbyte’s API hostname, i.e. ‘http://airbyte-server’

–port

Airbyte’s API port, i.e. ‘8001’

–secrets

Secret files location for Airbyte configuration, i.e. ‘./secrets’

Select one of the available sub-commands with –help to find out more about them.

Thanks

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

Authors

• Sebastian Sassi @sebasuy – Convexa

• Noel Gomez @noel_g – Ninecoves

About

Learn more about Datacoves.