Create a dbt model

Create a dbt model

Define and configure a dbt model in Data Catalog or Code Editor mode.


A dbt model is composed of two files:

  • a SQL file containing the SELECT statement that queries your database, and
  • the YAML file that holds the model configuration.

You can create a dbt model in Y42 via the Data Catalog or the Code Editor. Using the Data Catalog is convenient for models with many properties, as the graphical user interface streamlines the process of configuring the asset. On the other hand, the Code Editor offers full flexibility to customize models through granular control of YAML-defined configuration files.

Anything you define in the Code Editor is rendered in the Data Catalog and the Data Lineage views and vice versa. These modes simply offer different representations of your Y42 project, allowing you to interact with your data in a way that suits your workflow.

Create a dbt model in Data Catalog mode

When you create a dbt model in Data Catalog mode, Y42 automatically generates a SQL and YAML. You can view these files via the Code Editor.

Add a dbt model in the Data Catalog mode.

Create a dbt model in Code Editor mode

By default, models are defined inside the /models folder. Although you can specify other model paths in dbt_project.yml, we recommend keeping the default project settings.

Add a .sql file

Navigate to the Models folder. Click the "new file" icon or right-click on the folder to create a new model file. Assign a name to the model and use the .sql file extension.

Create new dbt model.Create new dbt model.

Configure the model

A model consists of a .sql file and its corresponding configurations, which include properties such as metadata, materialization lgic, tests etc. While you can define model-specific configurations using a configuration block in the .sql file, we recommend placing them in a .yml file.

Note that configurations prioritized by order of specificity and applied hierarchically and are. Generally, configurations defined in the .sql file will take precedence over those defined in .yml files, which in turn overrides configurations defined in the project file.

Configure dbt model.Configure dbt model.

Preview the model

You can verify the model's logic using the preview function, located at the bottom-right corner of the browser window.

However, note that some queries that work in the preview but may fail when you run the model job. For instance, Y42 adds the snippet create or replace view <view_name> (<your_query>) when running the job. This difference might cause the compiled query and the query run against your data warehouse to differ.

Preview the model.Preview the model.

Commit and build the model

Commit the model using the Git interface, which can be accessed via the top navigation bar. Then, build the model using the Build drawer at the bottom of the browswer window. You can either trigger a build manually via the Build history tab, or use the y42 build command.

After the build has completed, you can preview the materialized table or view using the Data tab in the Build drawer.

Build history tab in the Build drawer.Build history tab in the Build drawer.