In this article, we’ll dive into the key concepts of DBT, including models, snapshots, seeds, tests, macros, docs, sources, exposures, metrics, and analysis.
Models
In DBT, a model is a SQL query that defines a single table or view in your data warehouse. Models can reference other models, and they can be used to build more complex data transformations. A model is defined using a SQL file that lives in a designated directory in your project. Here’s an example of a model that aggregates order data by customer:
{{ config(materialized='table') }}
SELECT customer_id, sum(order_total) as total_spent
FROM orders
GROUP BY 1;
The {{ config(materialized=’table’) }} statement at the beginning of the file tells DBT that this model should be materialized as a table in your data warehouse.
Snapshots
A snapshot is a type of model that allows you to track changes to a table over time. Snapshots are useful for building historical reporting or auditing systems. To create a snapshot, you define a model that references a table or view in your data warehouse, and you add a {{ snapshot() }} macro to the end of the file. Here’s an example of a snapshot that tracks changes to the orders table:
{{ config(snapshot_strategy='check') }}
SELECT *
FROM orders;
{{ snapshot() }}
The {{ config(snapshot_strategy='check') }} statement at the beginning of the file tells DBT to use the “check” snapshot strategy, which means that DBT will compare the current state of the table to the previous state, and only insert new records or update changed records.
Seeds
Seeds are a way to load data into your data warehouse when you’re starting a new project or when you want to add reference data that doesn’t change often. A seed is defined using a CSV file or a SQL query that selects data from an external data source. Here’s an example of a seed that loads product data from a CSV file:
{{ config(materialized='seed') }}
SELECT *
FROM "{{ source('products_csv') }}";
The {{ config(materialized='seed') }} statement at the beginning of the file tells DBT that this model should be materialized as a seed in your data warehouse.
Tests
Tests are a way to validate the data in your data warehouse and ensure that your DBT code is working as expected. DBT comes with a number of built-in tests, including unique, not_null, and accepted_values. You can also define custom tests using SQL queries. Here’s an example of a test that checks that the orders table has at least 10,000 rows:
SELECT COUNT(*) >= 10000 as orders_are_big_enough
FROM orders;
Tests are defined in separate SQL files that live in a designated directory in your project.
Macros
Macros are reusable snippets of SQL code that you can use in your models and tests. Macros are defined in separate SQL files that live in a designated directory in your project. Here’s an example of a macro that calculates the number of days between two dates:
{% macro days_between(start_date, end_date) %}
SELECT DATEDIFF('{{ end_date }}', '{{ start_date }}') as days_between
{% endmacro %}
Read : Explain on DBT Project configuration in dbt_project.yml
Get more useful articles on dbt
