Data Architecture and Pipelines Questions

Designing data storage, integration, and processing architectures. Topics include relational and NoSQL database design, indexing and query optimization, replication and sharding strategies, data warehousing and dimensional modeling, ETL and ELT patterns, batch and streaming ingestion, processing frameworks, feature stores, archival and retention strategies, and trade offs for scale and latency in large data systems.

HardTechnical

60 practiced

Design a scalable data governance and lineage solution across hundreds of pipelines and teams. The system must capture field-level lineage, support impact analysis for changes, enforce PII handling policies, and provide auditable reports. Describe instrumentation, storage choices, UI/graph model, and policy-enforcement mechanisms.

Sample Answer

Requirements clarification:- Capture field-level lineage across ETL/streaming, enable impact analysis, enforce PII handling at ingest/transform/runtime, and produce auditable reports. Assume cloud (AWS) but design is cloud-agnostic.

High-level approach:1) Instrumentation- Standardize metadata events using OpenLineage/Marquez schema emitted by every pipeline step (batch & streaming). Each event includes dataset id, schema (field-level), transformation metadata (SQL, UDF id), input->output field mappings, code commit hash, job run id, timestamp, and actor.- Integrate with SDK wrappers for Spark, Flink, Airflow tasks and with Kafka Connect connectors. Provide lightweight agents that auto-infer field mappings for simple ops (column rename/propagate) and require explicit mappings for complex UDFs; support annotation in code to declare mappings.- Automate PII detection at emission: regex rules + ML classifier (named-entity model) that tags fields with sensitivity level and confidence.

2) Storage & indexing- Raw event stream -> durable object store (S3/GCS) as append-only parquet for audit trail (immutable partitioned by date/job).- Real-time ingestion into a metadata graph DB (JanusGraph with Cassandra backend or Neo4j/DGraph) representing nodes: datasets, fields, jobs, runs, transformations, policies, users. Edges capture field-level lineage (fieldA in datasetX -> fieldB in datasetY via jobZ).- Search and fast queries backed by Elasticsearch for dataset/field/property lookups and facets.- A relational store (RDS/Cloud SQL) for policies, roles, and audit reports summaries.

3) Graph model & UI- Graph model: field nodes are first-class, with properties (name, type, pii_tag, schema_version). Transformation nodes hold SQL/AST or transformation fingerprint. Edges: reads_from, writes_to, transforms_into.- UI components: lineage explorer (zoomable graph, upstream/downstream traversal), impact analysis panel (what jobs/tables/fields would be affected by change X), field detail view (PII tags, last updated, owners, sample values safe-mode), run timeline and diff view between schema versions.- Provide programmatic APIs: get_upstream(field, depth), impact_report(change_descriptor) returning affected jobs with risk scores (PII exposure, freshness, consumers).

4) Policy enforcement & PII handling- Central Policy Engine using OPA (Rego) evaluating metadata + runtime context. Policy examples: "PII cannot be written to S3://public-bucket", "PII must be masked before export", "PII with confidence >0.9 requires owner approval".- Enforcement points: - Pre-commit CI checks: pipeline code linting that runs policy queries against declared mappings. - Runtime interceptors: SDK agents call policy engine before completing a write; blocking actions or applying transformations (masking, tokenization) when policy dictates. - Deployment gates in Airflow/Kubernetes admission controller to prevent non-compliant jobs.- Masking/tokenization library available (deterministic or irreversible) integrated into SDK and as a managed service (AWS KMS/HSM-backed tokens).

5) Auditing & reports- Immutable event log in object store; periodic ETL to analytics store produce auditable reports: who accessed what PII, when, and how transformed. Reports signed and versioned for compliance.- Exportable PDFs/CSV and an API for compliance queries (e.g., all records with field X exposed to external consumer Y between dates).- Alerting & SLA tracking: anomaly detection on lineage (sudden new downstreams for PII fields) and drift.

6) Scalability & trade-offs- Graph DB scales horizontally (Cassandra backend) for billions of field nodes; use sharding and TTL for old run metadata while retaining audit in S3.- Storage cost vs query latency: keep raw events cheap in S3; maintain hot indices in ES/graph for recent/critical assets.- Partial automation for complex UDFs: require developer annotations or lineage tests (unit tests asserting mapping), reducing false negatives.

Example: impact query- UI triggers get_downstream(field=customer.email, depth=3) -> graph traversal returns datasets, jobs, external sinks; policy engine computes exposure risk; UI suggests mitigation (mask before sink) and provides one-click enforcement patch (inject masking operator into pipeline via PR).

This design balances precise field-level lineage, enforceable PII controls, auditable immutable logs, and scalable graph-backed queries while providing developer-friendly instrumentation and automation.

MediumTechnical

56 practiced

An ingestion pipeline calls an external enrichment API and intermittently fails due to API rate limits, causing downstream job lag. Describe architectural and operational strategies to make the pipeline resilient: client-side throttling, backoff strategies, batching, local caching, queueing with dead-letter queue, circuit breakers, and testing/monitoring approaches.

Sample Answer

Start by clarifying SLAs: acceptable latency, throughput, and failure tolerance. Then apply layered architectural and operational controls so transient API rate limits don’t cause downstream lag.

Client-side throttling- Implement a global rate limiter (token bucket or leaky bucket) per API-key to ensure you never exceed known RPS. Place it close to callers (sidecar or shared library) so all workers cooperate.- Example: token bucket in a shared Redis for distributed workers.

Backoff & retry strategies- Use exponential backoff with jitter for retries to avoid synchronized retry storms.- Limit retries and switch to fallback after N attempts.- Example (python):

python

import random, time
def backoff(attempt):
    base = 0.5 * (2**attempt)
    return base + random.uniform(0, min(1, base))

Batching & aggregation- Bulk enrichment requests when API supports it to amortize per-request overhead and reduce RPS.- Use size/time windows (e.g., 1s or 1000 records) and adaptively tune based on latency.

Local caching- Cache enrichment responses (Redis/Memory) with TTL appropriate to data freshness; add cache-aside pattern to reduce API calls for repeated keys.

Queueing + DLQ- Decouple ingestion from enrichment with an intermediate durable queue (Kafka/SQS). Workers consume at controlled rate.- If message fails after retries, send to Dead-Letter Queue for later replay and investigation. Tag with failure reason and attempt count.

Circuit breaker- Use circuit breakers (open after error threshold) to stop calling the API when it's unhealthy; while open, either serve degraded results (cached/stub) or defer enrichment.- Automatically half-open after cooldown and test with a probe request.

Operational practices (monitoring & testing)- Metrics: request rate, throttled counts (429), latency, retry counts, queue lag, DLQ size, circuit state. Emit to Prometheus/Grafana and create alerts (e.g., >1% 429s or queue lag spike).- Distributed tracing (OpenTelemetry) to observe end-to-end latency and pinpoint enrichment hotspots.- Chaos & load testing: simulate 429s and spikes in staging; run spike tests to validate throttling, backoff and DLQ behavior.- Run synthetic probes against the enrichment API to detect capacity changes and dynamically adjust client limits.

Trade-offs & tuning- Batching increases tail latency for individual records — choose windowing to balance throughput vs timeliness.- Aggressive caching may serve stale data — choose TTL based on business needs.- Distributed rate limiting via Redis adds operational complexity but is necessary for many workers.

Putting it together- Ingest → durable queue → enrichment workers with distributed token-bucket + batching + cache → circuit breaker + backoff on API calls → on persistent failure send to DLQ → downstream processors. Monitor SLAs and iterate thresholds based on observed telemetry.

EasySystem Design

55 practiced

Design an archival and retention strategy for event logs where raw logs must be kept for 1 year (cold), parsed/enriched events remain hot for 90 days, and queries occasionally need access to archived data. Include lifecycle policies on object storage, compaction/partitioning for cost and query performance, and processes to restore archived data when needed.

Sample Answer

Requirements:- Raw logs retained cold for 1 year- Parsed/enriched ("hot") events kept online for 90 days- Occasional queries over archived data- Cost optimization + reasonable query latency when restoring

High-level design:- Ingest raw logs to an append-only object store (S3/GCS/Azure Blob) and stream to processing (Kafka + Spark/Beam).- Write parsed/enriched events to a hot analytics store (e.g., parquet on S3 with a query engine like Athena/Trino or a data warehouse).- Apply lifecycle policies to transition objects to colder tiers (S3 Standard → Infrequent → Glacier/Archive) and finally delete after 1 year.

Lifecycle policy examples:- Raw logs objects: - After 30 days → move to Infrequent Access - After 90 days → move to Glacier/Archive - After 365 days → Delete- Parsed/enriched datasets: - Partitioned by date; keep last 90 days in Standard - After 90 days → transition partitions to Infrequent/Archive - After 365 days → Delete (if raw + reprocessable) or keep raw for 1 year

Compaction & partitioning:- Partition by event_date / hour for recent data; compact small files into larger parquet files (e.g., 256 MB) using Spark to improve query performance.- Maintain time-based folders: /events/yyyy=2025/mm=06/dd=21/ and within partitions use file size targets.- Use columnar formats (Parquet/ORC) with ZSTD/snappy compression and pushdown predicates for efficient scans.

Cost vs query performance:- Keep recent 90 days small-file-optimized, low-latency tiers.- Archive older partitions to Glacier; expect restore latency (minutes-hours) but much cheaper.

Restore process:- Provide automated restore workflow: - User requests partition(s) via a ticket or API. - Orchestrator (Airflow/Lambda) triggers object-store restore API for selected partition keys. - Once restored, run compaction job to place files into hot partition location and update metastore/Glue catalog. - Notify user when queryable; optionally create temporary views in query engine.

Operational items:- Maintain metadata/catalog (Glue/Hive) with partition lifecycle state.- Enforce retention via scheduled jobs that validate lifecycle actions.- Monitor cost and access patterns; adjust transition windows.- If raw reprocessing is allowed, prefer deleting archived enriched data and regenerate from raw to save cost.

Trade-offs:- Archive reduces cost but increases restore latency and potential egress cost.- Keeping derived data longer speeds queries but duplicates storage; choose based on query frequency and reprocessing cost.

HardTechnical

46 practiced

Explain how to implement windowed stream joins with event-time semantics in a stateful stream processor (e.g., Flink). Cover watermark strategy, handling out-of-order events, state retention, timer usage, eviction policies, and how to ensure exactly-once semantics to downstream sinks.

Sample Answer

To implement event-time windowed joins in a stateful stream processor like Flink, design around watermarks, out-of-order tolerance, per-key state buffers, timers for event-time firing, state eviction/retention, and end-to-end exactly-once delivery.

Approach summary:- Use event-time processing with watermark generation (periodic or punctuated) that lags the max observed event time by a configured allowed lateness (maxOutOfOrderness). Handle idle partitions with WatermarkStrategy.forMonotonousTimestamps().withIdleness or assignTimestampsAndWatermarks.- Buffer left and right stream events in keyed state (e.g., ListState or MapState keyed by join-key). When an element arrives, store it and register an event-time timer for event.timestamp + joinWindowEnd + allowedLateness to attempt matching and eventual eviction.- On timer firing (onEventTime): - Probe opposite-side buffer for matching records within window bounds and emit joined results. - Remove entries older than retention threshold.- Use side outputs for late events (beyond allowedLateness) or metrics for inspection.

Example (Java KeyedProcessFunction sketch):

java

public class WindowedJoinFunction extends KeyedProcessFunction<Key, Event, Joined> {
  private ListState<Event> leftState, rightState;
  private ValueState<Long> cleanupTimer;
  public void processElement(Event e, Context ctx, Collector<Joined> out) {
    if (e.isLeft()) leftState.add(e); else rightState.add(e);
    long triggerTime = e.getEventTime() + joinWindowSize + allowedLateness;
    ctx.timerService().registerEventTimeTimer(triggerTime);
    long currentCleanup = cleanupTimer.value()==null?0:cleanupTimer.value();
    if (triggerTime>currentCleanup) cleanupTimer.update(triggerTime);
  }
  public void onTimer(long ts, OnTimerContext ctx, Collector<Joined> out) {
    // match left/right within [t-window, t+window]
    // emit joins
    // evict outdated state entries
    cleanupTimer.clear();
  }
}

Key design points:- Watermarks: choose skew based on observed lateness; too small drops late events, too large increases latency. Use per-source watermark strategies if sources have different delays.- Out-of-order handling: allowedLateness determines how long to accept late events for joining; use side outputs for beyond-lateness late records.- State retention & eviction: set TTL on state (StateTtlConfig) and also explicit eviction on timers. Avoid unbounded state growth by limiting buffer size or window length.- Timers: use event-time timers to produce deterministic, replay-safe firing tied to watermarks (not processing-time).- Exactly-once semantics: enable checkpointing; ensure stateful operator uses Flink managed state. For sinks, use transactional/2PC sinks (e.g., Flink KafkaSink with transactions or FileSink with atomic commits). This plus checkpoints ensures that on failure and restart, operator replays from the latest consistent checkpoint and duplicates are avoided.- Backpressure/scale: partition by join key; ensure key cardinality and skew are known. For high-cardinality joins consider windowed Bloom filters or pre-aggregation.

Trade-offs:- Larger allowedLateness -> better correctness for late data, higher state and latency.- Aggressive TTL -> lower state usage, risk dropping valid late matches.- Exactly-once sinks add complexity and throughput cost (transactions).

This pattern yields deterministic, event-time-correct windowed joins with controlled state and exactly-once downstream guarantees when combined with Flink checkpointing and transactional sinks.

HardTechnical

85 practiced

You're a Data Engineering lead with limited team resources. How would you prioritize platform initiatives (e.g., reliability, cost reduction, new feature requests from product teams)? Describe the framework you would use, metrics and KPIs to drive decisions, stakeholder communication approach, and a 90-day plan with key milestones.

Sample Answer

Framework:- Use a value-risk-effort matrix informed by OKRs + RICE (Reach, Impact, Confidence, Effort) for scoring initiatives. Prioritize items with high impact on SLAs/OKRs and low-to-medium effort; reserve capacity for urgent reliability incidents.

Metrics & KPIs to drive decisions:- Reliability: SLOs/SLA adherence (data freshness %, successful pipeline run %), MTTR for pipeline failures, incident frequency.- Business impact: Time-to-insight (avg time from event to analytics), number of blocked downstream tickets, revenue/metric impact tied to data features.- Cost: $/TB processed, $/query, monthly cloud infra spend by pipeline, cost per user report.- Delivery: Cycle time for platform changes, % of planned work delivered.Use dashboards with these KPIs to score and re-rank quarterly.

Stakeholder communication:- Quarterly roadmap aligned to top-level OKRs; monthly syncs with product/data consumers.- Triage cadence: weekly intake + scorecard review with product and analytics leads to re-evaluate priority.- Runbooks and SLA agreements for teams; transparent backlog with RICE scores and expected delivery windows.- Escalation paths for reliability incidents and urgent requests.

90-day plan (key milestones):- Days 0–14: Discovery & alignment — inventory platform pain points, collect blocked tickets, map costs, run RICE scoring workshop with stakeholders; define top 3 OKRs.- Days 15–45: Stabilize & quick wins — fix highest-impact reliability issues (reduce failed runs by X%), implement alerting/SLO dashboards, start cost optimization (identify 20% of idle resources).- Days 46–75: Deliver mid-sized initiatives — implement two prioritized improvements (e.g., automated retries + schema registry; or a self-serve data onboarding flow) with measurable KPIs.- Days 76–90: Measure, document, roll out — validate KPI improvements, run stakeholder review, update roadmap, hand off runbooks and training, set next quarter goals.

Outcome focus: balance short-term reliability and cost wins that unblock users while incrementally delivering product features with clear ROI.

Unlock Full Question Bank

Get access to hundreds of Data Architecture and Pipelines interview questions and detailed answers.

Join thousands of developers preparing for their dream job.