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 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 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.
Some dbt properties can be obtained directly from the relational model (i.e., data types and constraints) and therefore, do not need to be maintained by hand in Database documentation. Instead, these can be enabled on the Forward Engineering screen in Generation Options.
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 Model 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 available below.
User-maintained
* for meta tags, please see the related article at the end of this post.
Auto-generated
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 - (<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.
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 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 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.
Access the Forward Engineering screen and select the objects you wish to create as dbt sources.
In the Generation Options, select the type of YAML to be generated
dbt source YAML - includes source header and related properties
dbt model YAML - follows dbt model format and related properties
generate data type - will include data type property for columns
generate constraints - will include supported dbt constraints
If you want dbt to apply quoting to the source objects, set the Quote Options accordingly.
"Double Quotes" - This will set the quoting parameter to true.
"Empty" - Sets quoting parameter to false
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