Validating Data Models | Dataford Interview Questions

Describe your process for validating data models before presenting them to stakeholders.

Use the supplied customers, policies, and policy_transactions tables. Write a query that identifies policy records whose modeled transaction counts or amounts do not reconcile with source transactions, or whose customer reference is missing.

Output

One row per invalid policy record
Include policy_id, model_status, customer_id, customer_name, modeled and source transaction counts, modeled and source amounts, and validation_status
Sort by policy_id ascending

Schema

customers

Column	Type	Description
customer_idPK	INT	Unique customer identifier
customer_name	VARCHAR(120)	Customer display name
customer_segment	VARCHAR(40)	Customer segmentation label

policies

Column	Type	Description
policy_idPK	INT	Unique policy identifier
customer_id	INT	Referenced customer identifier
model_status	VARCHAR(30)	Policy status in the modeled data
model_transaction_count	INT	Transaction count recorded by the model
model_total_amount	NUMERIC(12,2)	Total amount recorded by the model

policy_transactions

Column	Type	Description
transaction_idPK	INT	Unique source transaction identifier
policy_id	INT	Referenced policy identifier
transaction_type	VARCHAR(30)	Type of source transaction
amount	NUMERIC(12,2)	Transaction amount

Your question is Validating Data Models. Start with the requirements and the three tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem