Skip to main content

Available materializations

Views and tables and incremental models, oh my! In this section weโ€™ll start getting our hands dirty digging into the three basic materializations that ship with dbt. They are considerably less scary and more helpful than lions, tigers, or bears โ€” although perhaps not as cute (can data be cute? We at dbt Labs think so). Weโ€™re going to define, implement, and explore:

  • ๐Ÿ” views
  • โš’๏ธย tables
  • ๐Ÿ“šย incremental model
info

๐Ÿ‘ป There is a fourth default materialization available in dbt called ephemeral materialization. It is less broadly applicable than the other three, and better deployed for specific use cases that require weighing some tradeoffs. We chose to leave it out of this guide and focus on the three materializations that will power 99% of your modeling needs.

Views and Tables are the two basic categories of object that we can create across warehouses. They exist natively as types of objects in the warehouse, as you can see from this screenshot of Snowflake (depending on your warehouse the interface will look a little different). Incremental models and other materializations types are a little bit different. They tell dbt to construct tables in a special way.

Tables and views in the browser on Snowflake.

Viewsโ€‹

  • โœ…ย The default materialization in dbt. A starting project has no configurations defined for materializations, which means everything is by default built as a view.
  • ๐Ÿ‘ฉโ€๐Ÿ’ปย Store only the SQL logic of the transformation in the warehouse, not the data. As such, they make a great default. They build almost instantly and cost almost nothing to build.
  • โฑ๏ธย Always reflect the most up-to-date version of the input data, as theyโ€™re run freshly every time theyโ€™re queried.
  • ๐Ÿ‘Žย Have to be processed every time theyโ€™re queried, so slower to return results than a table of the same data. That also means they can cost more over time, especially if they contain intensive transformations and are queried often.

Tablesโ€‹

  • ๐Ÿ—๏ธย Tables store the data itself as opposed to views which store the query logic. This means we can pack all of the transformation compute into a single run. A view is storing a query in the warehouse. Even to preview that data we have to query it. A table is storing the literal rows and columns on disk.
  • ๐ŸŽ๏ธย Querying lets us access that transformed data directly, so we get better performance. Tables feel faster and more responsive compared to views of the same logic.
  • ๐Ÿ’ธย Improves compute costs. Compute is significantly more expensive than storage. So while tables use much more storage, itโ€™s generally an economical tradeoff, as you only pay for the transformation compute when you build a table during a job, rather than every time you query it.
  • ๐Ÿ”ย Ideal for models that get queried regularly, due to the combination of these qualities.
  • ๐Ÿ‘Žย Limited to the source data that was available when we did our most recent run. Weโ€™re โ€˜freezingโ€™ the transformation logic into a table. So if we run a model as a table every hour, at 10:59a we still only have data up to 10a, because that was what was available in our source data when we ran the table last at 10a. Only at the next run will the newer data be included in our rebuild.

Incremental modelsโ€‹

  • ๐Ÿงฑย Incremental models build a table in pieces over time, only adding and updating new or changed records.
  • ๐ŸŽ๏ธย  Builds more quickly than a regular table of the same logic.
  • ๐Ÿขย Initial runs are slow. Typically we use incremental models on very large datasets, so building the initial table on the full dataset is time consuming and equivalent to the table materialization.
  • ๐Ÿ‘Žย Add complexity. Incremental models require deeper consideration of layering and timing.
  • ๐Ÿ‘Žย Can drift from source data over time. As weโ€™re not processing all of the source data when we run an incremental model, extra effort is required to capture changes to historical data.

Comparing the materialization typesโ€‹

viewtableincremental
๐Ÿ› ๏ธโŒ›ย build time๐Ÿ’šย  fastest โ€” only stores logicโค๏ธย  slowest โ€” linear to size of data๐Ÿ’›ย  medium โ€” builds flexible portion
๐Ÿ› ๏ธ๐Ÿ’ธย build costs๐Ÿ’šย  lowest โ€” no data processedโค๏ธย  highest โ€” all data processed๐Ÿ’›ย  medium โ€” some data processed
๐Ÿ“Š๐Ÿ’ธย query costsโค๏ธย  higher โ€” reprocess every query๐Ÿ’šย  lower โ€” data in warehouse๐Ÿ’šย  lower โ€” data in warehouse
๐Ÿ…๐ŸŒฑย freshness๐Ÿ’šย ย best โ€” up-to-the-minute of query๐Ÿ’›ย  moderate โ€” up to most recent build๐Ÿ’›ย  moderate โ€” up to most recent build
๐Ÿง ๐Ÿค” complexity๐Ÿ’š simple - maps to warehouse object๐Ÿ’š simple - map to warehouse concept๐Ÿ’› moderate - adds logical complexity
info

๐Ÿ”‘ Time is money. Notice in the above chart that the time and costs rows contain the same results. This is to highlight that when weโ€™re talking about time in warehouses, weโ€™re talking about compute time, which is the primary driver of costs.

0