Data Lake Testing: The Checklist Most Teams Skip

Most data teams test their data lake — just not the right parts of it. They run row count checks on the final serving layer, wire up alerts on pipeline failures, and call it done. Then a downstream analyst notices that revenue figures for Q3 are off by 12%, and the investigation reveals that a schema change in the raw zone three weeks ago introduced a silent type mismatch that propagated through every transformation layer without triggering a single alert.

Data lake testing fails not because teams don't test, but because they test the wrong things at the wrong layer. The medallion architecture — raw, ingestion, cleansed, conformed, enriched, gold — creates six distinct testing contexts. Each layer has its own failure modes. A check that catches problems in the raw zone catches nothing in the gold zone, and vice versa.

This guide covers all six layers: what to test at each one, why those checks exist, and what specific query patterns to use on AWS S3, Azure ADLS, and Databricks. If you implement every check in this framework, bad data stops at the layer it entered — not at the dashboard where an executive spots it.

What this covers:

• Layer 1: Raw Zone — file integrity, format conformance, arrival completeness

• Layer 2: Ingestion Zone — deduplication, row count parity, latency SLAs

• Layer 3: Cleansed Zone — null handling, type casting, encoding normalization

• Layer 4: Conformed Zone — business rule enforcement, referential integrity

• Layer 5: Enriched Zone — join accuracy, derived field validation

• Layer 6: Gold/Serving Zone — BI-readiness, metric consistency, SLA compliance

Layer 1 — Raw Zone Testing

The raw zone is where files land — S3 prefixes, ADLS containers, or Databricks external locations. Nothing has been transformed. This is your first and best opportunity to catch problems before they propagate.

What goes wrong here: Files arrive in the wrong format (Parquet when you expected JSON), with unexpected schema changes, at the wrong volume, or not at all. A missing partition that silently drops a day's worth of records is a raw zone failure.

Checks for the Raw Zone

Check	What It Catches	SQL / Approach
File arrival completeness	Missing partitions, skipped hours	Count expected vs. actual partition folders by date/hour
Row count by partition	Drop vs. prior day baseline	Compare today's count to 7-day rolling average
File format conformance	Schema-on-read failures	Attempt schema inference; fail if column count or types differ from registered schema
Null rate on required fields	Source system stopped populating a key field	COUNT(*) - COUNT(field) per column, alert if > 0.1%
File size anomaly	Truncated files, empty loads	Alert if file size < 10% of rolling average
Duplicate file detection	Re-delivered files causing double-counts	Hash file contents or use ETag/MD5; alert if same hash seen twice in 24 hours

AWS S3 approach: Use S3 Inventory or Athena to query s3://bucket/prefix/date=*/ partition counts against your expected schedule.

Azure ADLS approach: Azure Data Factory has native file existence checks in its validation activity. For custom checks, query the dfs.core.windows.net REST API for directory listing.

Databricks approach: Use dbutils.fs.ls() to enumerate partitions and compare against a schedule table, or use Delta Lake's DESCRIBE HISTORY to audit when each partition was last written.

-- Raw zone: detect missing date partitions
SELECT
  expected_date,
  CASE WHEN actual_count IS NULL THEN 'MISSING' ELSE 'PRESENT' END AS status,
  COALESCE(actual_count, 0) AS row_count
FROM (
  SELECT date_add(current_date(), - seq) AS expected_date
  FROM (SELECT explode(sequence(0, 6)) AS seq)
) expected
LEFT JOIN (
  SELECT DATE(partition_date) AS actual_date, COUNT(*) AS actual_count
  FROM raw.events
  GROUP BY 1
) actual ON expected.expected_date = actual.actual_date
HAVING status = 'MISSING'
ORDER BY expected_date DESC;

The failure pattern to watch for: A source system moves from a UTC timestamp to a local timestamp without notice. Your partition logic splits records across the wrong day boundaries. Row counts look plausible per partition — the data just exists in the wrong partitions. This is caught only if you cross-check partition-level counts against an expected volume from the source system.

Layer 2 — Ingestion Zone Testing

The ingestion zone is where raw files get parsed, decoded, and loaded into your processing engine. This is the first point where records become rows — and where duplicate records, parsing failures, and latency violations first become measurable.

What goes wrong here: The same file gets re-delivered and loaded twice. A Kafka consumer resets its offset and replays 6 hours of events. A CDC stream catches an UPDATE as both a DELETE and an INSERT. Deduplication logic that worked for yesterday's volume fails under today's load.

Checks for the Ingestion Zone

Check	What It Catches	Implementation
Row count parity	Rows lost or added during parsing	Compare raw file row count to ingested table count by partition
Duplicate record detection	Re-delivered files, offset resets	Count distinct primary keys vs. total rows; alert if duplicates > 0
Parse error rate	Malformed records silently dropped	Log parse failures; alert if error rate > 0.01%
Ingestion latency	Files landing late, consumer lag	Compare file_arrival_time to ingestion_complete_time; alert if > SLA
Schema evolution detection	New column added without notification	Compare column list to registered schema at each load
Null primary key rate	Source sent records with no business key	COUNT(*) WHERE primary_key IS NULL; this should always be 0

-- Deduplication check: identify duplicate records by primary key
SELECT
  source_id,
  COUNT(*) AS occurrence_count
FROM ingestion.events
WHERE partition_date = CURRENT_DATE - INTERVAL '1' DAY
GROUP BY source_id
HAVING COUNT(*) > 1
ORDER BY occurrence_count DESC
LIMIT 100;

-- Row count parity: raw vs ingested
SELECT
  r.partition_date,
  r.raw_row_count,
  i.ingested_row_count,
  r.raw_row_count - i.ingested_row_count AS row_delta,
  ROUND(100.0 * (r.raw_row_count - i.ingested_row_count) / r.raw_row_count, 2) AS loss_pct
FROM (
  SELECT partition_date, COUNT(*) AS raw_row_count FROM raw.events GROUP BY 1
) r
JOIN (
  SELECT partition_date, COUNT(*) AS ingested_row_count FROM ingestion.events GROUP BY 1
) i USING (partition_date)
WHERE ABS(r.raw_row_count - i.ingested_row_count) > 0
ORDER BY partition_date DESC;

The failure pattern to watch for: A Kafka consumer group is reset to replay from the beginning of a topic to fix a processing bug. The ingestion zone accumulates 3x the expected records. Aggregate queries downstream triple their output. The issue surfaces only when a director notices that weekly active users jumped 200% overnight.

Layer 3 — Cleansed / Standardized Zone Testing

The cleansed zone is where raw data gets normalized: nulls handled, types cast, strings trimmed, dates standardized. This is the highest-leverage layer for data quality checks because transformations here affect every downstream consumer.

What goes wrong here: A date field gets cast from VARCHAR to DATE, and records with dates like 99/99/9999 or 0000-00-00 silently become NULL instead of failing the job. A phone number field gets stripped of its country code. An encoding normalization converts a multi-byte UTF-8 character to a replacement character � and nobody notices.

Checks for the Cleansed Zone

Check	What It Catches	Implementation
Type cast success rate	Silent NULL conversion from bad values	Compare NULL count before and after cast; alert if post-cast NULLs > pre-cast NULLs
Encoding validation	UTF-8 corruption, replacement characters	Scan for � (U+FFFD) or ? substitution characters in text fields
Date range validity	Impossible dates, future dates on historical fields	WHERE event_date > CURRENT_DATE OR event_date < '2000-01-01'
String length anomalies	Truncation, padding, runaway concatenation	MAX(LENGTH(field)) vs. registered max length; alert on outliers
Standardization completeness	Inconsistent formats after normalization	Count format variants in a standardized field (e.g., phone formats)
Reference field validity	Foreign keys referencing retired or non-existent values	LEFT JOIN to reference table; count unmatched rows

-- Encoding check: find records with UTF-8 replacement characters
SELECT
  record_id,
  customer_name,
  LENGTH(customer_name) - LENGTH(REPLACE(customer_name, '�', '')) AS replacement_char_count
FROM cleansed.customers
WHERE customer_name LIKE '%�%'
LIMIT 50;

-- Type cast integrity: compare NULL rates before and after cleansing
SELECT
  'raw' AS layer,
  COUNT(*) AS total_records,
  SUM(CASE WHEN birth_date IS NULL THEN 1 ELSE 0 END) AS null_count
FROM raw.customers
UNION ALL
SELECT
  'cleansed',
  COUNT(*),
  SUM(CASE WHEN birth_date IS NULL THEN 1 ELSE 0 END)
FROM cleansed.customers;
-- If cleansed null_count > raw null_count: cast failures are silently creating NULLs

The failure pattern to watch for: A source system switches from DD/MM/YYYY to YYYY-MM-DD date format partway through a migration. Your cleansing logic handles the new format, but records from the first 3 months of the year ambiguously parse — 01/02/2024 is both Jan 2 and Feb 1 depending on format. Silently, half your records get wrong dates. This is only caught by comparing a sample of cleansed dates against the source system's own display.

Layer 4 — Conformed Zone Testing

The conformed zone applies business rules and integrates data across sources. This is where a customer_id from your CRM gets matched to a user_id from your product database. It is also where the most expensive data quality failures occur — because by this point, multiple upstream sources have contributed records.

What goes wrong here: A cross-source join on a key that is not truly unique in one of the source systems fans out records. A business rule that was valid last year (order_amount > 0) no longer covers edge cases (order_amount = 0 for fully comped orders). A reference dataset gets updated, and conformed records referencing the old values silently become unmatched.

Checks for the Conformed Zone

Check	What It Catches	Implementation
Referential integrity	Orphaned foreign keys after join	LEFT JOIN reference_table; WHERE ref.key IS NULL; COUNT > 0 = violation
Business rule enforcement	Records violating domain constraints	Run registered rule set against every batch; fail if violation rate > threshold
Fan-out detection	Many-to-many joins inflating row counts	Compare post-join count to pre-join count; > 5% increase = investigate
Cross-source key match rate	CRM-to-product ID mismatches	Count matched vs. unmatched records across source systems
Temporal consistency	Event sequences that violate time order	WHERE event_end < event_start or created_at > updated_at
Aggregate totals reconciliation	Conformed layer totals don't match source	Sum the same metric in conformed vs. raw; diff should be < 0.001%

-- Fan-out detection: confirm joins aren't multiplying rows
WITH before_join AS (
  SELECT COUNT(*) AS order_count FROM conformed.orders
),
after_join AS (
  SELECT COUNT(*) AS joined_count
  FROM conformed.orders o
  JOIN conformed.promotions p ON o.customer_id = p.customer_id
)
SELECT
  b.order_count,
  a.joined_count,
  a.joined_count - b.order_count AS row_inflation,
  ROUND(100.0 * (a.joined_count - b.order_count) / b.order_count, 2) AS inflation_pct,
  CASE WHEN a.joined_count > b.order_count * 1.05 THEN 'FAIL' ELSE 'PASS' END AS test_status
FROM before_join b, after_join a;

-- Cross-source key match rate
SELECT
  COUNT(*) AS total_crm_customers,
  SUM(CASE WHEN p.user_id IS NOT NULL THEN 1 ELSE 0 END) AS matched,
  SUM(CASE WHEN p.user_id IS NULL THEN 1 ELSE 0 END) AS unmatched,
  ROUND(100.0 * SUM(CASE WHEN p.user_id IS NULL THEN 1 ELSE 0 END) / COUNT(*), 2) AS unmatch_pct
FROM conformed.crm_customers c
LEFT JOIN conformed.product_users p ON c.email = p.email;

The failure pattern to watch for: A marketing team loads a promotions table where one promotion applies to all customers. Every order record joins to every promotion record. The orders table has 1 million rows. The promotions table has 50 entries, but one entry has a NULL on the join key, which your database treats as matching everything. Revenue reports suddenly show 50x the expected figures.

Layer 5 — Enriched Zone Testing

The enriched zone adds derived features: calculated fields, ML model scores, third-party data appends, aggregated metrics. This layer is where business logic gets encoded into data — and where the widest variety of unique failure modes live.

What goes wrong here: A lifetime_value field is calculated using a window function with an off-by-one error in the date range. An ML model scoring pipeline returns NaN for records outside its training distribution, and downstream systems treat NaN as 0. A third-party data append (firmographic data for B2B accounts) has a 30-day lag, enriching last month's data with last-last-month's firmographic snapshot.

Checks for the Enriched Zone

Check	What It Catches	Implementation
Derived field range validity	Calculated values outside expected range	WHERE ltv < 0 OR ltv > 1000000; configure expected bounds per metric
NaN/Infinity detection	ML model score failures	WHERE score != score (NaN check in SQL) or IS_NAN(score) in Spark
Third-party append match rate	Low join rate = stale or missing enrichment	Count enriched vs. unenriched records; alert if append rate drops 10% vs. baseline
Aggregation window correctness	Wrong date range in window functions	Spot-check aggregated values against manually calculated point values
Feature drift detection	ML input features shifting from training distribution	Compare mean and std dev of each feature column to training baseline
Derived field consistency	Two fields that should sum/relate don't	WHERE field_a + field_b != field_c where c is defined as their sum

-- NaN detection in Databricks/Spark SQL
SELECT
  COUNT(*) AS total_scored,
  SUM(CASE WHEN isnan(model_score) THEN 1 ELSE 0 END) AS nan_count,
  SUM(CASE WHEN isinf(model_score) THEN 1 ELSE 0 END) AS inf_count,
  SUM(CASE WHEN model_score IS NULL THEN 1 ELSE 0 END) AS null_count,
  ROUND(100.0 * SUM(CASE WHEN isnan(model_score) OR isinf(model_score) OR model_score IS NULL THEN 1 ELSE 0 END) / COUNT(*), 2) AS bad_score_pct
FROM enriched.customer_scores
WHERE score_date = CURRENT_DATE;

The failure pattern to watch for: A customer_segment field is calculated from lifetime_value with a threshold of $500 for the "premium" tier. The LTV calculation uses SUM(order_amount) with a date range filter. A timezone bug causes one day of orders to be counted twice for some customers. LTV is inflated, and 15% of customers are promoted to "premium" who shouldn't be. Email campaigns fire to the wrong segment.

Layer 6 — Gold / Serving Zone Testing

The gold zone is what your BI tools, analysts, and applications read from. It is the end of the pipeline and the last chance to catch anything that made it through all previous layers. By this point, fixing a problem means tracing it back through five upstream layers — which is why catching it here is expensive.

What goes wrong here: A metric that was correct yesterday reads wrong today because the underlying aggregation logic assumes a complete day of data, but the gold zone gets refreshed at 6 AM before all source data has arrived. A rollup table that powers an executive dashboard hasn't been updated since the underlying model was refactored. A new data consumer reads from the gold zone directly and runs a query that doesn't apply the expected filter — so they see 40% more records than intended.

Checks for the Gold Zone

Check	What It Catches	Implementation
Freshness SLA compliance	Stale tables served to BI tools	MAX(updated_at) vs. expected refresh time; alert if > SLA threshold
Metric consistency with conformed layer	Gold zone metric doesn't match conformed source	Reconcile key metrics (revenue, MAU, claims) between gold and conformed
Row count stability	Unexpected drops or spikes in serving tables	Compare today's count to 7-day rolling average; alert if > 10% deviation
Null rate in key BI dimensions	Dimensions that drive filters or GROUP BY	Zero-tolerance null check on dimension keys used in BI aggregations
BI access filter validation	Data leakage across tenants or access boundaries	Sample records per access group; confirm correct records are visible
Partition coverage	Partitions expected by BI tools exist	Assert that all expected date/region/product partitions are present

-- Gold zone freshness check
SELECT
  table_name,
  MAX(updated_at) AS last_refresh,
  CURRENT_TIMESTAMP - MAX(updated_at) AS staleness,
  expected_refresh_interval,
  CASE
    WHEN CURRENT_TIMESTAMP - MAX(updated_at) > expected_refresh_interval * 1.2
    THEN 'STALE'
    ELSE 'FRESH'
  END AS freshness_status
FROM gold.table_refresh_log
GROUP BY table_name, expected_refresh_interval
HAVING freshness_status = 'STALE'
ORDER BY staleness DESC;

-- Metric reconciliation: gold vs. conformed
SELECT
  'gold' AS layer,
  SUM(revenue_amount) AS total_revenue
FROM gold.revenue_summary
WHERE report_date = CURRENT_DATE - INTERVAL '1' DAY
UNION ALL
SELECT
  'conformed',
  SUM(order_amount)
FROM conformed.orders
WHERE order_date = CURRENT_DATE - INTERVAL '1' DAY
  AND status = 'completed';
-- Revenue delta > 0.1% = reconciliation failure

The Complete 6-Layer Data Lake Testing Checklist

Use this as your implementation checklist. Layer 1–2 checks should be automated and run at ingestion time. Layer 3–6 checks should run as part of your transformation pipeline.

Layer	Zone	Priority Checks	Run At
1	Raw	File arrival, row count vs. baseline, format conformance, null rate on PKs	File landing
2	Ingestion	Deduplication, row parity, parse error rate, latency SLA	Post-load
3	Cleansed	Type cast null rate, encoding scan, date range validity, string length	Post-transform
4	Conformed	Referential integrity, fan-out detection, cross-source match rate, business rules	Post-join
5	Enriched	Derived field ranges, NaN/Inf detection, append match rate, feature drift	Post-enrichment
6	Gold	Freshness SLA, metric reconciliation, row stability, BI filter validation	Pre-serve

Implementation Priority

If you're starting from zero, implement in this order:

1. Raw zone file arrival check — know when data doesn't arrive before your downstream pipeline starts

2. Ingestion row count parity — confirm nothing is lost in parsing

3. Gold zone freshness — know when your serving layer is stale before analysts report it

4. Conformed fan-out detection — the most expensive failure to fix if missed

5. Everything else in layers 3 and 5 — by priority based on your specific failure history

The pattern is clear: test at the point of entry for each layer, not just at the output. A problem caught at layer 1 costs nothing to fix. The same problem caught by an executive at layer 6 costs days of investigation and remediation.

Vexdata automates data lake testing across all six layers, with pre-built checks for AWS S3, Azure ADLS, and Databricks that deploy in hours, not months. See how it works at vexdata.io/data-lake-testing or book a 30-minute walkthrough to see it running against your stack.