From Legacy to Lakehouse - DBT Meetup

Transcript

1. The birth of dbt model in PayFit 2024-06-20
2. WHO IS PAYFIT? PayFit is a cloud-based payroll software designed to simplify and automate payroll processes. It offers a range of features including: • Automated Payroll functions • Real Time Reporting • HR & Admin Tools
3. INTERNAL Lorena Analytics Engineer - Working with data for almost a decade - 2 years as Analytics Engineer - Brazilian living in Madrid - Hobbies: workout & youtube
4. Data @ Payfit 648 3 Users Countries 550+ 3Tb 113 tests Dashboards Data stored 1537 scenarios
5. Data Stack Data Sources Events, BackOffice, Zuora, Salesforce, Gspread Ingest Custom Orchestrate & Data Governance Transformation Data Storage Data Uses
6. From Legacy to Lakehouse
7. OUR LEGACY SITUATION
8. BEFORE & PAIN POINTS 💥 NO SOURCE OF TRUTH UNRELIABLE NO TESTS & NO DOCUMENTATION • Business and DA working together on Looker • Each of them following different business rules. • With many issues, the data was not reliable within Payfit. • There was no test, no documentation, no best practices applied.
9. AFTER & BENEFITS One source of truth Lakehouse is our source of truth ✅ Reliability As all of our metrics are centralized, data is more reliable 🫡 100% tests and documentation All columns are documented and all models are tested 📝 Organized, clean and best practice We use linters to keep our code clean, internal sql conventions and DRY. 🧼
10. How we got there?
11. Our DEV Environment Container Snowflake We are building an image that every dev is using. This image is also the one built for the production This image contains mainly dbt We are using remote container on vscode. In addition of building our container, it also manage vscode extension + additional python package for test Every member of the AE team has the right to create its database. Everyone has at least one dev database. VsCode
12. Our Acceptance Tests When Metrics Ideally, before any new feature We are using behave Python Package It implements gherkin syntax The test has to be as simple as possible 113 feature files 1537 scenarios 12507 steps How When we found a bug
13. Our Acceptance Tests - Gherkin Example
14. Tests definition Analytics Engineer Data Analyst
15. Acceptance Test Strategy
17. Let's build our models After writing the tests
18. Our loading schema architecture Sent from our application Raw Data Source oriented modelisation Staging Foundation modelisation Core Answer business needs Data Marts LAKEHOUSE ➡ ➡
19. Tests definition Analytics Engineer Data Analyst Code Tests
20. Linter - SQLFluff
21. Properties & Documentation
25. Our CI/CD process
26. What does our CI check ? No SQL lint issue 100% of doc coverage All the tests defined in our behave tool are executed All tests must pass For all modified models and their children - We run models on production data - We ensure the constraint defined are valid Syntax & Docs Acceptance tests Testing with production data
27. What does our CD do ? The new image is pushed to our ECR repo A deploy dag is executed. It executes all modified models + their children The documentation is published to our data catalog (CastorDoc) Build the new container Trigger airflow Publish documentation
28. Production Monitoring
29. Data Catalog
30. Data Catalog
32. Thank you!
33. Q&A