Audit quality before trusting the result

Estimated study time: 75–105 minutes. Read, predict, run, explain, then attempt the independent work.

So far, curated tables have had valid keys and constrained values. Real data often arrives in a raw area before those guarantees exist. Our raw_orders table deliberately contains repeated source identifiers, a negative amount, an unknown customer, an impossible date, an unexpected status and missing fields. Your task is to identify and document these defects, not make them disappear until the output looks tidy.

A primary key on batch_row only identifies the received row. It does not make source_order_id unique. The duplicate query groups by the business identifier and finds two versions of 9001. They arrived at different times. This fixture's policy is to prefer the latest loaded_at value, with batch_row as a deterministic tie-breaker. That is a source-specific policy, not a universal truth about duplicates.

Separate normalization from invention

LOWER(TRIM(status_text)) removes surrounding whitespace and normalizes letter case. It converts ' Completed ' and 'COMPLETED' into 'completed'. It does not turn 'complete' into 'completed'; that mapping needs an approved meaning, because an unfamiliar source value might have a different definition. The audit flags it rather than guessing.

A missing amount differs from a negative amount. Both fail this fixture's acceptance rule, but they require different investigation. Similarly, customer_id 99 is an unknown reference while a null customer_id is absent altogether. Keep reason columns separate so that one row can show several problems at once. A single first-error label can hide additional defects.

Dates arrive as text in the raw table. A string shaped like YYYY-MM-DD can still be impossible, such as 2025-02-30. PostgreSQL's pg_input_is_valid checks whether the text can be read as the specified type without forcing a failing cast. This function requires PostgreSQL 16 or later, and this package is tested on the bundled PostgreSQL 18-based runtime. It validates PostgreSQL date input, not a strict organizational ISO-only policy. Add an explicit format rule if that is part of your contract.

Deduplication is not validation

The dedupe example chooses one latest version per source_order_id using ROW_NUMBER. Its output still contains the negative amount, bad customer and invalid date. Fewer rows do not mean cleaner rows. In the independent task, choose the latest version first and then apply the acceptance checks. If the latest version is invalid, quarantine that identifier for review rather than silently falling back to an older record. A different policy would need an explicit business decision.

Do not mutate raw evidence during an audit. Preserve the received rows, record the rule version, and produce a separate accepted result and issue report. This makes it possible to explain why a record was excluded, investigate a supplier correction and reproduce the report. Excluding bad rows also changes coverage; a report should name how many source entities were accepted and what remains unresolved.

Quality is broader than row validity. Ask whether the file is fresh, whether the expected time range is complete, whether monetary units agree, whether an identifier is stable, and whether the data represents the population your question concerns. A perfectly valid table from last month cannot answer today's operational question. Our small synthetic file is intentionally complete for its stated teaching period, not evidence about a real business.

The curated schema's constraints and the raw audit serve different stages. Constraints stop certain invalid states from entering a curated table. Read-only audit queries explain what was wrong before that boundary. Neither proves that a name, amount or event is factually true in the outside world.

Worked example 10 duplicates

There are eight received rows but only seven source identifiers. The repeated identifier is not automatically an extra order.

SELECT source_order_id,COUNT(*) AS versions
FROM aabha_sql.raw_orders GROUP BY source_order_id HAVING COUNT(*)>1 ORDER BY source_order_id;

Expected result from the bundled dataset:

The runnable file is sql/examples/10_duplicates.sql.

Worked example 10 audit

Inspect every reason column. Raw row 7 has both a missing customer and a missing amount, while row 5 has an impossible calendar date.

SELECT r.batch_row,r.source_order_id,
 CASE WHEN r.customer_id IS NULL THEN 'missing_customer'
      WHEN c.customer_id IS NULL THEN 'unknown_customer' ELSE 'ok' END AS customer_check,
 CASE WHEN r.amount IS NULL THEN 'missing_amount'
      WHEN r.amount<0 THEN 'negative_amount' ELSE 'ok' END AS amount_check,
 CASE WHEN lower(trim(r.status_text)) IN ('completed','pending','cancelled') THEN 'ok'
      ELSE 'unknown_status' END AS status_check,
 CASE WHEN r.order_date_text IS NOT NULL AND pg_input_is_valid(r.order_date_text,'date')
      THEN 'ok' ELSE 'invalid_date' END AS date_check
FROM aabha_sql.raw_orders AS r LEFT JOIN aabha_sql.customers AS c
 ON c.customer_id=r.customer_id ORDER BY r.batch_row;

Expected result from the bundled dataset:

The runnable file is sql/examples/10_audit.sql.

Worked example 10 dedupe

Row 2 wins over row 1 because it arrived later. The remaining defects are intentionally still present, demonstrating why deduplication and validity are separate steps.

WITH ranked AS (
 SELECT batch_row,source_order_id,lower(trim(status_text)) AS status,
 ROW_NUMBER() OVER (PARTITION BY source_order_id ORDER BY loaded_at DESC,batch_row DESC) AS position
 FROM aabha_sql.raw_orders
)
SELECT batch_row,source_order_id,status FROM ranked WHERE position=1 ORDER BY source_order_id;

Expected result from the bundled dataset:

The runnable file is sql/examples/10_dedupe.sql.

Independent practice

10a

Return batch_row and customer_id for raw rows with a non-null customer identifier that is absent from customers.

Hint: Distinguish unknown identifiers from missing identifiers; use a left join and an explicit non-null source test.

10b

Choose the latest version per source_order_id, then accept only non-null source_order_id values, known customers, non-null nonnegative amounts, recognized normalized statuses and valid non-null dates. Return batch_row, source_order_id, normalized status and amount.

Hint: Use the ranked CTE from the example. In WHERE, a null amount does not satisfy amount >= 0. Do not cast date text before validating it.

Check before moving on

Explain why only two raw rows survive the acceptance policy, why raw evidence should be preserved, and which quality risks are not covered by these row-level checks.