In this lesson we will:
- Go through the process of materialising our dbt models as tables, views and ephemerals.
We recommend using a free ClickHouse Cloud Account when studying this lesson
## 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
Done. PASS=1 WARN=0 ERROR=0 SKIP=0 TOTAL=1
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.