Testing and Observability in dbt project — meta analysis

Читать на русском

Premise

Let's assume we are a company that has just passed the first level of organizing our data:

We've learned that it's not done by duct-taping things together and randomly creating tables in databases, spreadsheets, and other sources
We've discovered dbt and written our first warehouse, sized in the tens to small hundreds of models
We've realized that since such a wonder has appeared, it should be used to its full extent
We know about the benefits of automatic software testing, but now we're thinking about the fact that data testing is also useful
We've learned about dbt test, wrote simple tests for many models, and saw how cool it is
We started looking at our data to find flaws, incompleteness, violations
And now we want to understand — how to manage these data, test them, and monitor the infrastructure of our pipeline
In short, we want to move from level one to level two
Money matters. We don't want to spend $1000+ a month

Road to Level 2

Short Path

Additional Solutions

Data Observability Services

DataHub — A Metadata Platform for the Modern Data Stack
- An OS solution with a paid cloud option
- A catalog of your assets from various sources with lineage
- A centralized place for viewing metadata, owners, documentation, tests, etc.
- Looks promising, but young (version 0.13)
- Many integrations, good support
- Requires developer support for configuration and use (writing yaml configs for alerts, etc.)
Elementary Data — dbt native data observability
- From $600 per month
- Quite simple overview of dbt test run results
- Column level lineage
- Integration with BI for lineage
- Dashboard about the time spent on assembling models
- There is a free CLI version for generating reports and sending alerts
- GitHub - elementary-data/elementary: The dbt-native data observability solution for data & analytics engineers. Monitor your data pipelines in minutes. Available as self-hosted or cloud service with premium features.
- GitHub - elementary-data/dbt-data-reliability: Data anomalies monitoring as dbt tests and dbt artifacts uploader.
Synq — Reliability Platform for business-critical data
- In private beta
- Quite simple, mainly - incident tracking
- Column-level lineage
- Impact analysis on consumers
- Alert system with identification of owners of broken objects
Sifflet — Full Data Stack Observability for Data Engineers and Data Consumers
- No information on pricing and open registration
- All the usual integrations
- Search across all your data assets
- Metadata collection and analysis from all systems
- Monitoring with ML capabilities
- Ready-to-use catalog of monitoring strategies
Bigeye — Find and fix data issues before they break your business
- No open registration (demo only)
- Automatic monitoring of data quality
- Column-level lineage
- Automatic anomaly detection
- Error cause analysis and incident management
- Row-level analysis — queries for debugging problems, information about data affected by the issue
Metaplane — Trust the data that powers your business
- Integrates with dbt and metabase
- dbt integration cannot be set up with macOS (only Linux)
- Auto-detection of anomalies
- Capable of column-level lineage and generally shows lineage well (including based on query history)
- Free plan (10 tables, 3 custom monitors, one user)
- Paid $1500 per month (100 tables, 5 users, column lineage)
- Has an alert system
- Monitors for failing connectors
- Reports on which consumers are affected by an incident (exposures)
Datafold — Automated testing for data engineers
- Main feature: direct comparison of data
- Shows how each code change directly affects the data at the column and row level
- Shows changes at the value level ("increased by 33%")
- Runs in CI and makes reports in PR
- Compliance analysis between stores (compare two stores - that they are correctly synchronized)
- There is an OS package that can be used independently GitHub - datafold/data-diff: Compare tables within or across databases
Soda — Data Quality Platform, Test and deliver data that everyone can trust
- 45-day trial
- Its own testing system in a powerful human-readable language
- Simple but nice dashboard about all checks
- Integrations with dbt, airflow, and all the usual suspects
- Alert system (slack, email, pagerduty)
dbt Cloud — An integrated cloud solution for dbt
- Most importantly: allows for PR-based development of dbt (each branch separately)
- Can schedule model builds and runs tests
- Has logging and alerts, integrations
- Can do metrics, can combine dbt projects into a mesh
- $100 per month (there is also a free plan)

Important dbt packages

dbt-labs/dbt-utils — a standard set of additional functions from dbt-labs
calogica/dbt-expectations — a large set of test macros for all cases of data quality checks
mjirv/dbt-datamocktool — unit tests for dbt
EqualExperts/dbt-unit-testing — macros for dbt unit tests based on SQL
dbt-labs/dbt-project-evaluator — quality control of the dbt project as a whole, in terms of compliance with dbt-labs practices
dbt_artifacts — a package that exports information about the dbt project itself (models, their runs, tests, etc.) into your warehouse and generates marts for their analysis. This allows, for example, to have metrics on resources spent or model freshness.
dbt profiler — a set of macros generating a profile of the table: statistical indicators, relationships (relations), types. This package can be used for generating documentation.
dbt_meta_testing — a dbt package that allows setting requirements for tests: that they exist, that they are documented
gouline/dbt-metabase — integration of dbt and Metabase

Reading List

State of Data Quality Monitoring in 2024
- a good overview article
Building a Data Platform in 2024. How to build a modern, scalable data…
- Fivetran | Automated data movement platform and Airbyte | Open-Source Data Integration Platform | ELT tool — leading solutions for batch data upload/download
- Confluent | Apache Kafka Reinvented for the Cloud — leading solution for data streaming
- Debezium — CDC solution
  - Change Data Capture is when the database's own CRUD commands are used as a source of events for synchronization
- About dbt Core and installation | dbt Developer Hub — an indispensable and key element of the data platform, in the context of the transformation layer.
- dbt mesh — a way to combine several dbt projects and scale the transformation system
- Apache Airflow remains the main solution for orchestration, along with its managed versions:
- In visualization, traditional solutions continue to dominate: Tableau, PowerBI, Looker, and Qlik
- New platforms Sigma Computing. Business Intelligence and Analytics Solution and Welcome | Superset are interesting
- The package Streamlit • A faster way to build and share data apps, allowing quick building of interfaces for working with data in Python, is interesting
- Reverse ETL — uploading data from analytics back to end applications, where Hightouch | Composable CDP & Reverse ETL | Activate data | Hightouch undoubtedly leads
- The open-source project DataHub and its Managed variant — a solution for combining metadata from different storages and systems and ensuring observability
- Monte Carlo | Data Reliability Delivered — a system for monitoring data and managing incidents (closed)
- GX: a proactive, collaborative data quality platform • Great Expectations
The Four Pillars of Data Observability | Metaplane
- There are 4 pillars of data observability
- Metrics — internal characteristics of data. Statistics, uniqueness, accuracy, etc.
- Metadata — external characteristics. Volume, schema, freshness, owner.
- Lineage — relationships within data. What depends on what, what came from what
- Logs — interactions between machines and between machine and human. Queries to the warehouse, dbt transformations, upload and download.
7 dbt Testing Best Practices | Datafold
- There are singular tests, simple .sql files with select statements that return erroneous rows (or nothing if the test passes)
- There are generic tests, which can be applied to different tables (like not null for a column)
- Start with generic tests, checking for not_null, unique, accepted_values, relationships
- Then write specific singular tests
- You can use dbt-expectations - a library of generic tests for different life situations
  - GX: a proactive, collaborative data quality platform • Great Expectations — a special separate service for working with data quality
  - Useful tests expect_row_values_to_have_recent_data, expect_column_to_exist
- You should test not only downstream models but also sources, including CSV
- For alerts, you can use dbt cloud, or analyze logs (enable json-structured logs)
- Tests must be Accelerating dbt core CI/CD with GitHub actions: A step-by-step guide | Datafold
What the heck is data diffing?! | Datafold
- Datafold - Automated testing for data engineers — automated tests for data, checking data changes within CI, providing access to column-level lineage
- (price model not disclosed, exclusively through a call with their engineer, so presumably expensive)
- there is a free CLI tool for data diff
- Data Diff — testing by directly comparing data in different storages (e.g., new version vs current production)
- dbt tests don't allow seeing "unexpected data changes" — they inherently only monitor for "expected" changes
- diffs should be checked during development to see exactly how your code changes data
- during deployment: as a check within CI to not release something broken
- during data migrations — for direct quality check of the migration
- during replications — for quality control of replication
A Simple (Yet Effective) Approach to Implementing Unit Tests for dbt Models | by Mahdi Karabiben | Towards Data Science
- Besides dbt tests, one can also talk about dbt unit tests
- The tested unit is both the models themselves and the CTEs from which they are composed
- The model structure should be standardized: import CTEs, Intermediate CTEs, Final CTE
- Intermediate CTEs and final CTE should be tested
- The first component of the testing system: mock inputs in the form of dbt seeds
- The second component: expected outputs, in the form of the same CSVs.
- The testing process is simply comparing the table created from seed.input through dbt transformations and the table of expected outputs
- For direct comparison, you can use the package dbt_audit_helper or a system like Soda Core
- The general process of the unit testing system is described, but implementing it (substituting references to tables with mocks and comparing with expectation) will need to be implemented by yourself
Unit Testing · dbt-labs/dbt-core · Discussion #8275 · GitHub
- Detailed discussion of the proposed Unit-testing process in dbt core, which is planned to be released in version 1.8
Audit_helper in dbt: Bringing data auditing to a higher level | dbt Developer Blog
- a package that helps conduct an audit of tables through data comparison (usually for migrations or refactoring)
- provides two main macros: compare_queries and compare_column_values
- compare_queries allows comparing rows, excluding some columns if necessary, and getting a report on data match
- compare_column_values compares values in a specific column to check its compatibility between storages
Data Observability dbt Packages | The Infinite Lambda Blog
- Describes standard packages codegen, utils, expectations
- Describes packages dbt-labs/dbt-project-evaluator, dbt profiler, dbt_artifacts — see above
Data Observability on Steroids
- What is re_data? — a report generator about the dbt project, includes data lineage, their statistical characteristics, and test results
- What is re_cloud? — hosting for dbt reports, including redata, dbt docs, elementary reports
- Data Observability Features Built for Today's Data Teams | Metaplane — a platform for data monitoring, incident management, and alerting. Allegedly capable of automated anomaly detection.
Data quality dimensions for better decision-making | Datafold
- There are seven dimensions of "data quality"
- Accuracy. Usually measured in percentages. Can drop due to delays, input errors, processing errors.
- Completeness. For example, "we have the address of 95% of clients."
- Consistency. Uniformity in formats (24.99USD vs. $24.99), time zones. Easiest to measure by comparing the same data in different reports.
- Reliability. The constancy of data, absence of "flickering," regularity. How much one can trust a particular measurement. For verification, one can compare with an independent source or conduct repeated measurements.
- Timeliness. Tomorrow's weather forecast will not be needed in a week. If data arrives slowly, it's impossible to make quality forecasts. Metric: the delay between data collection and when they are ready for use. Can have alerts for staleness.
- Uniqueness. Absence of (erroneously) duplicated data. Another example: poorly organized versioning — two tables like customers and customers_v1.
- Usefulness. A subjective characteristic of how useful your models are to someone. Reminds that it's not always necessary to build complex ML models where pivot tables could suffice.
dbt at Super Part 3: Observability | by Jonathan Talmi | Super.com | Medium
- Store run results and other artifacts in the warehouse and use BI (metabase) for analysis and alerts.
- elementary-data/dbt-data-reliability
- Add the data owner's name to the model manifest (dbt meta) so that alerts can directly tag the person responsible, for example, for freshness.
- OS Python Package by Elementary, capable of sending notifications to Slack / MS Teams (note: is there a way to use the slack API to receive messages in Discord?)
- Elementary allows exploring the run time of models and their cost.
dbt: How We Improved Our Data Quality by Cutting 80% of Our Tests | by Noah Kennedy | Better Programming
- The main blocker to a good testing system: observability and actionability of tests (when they fail). We improved everything, meanwhile removing 80% of tests.
- Three-step process: detailed high-quality tests, aggregation of testing results, building views for test results separated by owners and/or importance.
- Tests should explicitly indicate their severity.
- Each test should adhere to established norms RACI (who will fix it, who needs to be informed).
- Two classes of tests: data integrity and context-driven.
- Data integrity tests are fixed by programmers; context-driven involve people from the subject area.
- Strict naming of tests (table_name__column_name__test_name) and test aliasing.
- Definitely set store_failures to true.
- Use a metadata file for tests, which is a seed. It stores the test name, owner, test importance, and other additional fields.
- Next, data are aggregated using dbt, combining test run results with metadata. From this base table, various views are built, providing dashboards for owners, by severity, and other dimensions.
dbt Tests. A necessary assertion to ensure the... | by Jimmy Pang | Data Panda | Medium
- In Elementary, there are additional tests for models.
- Specifying test severity, in the form of threshold values for the number of errors.
- Ensure all models have primary keys + set a test for not_null & unique.
- Generate surrogate keys using macro from dbt-utils.
So you're using dbt tests—Validio is what's next in data quality validation for scalability and comprehensiveness
- Besides warehouses (tables), there are other data (object stores and streams) that may not reach the warehouse but still need to be tested.

Findings Along the Way

Here's what doesn't relate to testing, dbt, or maybe even to development at all, but came across along the way.