Data quality monitoring (schema, nulls, drift)

Automation continuously monitors data quality in the data warehouse and detects anomalies before they reach reports and dashboards. Checks are triggered on a schedule or on a load event, and results are formatted as alerts with details — which table and which rule was violated.

What happens in the process

1. Inventory of critical tables. The team describes which datasets in the warehouse are critical for reporting and operational decisions, and records data owners.
2. Formalizing expectations. Three groups of rules are defined for each table: the expected schema (list of columns and their types), the acceptable NULL share per column, the value range for key metrics.
3. Capturing the historical baseline. For drift checks, the system calculates statistical characteristics (mean, median, category shares) over a window of the last N days.
4. Check on every new load. When a data increment arrives, a set of tests runs: the schema has not changed, NULLs are within the threshold, the value distribution has not shifted relative to the baseline.
5. Alerting with context. When a rule triggers, a message is sent to Slack or email with the table name, column, violated rule, actual value, and a link to the runbook.
6. Logging history. All runs and results are saved in a separate table for retrospective analysis and data-health reporting.

What automation does not do

1. Does not fix data automatically. The system records the fact of the anomaly, but remediation (fix in ETL, load rollback, manual correction) is handled by the data engineer or table owner.
2. Does not replace pipeline unit tests. The monitor operates on the result — with data that has already landed in the warehouse. Transformation logic is tested separately in CI/CD.
3. Does not define business rules on its own. NULL thresholds, acceptable ranges, and drift sensitivity are defined by the team — automation enforces these rules without deciding what they should be.

Technically, the solution consists of four layers: a rules store, a checks runner, integration with a data warehouse, and an alerts channel. The implementation is custom-code (Python + SQL), with no dependency on a specific SaaS tool.

Architecture

1. Rules in code or YAML. Each rule is described declaratively: table, column, check type (schema / null / drift), parameters (threshold, baseline window). Rules are stored in git — changes go through a standard code review.
2. Checks runner. A scheduler (cron, Airflow, Dagster, dbt — team's choice) triggers the runner after each load or on a schedule. The runner reads the rules, generates SQL queries to the warehouse, and compares results against expectations.
3. Warehouse connection. The runner accesses the data warehouse via a native SQL connector and executes aggregations on the database side — to avoid pulling millions of rows into the application.
4. Alerts and dashboard. Violations are sent to Slack or email. Run history is written to a separate warehouse table, on top of which a data-health dashboard is built.

Typical configuration options

Implementation steps

1. Audit of critical datasets (1 week). With analysts and data engineers, a list of tables on which key dashboards and metrics depend is established.
2. Defining rules for the first wave (1–2 weeks). Schema, NULL thresholds, and drift checks are formalized for the 5–10 most important tables. Work begins with conservative thresholds.
3. Setting up the runner and integrations (1–2 weeks). The runner is deployed in the existing orchestrator, connected to the warehouse and the alerts channel.
4. Baseline and calibration (1–2 weeks). The system runs in "silent" mode: it records triggers but does not send alerts. The team adjusts thresholds based on actual data to eliminate false positives.
5. Moving to production. Alerts are enabled, a runbook is added for each check type, and table owners are established.

Alternative approaches

Instead of custom-code, ready-made tools are available — Great Expectations, Soda Core, dbt tests, as well as commercial observability platforms. Custom-code is justified when control over rules logic, absence of vendor lock-in, and integration with an existing orchestrator are priorities. Ready-made solutions get started faster but add cost and customization limitations.

Security and compliance

The runner operates with a warehouse service account on read-only rights — monitoring does not modify data. Rules in git go through code review like any other code. Check results contain only aggregated values (counts, averages), without samples of raw rows — which reduces risks when working with sensitive datasets.

To launch monitoring, three things are needed: access to a data warehouse, basic orchestration, and a list of critical tables with owners.

Data and Access

• A data warehouse (Snowflake, BigQuery, Redshift, PostgreSQL, or equivalent) with the ability to run SQL aggregations on the database side.
• A service account with read-only permissions on the target tables and write permissions on the service schema for run history.
• Alert channel: a Slack workspace with the ability to create an incoming webhook, or SMTP access for email.

Infrastructure

• An orchestrator in which checks will run: Airflow, Dagster, dbt Cloud/Core, GitHub Actions, or cron on a dedicated machine.
• A Git repository for storing rules and runner code.
• A CI/CD process for deploying changes to rules.

Team Readiness

• A data engineer or analyst capable of writing SQL and working with Python.
• Data owners for key domains — people who receive alerts and are responsible for remediation.
• An agreed alert format and delivery channel.

Organizational Prerequisites

• A list of the first 5–10 critical tables for monitoring — it is reasonable to start with a narrow scope and expand.
• A runbook template: what to do for each type of trigger (schema change, NULL growth, drift).

Timelines

Full implementation takes 6–10 weeks for a medium-complexity case: 1–2 weeks for audit and scope alignment, 2–3 weeks for setup and the first wave of rules, another 2–3 weeks for baseline calibration and transition to production. The exact timeline depends on the maturity of the data platform and the number of tables in the first iteration.