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

Build Customer Data Cleaning Pipeline

MediumPipelines00:00
Practice interviewer
In session
5 left
00:00

Whenever you're ready, walk me through how you'd approach this.

You need to log in / sign up to chat or submit.

Problem

Context

Northstar Retail receives customer and order data from its e-commerce app, CRM, and payment platform. Today, analysts manually clean CSV extracts before loading them into Snowflake, causing inconsistent metrics, duplicate records, and 1-day reporting delays.

You need to design a production-grade batch pipeline that standardizes, validates, and prepares this data for downstream analysis. The company wants a repeatable process that improves data quality while remaining simple enough for a small data team to operate.

Scale Requirements

  • Sources: Shopify exports, Salesforce CRM dumps, Stripe transaction files, and internal PostgreSQL tables
  • Volume: ~120 GB/day raw data, ~45 million records/day across all sources
  • Batch frequency: Hourly ingestion, daily backfill support for up to 2 years
  • Latency target: Data available in analytics tables within 30 minutes of file arrival
  • Retention: Raw data for 1 year, curated tables for 5 years
  • Data quality target: <0.5% invalid records in curated tables

Requirements

  1. Design an ingestion and cleaning pipeline for structured CSV/JSON data from multiple systems.
  2. Standardize schemas, data types, timestamps, currencies, and null handling across sources.
  3. Detect and handle duplicates, malformed rows, missing required fields, and referential integrity issues.
  4. Create raw, cleaned, and analytics-ready layers in Snowflake.
  5. Support idempotent reruns and historical backfills without creating duplicate outputs.
  6. Orchestrate hourly loads, dependency management, and quality checks.
  7. Provide monitoring for freshness, row-count anomalies, and failed validations.

Constraints

  • Team: 3 data engineers, no dedicated platform team
  • Infrastructure: AWS already in use; Snowflake is the analytics warehouse
  • Budget: Prefer managed services over large always-on clusters
  • Compliance: PII includes email, phone, and billing address; access must be auditable
  • Operational requirement: Analysts must be able to trace every curated record back to its raw source file