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 or enabled on the Forward engineering screen for complete control over the final YAML output.

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. 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:

• dbt source properties

• dbt model properties

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 available below.

User-maintained

• access

• docs

• external

• freshness (table property. For source-level property see "sources")

• identifier

• latest_version

• loaded_at_field

• meta *

• model_config

• quote

• quoting (table property. For source-level property see "sources")

• sources

• tags

• tests

• versions

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

Auto-generated

• constraints

• data types

Configuring user-maintained properties

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 Model 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 - ( 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.

Working with sources and source headers

The sources property behaves slightly differently than the others in that it allows multiple elements (source headers) to be defined. Sources created under the sources property are then assigned to objects using a dropdown selector. This property is also unique in that it has a default element that cannot be removed (but can be edited).

• To add a source, click the "+" (plus) icon next to the sources property

  • 

• To edit a source, click the pencil icon next to any individual source and proceed to edit the values like in any other property or use the input template. Both the source name as well as its property can be edited here.

  • 

     

  • 

     

• Assign an object to a source by adding the sources property to the selected Database documentation columns and selecting from the available dropdown options

  • 

     

• Delete a source by pressing the trash can icon next to any individual entry.

  • 

     

• Observe that when generating YAML in Forward Engineering, tables are now generated under their designated source headers (when generating Source YAML)

  • 

     

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.

Assigning auto-generated properties

Data type and constraint properties can be obtained directly from the relational definition and do not need to be maintained by hand. To enable data type and constraints in output (Model) YAML, select the corresponding option on the Forward engineer screen.

Working with meta fields (Model Goverance Role)

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 Model 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 dbt YAML

dbt source and model YAML can be generated from the Forward Engineering screen for tables and views.

The Forward Engineering panel is accessed via the Forward Engineering button in the left pane. From the Configuration panel:

Format — select dbt / YAML to switch from SQL DDL generation to YAML output.

Type — select the type of YAML to be generated:

• dbt source — generates YAML for data sources, including the source header and related properties.
• dbt model — generates YAML for models, including full model text and related properties.

Settings — the following options are available:

• Generate data options — when enabled, includes the data type property for columns.
• Generate constraints — when enabled, includes supported dbt constraints.

Once the objects have been selected in the center panel and the options configured, the Generate SQL button produces the YAML output.

The YAML is then ready to be copied into the related dbt project. Make sure to verify the name, database, and schema values if they differ from the dbt settings.

See also

• Enforcing dbt Model Contracts

• dbt meta fields (Model Governance suite)

• Database Documentation

• Editing fields via Excel import/export

• dbt Manifest upload and project properties