Data Processing and Transformation Questions

Focuses on algorithmic and engineering approaches to transform and clean data at scale. Includes deduplication strategies, parsing and normalizing unstructured or semi structured data, handling missing or inconsistent values, incremental and chunked processing for large datasets, batch versus streaming trade offs, state management, efficient memory and compute usage, idempotency and error handling, and techniques for scaling and parallelizing transformation pipelines. Interviewers may assess problem solving, choice of algorithms and data structures, and pragmatic design for reliability and performance.

HardSystem Design

41 practiced

Design end-to-end observability for ETL pipelines: what telemetry to collect (throughput, latency, success/failure, schema changes), how to capture lineage per dataset and metric, how to expose dashboards/alerts to engineers and business owners, and automated remediation strategies for common failure patterns.

Sample Answer

Requirements & constraints:- Functional: detect ETL failures, schema drift, performance regressions, data lineage down to dataset/metric, deliver role-specific views for engineers and business owners.- Non-functional: low alert noise, near-real-time (<5m) visibility, retention for audits, integrated with Tableau/Looker/Power BI.

High-level architecture:ETL jobs → Instrumentation (emit telemetry) → Central observability plane (time-series DB + event store + metadata/catalog) → Lineage graph service → Alerting & remediation engine → Dashboards & stakeholder views.

Telemetry to collect:- Job-level: run_id, job_name, start/end timestamps, status, retries, worker/node.- Throughput/latency: rows_in, rows_out, throughput rows/sec, stage latencies.- Data quality: null rates, uniqueness, referential integrity, row counts vs expected.- Success/failure: error type, stacktrace, transient vs permanent.- Schema: schema hash, column diffs, incompatible type changes.- Business metrics: computed metric values, sample hashes.- Context: data source fingerprint, partition info.

How to capture lineage:- Emit semantic lineage events from ETL (inputs, transformations, outputs) with column-level mappings and transformation IDs.- Persist in a graph DB (e.g., Neo4j or managed metadata store like Amundsen/Atlas) linking datasets → fields → metrics → jobs.- Compute derived-metric lineage: trace metric = f(dataset.column...) with provenance and aggregation windows.- Store snapshots of dataset schemas and hash of data samples for drift detection.

Dashboards & alerts:- Engineer view: per-job dashboards (latency, throughput, error rates, recent runs), dependency graphs, schema diff explorer, APM traces for slow stages. Alerts: job failure, SLA breach (>X latency), repeat transient errors.- BI/Business view: dataset freshness, hit-rate of key metrics, data quality scorecards, last good-run timestamp, impact analysis (which dashboards/metrics affected).- Alerting rules: multi-stage (warning → critical), deduplication, grouping by root-cause, use SLOs for data freshness and accuracy (e.g., 99% of partitions loaded within 2 hours).- Delivery: Slack/Email for engineers, PagerDuty for on-call, scheduled digest for business owners.

Automated remediation:- Retry with backoff for transient failures; circuit breaker for noisy retries.- Auto-skip/mark partition and run compensating loads when partial data acceptable; flag for manual replay otherwise.- Schema adapters: if non-breaking additive column added, auto-map and continue; for incompatible schema, route to quarantine pipeline and notify owners with example rows.- Auto-rollforward: for known flaky external sources, switch to cached snapshot and mark degraded mode for downstream dashboards.- Self-heal integrations: job restarts via orchestration (Airflow/Kubernetes) with idempotent operators; automated replays for missing partitions triggered when fix available.- Runbook automation: attach runbook links in alerts with one-click actions (restart job, replay last N partitions, promote snapshot).

Key metrics & SLOs:- Data freshness SLO (e.g., 99% of partitions <2h)- Data accuracy SLO (e.g., row-count within ±1% of expected)- Pipeline reliability (success rate per week)

Trade-offs & considerations:- Lineage granularity vs cost (column-level is more precise but heavier).- Alert sensitivity: tune thresholds and use anomaly detection to reduce noise.- Security & governance: RBAC on lineage and change history; immutable audit logs.

This design gives BI teams immediate confidence in data quality, quick root-cause for engineers, and automated mitigations to keep dashboards reliable while surfacing issues to stakeholders.

EasyTechnical

39 practiced

You have a customers table with schema:

sql

customers(customer_id VARCHAR, source VARCHAR, email VARCHAR, name VARCHAR, updated_at TIMESTAMP)

Write an ANSI SQL query that deduplicates rows keeping one record per customer_id: prefer the most recent record by updated_at and, when timestamps are equal, prefer rows with non-null email. Provide a single SQL statement (use window functions) that produces the deduplicated result.

Sample Answer

Approach: use a window function to rank rows per customer_id by updated_at (most recent first) and break ties by preferring non-null email. Then select rows with rank = 1.

sql

SELECT customer_id, source, email, name, updated_at
FROM (
  SELECT *,
         ROW_NUMBER() OVER (
           PARTITION BY customer_id
           ORDER BY updated_at DESC,
                    CASE WHEN email IS NOT NULL THEN 1 ELSE 0 END DESC
         ) AS rn
  FROM customers
) t
WHERE rn = 1;

Key points:- PARTITION BY customer_id ensures one group per customer.- ORDER BY updated_at DESC picks the newest row.- CASE WHEN email IS NOT NULL ... DESC prefers non-null email when timestamps tie.- ROW_NUMBER ensures exactly one row kept per customer_id; if you want deterministic tie-breaking beyond this, add additional ORDER BY columns (e.g., source or name).

Edge cases: null customer_id (will be deduped per NULL group), identical rows — arbitrary pick among exact ties. This is ANSI SQL and works in Postgres/most RDBMS.

EasyTechnical

51 practiced

As a BI analyst, you must decide when to impute missing values vs keeping nulls for dashboards and aggregated metrics. Describe a structured approach: how you assess missingness (MCAR/MAR/MNAR), how visualization or aggregation is impacted, stakeholder expectations, and concrete imputation strategies for numeric and categorical features used in reporting.

Sample Answer

Start with a decision framework: assess missingness → evaluate dashboard impact & stakeholder needs → choose keep-null vs impute → document and monitor.

1) Assess missingness- Calculate missing rate by field, time, segment. Visualize with heatmaps/time-series of null% and cross-tabs.- Test mechanism: - MCAR: run Little’s test or compare distributions of observed vs missing cohorts. If no pattern, likely MCAR. - MAR: correlate missingness with other observed variables (e.g., missing promo_code more common for web vs mobile). - MNAR: if missing depends on the unobserved value (e.g., high refund amounts purposely blanked), treat cautiously.

2) How visualization & aggregation are impacted- Nulls reduce counts in averages, sums; can bias rates and percentiles.- In charts, nulls may break trend lines or create misleading denominators (e.g., conversion rate = conversions / exposures where exposures are partially null).- Use explicit annotations: show “n” and % missing on KPI cards; allow filter to include/exclude imputed values.

3) Stakeholder expectations- Discuss tolerance for bias vs transparency. For executive KPIs prefer conservative approaches (keep nulls or flag imputed). For operational dashboards where completeness is critical, stakeholders may accept reasonable imputation.- Agree on SLA: which fields get imputed automatically vs require data fixes.

4) Concrete imputation strategies- Numeric: - Low missing rate & MCAR: mean/median imputation (median if skewed). - MAR: model-based imputation (regression, k-NN, or iterative imputer) using correlated features. - Time-series: forward/backward fill or seasonal interpolation for metrics with temporal continuity. - Always create a missing indicator flag column for downstream analysis.- Categorical: - Low cardinality: mode or “Unknown/Missing” bucket (explicit category). - MAR: predict category with classification model. - High cardinality: map to “Other” or leave null and show as “Unknown” in visuals.- Aggregation rules: - For ratios, use aggregated numerators/denominators computed on raw values and exclude imputed where appropriate; show sensitivity by comparing imputed vs non-imputed KPI. - Provide drilldowns that show counts of imputed vs original rows.

5) Documentation & monitoring- Log imputation method per field, include flag columns, and create monitoring alerts if missing rate spikes.- Re-evaluate periodically and after upstream changes.

This approach balances statistical rigor, dashboard clarity, and stakeholder trust.

HardTechnical

36 practiced

You are responsible for financial reporting dashboards that must be auditable and governed. Design the process and infrastructure to ensure auditability: dataset versioning, end-to-end lineage per metric, role-based access controls, reproducible ETL runs, reconciliation checks, and sign-off controls. Suggest concrete tools and policies.

Sample Answer

Approach: design an end-to-end governed pipeline with immutable dataset versioning, automated lineage capture, role-based access, reproducible ETL, reconciliation tests, and formal sign-offs — using proven tools and clear policies.

Design (high-level):1. Ingest & raw landing- Store raw files immutably in cloud object storage (S3/GCS) with object versioning and server-side encryption.- Catalog raw datasets in a data catalog (AWS Glue / Google Data Catalog).

2. Transformation & dataset versioning- Use dbt for transformations; compile artifacts (compiled SQL, manifest.json) stored in git and as build artifacts in artifact store (S3/GCS).- Use a table format that supports time-travel and snapshots (Delta Lake / Iceberg / Snowflake) so every dataset version is queryable.

3. Workflow orchestration & reproducibility- Orchestrate with Airflow/Prefect. Each DAG run records exact git commit, dbt run-id, container image digest, and input dataset versions in run metadata.- CI/CD pipeline: all changes require PRs, automated tests, and signed approvals before deploy to prod.

4. Lineage & metric lineage- Emit OpenLineage events from ingestion, dbt, and orchestration; capture into Marquez or Monte Carlo to provide per-metric end-to-end lineage (column-level where possible).- Maintain a metrics layer (dbt metrics or a metric registry like Transform/MetricFlow) so each metric has definition, SQL, owner, and linked lineage.

5. Access controls & audit logging- Enforce RBAC via cloud IAM + tool-level roles (Snowflake roles, BigQuery IAM, Power BI / Looker model permissions, Tableau Server).- Implement least privilege, separation of duties (developers vs approvers vs consumers).- Enable and centralize audit logs (CloudTrail / Cloud Audit Logs / Snowflake ACCESS_HISTORY) into an immutable logging store for retention per compliance.

6. Reconciliation & data quality- Use Great Expectations + dbt tests for schema, row-count, distribution checks; create reconciliation jobs that compare prior version vs current and store diffs.- Fail pipeline on material reconciliation deviations; route to incident channel + ticket.

7. Sign-off & controls- Introduce production release policy: new report or metric promoted only after: - Automated tests pass - Data reconciliation OK - Peer review + business owner sign-off (Jira approval workflow) - Compliance/finance periodic review for GL-impacting metrics- Record sign-off artifacts (PRs, Jira ticket, signed approval) linked to the deployed artifact.

Concrete tools (example stack)- Storage & compute: Snowflake or BigQuery + S3/GCS; Delta Lake if using Spark.- Transform: dbt (SQL + tests), dbt Cloud or self-hosted with CI.- Orchestration: Airflow or Prefect.- Lineage: OpenLineage + Marquez / commercial Monte Carlo.- QA: Great Expectations + dbt tests.- Catalog & governance: Collibra or Alation (or Glue/Data Catalog + Lake Formation).- Dashboards: Looker (LookML metrics), Power BI with deployment pipelines, or Tableau Server.- CI/CD & artifacts: GitHub Actions / GitLab CI; container registry.- Ticketing/sign-off: Jira with approval workflows.- Monitoring/logs: CloudWatch/Cloud Monitoring + SIEM for audit.

Policies & responsibilities- Data ownership: assign owners for each dataset and metric; owners maintain definitions and SLAs.- Retention & immutability: raw and processed snapshot retention policy; time-travel enabled for minimum 90 days (or per compliance).- Change control: all changes must go through PR -> CI -> staging -> prod; critical metric changes require business owner and finance approval.- Audit reviews: quarterly audit of key metrics (reconciliation reports, lineage attestations).- Access reviews: quarterly attestation of IAM roles and dashboard consumers.

Operational examples- Reproducible run: To reproduce a dated dashboard, check the dashboard’s metadata for the run-id → fetch git commit + dbt manifest + table snapshot (time-travel) → re-run dbt with same inputs or query historical tables to validate numbers.- Incident flow: reconciliation job fails → Airflow marks failed → automated Slack alert + Jira ticket created → owner investigates using lineage to trace upstream change → rollback via git revert and re-run or use time-travel restore.

Why this works- Immutable storage + versioned transforms + recorded run metadata make every dashboard value reproducible.- OpenLineage + metric registry provide traceability from metric to raw source.- RBAC + audit logs + approval workflows satisfy governance and compliance auditors.- Automated tests and reconciliation ensure data integrity; sign-off controls ensure accountability.

Key metrics to monitor- Percent of production dashboard runs with successful reconciliation- Time-to-reproduce a metric (target < 2 hours)- Percent of datasets with defined owner and SLA- Number of stale or over-permissioned accounts identified in access reviews

This architecture balances developer agility with strict controls required for financial reporting.

MediumTechnical

31 practiced

Explain watermarking and event-time windowing in stream processing. For a BI aggregation that tolerates 2 minutes latency but must include late events up to 30 minutes, describe how you'd configure watermarks/allowed-lateness and trade-offs involved (completeness vs latency, state size).

Sample Answer

Watermarking (event-time progress) and event-time windowing are how stream systems align real-world timestamps (event time) with processing. A watermark is the system’s estimate “no more events with timestamp <= W will arrive.” Event-time windows group events by their timestamps (e.g., 5‑minute tumbling), using watermarks to decide when a window is ready to emit results.

Configuration for your BI requirement:- Watermark strategy: allow 2 minutes of out-of-order arrival so dashboards see near-real-time results. In Flink: WatermarkStrategy.forBoundedOutOfOrderness(Duration.ofMinutes(2)) This emits watermarks = maxSeenEventTime - 2min, so windows can fire ≈2min after window end.- Allowed lateness: accept and incorporate updates for late events up to 30 minutes: window.allowedLateness(Time.minutes(30)) This keeps window state after initial firing and applies late arrivals, emitting updated aggregates. For anything beyond 30min, route to a side-output for manual reconciliation.

Trade-offs:- Completeness vs latency: With a 2‑min watermark you get low apparent latency (dashboards update quickly), but final, complete aggregates may still change for 30 minutes as late events arrive. Communicate to users that numbers are “near-real-time, final after 30min.”- State size and resource use: allowedLateness = 30min keeps window state longer, increasing memory/disk for keyed state and checkpoint size. Higher cardinality keys amplify this.- Complexity of downstream consumers: repeating updates (corrections) require idempotent sinks or UPDATE semantics in your BI pipeline (use upserts or CDC-style writes). Consider early triggers (e.g., processing-time early firings) to surface provisional metrics, then final updates when watermark passes + allowed lateness finishes.- Operational: pick side-output retention and monitoring for spikes of late data; consider increasing watermarks during stable times or using per-key watermarking if skewed.

Practical suggestions: emit early/partial aggregates for UX, store final state in a compacted store (e.g., upsert Kafka topic, materialized view) and show “last updated” + completeness badge in dashboards.

Unlock Full Question Bank

Get access to hundreds of Data Processing and Transformation interview questions and detailed answers.

Join thousands of developers preparing for their dream job.