-
Notifications
You must be signed in to change notification settings - Fork 0
12. DBT
dbt is a transformation workflow that helps you get more work done while producing higher quality results. You can use dbt to modularize and centralize your analytics code, while also providing your data team with guardrails typically found in software engineering workflows. Collaborate on data models, version them, and test and document your queries before safely deploying them to production, with monitoring and visibility.
dbt compiles and runs your analytics code against your data platform, enabling you and your team to collaborate on a single source of truth for metrics, insights, and business definitions. This single source of truth, combined with the ability to define tests for your data, reduces errors when logic changes, and alerts you when issues arise.
dbt follows a modular and structured approach to building data pipelines It consists of three main components:
- Models: SQL-based transformations that define how raw data should be transformed into analysis-ready data
- Tests: Automated tests to ensure data quality and integrity throughout the pipeline
- Documentation: Automatically generated documentation to maintain data lineage and promote collaboration
dbt offers several advantages over traditional SQL editors:
- Reproducibility: dbt ensures consistent and reproducible data transformations by leveraging version control.
- Collaboration: With dbt, multiple team members can work on the same codebase, promoting collaboration and reducing duplication.
- Automation: dbt automates the execution of data transformations, making it easy to build and maintain data pipelines at scale.
dbt leverages Jinja templating language for dynamic SQL transformations Jinja allows for the use of control structures, variables, and macros in SQL code This enables conditional logic, loops, and modular code reusability, enhancing the flexibility and scalability of data transformations.
Example to show all the distinct values for each column in a table, and count how many records for each. i.e. loop through all columns.
Dynamic SQL:
DECLARE @sql VARCHAR(MAX) = '';
DECLARE @tablename VARCHAR(255) = 'your_table_name';
SELECT @sql = @sql + 'SELECT [' + c1.name + '] AS KeyColumn, COUNT(*) AS RecordCount FROM [' + @tablename + '] GROUP BY [' + c1.name + '];'
+ 'SELECT [' + c2.name + '] AS DistinctColumn, COUNT(*) AS RecordCount FROM [' + @tablename + '] GROUP BY [' + c2.name + '];'
+ 'SELECT [' + c3.name + '] AS DistinctColumn, COUNT(*) AS RecordCount FROM [' + @tablename + '] GROUP BY [' + c3.name + '];'
+ 'SELECT [' + c4.name + '] AS DistinctColumn, COUNT(*) AS RecordCount FROM [' + @tablename + '] GROUP BY [' + c4.name + '];'
FROM sys.columns c1, sys.columns c2, sys.columns c3, sys.columns c4
WHERE c1.object_id = OBJECT_ID(@tablename)
AND c2.object_id = OBJECT_ID(@tablename)
AND c3.object_id = OBJECT_ID(@tablename)
AND c4.object_id = OBJECT_ID(@tablename)
AND c1.column_id = 1
AND c2.column_id = 2
AND c3.column_id = 3
AND c4.column_id = 4;
EXEC (@sql);
dbt jinja
{% set tablename = 'your_table_name' %}
{% set columns = ['column1', 'column2', 'column3', 'column4'] %}
{% set sql = '' %}
{% for column in columns %}
{% set query = "SELECT [' || column || '] AS KeyColumn, COUNT(*) AS RecordCount FROM [' || tablename || '] GROUP BY [' || column || '];" %}
{% set sql = sql + query %}
{% endfor %}
{% do run_query(sql) %}
{% set tablename = 'your_table_name' %}
{% set columns_query = adapter.get_columns_in_relation(database=target.database, schema=target.schema, relation=tablename) %}
{% set sql = '' %}
{% for column in columns_query %}
{% set column_name = column.name %}
{% set query = "SELECT [" ~ column_name ~ "] AS KeyColumn, COUNT(*) AS RecordCount FROM [" ~ tablename ~ "] GROUP BY [" ~ column_name ~ "];" %}
{% set sql = sql + query %}
{% endfor %}
{% do run_query(sql) %}
- Streamlined Workflow: dbt simplifies the end-to-end data analytics workflow, from data transformation to analysis, by providing a unified platform.
- Data Confidence: dbt's testing capabilities ensure data quality, reducing the risk of erroneous analysis.
- Documentation: dbt automatically generates documentation, enabling easy exploration and understanding of data models.
- Scalability: dbt's modular structure allows for scalable development and maintenance of data pipelines as your organization's data needs grow.
DBT is the T part of our ELT process (Extract, Load, and Transform). It is fundamental in most analytics projects.
Getting started: https://docs.getdbt.com/quickstarts
Docs: https://docs.getdbt.com/
Learning: https://courses.getdbt.com/