dbtgen

Contents

• Install
• Usage
• Commands
  • model
  • model-properties [*]
  • source [*]
  • package [*]
  • clean

[*] These sub-commands are not working as expected and have been temporarily disabled

Install:

Make sure all requirements are installed and then install dbtgen like so:

python -m pip install -e . 

Usage:

Navigate to your dbt project working directory. From here, create a .dbtgen/ folder and start adding your model templates.

Commands:

The sub-commands / actions available to run with dbtgen are:

• dbtgen model [OPTIONS]
• dbtgen model-properties [OPTIONS]
• dbtgen source [OPTIONS]
• dbtgen package
• dbtgen clean

---

model

  dbtgen model [OPTIONS]

Options:

• -s (--select): The model/schema selected (e.g. -s staging.my_model)
• -o (--overwrite): Overwrite existing models in the target folder of the project
• -r (--run): Create the model files (by default, this is not used)

This command is used to help generate dbt model files (.sql) using a parameterised SQL template and model scoped variables defined in YAML.

The nested folder structure within ./.dbtgen/ represents the same structure used in the dbt models directory (./models by default). When the application runs, it iterates through every sub-directory and checks for two types of files:

Template file (*.sql)

These files contain parameterised SQL representing the contents of dbt models. Variables can be specified using curly braces (e.g. {my_variable}). The variable keys used here must be those defined in the models variable file (see below). The file name of the template file itself can (and should) be parameterised. This will be used to generate the individual file names for the models.

There can be an arbitrary number of templates within a folder.

Models variable file (models.yml)

This defines the model scoped variables. The values for these variables defined here will be used in place of those found in any template file(s).

YAML structure

# models.yml

models:
  <model-name>:
    <key>: <value>

When the application finds a template file it will, by default, check for a models.yml in the same directory. If it can't find one, it will traverse through parent directories and take the first one it encounters. This allows us to use one models variable file to generate multiple different dbt models using many nested templates.

Compile mode (default)

By default, the application will execute in compile mode and print the contents of each model file to terminal. You should check these look as expected before executing in run mode.

Example usage

dbtgen model
dbtgen model -s staging

Run mode

By passing in the command line flag -r or ---run will create the models in the projects models directory.

Example usage

dbtgen model -s staging -r
dbtgen model -s staging -r -o

---

model-properties

  dbtgen model-properties [OPTIONS]

Options:

• -s (--select): The model/schema selected (e.g. -s staging)
• -t (--target): The target environment to use to generate the contents (e.g. -t prod). Note, by default this is the target name of the default dbt profile (e.g. dev007), however for generating accurate recency tests, prod data is recommended
• -uv (--use-views): Whether to use view objects only
• -ut (--use-tables): Whether to use table objects only
• -w (--warn-only): Use severity warn only for all recency tests (by default, this will generate both warn and error tests)

This command is used to scrape data from Snowflake and generate a model_properties file.

The structure of the file generated will include:

• model names
• model recency schema tests
• SYS_ columns and schema tests

Model properties files are generated with file name .dbtgen__*.yml and are git ignored by default. This requires the user to check and rename (or replace any other properties file) if they are happy with the contents.

Example usage

dbtgen model-properties -s staging -uv --target prod

TO DO

Include deep merge of existing complex YAML structures, in order to overlay any pre-existing model properties file with the one generated by the application. This will preserve descriptions in the .yml file that is created.

---

source

  dbtgen source [OPTIONS]

---

package

  dbtgen package

This command takes all dbt models defined in properties files (*.yml) from the dbt project and creates a sources YAML file for each.

These are generated in: ./.export/<project-name>/sources/.

These sources files will form part of another "export" dbt project that can be used as a package and imported into any dependent project(s).

---

clean

  dbtgen clean

This will clean up all temporary files created by dbtgen.

The files which are cleaned by this command are controlled using the params.CLEAN_PATHS list of glob strings.