Finding the right balance for data quality at scale

Dropbox stores data about product and service usage in a Hadoop-based data lake that has grown to more than 55 petabytes. Analytics, billing, and feature development all depend on that data being accurate, but at this scale, quality problems are hard to spot. A dedicated data engineering team, formed in 2018, was tasked with catching errors before they propagate downstream.

Data validation sits between two failure modes. Too permissive, and unreliable data reaches users quickly. Too restrictive, and data arrives late or costs too much to process. The framework Dropbox built occupies the middle ground: simple to maintain, yet broad enough to catch the problems that matter most.

Coverage, code, and configuration decisions

Quality issues come in many forms—duplicate rows, missing mandatory fields, unexpected negative amounts, timestamps that are implausibly far in the past or future. The team evaluated open source options like Great Expectations, dbt, and Evidently, but found each lacking. Great Expectations and dbt support basic checks (NOT NULL, bounds, UNIQUE) stored in YAML, but complex validations still require custom SQL, and both are standalone services that don't integrate cleanly with Dropbox's Airflow orchestration. Evidently was too complex and required substantial Python work.

Building from scratch meant deciding three things: what to cover, what language to use, and where to store the rules. On coverage, the team applied an 80/20 principle—a small set of checks handles the most common problems, while rare or unusual cases are addressed individually.

For implementation, the team chose SQL. It's familiar across the engineering org, flexible enough for both simple and complex validations, and easy for engineers at any level to write and maintain. The validation rules themselves live as code in Git rather than in a database, making changes reviewable and history traceable.

Executing validation in Airflow

The framework adds a validation operator to Airflow that runs after new data is ingested. For performance, a single query handles all validations for a table, returning one row with many columns—each column represents one validation check. A value of zero means the check passed; any non-zero value means it failed.

Airflow's ability to combine source tables into a single table enables progressive validation: validate intermediate results, then validate again after further joins or transformations. When a validation fails, the framework collects all non-zero values, marks the Airflow task as failed, and triggers a PagerDuty alert to the on-call engineer. The exception message includes enough detail to identify which data needs investigation.

The following examples illustrate the kinds of checks the framework supports.

Upstream data issues

Detect an empty source table:

SELECT CASE WHEN COUNT(*)=0 THEN 1 ELSE 0 END AS is_no_data
  FROM "user"

Check that a mandatory column has no NULLs:

SELECT SUM(CASE WHEN email IS NULL THEN 1 ELSE 0 END) AS email_nulls
  FROM "user"

Check for duplicate IDs:

SELECT SUM(CASE WHEN rows_per_user_id > 1 THEN 1 ELSE 0 END) AS duplicated_user_id
  FROM
  (
  SELECT *
       , COUNT(*) OVER (PARTITION BY user_id) AS rows_per_user_id
    FROM "user"
  ) u

Profiling alerts

Verify that remaining NULLs stay below an acceptable threshold:

SELECT CASE WHEN locale_null_perc > 0.05 THEN locale_null_perc ELSE 0 END AS high_null_perc_locale
  FROM
   (
   SELECT SUM(CASE WHEN locale IS NULL THEN 1 ELSE 0 END)/COUNT(*) AS locale_null_perc
     FROM "user"
   ) t

Business logic checks

Flag users with negative account balances:

SELECT SUM(CASE WHEN u.balance <=0 AND COALESCE(p.ttl_amount, 0) < COALESCE(i.ttl_amount, 0) THEN 1 ELSE 0 END) AS users_with_incorrect_non_pos_balance
  FROM "user" u
       LEFT OUTER JOIN
        (
        SELECT user_id, SUM(amount) AS ttl_amount FROM payment GROUP BY user_id
        ) p ON p.user_id = u.user_id
       LEFT OUTER JOIN
        (
        SELECT user_id, SUM(amount)AS ttl_amount FROM invoice GROUP BY user_id
        ) i ON i.user_id = u.user_id

Combine all checks into the final validation result for the user table:

SELECT is_no_data
     , duplicated_user_id
     , suspiciously_low_data
     , email_nulls
     , CASE WHEN locale_null_perc > 0.05 THEN locale_null_perc ELSE 0 END AS high_null_perc_locate
  FROM
   (
   SELECT CASE WHEN COUNT(*)=0 THEN 1 ELSE 0 END AS is_no_data
        , SUM(CASE WHEN rows_per_user_id > 1 THEN 1 ELSE 0 END) AS duplicated_user_id
        , CASE WHEN COUNT(*)<1000000 THEN COUNT(*) ELSE 0 END AS suspiciously_low_data
        , SUM(CASE WHEN email IS NULL THEN 1 ELSE 0 END) AS email_nulls
        , SUM(CASE WHEN locale IS NULL THEN 1 ELSE 0 END)/COUNT(*) AS locale_null_perc
     FROM
       (
       SELECT *
            , COUNT(*) OVER (PARTITION BY user_id) AS rows_per_user_id
         FROM "user"
       ) u
   ) t

Keeping unvalidated data out of production

One inherent limitation of this approach: once data lands in the lake, anyone can technically query it before checks finish, since validation can take hours on large datasets. Making downstream pipelines wait for validation doesn't scale.

The solution was to merge validation into the existing SQL execution operators that populate tables. The operator now performs two queries: first it fills a temporary table, then it runs the validation query against that temporary table. Only if validation passes does the operator move the data into the production table.

This process can be optional or mandatory per table. Non-critical data can be available during validation, while business-critical data is blocked from downstream use until checks pass. The guarantee is simple: only validated, correct data reaches production tables.

Results after a year in production

Validation frameworks that block bad data introduce a built-in delay: it takes time for downstream quality issues to surface, so you can't measure success immediately. With that caveat, we waited over a year before judging our system's impact. The results are in, and the framework has performed well beyond expectations.

Compared to the previous year, we saw 95% fewer data quality incidents, or SEVs as we call them internally. For a deliberately simple, 80/20-focused system, that's a strong outcome. The framework caught a range of real-world problems, including:

  • An email campaign that repeatedly sent the same message to recipients
  • Active accounts incorrectly flagged as churned, which would have undercounted revenue
  • A Dropbox and Dropbox Sign subscription bundle being miscounted as a standard Dropbox subscription
  • 24 instances of duplicate data over six months that would otherwise have gone unnoticed

Keeping the system simple to extend

The framework's simplicity is its main operational advantage—easy to maintain, modify, and extend. Our roadmap includes adding validation to legacy pipelines and to pipelines with less stringent requirements that would still benefit from these checks. We're also investing in analytics to review existing pipelines, suggest potential validations, and track how our validation coverage evolves as the data lake grows.