Estimation and Scheduling Questions

Requirements summary: 99.9% availability (~43.2 min downtime/year), zero data loss. Design a conservative, observable phased rollout: Dev → Canary → Ramp → Full.

Dev (internal QA)

• Scope: end-to-end in non-prod with synthetic and replayed production traces.
• Validate: schema compatibility, exactly-once or idempotent writes, retries/backpressure behavior, connector offsets, checkpointing, retention, alarms integration.
• Monitoring: unit test pass, integration test coverage, E2E latency with synthetic load.
• Rollback: fix in branch; no production impact.

Canary (small subset of prod)

• Traffic: 0.5–2% of producers or partitions (pick low blast radius).
• Validate: consumer end-to-end correctness, no message loss, offset progression, duplicate rate, commit/ack guarantees, and compatibility with downstream jobs.
• Monitoring (must be live): input throughput, per-partition lag, consumer error-rate, retry counts, commit failures, processing latency, broker errors, disk/CPU, GC, SLO slips.
• Acceptance thresholds: lag within X (e.g., <5s for streaming), error-rate <0.1% above baseline, no data loss for 24–48h.
• Rollback: immediate re-route canary traffic off service if error-rate or sustained lag breach thresholds; replay canary input after rollback.

Ramp (progressive increase)

• Traffic steps: 5% → 20% → 50% with 1–3 hour observation windows for steady-state; longer (24h) at 50%.
• Validate: autoscaling behavior, cross-region replication, backpressure propagation, long-tail latency, checkpoint recovery after simulated failures.
• Monitoring: same metrics aggregated; add SLA calendar (availability), end-to-end data completeness checks, downstream processing success rate.
• Acceptance: stable metrics within thresholds and no missed checkpoints for 24 hours at 50%.
• Rollback: step-back to previous stable percentage if thresholds breached; if persistent, rollback to previous release and trigger postmortem.

Full

• Traffic: 100% after sustained stability (>=72h at ≥50% and 24h at 100% dry-run for non-critical producers).
• Validate: long-term retention, failover drills, disaster recovery, and sustained SLO adherence.
• Monitoring: alerting on lag, error-rate, write failures, commit latency, consumer groups stuck, data completeness. Add business-level checks and daily reconciliation.
• Rollback: staged failover to old ingestion path or pause producers; require runbook and hotfix cadence.

Additional practices

• Canary isolation by producer, tenant, or partition; consumer-side feature flags; automatic traffic steering via load balancer or topic routing.
• Strong guarantees: end-to-end checksums, seq-numbering, idempotent sinks, durable checkpoints, and retention to allow replay.
• Automated playbooks: automated rollback triggers, runbooks for manual intervention, and postmortems for any incident.

Situation: Preparing ETL pipelines for production go‑live — below is an operational readiness checklist tailored for a Data Engineer to ensure a safe, observable, and reversible launch.

Environment provisioning

• Infrastructure provisioned (dev/stage/prod) and IaC applied (Terraform/ARM/CloudFormation)
• Resource sizing validated (compute, storage, network, IAM)
• Secrets stored in vault (e.g., AWS Secrets Manager) and accessible to services
  Acceptance: infra deployed via pipeline; smoke tests pass.

Runbooks

• Runbook documents created for each pipeline (purpose, dependencies, schedule, input/output locations)
• Runbook includes step-by-step start/stop, data validation, common errors, and escalations
  Acceptance: runbooks stored in central repo (Confluence/Git), versioned.

Runbook owners assigned

• Primary and secondary owners named with contact info and on-call rotation
  Acceptance: owners acknowledged and reachable; Slack/phone escalation paths verified.

Monitoring & alerts

• Metrics instrumented (job success/failure, latency, throughput, data volume)
• Dashboards configured (Grafana/CloudWatch/Datadog) for SLOs
• Alerts set (severity levels), tested, and routed to on-call
  Acceptance: test alert fires and is acknowledged by owner.

Security scans

• Static and dependency scans complete; infra scanned for misconfigurations (CIS, Prisma, AWS config)
• Data access reviewed (principle of least privilege), PII handling validated
  Acceptance: open high/critical findings remediated or documented risk accepted.

Data reconciliation scripts

• Automated reconciliation and row-count scripts ready (source vs. target comparison, checksums)
• Baseline sample runs completed on historical data
  Acceptance: reconciliation outputs match expected thresholds.

Rollback procedures tested

• Clear rollback plan: stop ingestion, restore previous datasets/snapshots, re-run ETL, and validate
• Dry-run executed in staging with timed runbook steps and recovery time recorded
  Acceptance: rollback completed within RTO and validated by data checks.

Pre-go / final checks

• End-to-end smoke run on production with synthetic or backfill data
• SLA/SLOs agreed and communicated
• Stakeholders notified of go-live window
• Post-go monitoring window scheduled and incident playbook ready

This checklist should be tracked in a pre-launch runbook with sign-offs for each acceptance criterion.

Requirements & constraints:

• Cross-team implementation with data pipelines, ETL jobs, schema changes and infra tasks.
• Need to surface end-to-end latency / blockers (critical path), owners, SLAs, and recovery points.

High-level approach:

1. Clarify scope & granularity: define work units = pipeline jobs, schema migrations, infra tickets, validation tasks. Include manual handoffs as tasks.
2. Build a directed dependency model (DAG) where nodes = tasks or datasets and edges = “depends-on / consumes”. Weight nodes by expected duration (or latency window) and include earliest/latest start constraints.

Visual tools:

• DAG visualization (Graphviz, Airflow/Prefect UI, Marquez) for technical lineage and algorithmic critical-path computation.
• Swimlane Gantt (MS Project / Jira Portfolio / Smartsheet / Google Sheets) for timeline & team responsibilities.
• Interactive dependency graph in Miro or a web app (D3.js/Neo4j Bloom) for stakeholder walkthroughs.

Data to maintain:

• Node metadata: ID, owner/team, type (job/schema/infra/manual), estimated duration, earliest start/latest finish, SLA, scheduled window, retry policy, cadence.
• Edge metadata: dependency type (hard/soft), data contract/version, expected data readiness time.
• Runtime/observability: last run status, historical durations, failure rates, alerts.
• Source of truth: tickets (Jira), pipeline metadata store (Airflow/Prefect metadata DB), data catalog/lineage (Amundsen/Marquez), CI/CD pipelines.

Identifying critical path:

• Compute longest path on DAG using node durations (use historical medians for estimates). Recompute after schedule changes. For probabilistic uncertainty, compute P50/P90 critical paths or add buffers for high-variance nodes.

Keeping it updated during execution:

• Automate ingestion: sync Jira tickets, pipeline schedules, and lineage into a metadata service nightly/real-time via webhooks.
• Instrument pipelines to push run-times/status to the metadata store and monitoring (Prometheus/Grafana).
• Set rules to surface changes (owner changes, delayed tasks) via alerts and update visualizations dynamically.
• Operational cadence: daily standups for active rollout teams; weekly sync + automated dashboard showing current critical path and top blockers.
• Governance: require team updates for any change to dependencies or estimates; enforce data-contract versioning to avoid hidden blockers.

Trade-offs:

• High automation requires upfront engineering (metadata integration). Manual maps are simpler but stale.
• Granularity vs noise: choose node size that balances visibility with manageability.

This yields a living dependency map that identifies the single source of truth for the critical path and enables proactive mitigation.

Approach: I’d run a Monte Carlo probabilistic schedule risk analysis by simulating many possible project timelines based on uncertain task durations and dependencies, then report percentile outcomes (P50, P80) and recommended contingencies.

Required inputs:

• Work breakdown: tasks, owners, planned (deterministic) durations.
• Uncertainty distributions per task: choose distribution type and parameters (e.g., triangular: optimistic-mostlikely-pessimistic; lognormal for skewed tasks; uniform if little data).
• Precedence/dependency graph: Finish-to-Start, Start-to-Start, lags, parallelism.
• Resource constraints (optional): shared resource calendars, shift patterns, and reallocation rules.

Modeling dependencies:

• Represent tasks as nodes in a DAG with dependency types and lags.
• In each simulation iteration sample every task duration from its distribution.
• Respect dependencies: a task’s start = max(all predecessor finish + lags, resource availability).
• If modelling resource contention, include resource leveling logic or queuing rules per iteration.

Generating percentiles:

• Run N iterations (10k–100k depending on variance).
• For each iteration compute project finish date (or milestone dates).
• Aggregate results into a distribution and compute percentiles: P50 (median), P80, P90.
• Also compute criticality index per task = fraction of iterations where task lies on critical path.

Presenting contingency recommendations:

• Show a concise dashboard: histogram/CDF of completion date with vertical lines at P50/P80/P90.
• Recommend contingency buffer based on stakeholder risk appetite (e.g., hold P80 as target date; contingency = P80 − baseline).
• Tie contingency to causes: allocate contingency to specific high-criticality tasks or a program-level reserve.
• Provide "what-if" scenarios (e.g., reduce uncertainty on top 5 critical tasks and re-run) to show ROI of mitigation actions.
• Document assumptions, distributions, iteration count, and sensitivity analysis so stakeholders can trust and iterate on the model.

As a data engineer I’d automate simulations (Python/Spark for scale), persist iteration outputs for analysis, and schedule re-runs as estimates update.

Approach: compute the latest change per business key from CDC/soft-delete source, normalize target rows, then join to detect mismatches and produce a daily-partitioned mismatches report. Key points: use change_ts to pick latest record per key, treat deleted_at IS NOT NULL as tombstone, and compare normalized payloads (or checksums) to detect divergence.

-- 1. latest source state per business key (e.g., customer_id)
WITH src_ranked AS (
  SELECT
    customer_id,
    name, email, address,
    deleted_at,
    change_ts,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY change_ts DESC) AS rn
  FROM customers_src  -- assumed CDC table with history
),
src_latest AS (
  SELECT
    customer_id,
    name, email, address,
    deleted_at,
    change_ts AS src_change_ts,
    CASE WHEN deleted_at IS NOT NULL THEN TRUE ELSE FALSE END AS src_deleted,
    -- optional checksum to simplify comparisons
    MD5(CONCAT_WS('|', COALESCE(name,''), COALESCE(email,''), COALESCE(address,''))) AS src_checksum
  FROM src_ranked
  WHERE rn = 1
),

-- 2. target current state (assume customers_tgt is current table)
tgt_latest AS (
  SELECT
    customer_id,
    name, email, address,
    updated_at AS tgt_change_ts,
    CASE WHEN is_deleted = TRUE THEN TRUE ELSE FALSE END AS tgt_deleted,
    MD5(CONCAT_WS('|', COALESCE(name,''), COALESCE(email,''), COALESCE(address,''))) AS tgt_checksum
  FROM customers_tgt
),

-- 3. full outer join to find mismatches, including missing rows and tombstones
compare AS (
  SELECT
    COALESCE(s.customer_id, t.customer_id) AS customer_id,
    s.src_change_ts, t.tgt_change_ts,
    s.src_deleted, t.tgt_deleted,
    s.src_checksum, t.tgt_checksum,
    -- classify mismatch types
    CASE
      WHEN s.customer_id IS NULL THEN 'missing_in_source'
      WHEN t.customer_id IS NULL THEN 'missing_in_target'
      WHEN s.src_deleted = TRUE AND (t.tgt_deleted IS NULL OR t.tgt_deleted = FALSE) THEN 'source_deleted_target_present'
      WHEN s.src_deleted = FALSE AND (t.tgt_deleted = TRUE) THEN 'source_active_target_deleted'
      WHEN s.src_checksum <> t.tgt_checksum THEN 'data_mismatch'
      ELSE 'match'
    END AS mismatch_type
  FROM src_latest s
  FULL OUTER JOIN tgt_latest t USING (customer_id)
)

-- 4. write mismatches to a partitioned reporting table by date (use source change date or report run date)
INSERT INTO reconciliation_mismatches PARTITION (report_date)
SELECT
  customer_id,
  src_change_ts,
  tgt_change_ts,
  src_deleted,
  tgt_deleted,
  src_checksum,
  tgt_checksum,
  mismatch_type,
  CAST(COALESCE(DATE(src_change_ts), CURRENT_DATE) AS DATE) AS report_date
FROM compare
WHERE mismatch_type <> 'match';

Key considerations:

• Use change_ts ordering to pick latest CDC row.
• Treat deleted_at as authoritative tombstone; propagate deletes to target.
• Use checksums to simplify field-wise comparison; expand to column diffs when root-cause needed.
• Handle late-arriving CDC events by also checking change_ts drift windows.
• Edge cases: concurrent writes, identical checksums but different null vs empty semantics, timezone normalization for change_ts.