1. Prompt and context
A data lake receives object-storage files every day, writes them as Parquet, and commits table snapshots for dashboards and models. The team worries about truncated transfers, replaced objects, metadata that no longer matches content, and duplicate writes after retries. Design checks that locate the failure boundary instead of comparing only one final row count.
2. What the interviewer is testing
- Separate transport integrity, file corruption, table consistency, and business correctness.
- Explain that a checksum detects accidental byte changes but does not prove semantic correctness or stop a malicious rewrite by itself.
- Distinguish write-time, read-time, and background checks, with their cost and coverage.
- Provide an isolatable, replayable alert, repair, and audit workflow.
3. Questions to clarify first
- Are we preventing transfer errors, detecting silent corruption, or creating compliance evidence? Each goal changes algorithms and retention.
- Are files immutable? If in-place replacement is allowed, object version or content digest must be part of the table snapshot.
- What detection delay and false-positive rate are acceptable? Real-time checks and daily full scans have different budgets.
- Are there multiple formats, stores, or cross-region copies? Each boundary needs a fresh verification.
4. Thirty-second answer framework
“I would use four layers: verify an object checksum during upload, verify Parquet page CRCs during reads, verify the file manifest and metadata when committing a snapshot, and reconcile row counts, partition ranges, and critical measures at the business layer. Store the algorithm, digest, object version, job ID, and timestamp for each check. Quarantine failed files and rebuild them from an upstream source or trusted replica. Combine risk-based sampling with full scans, and grade alerts by dataset and blast radius. Finally, write results to an auditable table instead of leaving failures only in logs.”
5. Step-by-step reasoning
First, protect transfer. The producer computes a digest before upload and the storage service validates it; a mismatch rejects the object and triggers retry. Amazon S3 supports checksums for single-part and multipart uploads and lets clients request a checksum during download. A multipart ETag must not be treated as an MD5 of the whole object.
Second, protect the file. Parquet can checksum each data page with CRC32, allowing a reader to find corruption before decompression. Page checksums narrow the damaged area but do not enforce business rules, so the manifest should also store path, size, format version, and content digest.
Third, protect the snapshot. At commit time, create an immutable file manifest containing object version or digest, partition, row count, and writer job ID. Reject a snapshot when a file is missing, duplicated, or has a different digest; otherwise a bad file can enter downstream reads.
Fourth, reconcile business signals. For critical partitions compute row count, null rate, amount totals, and distinct-key counts, then compare them with an upstream ledger or prior version. Equal metrics do not prove equal content, so reconciliation is an independent signal beyond checksums.
Fifth, patrol and repair. Verify hot data on read and sample cold data by risk; run full checks on high-value or regulated datasets. Amazon S3 Batch Operations can asynchronously produce full-object or composite checksum reports for large sets of objects at rest. Quarantine anomalies with the original digest, detection time, and snapshot reference, rebuild from a trusted replica, and commit again only after verification.
6. High-quality sample answer
“I would not treat a checksum as the whole data-quality plan. The upload layer verifies a transport digest, the Parquet reader enables page CRCs, the snapshot commit stores an immutable manifest with object versions and writer IDs, and the business layer reconciles counts, keys, and amounts for critical partitions. Every check records its algorithm, digest, and time. A read or patrol failure quarantines the file and prevents a new snapshot from referencing it. I would verify hot data synchronously, cover ordinary data with risk-based sampling, and scan regulated data fully on a schedule. Repairs rebuild from a trusted replica and retain the original failure evidence. The layers distinguish transfer, storage, snapshot, and business-logic failures while making their cost and blind spots explicit.”
7. Common mistakes
- Mistake → Compare only total rows → replacement or duplicate rows can still pass → add object digests, distinct-key checks, and critical reconciliations.
- Mistake → Treat a multipart ETag as the file’s MD5 → the algorithm semantics do not hold → store the declared checksum type and object version.
- Mistake → Perform a full strong check on every read → latency and cost become unbounded → check hot data synchronously and patrol cold data by risk.
- Mistake → Rerun the whole pipeline after one bad file → confirmed good files may be written twice → quarantine, locate by job ID, and rebuild only affected files.
- Mistake → Use a checksum as proof of business correctness → a digest proves bytes changed or did not change → maintain independent business-quality rules.
8. Follow-up questions
What if an attacker replaces a file and updates its digest too?
A normal checksum handles accidental corruption. Add protected signatures, access control, object version locking, and an audit chain. Keep the trusted digest in metadata storage separated from write permissions and record who approved a new version.
You have only one hour per day for full scans. How do you allocate it?
Score partitions by data value, recent changes, historical failures, and replication paths. Scan the riskiest first, and cover the rest with read-time checks, sampling, and rolling windows. Report coverage and unverified windows instead of claiming the entire lake is verified.
How do you prevent a repair job from duplicating data?
Use the original file ID, target snapshot, and an idempotent write key. Before commit, check whether the manifest already contains the same content digest, then atomically update the snapshot pointer. Retries rebuild only failed files and never append files already confirmed.