Skip to main content

CLI tool for dbt users adopting analytics engineering best practices.

Project description

dbt-coves

Maintenance PyPI version fury.io Code Style Checked with mypy Imports: isort Imports: python Build pre-commit.ci status codecov Maintainability Downloads

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

image

Supported dbt versions

Version Status
<= 0.17.0 ❌ Not supported
0.18.x - 0.21x ✅ Tested
1.x ✅ Tested

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 + '"' }}::{{ 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.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: {{ 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

About

Learn more about Datacoves.

CLI Reference

For a complete detail of usage, please run:

dbt-coves -h

Project details


Release history Release notifications | RSS feed

Download files

Download the file for your platform. If you're not sure which to choose, learn more about installing packages.

Source Distribution

dbt_coves-1.1.1a2.tar.gz (44.5 kB view hashes)

Uploaded Source

Built Distribution

dbt_coves-1.1.1a2-py3-none-any.whl (56.2 kB view hashes)

Uploaded Python 3

Supported by

AWS AWS Cloud computing and Security Sponsor Datadog Datadog Monitoring Fastly Fastly CDN Google Google Download Analytics Microsoft Microsoft PSF Sponsor Pingdom Pingdom Monitoring Sentry Sentry Error logging StatusPage StatusPage Status page