Course Overview
Introduction to dbt

Materialising As Views and Tables

Lesson #13

In this lesson we will:

  • Go through the process of materialising our dbt models as tables, views and ephemerals.

## Materialisation Options

In the previous lesson, we explain how we can choose whether to materialise objects as views, tables, ephemeral or incremental options. We will now look at how this is controlled in dbt in practice.

Controlling dbt

When we create dbt models, we have a choice whether to "materialise" them as tables or views.

Technically, making this choice is fairly simple:

nano models/pizzas_sold_by_pizza_type.sql

And change this line:

{{ config(materialized='table') }}

To this:

{{ config(materialized='view') }}

When you execute dbt run again, observe that we created a view instead of a table:

Running with dbt=0.21.0
[WARNING]: Configuration paths exist in your dbt_project.yml file which do not apply to any resources.
There are 1 unused configuration paths:
- models.my_new_project.example

Found 1 model, 0 tests, 0 snapshots, 0 analyses, 162 macros, 0 operations, 0 seed files, 0 sources, 0 exposures

14:42:55 | Concurrency: 1 threads (target='dev')
14:42:55 |
14:42:55 | 1 of 1 START view model dev_pizzastore.pizzas_sold_by_pizza_type..... [RUN]
14:42:55 | 1 of 1 OK created view model dev_pizzastore.pizzas_sold_by_pizza_type [CREATE VIEW in 0.09s]
14:42:55 |
14:42:55 | Finished running 1 view model in 0.20s.

Completed successfully


So technically controlling dbt to output tables or views is simple, but choosing the right option requires an understanding of the tradeoffs between speed, storage, performance, cost implications as described in the previous lesson.

Next Lesson:

Materialising As Views and Tables

In this lesson we will use dbt to materialise to tables and views, and the associated incremental and ephemeral options.

0h 15m

Work With The Experts In ClickHouse & Real-Time Analytics

We help businesses build and run advanced Data and Analytics solutions based on ClickHouse.

Join our mailing list for regular insights:

We help businesses build and run real-time data, analytics and AI platforms based on ClickHouse.

© 2023 Ensemble Analytics. All Rights Reserved.