Data Pipeline Architecture Questions

Design end to end data pipeline solutions from problem statement through implementation and operations, integrating ingestion transformation storage serving and consumption layers. Topics include source selection and connectors, ingestion patterns including batch streaming and micro batch, transformation steps such as cleaning enrichment aggregation and filtering, and loading targets such as analytic databases data warehouses data lakes or operational stores. Cover architecture patterns and trade offs including lambda kappa and micro batch, delivery semantics and fault tolerance, partitioning and scaling strategies, schema evolution and data modeling for analytic and operational consumers, and choices driven by freshness latency throughput cost and operational complexity. Operational concerns include orchestration and scheduling, reliability considerations such as error handling retries idempotence and backpressure, monitoring and alerting, deployment and runbook planning, and how components work together as a coherent maintainable system. Interview focus is on turning requirements into concrete architectures, technology selection, and trade off reasoning.

EasySystem Design

55 practiced

Design a simple nightly batch ETL DAG using Apache Airflow: show DAG structure, task types (extract, transform, load), sensors or dependencies for upstream data availability, retry and SLA settings, how to prevent concurrent runs, how to capture lineage/metadata, and how you would alert on failures. Be specific about operator choices and scheduling parameters.

Sample Answer

Requirements (clarified):- Nightly ETL at 02:00 for previous day's data- Ensure upstream files available, single concurrent run, retries/SLA, lineage metadata, alerting

High-level DAG design:- schedule_interval="0 2 * * *" (daily at 02:00)- catchup=False, max_active_runs=1, concurrency=1, default_args with retries and SLA at task-level- Sensors: upstream file/object sensors before extract- Tasks: sensor -> extract -> transform -> load -> notify- Operators: File/GCS/S3 sensors; SparkSubmitOperator or DataprocSubmitOperator for heavy transforms; PythonOperator or BashOperator for small tasks; BigQueryInsertJobOperator / PostgresHook for load- Lineage: emit OpenLineage events (openlineage-airflow) or call Marquez/OpenLineage hook; push metadata to XCom; write run metadata to metadata DB- Alerts: on_failure_callback to send Slack/email/PagerDuty; integrate with Sentry for exceptions

Example DAG (concise illustrative snippet):

python

from airflow import DAG
from airflow.providers.google.cloud.sensors.gcs import GCSObjectExistenceSensor
from airflow.providers.apache.spark.operators.spark_submit import SparkSubmitOperator
from airflow.providers.google.cloud.operators.bigquery import BigQueryInsertJobOperator
from airflow.operators.python import PythonOperator
from airflow.utils.dates import days_ago
from datetime import timedelta

default_args = {
    'owner': 'data_eng',
    'retries': 3,
    'retry_delay': timedelta(minutes=10),
    'email': ['oncall@example.com'],
    'email_on_failure': False,  # using on_failure_callback instead
}

def failure_alert(context):
    # send Slack/PagerDuty/Sentry event with context
    pass

with DAG(
    dag_id='nightly_etl',
    default_args=default_args,
    description='Nightly ETL: sensor -> extract -> transform -> load',
    schedule_interval='0 2 * * *',
    start_date=days_ago(1),
    catchup=False,
    max_active_runs=1,
    tags=['etl'],
    on_failure_callback=failure_alert,
) as dag:

    wait_for_file = GCSObjectExistenceSensor(
        task_id='wait_for_source',
        bucket='raw-data-bucket',
        object='incoming/{{ ds }}/data_*.parquet',
        timeout=60*60,
        poke_interval=60,
        mode='reschedule'
    )

    extract = PythonOperator(
        task_id='extract',
        python_callable=lambda **ctx: None,  # implement extraction or use Dataflow
        provide_context=True
    )

    transform = SparkSubmitOperator(
        task_id='transform',
        application='/opt/airflow/dags/jobs/transform.py',
        name='nightly_transform',
        conf={'spark.executor.memory': '4g'},
        application_args=['--date','{{ ds }}']
    )

    load = BigQueryInsertJobOperator(
        task_id='load',
        configuration={
            "query": {
                "query": "INSERT INTO dataset.table SELECT * FROM staging.table_{{ ds }}",
                "useLegacySql": False
            }
        }
    )

    publish_lineage = PythonOperator(
        task_id='publish_lineage',
        python_callable=lambda **ctx: None  # call OpenLineage / Marquez client using XCom metadata
    )

    wait_for_file >> extract >> transform >> load >> publish_lineage

Key operational details and reasoning:- Sensor choice: use cloud-specific object sensors (GCS/S3) instead of generic FileSensor; use mode='reschedule' to avoid worker slot blocking.- Prevent concurrent runs: max_active_runs=1 and concurrency=1 on DAG; set depends_on_past=False but use task-level idempotency (use job-run id).- Retries/SLA: default_args retries=3, retry_delay=10m; set task.sla=timedelta(hours=3) for SLA alerts in Airflow UI; on_failure_callback handles immediate alerting to Slack/PagerDuty.- Lineage/metadata: capture dataset names, row counts, run id and schema in XComs; publish to OpenLineage/Marquez after successful load; store run metadata to a metadata table for auditing.- Idempotency & data quality: each task writes to a staging path with run_id/ds; transform should write atomically (write to temp then rename). Add simple QA task (row count and null checks) before load; fail fast on quality breaches.- Monitoring: use Airflow SLA misses, task failures, DAG duration metrics; integrate with Prometheus/Grafana for pipeline latency and success rate.

Edge considerations:- Large upstream files: consider pre-signed URL ingestion or object change notification sensor to trigger earlier.- Long-running Spark jobs: set task-level execution_timeout to avoid runaway jobs.- Security: use least-privileged service accounts and store credentials in Airflow Connections/Secrets backend.

This design provides clear ordering (sensor → extract → transform → load), robust retries and alerting, single-run guarantees, and lineage capture using OpenLineage/XComs for traceability.

HardTechnical

48 practiced

Compare Avro, Parquet, and ORC across common pipeline workloads: streaming ingestion, OLAP analytical queries, and ML feature store storage. For each format discuss columnar vs row characteristics, compression, predicate pushdown, schema-evolution support, and read/write performance trade-offs.

Sample Answer

Situation: When choosing a storage format for pipelines, I evaluate trade-offs across workloads: streaming ingestion, OLAP queries, and ML feature-store storage. Below is a focused comparison of Avro, Parquet, and ORC across columnar vs row characteristics, compression, predicate pushdown, schema evolution, and read/write trade-offs.

Summary by format:- Avro: - Row-oriented (record-based), optimized for write-heavy, streaming ingestion and RPC. - Compression: block-level codecs (snappy, deflate) yield small write/serialize overhead; good for transport. - Predicate pushdown: limited—row format prevents efficient column pruning; can still filter at record level. - Schema evolution: excellent—explicit schema with forward/backward compatibility, default values, field-level changes. - Performance trade-offs: very fast writes and low-latency streaming; not ideal for analytical scans or columnar-heavy ML feature reads.

- Parquet: - Columnar, page/column chunk layout, excellent for OLAP and analytical scans. - Compression: column-level codecs (snappy, gzip, zstd, brotli) with per-column encoding (dictionary, RLE) — high compression and CPU tradeoffs. - Predicate pushdown: strong — statistics (min/max, null counts) enable skip of row groups and pages. - Schema evolution: good — supports adding nullable columns; renames/complex changes require care (tooling like Spark helps). - Performance trade-offs: read-optimized, great for selective column reads and aggregation; writes are heavier (buffering/columnization), not ideal for per-record streaming.

- ORC: - Columnar like Parquet, with sophisticated stripe/column index structures. - Compression: excellent — built-in codecs (snappy, zlib, zstd) plus lightweight encodings; often slightly better compression than Parquet for some workloads. - Predicate pushdown: very strong — fine-grained column statistics and lightweight indexes enable aggressive skipping. - Schema evolution: solid — supports adding/removing fields, but cross-engine compatibility varies. - Performance trade-offs: optimized for fast analytical reads and vectorized processing (Hive/Spark); write cost similar to Parquet, sometimes faster reads for complex nested types.

Workload recommendations:- Streaming ingestion: Avro (low-latency writes, schema registry). If you must produce columnar files for downstream OLAP, buffer and convert to Parquet/ORC in micro-batches.- OLAP analytical queries: Parquet or ORC. Choose ORC where Hive/Hadoop ecosystem and heavy aggregation/vectorized SQL dominate; choose Parquet for broad cross-platform support (Spark, Presto, BigQuery).- ML feature store storage: Parquet often preferred (columnar reads, wide tool support, fast selective retrieval). ORC is competitive if your stack favors it. For online feature store (low-latency row reads), use a KV store or Avro/row-store for fast single-row access.

Final trade-offs:- Avro = write/streaming, schema evolution, low-latency per-record IO.- Parquet = cross-platform columnar balance, wide tooling, good compression & predicate pushdown.- ORC = aggressive columnar optimizations, slightly better compression/indexing for heavy OLAP.

In practice, use Avro for ingestion + schema registry, then convert to Parquet/ORC for analytical/feature-store workloads; pick Parquet for portability, ORC for highest OLAP throughput in Hadoop-native environments.

HardTechnical

61 practiced

You must decide between managed cloud services (e.g., AWS MSK, BigQuery) and a self-managed open-source stack (Kafka on VMs, Presto/Trino) for your data platform. Present a decision framework covering operational cost and staffing, SLAs and support, vendor lock-in, performance, long-term maintainability, and migration risk.

Sample Answer

Situation: Our team must choose between managed cloud services (e.g., AWS MSK, BigQuery) and a self-managed open-source stack (Kafka on VMs, Presto/Trino) for the company’s data platform.

Decision framework (apply weighted scoring per business needs):

1. Operational cost & staffing- Questions: Ongoing cloud bills vs. people cost to run infra (SREs, ops, patching).- Consider: TCO over 3–5 years, peak vs. steady usage, automation maturity.- Guidance: Choose managed when ops headcount is limited or need rapid time-to-value; choose self-managed if sustained high load where resource efficiency offsets staffing.

2. SLAs & support- Questions: Required uptime, RTO/RPO, compliance.- Consider: Managed SLAs, paid support tiers, escalation paths vs. in-house on-call and runbooks.- Guidance: Critical SLAs favor managed with an enterprise support contract.

3. Vendor lock-in- Questions: Data egress cost, proprietary features, portability of schemas/pipelines.- Consider: Managed often increases lock-in (query dialects, storage formats); mitigations include abstraction layers, open formats (Parquet/ORC), CI for infra-as-code.- Guidance: If portability is strategic, prefer self-managed or strict abstractions on top of managed services.

4. Performance- Questions: Latency, throughput, concurrency patterns.- Consider: Benchmarks on representative workloads; managed services excel in scale-on-demand; self-managed can be tuned for specialized workloads.- Guidance: Do proof-of-concept benchmarks under realistic loads before choosing.

5. Long-term maintainability- Questions: Upgrades, version compatibility, community velocity.- Consider: Managed offloads upgrades; self-managed requires upgrade plans and compatibility testing.- Guidance: Managed reduces maintenance burden; self-managed requires defined upgrade cadence and automation.

6. Migration risk- Questions: Data transfer volume, schema/SQL differences, downtime windows.- Consider: Pilot migrations, dual-write strategies, feature parity gaps.- Guidance: Use incremental migration, abstractions, and runbooks; estimate migration effort and risk-adjust costs.

Decision process- Assign weights to criteria per business priorities (e.g., reliability 30%, cost 25%, portability 20%, performance 15%, migration risk 10).- Run POC for 4–8 weeks validating costs, perf, and operational effort.- If POC shows managed meets SLAs, latency and cost targets and reduces ops burden, choose managed; otherwise choose self-managed with committed automation investment.

Example outcome- For a startup with small ops: choose BigQuery/MSK for speed and low ops.- For a data-intensive company with custom performance needs and preference for portability: choose self-managed with strict CI/CD and automated scaling.

This framework makes the trade-offs explicit, ties them to measurable pilots, and reduces political/technical bias in the final decision.

EasyTechnical

57 practiced

Contrast data warehouses and data lakes for an analytics platform. Discuss storage formats, query patterns, access controls, cost and performance trade-offs, governance considerations, and when to place curated datasets into a warehouse versus leaving raw data in a lake.

HardTechnical

63 practiced

You inherit a production pipeline that intermittently loses ~10% of events. Outline an incident response plan: immediate mitigation to stop ongoing data loss, short-term fixes to restore missing data for critical consumers, long-term remediation steps to prevent recurrence, runbook updates, and metrics you would track during recovery.

Sample Answer

Situation: Production event pipeline intermittently drops ~10% of events, affecting downstream analytics and alerting.

Immediate mitigation (stop ongoing loss — first 0–2 hours)- Switch pipeline to safest mode: pause non-critical sinks and enable durable buffering (turn on topic/queue retention, increase ACKs, disable at-most-once paths).- Route incoming traffic to a parallel read-through passthrough that writes raw events to durable cold storage (object store with append) so no new data is lost.- Increase logging and enable verbose tracing for a small sample; capture IDs/offsets of lost ranges.- Communicate an incident banner to stakeholders with ETA and impacted consumers.

Short-term fixes (restore missing data for critical consumers — 2 hours to 3 days)- Identify missing event windows via sequence IDs, offsets, or watermark gaps. Produce a “delta” list of missing keys.- If source supports replay: replay from source into a recovery job that deduplicates and backfills critical topics/tables.- If not replayable: reconstruct from durable raw store (ingest raw files), or rebuild approximations using auxiliary sources (API logs, application DBs).- Prioritize SLAs: restore critical consumer tables first (analytics, billing). Validate with checksums, row counts, and business-key reconciliation.

Long-term remediation (prevent recurrence)- Root-cause analysis (postmortem) to identify failure mode (ack/NACK bugs, transient broker loss, consumer crashes, batching/truncation).- Implement fixes: make pipeline idempotent, add exactly-once or at-least-once guarantees (transactional writes, stronger ACKs), increase retention, add consumer backpressure handling and retries with exponential backoff.- Add automated replays (easy replay from source or raw lake), and durable write-ahead log for every ingest.

Runbook updates- Add playbook: how to detect >1% loss, steps to enable buffering/passthrough, commands to trigger replay, verification queries, owners, and communication templates.- Include postmortem checklist, rollback procedures, and decision matrix for pausing sinks vs. replaying.

Metrics to track during recovery- Ingest rate vs. expected, error/ drop rate, offset lag, retention utilization, replay throughput, duplicate ratio, downstream table row counts and hash checksums, consumer restart frequency, and business KPIs (e.g., revenue events restored).- Dashboards with alerts for anomaly thresholds and a “recovery progress” bar per consumer.

Outcome & learning- Restore critical data first, iterate on fixes, and convert incident insights into automated safeguards so future loss is detected and self-healed.

Unlock Full Question Bank

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

Join thousands of developers preparing for their dream job.