Differentiate structural null values from missing data

Identify whether null values represent expected business logic or export dropouts. Use it when auditing schema quality or preparing datasets for modeling.

You getNull Audit Table, root-Cause Diagnosis and dbt or SQL Assertion Rules

Prompt

Prompt
Act as an analytics engineer. Review the table schema, null distribution stats, and sample records below to distinguish structural nulls from true missing data.
- Classify every null occurrence as either Structural (expected non-applicability based on entity type or status) or Data Defect (pipeline dropout, network drop, unmapped field).
- Define dependent conditions that explain why the field is legitimately blank (such as null cancellation dates for active subscriptions).
- Specify data quality validation tests to catch future unintended nulls without alerting on expected ones.
Output format:
1. Null Audit Table: Column Name, Observed Null %, Classification, Business Condition
2. Root-Cause Diagnosis: Explanation for fields identified as defects
3. dbt or SQL Assertion Rules: Specific conditional tests to monitor incoming pipelines

The table schema and null summaries are below:

Example output

Example for: customer billing and subscription churn dataset

1. Null Audit Table
Column NameObserved Null %ClassificationBusiness Condition
churn_date74.2%Structural NullLegitimate: Customer is currently active (status = 'ACTIVE').
cancellation_reason74.2%Structural NullLegitimate: Applies only when churn_date IS NOT NULL.
payment_gateway_id4.8%Data DefectDefect: Every completed transaction must have a gateway reference ID.
shipping_address_line_268.0%Structural NullLegitimate: Optional field for standalone single-family residences.
tax_amount3.1%Data DefectDefect: Nulls appear on non-exempt sales where rate is greater than 0%.
2. Root-Cause Diagnosis
  • payment_gateway_id: The 4.8% null rate coincides with manual wire payments processed outside the automated checkout cart. The ingestion pipeline did not assign a synthetic fallback transaction key for offline invoices.
  • tax_amount: Caused by timeout drops in the third-party tax API during flash-sale traffic spikes, resulting in empty values instead of zero or calculated amounts.
3. SQL Assertion Rules
-- Test 1: Flag missing payment gateway references on completed transactions
SELECT order_id
FROM orders
WHERE status = 'COMPLETED'
  AND payment_gateway_id IS NULL;

-- Test 2: Ensure churn date exists only for churned status
SELECT customer_id
FROM subscriptions
WHERE (status = 'CHURNED' AND churn_date IS NULL)
   OR (status = 'ACTIVE' AND churn_date IS NOT NULL);
Written by AI from this prompt. Your results will vary.

Paste your table schema, column descriptions, and null counts into Gemini, Claude or ChatGPT. As a next step, ask the model to produce generic dbt tests or Great Expectations expectations based on the output.

Make it repeatable

Keep using this prompt without pasting it in every time.

Keep exploring

More in Data Analysis