YAML generation for dbt

Generate dbt sources in YAML format

S
Written by Serge Gershkovich
Updated over a week ago

YAML generation is available in all Enterprise plans.

Generating SqlDBM project objects in dbt-compatible YAML format (i.e., sources and models) is a time-saving feature that lets users jump straight from relational to transformational modeling.

What YAML is generated

SqlDBM users can forward engineer dbt-compatible source or model YAML for tables and views. By default, YAML output will include the object name, columns, and descriptions. Additional properties and meta fields can be maintained in Database Documentation, and, thereby, included in the output as well.

Default YAML output includes:

  • Source header (for sources) - this includes a separate source header per schema and default source properties such as quoting, database, and schema. Note,

  • Tables - tables (or views) to be used as sources

  • Columns - list of all columns for each table

  • Description - any descriptions added to the project

Additional dbt fields and properties:

Using fields, users can maintain dbt source and model properties such as tests, freshness, and docs, and output them as dbt-compatible YAML. There are two kinds of dbt fields that SqlDBM allows users to maintain:

  • Standard dbt properties - dbt source and model properties (e.g., tests, freshness, docs) are available as pre-configured fields in the database documentation available to users on all Enterprise plans. Where applicable, these fields will include pre-defined and configurable YAML snippets to allow users to easily reuse and set parameters without having to worry about syntax and formatting.

  • Meta fields - users can include many kinds of metadata (e.g., owner, medallion status, department) under the dbt meta tag. For additional information on maintaining fields in the Database Documentation, please see the related article at the end of this page.

For the complete reference on dbt source and model properties, refer to the official dbt documentation:

While dbt meta-enabled fields require a Data Governance license, standard properties (e.g., tests, freshness, docs) will be available to all Enterprise plans. Both dbt meta and standard fields will be found on the Database documentation screen.

Working with dbt properties and meta fields

This section covers the configuration and assignment of dbt properties to project objects (i.e., models, sources, and columns).

Configuring dbt properties and their settings and assigning them to project objects is done through the Database Documentation screen.

Working with properties

To facilitate the assignment of dbt properties—which frequently follow a predictable pattern (e.g., freshness)—SqlDBM offers configurable YAML templates to be used as defaults when assigning them.

What properties are currently supported

The list of properties with links to dbt documentation is the following:

* for meta tags, please see the related article at the end of this post.

What properties are coming soon

The list of properties with links to dbt documentation is the following:

Configuring property fields

Dbt properties can be configured to allow users to set their defaults or optionally ignore them in YAML generation. Property defaults serve as on-click input help and will not be automatically applied to all objects (unlike default values in Data Governance fields)

To edit a dbt property, go to Database documentation and click on the pencil icon next to any property name.

In this section, you can maintain the following settings:

  • Include in YAML - (selected by default) determines if this property will be included or ignored in YAML generation.

  • Input template - (some properties contain default text) determines the on-click input help that will be generated when applying this dbt property on a given object or column.

    • Where default templates exist, you can re-generate them in case they have been deleted using the input help.

  • Applicable to - (<Objects | Columns> locked where not editable) allows users to restrict the property for entry on objects or columns. Properties that only support a single selection (e.g., freshness) will have this setting disabled.

Assigning dbt properties on project objects

Dbt properties can be set on project objects directly on the Database documentation screen or through Excel upload.

Assigning dbt properties in Database documentation

To assign a dbt property on an object or column, select that property from the right-hand properties panel to bring it onto the screen.

Once the dbt property has been added as a column in Database documentation, enter the desired value according to the property input type (e.g., text, dropdown, multi-select). Values can be entered by hand in free-text fields or populated and edited using the input help button in the lower right corner of each field.

Assigning dbt properties via Excel

Assigning dbt properties can be done (in bulk) via Excel upload. Please see the related article (below) on the complete details of this functionality.

Working with meta fields (Data Goverance license)

The dbt meta tag allows users to include any metadata they want (e.g., owner, medallion status, department). To do so, mark the "Include as dbt meta" checkbox on any Data Governance field. When values are entered, corresponding tables or columns will be included in the meta field in the corresponding YAML.

For additional information on maintaining fields in the Database Documentation, please see the related article at the end of this page.

How to generate source YAML

Source YAML can be generated from the Forward Engineering screen for tables and views.

  1. Access the Forward Engineering screen and select the objects you wish to create as dbt sources.

  2. In the Generation Options, select the type of YAML to be generated

    1. dbt source YAML - includes source header and related properties

    2. dbt model YAML - follows dbt model format and related properties

  3. If you want dbt to apply quoting to the source objects, set the Quote Options accordingly.

    1. "Double Quotes" - This will set the quoting parameter to true.

    2. "Empty" - Sets quoting parameter to false

  4. Once the objects have been selected and quoting options set, press the "Generate YAML" button.

The YAML is now ready to be copied to the related dbt project.

  • Copy the generated script into the desired dbt source file.

  • Make sure to edit the Name, database, and schema values if they differ from your dbt settings.


See also

Did this answer your question?