Sooner or later, the analyst is faced with the problem of organizing the data. There are more and more of them, the structure is no longer transparent, and the same SQL queries have to be rewritten several times. To solve this problem, you can use dbt - a tool that opens a new approach to data transformation and modeling. Below the cut is a translation of David Crewitt's excellent article on what dbt is and how it helps analysts to make their work easier.
dbt (data build tool) is an open source framework for executing, testing and documenting SQL queries that brings an element of software engineering to the data analysis process.
This is a great piece of the lazy toolbox to help you never repeat yourself when analyzing your data.
Replacing a Saved Query
Every SQL query deserves a good home. In dbt, SQL queries are structured and organized into project folders, so all team members always know where to find them:
Every time you need to run queries, you use the dbt run command.
This command takes a collection of SQL models in the dbt project and updates them in the datastore.
Simple but effective.
This means that you no longer need to save fragments of SQL queries to your desktop or elsewhere like a treasure chest.
— . - , .
dbt — , .
ref(‘ ‘)
dbt * * SQL-, {{ ref(‘model name’) }}.
:
, . dbt , , :
/admin
/base
/join /
/math (, ) ( . .)
/visualization + , .
, .
« Google Analytics?»
base -> google-analytics.
« ?»
Math -> cohort-analysis.
SQL-. dbt — Monster Queries™, .
SQL
dbt SQL- : + JINJA.
SQL- .
, CASE 15 :
CASE WHEN x = y THEN z
WHEN 2x = 2y THEN 2z
--… .
— , :
dbt SQL .
dbt dbt_utils, .
, CIFL :
JINJA
, : FOR, IF-THEN . .
dbt SQL-, JINJA.
, SQL- , .
1 №2 №9.
2 №4 №11.
, « » .
dbt :
dbt , JINJA:
, , , , . «» .
dbt + Git
dbt + Git . «Pull Requests» «Issues». SQL- Slack/Notion/email.
:
SQL- , . — .
, . , .
SQL , .
SQL-
« ?» – .
dbt .
Fishtown Analytics (, dbt) dbt Cloud, hosted dbt .
dbt Cloud , .
, — $50 .
«»
« ?»
« ?»
— . :
, .
, , , , 100% , , .
, — , , , .
, : , Google BigQuery Google Data Studio.
dbt :
`dbt docs` , SQL .
dbt + .yml .
BigQuery:
Data Studio:
(Snowflake . .) (Looker . .) . , .
?
, , . , , .
dbt .
, - , dbt . JOIN , dbt .
, SQL- , dbt .
— BigQuery, Snowflake PostgreSQL — , SQL. BigQuery Snowflake Redshift BigQuery, SQL, .
dbt ! . dbt . dbt , ( , , MS SQL).
CIFL , , , .
dbt
If you are ready to tackle dbt implementation in your team, the following can help:
Free course " Getting Started with BigQuery SQL ", which includes an introduction to data modeling with dbt.
The recently launched Build Your Data Agency course for a deeper dive into dbt-based data analysis.