From Fabric to Fantastic: How dbt Makes Your Lakehouses and Warehouses Shine

FROM FABRIC TO FANTASTIC HOW DBT MAKES LAKEHOUSES AND WAREHOUSES SHINE Sam Debruyn Who am I? Sam Debruyn 📍 Heist-op-den-Berg, BE 💼 Consultant / Data & Cloud Architect 5⃣ years in data 🔟 years in software / architecture / cloud 🫶 dbt, Microsoft, modern data stack dbt-fabric: a quick lookback Lakehouse/Warehouse Where does SQL fit in? 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 oCers 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 The common language of data transformations is not drag-and- drop Data architects ERWIN Wherescape Data engineers Data Factory Matillion Analytics engineers Alteryx Talend BI developers Tableau Power BI Analysts Excel / Sheets Power BI The common language of data transformations is SQL Data architects ERWIN Wherescape SQL Data engineers Data Factory Matillion SQL Analytics engineers Alteryx Talend SQL BI developers Tableau Power BI SQL Analysts Excel / Sheets Power BI SQL Introducing dbt Open-source Python utility for building data transformations Free/OSS version: dbt Core / version with all the bells & whistles included: dbt Cloud The de facto default tool for analytics engineering Analytics engineering Your entire analytics engineering workflow Analytics engineering is the data transformation work that happens between loading data into your warehouse and analyzing it . dbt allows anyone comfortable with SQL to own that workflow. While data scientists and analysts are writing a lot of code, being great software engineers isn’t what they’ve been trained for and it often isn’t their first priority. Similarly, while data engineers are great software engineers, they don’t have training in how they data are actually used and so can’t always partner e=ectively with analysts and data scientists. I believe this gap should be filled in by analytics engineers. Michael Kaminsky, 2019 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 adoption 2020 2019 2021 2022 2018 2017 October 2023: 30000+ weekly active projects dbt adoption Spark popularity is not increasing anymore 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 Compatibility There is more Implement SCD with snapshots Incremental loads Hooks & operations Manage access with grants Track dataset usage in BI & ML with exposures … dbt on Fabric features/roadmap dbt-fabricspark: run on Spark SQL instead of T-SQL Leaked dbt Activity in Fabric Data Factory: is Microsoft building native dbt integration in Fabric? In my fork: - Semi-working support for Python models - Authenticate by providing Workspace ID - Bug squashing – integration tests - Integration with Purview? dbt Fusion? 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 Slides Slides available at https://debruyn.dev/ sb25 Questions? sam@debruyn.dev https://debruyn.dev