SQL Resurgence: Unleashing Data Potential with dbt - session

SQL RESURGENCE Sam Debruyn Data Makers Fest September 2024 UNLEASHING DATA POTENTIAL WITH DBT Di#erent ways to transform data Programming languages Python and Scala. High learning curves and often creates a boundary between business users and specialized engineers. Very powerful and easy to maintain. Declarative languages SQL, SAS, and the likes. Code is easy to write and understand but offers limited flexibility and can be hard to maintain (adopting software eng. best practices). Low-code / UI-based Easy to adopt, use, and achieve results. Very high vendor lock-in and limited flexibility and modularity. A quick survey done at local meetups How would you rate these statements? 0 = Strongly disagree / 5 = Strongly agree Programming languages 2023 source: Stack Overflow worked with / wants to work with The common language of data transformations is not drag-and- drop Data architects ERWIN Wherescape Data engineers Informatica Matillion Analytics engineers Alteryx Talend BI developers Tableau Qlik Analysts Excel / Sheets Power BI The common language of data transformations is SQL Data architects ERWIN Wherescape SQL Data engineers Informatica Matillion SQL Analytics engineers Alteryx Talend SQL BI developers Tableau Qlik SQL Analysts Excel / Sheets Power BI SQL Introducing dbt = the T in ELT dbt adoption past 6 years 2020 2019 2021 2022 2018 2017 October 2023: 30000+ weekly active projects 3 things to know No compute dbt requires a data warehouse to function, it only sends SQL queries SQL with Jinja dbt is built for SQL, in some cases you can also use Python Free/self-hosted or cloud dbt Core is free but requires "plumbing" (e.g. an orchestrator) dbt Cloud is paid, but will be cheaper than building everything around it manually 📦 dbt Core Open source & free to use Complete dbt experience with all capabilities with regards to transformations, database engine compatibility ☁ dbt Cloud Free tier: 1 user Teams: starts at 100 USD/user/month Everything in dbt Core for the most common database engines Browser-based IDE CI/CD & job scheduling Hosted data docs Easy setup & configuration dbt Mesh dbt Explorer … Compatibility Getting started dbt Cloud Getting started dbt Core Modular development Write transformations in separate version-controlled files SQL on steroids with Jinja: control logic, loops Customize and parametrize with variables Reusable code blocks with macros Easy to follow DRY principles Manage data sources and monitor data freshness Sources Sources Dynamic schema selection Start tracking lineage from the source Data lineage Understand the flow of data Impact of modifying a transformation How a dimension/fact is constructed Data lineage Spot and detect bad data model design Data tests & unit tests Automated testing for your code, as well as for your data Tests can be integrated in other tooling to get a good view on your data quality Simple YAML- or SQL-based syntax to define tests Documentation and tests dbt docs Clear convention- based data documentation Good step-up to a data catalog dbt packages: don’t reinvent the wheel Similar to libraries in software development Benefit from global knowledge by using pre-built common data transformations and data modelling techniques Share publicly or privately within your organization Can contain models (transformations), macros, tests, … Date dimension in 1 line There is more Implement SCD with snapshots Incremental loads Hooks & operations Run Python models Manage access with grants Track dataset usage in BI & ML with exposures Data contracts … Accomplish great things Version controlled and reproducible ↗ Collaboration within the team & other teams Built-in docs & lineage ↗ Know and understand your data Test code & data ↗ Deploy & run with confidence Modular & easy to use ↗ Easy to extend and maintain Your next steps Questions? sam@debruyn.dev https://debruyn.dev