Dataford
Popular roles
Software EngineerData AnalystData ScientistData EngineerBusiness AnalystAI EngineerMachine Learning EngineerProduct Manager
Browse
Browse All RolesEvery role hub, from analyst to MLBrowse All CompaniesCompany-specific interview loopsAll Interview GuidesThe full guide library
Top questions by role
Software EngineerData AnalystData ScientistData EngineerBusiness AnalystAI EngineerMachine Learning EngineerProduct Manager
Top questions by skill
SQLPythonStatisticsMachine LearningA/B TestingSystem DesignGenerative AIProduct SenseMetricsBehavioral
Browse all questions →Try a mock interview
ExperiencesCoaching
New
Practice
Mock InterviewsTimed interview simulations with feedbackSuccess PathYour 6-week structured planModulesCurated lessons by topicWebinarsTalks from ex-Big Tech data leadsPlaygroundA free-form scratch editor
Learn
BlogInterview strategy and career adviceTech Job Market ReportHiring trends across data and AI rolesFor UniversitiesDataford for career centersAbout DatafordWho we are and how we build
Pricing
Get started
Validating Newly Ingested Data
00:00
5 left

Validating Newly Ingested Data

MediumSQL · PostgreSQL

Problem

Write a complex SQL query to validate the completeness and integrity of a newly ingested dataset.

Use the provided batch, account, and event data. Identify every validation failure for the most recently loaded batch.

Output

  1. One row per validation failure, with batch_id, check_name, issue_count, and details
  2. Include only failed checks and sort by validation priority, then check_name

Output details

  • Select the batch with the greatest loaded_at, breaking ties by greatest batch_id.
  • Sort by ascending validation priority, then ascending check_name.
  • Check exact check_name values: row_count_mismatch, duplicate_source_record_key, null_account_id, unknown_account_id, invalid_event_date, and negative_amount.
  • Count duplicate source_record_key occurrences beyond the first, excluding NULL keys, and report only checks with positive issue_count.
  • Treat event dates equal to the expected minimum or maximum as valid; treat NULL dates as invalid and count only amounts less than 0.
  • Use the exact details text templates: Expected N rows, found M; N duplicate source record(s); and corresponding N event(s) ... messages.

Schema

ingest_batches
ColumnTypeDescription
batch_idPKINTUnique ingestion batch identifier
dataset_nameVARCHAR(100)Name of the ingested dataset
expected_row_countINTExpected number of event rows
expected_min_event_dateDATEEarliest permitted event date
expected_max_event_dateDATELatest permitted event date
loaded_atTIMESTAMPTimestamp when the batch was loaded
source_accounts
ColumnTypeDescription
account_idPKINTReferenced account identifier
account_statusVARCHAR(20)Current account status
ingested_events
ColumnTypeDescription
event_idPKINTUnique warehouse event identifier
batch_idINTIngestion batch identifier
account_idINTSource account identifier
event_dateDATEBusiness date of the event
amountNUMERIC(12,2)Event amount
source_record_keyVARCHAR(80)Source-system record key
ingested_atTIMESTAMPTimestamp when the event was ingested
Tablesingest_batchessource_accountsingested_events
Interviewer

Whenever you're ready, walk me through your approach or go straight to the code. The requirements and the three tables are in the Question tab.

You need to log in / sign up to run or submit.
CodePostgreSQL
Sign up free to run your codeLog inLn 1
Run your query to see results here.