Workflow Orchestration and Scheduling Questions

Orchestrating multi-step data workflows with DAG schedulers (Airflow-style): dependency management, retries, backfills, idempotent tasks, and SLA/alerting. Covers scheduling strategies, sensor/trigger patterns, and coordinating pipelines across systems. The operational glue of a data platform.

HardSystem Design

56 practiced

Design a reproducible backfill pipeline for an immutable data lake that uses dataset snapshots and lineage tracking so that auditors can verify which code produced which data. Describe how you would tie code hashes, job runs, and dataset versions together, and how replays are recorded.

Sample Answer

Requirements & constraints:- Immutable data lake (objects are append-only / versioned)- Auditors must verify which code produced which dataset snapshot- Support reproducible backfills and replays, with lineage and provenance- Scale to large batch/stream jobs and long retention

High-level design:- Object store (S3/GCS/Azure Blob) with versioned buckets + immutable prefixes for snapshots- Metadata/catalog service (e.g., Hive Metastore/Delta Lake/Apache Iceberg + central Metadata DB like PostgreSQL)- Job orchestration (Airflow/Argo) that records run metadata- Artifact registry (container/image hashes, jar/wheel) and Git commit provenance- Lineage & provenance store (graph DB or relational schema) linking code, runs, datasets- Audit UI + API for reproducibility queries

Key components & how they tie together:1. Code provenance- CI builds produce immutable artifacts: container image digest (sha256), packaged artifact with build metadata, and Git commit SHA. These three are canonical code IDs.- Store artifact metadata in Artifact Registry: {artifact_id: image@sha256, git_sha, build_time, build_job_id, dependencies_hash}

2. Dataset snapshots & immutability- Each dataset write creates a snapshot object: snapshot_id = content-addressable hash of manifest (or monotonically increasing version + snapshot hash). Snapshot metadata includes: dataset_id, snapshot_id, object_prefix, creation_time, schema_hash, record_count, checksum.- Use table formats that support snapshotting (Iceberg/Delta) and write snapshot manifest to object store under immutable prefix: s3://lake/datasets/{dataset_id}/snapshots/{snapshot_id}/manifest.json

3. Job runs & lineage linking- Orchestrator emits a run record on start/finish: {run_id, job_name, start_ts, end_ts, parameters, upstream_run_ids, user, attempt, status}- When a job writes a snapshot, it must register a Provenance Record linking {run_id, artifact_id(s), dataset_id, snapshot_id, inputs: [{dataset_id, snapshot_id}], transform_code_checksum, config_hash}- Persist provenance records into the metadata DB / graph store.

4. Backfill & replay semantics- Backfill is a special orchestrator flow that replays a historical run or re-executes job for a specified input snapshot range.- Recording: every replay run gets a replay_id and records the original intended inputs and the actual artifact_ids used. If code artifact differs from original, the system flags a "non-reproducible" flag unless the exact artifact_id (image@sha256 + git_sha) is used.- Replays should be executed using the exact artifact digest. The orchestrator pulls image@sha256 and pins configs to recorded parameter set.

5. Auditing & verification- Audit queries: given dataset_id + snapshot_id → return producing run_id(s), artifact_id(s), git_sha, manifest hash, input snapshot lineage chain, and checksums.- Verification steps for auditors: - Confirm artifact digest exists in artifact registry and matches recorded git_sha and build metadata. - Pull the artifact and optionally run in a sandbox to verify deterministic output against snapshot checksum. - Ensure inputs used match immutable snapshots recorded.

Data model (core tables/objects):- artifacts(artifact_id, image_digest, git_sha, build_job, build_time)- runs(run_id, job_name, start_ts, end_ts, user, params_hash, replay_of_run_id?, replay_id?)- snapshots(snapshot_id, dataset_id, manifest_hash, object_prefix, schema_hash, row_count, checksum, created_by_run_id)- provenance(provenance_id, run_id, artifact_id, dataset_id, snapshot_id, inputs_json, config_hash, deterministic_flag)

Operational notes & best practices:- Enforce CI to produce artifacts tied to Git SHA and push mutability-free images (no mutable tags).- Use content-addressable snapshotting (manifest hashes) so identical outputs map to same snapshot_id.- Enforce that production runs must register provenance atomically with snapshot commit (transactional): either both run & snapshot metadata persist or rollback.- Store minimal replay logs: parameters, environment, secrets redaction, and exact artifact digests.- For large replays, support partial reprocessing using input snapshot ranges and idempotent transforms.

Trade-offs:- Strong reproducibility requires strict artifact pinning and CI discipline; more developer friction.- Storing every snapshot forever increases cost—use lifecycle rules for older snapshots but retain provenance metadata.- Graph DB improves lineage queries; relational metadata simpler to operate.

Example replay verification flow:1. Auditor queries snapshot S -> sees run R and artifact A.2. Orchestrator spins a sandbox pulling image A@sha256 and inputs S.inputs snapshots.3. Run produces new snapshot S' — compare manifest_hash/checksum to S. If match, reproducible; else flag divergence and surface differences in logs and dependency hashes.

This design ensures every dataset snapshot is tied to the exact code artifact and run that produced it, supports deterministic replays when artifacts are pinned, and exposes lineage for auditors to verify provenance.

MediumSystem Design

44 practiced

Design an Airflow DAG (describe components and high-level code) to implement an incremental daily ETL with these constraints: detect new rows in a MySQL source, extract changes, transform via Spark, and load into a partitioned data warehouse. Scale target: ~1M source rows/day; end-to-end target runtime <2 hours. Discuss sensors vs event triggers, checkpointing, handling late-arriving records, and backfill strategy.

Sample Answer

Requirements & constraints:- Daily incremental ETL detecting new/changed rows in MySQL (~1M rows/day), transform in Spark, load into partitioned DW, runtime <2h.

High-level architecture:- MySQL -> change detection (CDC/timestamp) -> Airflow DAG -> Extract task -> Stage in cloud storage (Parquet) -> Spark transform job -> Load to partitioned DW (date/hour partitions) -> Quality checks & commit.

DAG components (Airflow Python):- start -> check_for_changes (sensor or query) -> extract_mysql -> write_to_s3 -> spark_transform -> load_dw -> qc -> mark_checkpoint -> end

Example DAG (simplified):

python

from airflow import DAG
from airflow.operators.python import PythonOperator
from airflow.providers.apache.spark.operators.spark_submit import SparkSubmitOperator
from airflow.providers.amazon.aws.sensors.s3 import S3KeySensor
from datetime import datetime, timedelta

with DAG('daily_incremental_etl', start_date=datetime(2025,1,1), schedule_interval='@daily', catchup=False, default_args={'retries':1}) as dag:
    def detect_changes(**ctx):
        # query max(updated_at) checkpoint from metastore and select rows where updated_at > checkpoint
        pass

    detect = PythonOperator(task_id='detect_changes', python_callable=detect_changes)
    extract = PythonOperator(task_id='extract_mysql', python_callable=lambda **kw: ... )  # export to s3/parquet
    spark = SparkSubmitOperator(task_id='spark_transform', application='s3://bucket/scripts/transform.py', conf={'spark.executor.memory':'4g'})
    load = PythonOperator(task_id='load_dw', python_callable=lambda **kw: ... )  # upsert into DW partitions
    qc = PythonOperator(task_id='quality_checks', python_callable=lambda **kw: ... )
    detect >> extract >> spark >> load >> qc

Key design decisions & reasoning:- Change detection: prefer CDC (binlog) or incremental by updated_at with stored checkpoint (max updated_at). CDC reduces missed changes and handles deletes.- Sensors vs event triggers: Use event triggers (S3/event-driven or message on CDC topic) for latency and resource efficiency; fallback to time-based schedule and lightweight query-sensor if events not available.- Checkpointing: store last_processed_timestamp and last_primary_key in durable metastore (e.g., DynamoDB/Postgres). Use watermark + processed PK list for tie-breakers to guarantee exactly-once or idempotent loads.- Handling late-arriving records: implement a configurable lookback window (e.g., process data with updated_at <= run_time - grace_period) and support daily reprocessing of recent partitions (e.g., reprocess last 3 days) or append a “dirty” table and run reconciliation job.- Backfill strategy: use Airflow catchup or manual backfill DAG that reads full range, partitions parallelized by date and primary-key ranges; submit Spark jobs per partition to meet 2h SLA.- Scaling & perf: extract to columnar Parquet, partition by date, use compressed files ~128MB, tune Spark parallelism, autoscale cluster.- Idempotency & QA: write outputs to staging partitions and run row-count & checksum checks before swap/commit. Record job metrics and failure alerts.

This design balances reliability (CDC/checkpoints), scalability (Spark + partitioning), and operational efficiency (event triggers, idempotent loads, backfill controls).

MediumTechnical

42 practiced

Design an alerting strategy for an external API ingestion that sometimes returns partial or delayed data. Specify what metrics to monitor, how to detect missing or delayed records, appropriate alert thresholds, who to page, and what automated remediation (if any) should run before paging a human.

Sample Answer

Requirements and goals (brief): detect when the external API returns partial or delayed data so downstream analytics maintain SLOs (freshness, completeness), avoid alert fatigue, and automate safe remediation before paging.

Metrics to monitor- Ingest throughput: records/sec and bytes/sec (per endpoint).- Completeness ratio: actual_records / expected_records (expected from historical baseline or partner contract).- Ingest latency / watermark delay: time between event timestamp and ingest arrival.- Error rate: non-2xx responses, schema errors, partial payload flags.- Retries and 429/503 frequency.- Heartbeat: last-success timestamp per endpoint.

Detecting missing or delayed records- Expected vs actual: maintain rolling expected_count per window (hour/day) using moving average and business rules (weekdays vs weekends). Trigger when actual < expected * (1 - tolerance).- Watermark lag: alert when max(event_time) < now - allowed_freshness.- Schema/field-level checks: monitor presence of required fields and proportions (e.g., percentage of records with customer_id).- Use deduped IDs or sequence numbers from provider when available to detect gaps.

Alert thresholds (two levels)- Warning (no paging): actual < 90% expected for 30 minutes OR watermark lag > SLA/2 OR transient 5xx spike > 5% for 10 min. Send Slack, create incident in monitoring, run automated remediation.- Critical (page on-call): actual < 70% expected for 60 minutes OR watermark lag > SLA (e.g., 4 hours) OR sustained authentication/authorization failures or schema breaking changes. Page data-engineering on-call and notify API provider owner.

Who to page- Primary: data-engineering on-call (responsible for pipelines).- Secondary: SRE if infrastructure (connectors, network) suspected.- Tertiary: API provider contact if evidence points to upstream outage or contract breach.

Automated remediation before paging- Retry with exponential backoff and jitter for transient HTTP 5xx/429.- Circuit breaker: pause retries and switch to backup ingestion (queued snapshots, cached endpoints) if available.- Refresh credentials/token automatically (with safe guard: only if token-expiry error observed).- Switch to queued ingestion (buffering requests to Kafka/SQS) and mark downstream with partial-data flag.- Attempt a lightweight replay of recent pages if API supports pagination.- Run schema-tolerant parsing (ingest partial records into staging with flag) so downstream can continue.- After N automated attempts or if remediation fails/potential data loss detected, escalate to human.

Operational details- Instrument runbooks: exact checks to run, logs to inspect, Playbook for token rotation, commands to restart connector, and contact list for provider.- SLOs and alerts tuned to business impact (e.g., hourly freshness SLO 99.9% → tighter thresholds).- Alert deduplication and suppression windows to prevent storms.- Post-incident: add synthetic monitoring (end-to-end test calls with known records) and adjust expected baselines if behavior changes.

This strategy balances automation (retry, buffering, credential refresh) with clear thresholds for paging, and uses both volume and watermark checks to detect partial/delayed data.

MediumTechnical

40 practiced

How would you manage secrets (database credentials, API keys) for tasks running in an orchestrator across dev/staging/prod environments? Discuss Airflow Connections/Variables, Kubernetes Secrets, HashiCorp Vault integration, secret injection at runtime, rotation policies, auditing, and least-privilege access patterns.

Sample Answer

Situation: As a Data Engineer, I treat secrets as first-class infrastructure: centrally managed, injected at runtime, rotated automatically, and auditable. My approach balances usability for pipelines with strong security controls.

Design/Components:- Airflow: store non-sensitive metadata in Connections/Variables, but never plaintext secrets in DAGs. Use Airflow’s secrets backends (HashiCorp Vault, Kubernetes Secrets, AWS Secrets Manager) so Connections pull credentials at runtime. Configure Airflow’s secrets backend (e.g., Vault) to map connection URIs.- Kubernetes: keep secrets out of container images. Use Kubernetes Secrets for simple use-cases, mounted as files or exposed as env vars for short-lived pods. Prefer CSI Secrets Store or projected volumes to avoid env-var leakage.- HashiCorp Vault: central secret store with dynamic credentials (DB roles), leasing, and auto-rotation. Integrate via Vault Agent Injector or Airflow Vault backend.

Runtime injection patterns:- For Airflow tasks running in K8sPodOperator: use Vault Agent sidecar or CSI driver to mount secrets into /var/run/secrets; reference files in the task. Avoid env-vars for long-lived services.- For Spark/EMR jobs: fetch short-lived tokens at job bootstrap using instance role and Vault OIDC/Kubernetes auth.

Example (K8s secret mount):

yaml

apiVersion: v1
kind: Pod
spec:
  containers:
  - name: worker
    image: my-job
    volumeMounts:
    - name: secret-vol
      mountPath: /etc/creds
      readOnly: true
  volumes:
  - name: secret-vol
    secret:
      secretName: my-db-creds

Rotation & least privilege:- Use dynamic DB credentials from Vault (per-role, TTL). Rotate static secrets regularly; automate with CI/CD and Vault/Secrets Manager lifecycle hooks.- IAM / roles: grant Airflow scheduler/workers minimal Vault/K8s permissions using Kubernetes service accounts and vault policies. Use short-lived tokens and bound identities (Kubernetes auth).

Auditing & monitoring:- Enable Vault audit devices and K8s API audit logs. Log Airflow secret access events (via secrets backend) and centralize logs in SIEM. Alert on anomalous access patterns or failed auth attempts.

Trade-offs:- Vault adds operational complexity but provides dynamic secrets and rotation — recommended for prod. Kubernetes Secrets are simpler but require extra controls (encryption at rest, RBAC, avoid env-vars) for security.

This setup ensures pipelines get credentials only when needed, with rotation, auditability, and least-privilege access.

EasyTechnical

45 practiced

In Airflow, what is the difference between an Operator and a Sensor? Provide examples of each and discuss trade-offs between poll-based sensors and event-driven approaches (e.g., webhooks or message triggers), including resource usage and reliability.

Unlock Full Question Bank

Get access to hundreds of Workflow Orchestration and Scheduling interview questions and detailed answers.

Join thousands of developers preparing for their dream job.