Design how systems exchange synchronize and manage data across a technology stack. Candidates should be able to map data flows from collection through activation, choose between unidirectional and bidirectional integrations, and select real time versus batch synchronization strategies. Coverage includes master data management and source of truth strategies, conflict resolution and reconciliation, integration patterns and technologies such as application programming interfaces webhooks native connectors and extract transform load processes, schema and field mapping, deduplication approaches, idempotency and retry strategies, and how to handle error modes. Operational topics include monitoring and observability for integrations, audit trails and logging for traceability, scaling and latency trade offs, and approaches to reduce integration complexity across multiple systems. Interview focus is on integration patterns connector trade offs data consistency and lineage and operational practices for reliable cross system data flow.
HardTechnical
70 practiced
As a lead data engineer you discover a critical CRM connector is unstable and delaying a high-priority integration project. How would you prioritize fixes, allocate resources, communicate status to stakeholders, and implement process changes to reduce similar delays across other integrations? Provide a short plan that balances immediate remediation and long-term improvements.
Sample Answer
Situation: On a high-priority integration, we found the CRM connector intermittently failing, causing ingestion delays and blocking analytics deliverables with increased stakeholder pressure.Task: As lead data engineer I needed to stop the immediate impact, restore predictable delivery, and prevent similar delays across other integrations.Action — Immediate remediation (first 48–72 hours)- Triage & contain: Run a quick incident playbook — capture logs, identify failure patterns, set connector to “read-only” or pause non-critical jobs to avoid backpressure.- Hot fix prioritization: Apply the 3-minute/3-hour rule — if a configuration change or rate-limit adjustment can restore throughput in <3 hours, do it; else implement a short-lived workaround (batched retries, temporary queue).- Allocate rapid-response team: 1 senior engineer on fix, 1 SRE for infra and alerts, 1 QA to validate; others diverted from low-impact work.- Communicate: Immediate email & Slack alert to PM, analytics leads, and stakeholders with impact, ETA, mitigation steps, and next status update in 2 hours. Daily status until stable.Action — Short-term stabilization (1–2 weeks)- Root cause analysis: Post-stability RCA with logs, metrics, and replayed failures; document findings.- Harden connector: Add exponential backoff + jitter, idempotency keys, circuit breaker, and bounded queues (e.g., Kafka topic with monitoring).- Test & validate: Run load and failure-mode tests in staging; deploy canary to production.- Reprioritize backlog: Use RICE to score urgent connector fixes across integrations; assign sprint capacity accordingly.Action — Long-term improvements (1–3 months)- Observability: Standardize metrics (latency, error-rate, watermark lag) and dashboards; set SLOs/SLA for connectors with automated alerts.- Contract & schema governance: Enforce API contracts, versioning, and compatibility tests to reduce unexpected breaking changes.- Runbooks & automation: Create runbooks and automated remediation scripts (auto-retry, failover to backup connector) to reduce mean time to recovery.- Capacity & ownership model: Assign primary owner for each connector, cross-train for on-call rotation, and include connector health in quarterly operational reviews.- Process: Require integration readiness checklist (performance tests, SLAs, monitoring) before marking integrations “high-priority ready.”Result/Measure:- Short-term: Restore data delivery within 72 hours, unblock analytics sprint.- Long-term: Reduce connector-induced incidents by target (e.g., 70% in 3 months), improve MTTR by 50%, and increase stakeholder confidence via weekly health reports and SLAs.This plan balances urgent containment and fixes with structural changes (observability, governance, runbooks) to prevent recurrence while keeping stakeholders informed and prioritized work visible.
MediumTechnical
77 practiced
Describe a comprehensive testing strategy for data integrations that includes unit tests for transforms, contract/schema tests between services, integration tests against sandbox connectors, end-to-end golden dataset tests, and regression tests. Provide examples of test cases, data anonymization strategies for tests, and tooling you would use to automate tests in CI/CD.
Sample Answer
Approach summary: build layered tests that run fast in CI (unit + contract), run slower integration tests against sandboxes, and run periodic E2E golden-dataset + regression suites in staging before prod rollouts.1) Unit tests for transforms- Scope: pure, deterministic transform logic (UDFs, SQL/DBT models, Spark jobs).- Examples: - assert null-handling: transform([null,1,2]) -> expected rows - boundary cases: large strings, negative numbers, timestamp zones - dedup logic: input with duplicate keys -> single canonical row- Tooling: pytest + pytest-spark or dbt tests for SQL models. Mock external I/O, use small in-memory Spark DataFrames.2) Contract/schema tests between services- Scope: producer/consumer agreements (Avro/Protobuf/JSON Schema).- Examples: - schema compatibility (backward/forward) checks - required fields present, types match, field deprecations allowed- Tooling: Confluent Schema Registry / Avro tools, jsonschema CLI, Pact for HTTP contracts. Run CI gate that fails on incompatible schema changes.3) Integration tests against sandbox connectors- Scope: validate connector config, auth, rate-limits, incremental syncs.- Examples: - connector can authenticate and fetch sample pages - incremental cursor resumes correctly after simulated interruption- Tooling: run containers of connector (Airbyte/Custom), use ephemeral sandbox accounts or recorded VCR-style responses (see data anonymization below). Use pytest + requests, docker-compose, or Testcontainers.4) End-to-end golden dataset tests- Scope: entire pipeline on representative dataset; assert row counts, key KPIs, column-level distributions.- Examples: - given golden input X, pipeline output equals golden_output.csv (hash/row-by-row) - column-level assertions: null rate < 1%, unique user count = N- Tooling: Great Expectations for dataset expectations, dbt for lineage and tests, Spark jobs on staging. Store golden datasets in versioned S3/GCS.5) Regression tests- Scope: prevent reintroduction of past bugs.- Examples: replay previously failing input that caused schema drift or incorrect aggregations; assert bug does not reappear.- Tooling: maintain regression test suite executed in nightly runs or PR-level when related files change.Data anonymization strategies- Use synthetic data generators that mirror production distributions (Faker, mockaroo).- Tokenize/format-preserve anonymization for production extracts: hashed IDs (HMAC with key), truncation, generalization (age buckets), nulling PII.- For replaying production incidents, use reversible encryption with strict access controls or create partial sampled masked extracts combined with synthetic augmentation.- Keep keys and sensitive outputs out of CI logs; use secrets manager.CI/CD automation- Pipeline stages: - Fast checks (lint, unit tests, contract tests) on every PR — GitHub Actions / GitLab CI / Jenkins - Integration tests (sandbox containers) on merge to develop or when connector/config changes - E2E golden & regression nightly or gated before production deploy- Tooling: GitHub Actions + self-hosted runners (for Spark), Terraform for infra provisioning, Testcontainers for ephemeral services, Great Expectations + dbt+GitHub Actions reporter, Slack/PR checks for failures.- Observability: report test coverage, data diff summary, and failing expectation details. Auto-create issue templates when golden diffs exceed threshold.Why this works: layered tests localize failures (unit -> contract -> integration -> E2E), speed up feedback, enforce producer/consumer contracts, and ensure data quality while protecting privacy.
HardSystem Design
80 practiced
Design an enterprise-grade integration platform that synchronizes customer data across 20 SaaS systems and a central data warehouse. Requirements: support selective bi-directional sync, conflict-resolution rules per field, end-to-end audit trail and reconciliation, handle bursts up to 1M events/sec, be multi-tenant with per-tenant access control, support schema evolution and connector onboarding with low operational overhead. Describe key components, dataflow, control plane, monitoring, and recovery procedures.
Sample Answer
Requirements (clarify constraints):- Selective bi-directional sync per-tenant & per-object/field- Per-field conflict-resolution rules (priority, last-write, merge)- End-to-end immutable audit trail + reconciliation- Peak ingest 1M events/sec, multi-tenant isolation, schema evolution, low ops for connector onboardingHigh-level architecture:- Connectors Layer: lightweight, containerized connector agents (Kafka Connect / CDC-based) per SaaS with oauth, rate-limiters, backoff. Agents publish normalized events to secure tenant-scoped topic namespaces.- Ingestion & Streaming: Multi-tenant, partitioned Kafka cluster (or cloud Kinesis/EventBridge) with topic-per-tenant/object and tiered storage.- Transformation & Rules Engine: Stateful stream processors (Flink/Spark Structured Streaming) that apply mapping, enrichment, per-field conflict-resolution policies (policy engine supports: source priority, vector-timestamp, CRDTs, custom transforms).- Master Data Plane (MDM): A transactional materialized store (CockroachDB / Spanner) holding canonical customer records with versioning; exposes change API and supports selective sync subscriptions.- Orchestration / Control Plane: Kubernetes-based control plane with: - Tenant config service (policy, ACLs) - Connector registry and onboarding UI (templates, OAuth flows, schema discovery) - Sync scheduler, SLA profiles, backpressure controls - Audit & Reconciliation service that compares ledger vs warehouse- Data Warehouse Sync: Batch/stream loaders to DW (Snowflake/BigQuery) with change-logs using SCD2 / append-only enabled by CDC.- Audit Trail & Ledger: Immutable event store (append-only Kafka topics + compacted changelog) and per-tenant WAL in object store (Parquet) with indexed ledger entries (operation, origin, field-level diffs, vector clocks).- Security & Multi-tenancy: Tenant isolation via namespacing, RBAC, IAM integration, per-tenant encryption keys (KMS), network policies and per-connector scopes.Dataflow (example):1. Connector detects change or receives webhook -> emits normalized event to tenant.topic.object2. Stream processor validates, enriches, applies conflict-resolution (field-by-field), emits resolved change to MDM write API and audit topic3. MDM persists versioned record; change-event published to outbound topic4. Outbound connectors subscribe to outbound topic, apply connector-specific transforms, push to target SaaS; success/failure logged to audit5. DW loader consumes audit/changes to maintain warehouse tables and reconciliation viewsConflict resolution & consistency:- Per-field policy definitions stored in policy service; policies available: source-priority, last-writer-wins with vector timestamps, CRDTs for mergeable fields (sets, counters), custom lambda for complex merges.- Use causal metadata (source id, timestamp, sequence, optional vector clock) to detect concurrent updates.- For strong consistency on critical fields, support transactional compare-and-swap via MDM store.Scalability & performance:- Partition by tenant + sharding by customer-id to distribute 1M events/sec- Autoscaling Kafka brokers and stream processors; use tiered storage + compacted topics for long-term retention- Backpressure: connectors implement local buffering + DLQ; control plane throttles per-tenant throughput- Hot-tenant mitigation via adaptive partitions and separate resource poolsSchema evolution & connector onboarding:- Schema registry (Avro/Protobuf/JSON Schema) with compatibility checks; processors perform automated field mapping suggestions, optional manual overrides- Connector SDK + templated connectors with config-driven transformations, hosted marketplace for community connectors- Connector harness supports schema discovery, incremental sync, full-sync, delta-sync modesMonitoring, observability & reconciliation:- Metrics: throughput, latency (ingest-to-apply), error rates, DLQ counts, per-field conflict frequency- Traces: distributed tracing (OpenTelemetry) from connector -> MDM -> outbound- Audit UI: searchable ledger with per-field diffs, origin tracing, and replay capability- Reconciliation service runs continuous and on-demand jobs comparing MDM state vs DW and each SaaS (sampling + full reconcile); generates discrepancy reports and automated repair workflows (replay events, manual approval)- Alerts: SLA breaches, schema drift, connector failuresRecovery & operational procedures:- Idempotent events with dedupe keys; event replays supported from ledger (time-travel via compacted topics + object store)- Bulk rehydrate: snapshot + replay to rebuild MDM or resync a connector- DR: cross-region replicated Kafka + geo-redundant storage; automated failover with leader re-election- Incident runbook: isolate tenant, pause outbound syncs, enable replay mode, root-cause patch, reconcile and resume- Testing: staged sandbox tenants, contract tests for connectors, chaos tests for failoverTrade-offs:- Strong per-field consistency increases latency; provide configurable consistency levels per-tenant/field- CRDTs reduce conflicts for mergeable fields but add complexity for non-commutative data- Operational simplicity achieved via connector SDKs and templates; fully-managed connector runtime reduces per-connector ops costThis design provides an enterprise-grade, scalable, auditable integration platform with per-field conflict rules, strong multi-tenant controls, schema evolution support, and robust monitoring and recovery capabilities.
MediumTechnical
61 practiced
How would you design deduplication for a high-throughput streaming pipeline where duplicate events may appear across upstream producers and network retries? Discuss possible data structures and stores (Bloom filters, RocksDB local state, Redis/RedisBloom, Kafka compaction), memory and false-positive trade-offs, checkpointing and state persistence, retention windows, and the operational implications.
Sample Answer
Requirements & constraints:- High throughput (millions/sec), low latency, duplicates coming from multiple producers and retries, bounded retention window (e.g., 24h) acceptable, must control memory and false positives.High-level design:- Do deduplication in-stream at consumer/processor using a hybrid approach: probabilistic fast-path (Bloom/RedisBloom) + exact slow-path (local RocksDB / persistent store) within a retention window.Components & choices:- Bloom filters / RedisBloom: extremely memory-efficient for large ID space, O(1) checks, acceptable false-positive rate (configurable). Use for fast reject of obvious duplicates. Tune size/FP rate based on throughput and allowed data loss (e.g., 0.1%).- RocksDB local keyed state: store recent event IDs (or compacted fingerprints) for exact dedupe within window; persisted to disk, supports checkpointing (Flink/Beam state backend). Use TTL/compaction to evict old keys.- Redis (with RedisBloom): when state must be shared across workers or for cross-process dedupe; RedisBloom gives scalable shared probabilistic set. Beware network hop latency and throughput limits.- Kafka compaction: useful downstream for idempotent storage of latest events by key; does not prevent upstream duplicates in real-time but provides eventual dedupe for consumers and replay.Trade-offs:- Memory vs false positives: smaller Bloom => higher FP => potential data loss; RocksDB exact store => higher disk/IO and metadata overhead.- Latency vs correctness: local checks are fastest; cross-worker stores add latency.- Throughput vs persistence: writing every ID to durable store is costlier; use batching and async writes.Checkpointing & persistence:- If using streaming frameworks (Flink), use RocksDB state backend with periodic checkpoints to durable storage (S3/HDFS). Ensure recovery restores dedupe state to avoid re-processing bursts causing duplicates.- Also persist Bloom filter snapshots or rebuild deterministically (e.g., from compacted Kafka topic) on restart.Retention & eviction:- Define dedupe window based on business needs (e.g., 24–72 hours). Implement TTL-based eviction in RocksDB, rolling Bloom filters (time-partitioned Bloom per hour) to allow efficient expiry.Operational implications:- Monitor FP rate, memory, RocksDB compaction/IO, Redis memory usage, and checkpoint durations. Test recovery behavior under failure to ensure no surge of duplicates.- Capacity plan for peak ingestion + growth, and automate scaling for Redis/Kafka.- Have metrics and audit logs for discarded events (FPs) and suspected missed duplicates.Example pattern:- Per-shard: check RedisBloom; if absent, check RocksDB; if absent, accept event and write ID to RocksDB (and async add to RedisBloom). Periodically rotate Bloom/evict RocksDB entries by time windows. Downstream, write to compacted Kafka topic for durable idempotent state.This hybrid design balances throughput, memory, and correctness while providing operational observability and recovery guarantees.
HardTechnical
59 practiced
You inherited a monolithic ETL codebase with hundreds of scheduled jobs that frequently fail and are hard to maintain. Propose a pragmatic migration plan to modernize to a unified, observable, and scalable ETL platform (for example Airflow + dbt + Spark/SQL). Include discovery, grouping jobs by pattern, extraction of common primitives, incremental migration strategy with canary runs, monitoring improvements, and rollback/rollback verification.
Sample Answer
Situation: I inherited a fragile monolithic ETL codebase with hundreds of scheduled jobs that often fail and are hard to maintain. Goal: migrate pragmatically to Airflow + dbt + Spark/SQL with unified observability, minimal disruption, and safe rollback.Plan (high level)1. Discovery (2–4 weeks)- Inventory: catalog every job, provenance, schedule, owners, SLAs, failure modes, inputs/outputs, data volumes.- Run automated static analysis and runtime tracing to capture dependencies (table/file lineage), I/O, and resource use.- Score jobs by criticality (business SLA), churn (how often changed), complexity, and failure rate.2. Grouping by pattern- Cluster jobs into patterns: simple SQL loads, transform pipelines, incremental CDC, batch Spark jobs, orchestration-only glue scripts, ad-hoc reports.- For each pattern define target implementation: dbt models for SQL transforms, Spark structured streaming for CDC, Airflow DAGs for orchestration.3. Extract common primitives- Create reusable libraries: connection/credential manager, standardized extract/load operators, logging/tracing wrapper, retry/backoff policy, config schema, common transformations (parsing, dedupe, watermarking).- Publish as internal packages (PyPI/Conda) and version.4. Incremental migration with canary runs- Start small: pick low-risk, high-value jobs (one per pattern) owned by responsive teams.- Implement target pipeline: dbt for SQL transformations, Spark for heavy transforms, Airflow DAGs to orchestrate tasks using extracted primitives.- Run in parallel (shadowing): keep monolith as source of truth while new pipeline consumes same input and writes to temporary namespace.- Canary validation: compare outputs row-count, checksums, key metrics; run automated data diff tests and regression tests. Promote to production after N successful runs (e.g., 3–5).5. Monitoring and observability improvements- Centralized metrics: instrument tasks to emit Prometheus metrics and push to Grafana (success/failure, latency, data volumes).- Structured logs and tracing: adopt JSON logs, link logs to run_id and lineage ids; use distributed tracing for cross-job flows.- Data quality: integrate tests (dbt tests + Great Expectations) and SLA alerts; implement auto-notification (Slack/email) with run links and suggested remediation.- Dashboards: SLA health, job failure heatmap, resource usage, data lag.6. Rollback and verification- Maintain idempotent outputs and versioned target tables (shadow namespace + swap via atomic renames or views).- Rollback plan per job: revert DAG schedule to monolith, disable new DAG, restore last-good datasets from previous snapshots/backups, and run verification checks.- Automate verification: after rollback run reconciliation tests to ensure consumers see expected data.7. Governance, cadence, and cutover- Migration sprint cadence: each sprint migrate a cohort of jobs by pattern (2–4 weeks per cohort).- Owner sign-off: require owners to approve canary runs and metric thresholds.- Documentation & runbooks: playbooks for ops, on-call, and incident response.- Training: workshops for teams on Airflow, dbt, and primitives.Trade-offs and risks- Upfront cost for libraries and infra vs long-term maintainability. Mitigate by targeting high-failure jobs first to show ROI.- Dual-running increases cost temporarily; limit scope and automate shadowing.Result expectations- Reduced failure rate, faster debugging, standardized retries, clear lineage, measurable SLAs, and ability to scale processing with Spark.
Unlock Full Question Bank
Get access to hundreds of Data Integration and Flow Design interview questions and detailed answers.
