End To End Data Analysis Process Questions

Complete workflow from raw data to insights: data exploration (understanding structure, size, distributions), data cleaning (handling missing values, outliers, errors), analysis (calculating metrics, identifying patterns), visualization (creating charts to communicate findings), and recommendations (translating insights into actionable steps). Entry-level analysts should demonstrate ability to work through the full lifecycle independently.

MediumTechnical

33 practiced

Given an orders table: orders(order_id, user_id, amount, occurred_at DATE). Write a Postgres SQL query that returns, for each user and date, the 7-day rolling total amount and 7-day rolling average order amount using window functions. Keep the query concise and explain any assumptions.

Sample Answer

Assumptions:- "7-day" window = current date and the 6 preceding days (inclusive).- "7-day rolling average order amount" = average per-order amount across all orders in that 7-day window (not average of daily totals).

Approach:1) Aggregate orders by user & date (daily totals and counts).2) Use RANGE window of 6 days preceding to compute rolling sum and rolling average = rolling_sum / rolling_count.

Query:

sql

WITH daily AS (
  SELECT
    user_id,
    occurred_at::date AS day,
    SUM(amount)     AS day_amount,
    COUNT(*)        AS day_orders
  FROM orders
  GROUP BY user_id, occurred_at::date
)
SELECT
  user_id,
  day,
  SUM(day_amount) OVER (
    PARTITION BY user_id
    ORDER BY day
    RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW
  ) AS rolling_7d_total,
  (SUM(day_amount) OVER (
    PARTITION BY user_id
    ORDER BY day
    RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW
  )::numeric
   / NULLIF(SUM(day_orders) OVER (
       PARTITION BY user_id
       ORDER BY day
       RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW
     ),0)
  ) AS rolling_7d_avg_order_amount
FROM daily
ORDER BY user_id, day;

Notes:- NULLIF prevents division by zero for periods with no orders.- Using daily aggregation improves performance versus windowing raw orders when many orders share a date.

MediumSystem Design

43 practiced

Design a batch ETL pipeline that ingests raw JSON web logs, performs cleaning & enrichment, writes partitioned Parquet to a data lake, and produces a daily aggregated 'user_metrics' analytical table. Describe components (storage, compute), orchestration, idempotency, schema evolution handling, and monitoring/checks to ensure data quality.

Sample Answer

Requirements:- Ingest daily raw JSON web logs (~tens–hundreds GB/day), produce partitioned Parquet in data lake and a daily aggregated user_metrics table (by user_id/date) for analysts and models. Ensure idempotency, schema evolution, and data quality checks.

High-level architecture:- Ingestion storage: Cloud object store (S3/GCS/ADLS) as raw landing zone (raw/yyyy/mm/dd/*.json)- Compute: Spark (Databricks or EMR) or PySpark on Kubernetes for scalable batch processing- Metadata/catalog: Glue / Hive metastore for table discovery and schema versions- Orchestration: Airflow DAGs for scheduled runs, retries, backfills- Serving: Partitioned Parquet + optimized table (Delta/iceberg) and a daily aggregated table in a columnar store (Hive/BigQuery/Redshift Spectrum)

Pipeline steps:1. Landing: Producers drop JSON files to raw/ date partition. A file manifest (object metadata + checksum) recorded for each batch.2. Validate & Parse: Spark job reads only new files (based on manifest), applies JSON schema parsing, schema-on-read tolerant parser (use try/except + bad record capture). Produce cleaned staging Parquet.3. Enrichment: Join with dimension tables (geo IP, user profile) and compute derived fields (session_id, device_category).4. Write bronze/clean layer: Append partitioned Parquet to lake (date=YYYY-MM-DD/hour). Use atomic writes (write to temp location then move/commit). Use file-level checksums and record counts.5. Aggregate: Run a deterministic aggregation job that computes user_metrics per day (metrics: sessions, page_views, time_spent). Write to daily aggregated table partitioned by date. Use upsert semantics (MERGE) if using Delta/Iceberg; otherwise write to new partition path and update metastore atomically.6. Cataloging: Update Glue/Hive metastore; record schema version.

Idempotency & Exactly-once considerations:- Track processed file manifest (e.g., DynamoDB / RDBMS) keyed by file path + checksum; skip reprocessing if already processed with same checksum.- Use deterministic aggregation (group by user_id,date) and write to date-partition overwrite or MERGE by primary key to avoid duplication.- Atomic commit: write to temporary path and rename/move; use table formats (Delta/Iceberg) that support transactions.

Schema evolution handling:- Use a schema registry or store JSON schema in metastore. Parse with permissive mode: unknown fields captured into a JSON column ("raw_payload") and nullable for new columns.- For Parquet, manage evolution by writing with union schema and using Delta/Iceberg to evolve columns (add nullable columns). Include nightly schema reconciliation job that compares incoming schema to current and records changes; if breaking change, trigger alert and require downstream migration.

Monitoring & Data quality checks:- Pre-ingest: file-level checks (size, timestamp, checksum)- Row-level: null-rate, value ranges, regex validation for key fields (user_id, timestamp), duplicate detection- Aggregation-level: compare daily totals to expected baselines (e.g., 24h rolling avg ± tolerance); row-count reconciliation between bronze and aggregates- Implement DQ as unit tests in pipeline (Great Expectations or Deequ) that emit metrics to monitoring- Observability: push metrics (file counts, records in/out, latency, failed rows) to Prometheus/Cloud Monitoring; alerts via PagerDuty/Slack for failures or thresholds- Lineage & audit: record job run metadata (run_id, start/end, input files, record counts, hashes) to audit table for debugging and replays.

Operational patterns:- Airflow DAG design: separate tasks for discover → validate → clean/enrich → write → aggregate → tests → publish. Use short retries with backoff and human-in-the-loop on persistent schema or data-quality failures.- Backfills: Airflow parametrized DAGs that can reprocess date ranges; manifest+checksum ensures idempotent reprocessing.- Cost/performance: partition by date and user bucket for large joins; use predicate pushdown, compact small files periodically, and tune shuffle partitions.

This design provides scalable batch processing, safe atomic writes, schema flexibility, deterministic aggregations for idempotency, and robust monitoring/data-quality to keep the user_metrics table reliable for downstream analysis and modeling.

EasyTechnical

41 practiced

Describe at least three methods to detect outliers in numeric data (for example: IQR rule, z-score, and isolation forest). Given a heavily right-skewed distribution of order amounts, which method would you choose and why? Explain how distribution shape influences detection.

HardTechnical

58 practiced

You have a high-cardinality categorical feature (user_id) with millions of unique values and you need to capture user history for a predictive model. Describe encoding strategies (hashing trick, target mean encoding with smoothing, embeddings), memory and latency trade-offs, leakage risks, and how to handle unseen users at inference time. Provide practical implementation notes.

Sample Answer

Approach overview: For millions of unique user_ids, choose an encoding that balances information capture, memory/latency, and leakage risk. I'll present three practical options (hashing, target mean with smoothing, learned embeddings), trade-offs, leakage controls, unseen-user handling, and implementation notes.

1) Hashing trick (feature hashing)- What: Map user_id → fixed-size integer bucket (e.g., 2^16–2^20) and use one-hot or hashed counts.- Pros: Constant memory, deterministic, very fast at inference, no trainable per-id table.- Cons: Collisions (lossy), limited expressiveness for rare users.- Use when: Low-latency production constraints, simple models (GBM / linear).- Implementation notes: choose bucket size by memory vs collision trade-off; optionally use signed hashing to reduce bias; store hash function seed for reproducibility.

2) Target-mean encoding with smoothing- What: Replace user_id with smoothed mean(target | user) combining global mean and per-user mean.- Formula: encoded = (n_user * mean_user + k * global_mean) / (n_user + k)- Pros: Captures historical signal, simple numeric feature, works well with tree models.- Risks: Target leakage / data leakage and overfitting to rare users.- Mitigations: - Strict time-based aggregation: compute encodings only from past data (no peeking into future or same-fold). - Cross-fold strategy during training (k-fold out-of-fold means). - Add noise / regularization and use min-count threshold for using per-user statistic.- Unseen at inference: fallback to global_mean or population segment mean; use prior weight k tuned on validation.- Memory/latency: store mapping (user_id → (count, mean)) in a key-value store (Redis) or persisted lookup; memory grows with unique users.

3) Learned embeddings (neural or supervised factorization)- What: Learn dense vector per user via embedding layer (wide-and-deep, deep FM, or matrix factorization).- Pros: Captures complex interactions, compact (e.g., 32–128 dims), works well for deep models and cold-start mitigations via side-features.- Cons: Requires storing embedding table (millions × dim), slower lookup, needs GPU/serving infra.- Memory/latency: for 10M users × 64 dims × 4 bytes ≈ 2.56GB; can shard across parameter servers or use compressed/quantized embeddings (float16, int8).- Unseen at inference: use a special UNK embedding; better: compute embedding from user meta-features or a cold-start model that predicts embedding from attributes.

Hybrid patterns- Combine approaches: use hashing for raw id interaction features, target-mean for simple signal, and embeddings for deep model when infra allows.- Use feature crosses (hashed_id × item_id) to capture interactions without huge tables.

Practical implementation notes- Pipeline: maintain an offline aggregator that computes per-user counts and target means on a time-windowed basis; export snapshot to production KV store daily.- Leakage control: enforce strict evaluation protocol—compute user stats only from training window; for online learning, use streaming aggregators that update only from committed events.- Storage: small lookup (counts+means) fits in Redis or RocksDB; embeddings require a scalable parameter server or embedding service (e.g., TensorFlow Embedding Service, TorchServe) with caching.- Monitoring: track % unseen users, distribution drift of encoded values, latency percentiles for lookups, and model performance by user-frequency buckets.- Tuning: bucket size (hash), smoothing weight k and min_count (target-mean), embedding dim and quantization (embeddings).

Quick heuristics- If real-time low-latency and limited infra → hashing + simple aggregate features.- If model accuracy critical and infra supports → embeddings + fallback target-mean for cold-start.- If moderate infra and tabular models → target-mean with strict leakage controls and OOF folds.

This approach balances predictive power, operational constraints, and leakage prevention while providing clear fallback strategies for unseen users.

EasyTechnical

30 practiced

A CSV import produced many numeric columns as strings (e.g., 'amount' contains commas, '$' and 'N/A'). Describe a reproducible approach in Python to detect incorrect types, coerce columns to their expected types, report rows failing coercion, and log fixes. Provide high-level pseudocode or pandas snippets and mention validation steps.

Sample Answer

Approach: infer schema (expected dtypes), detect columns with string types that should be numeric/datetime, define deterministic cleaning rules, attempt vectorized coercion with errors='coerce', record failing rows, write audit/log entries, and persist cleaned output + report. Reproducible via a function with fixed rules and unit tests.

python

import pandas as pd
import numpy as np
import logging

logging.basicConfig(filename='etl_fix.log', level=logging.INFO, 
                    format='%(asctime)s %(levelname)s %(message)s')

CLEANING_RULES = {
    'amount': lambda s: s.astype(str).str.replace(r'[$,]', '', regex=True).replace({'N/A': None, '': None}),
    'date': lambda s: s.astype(str).str.strip()
}

def coerce_column(df, col, target_dtype, clean_fn=None):
    series = df[col].copy()
    if clean_fn:
        series = clean_fn(series)
    if target_dtype == 'float':
        coerced = pd.to_numeric(series, errors='coerce')
    elif target_dtype == 'int':
        coerced = pd.to_numeric(series, errors='coerce').astype('Int64')  # nullable int
    elif target_dtype == 'datetime':
        coerced = pd.to_datetime(series, errors='coerce')
    else:
        coerced = series.astype(target_dtype)
    failed = df[coerced.isna() & series.notna()]  # non-empty original -> failed coercion
    if not failed.empty:
        logging.warning(f"Coercion failed for {len(failed)} rows in column {col}")
        failed.to_csv(f'coercion_fail_{col}.csv', index=False)
    df[col] = coerced
    logging.info(f"Column {col} coerced to {target_dtype}; failures={len(failed)}")
    return df, failed

Validation steps:- Unit tests on synthetic rows covering cases: '$1,234', 'N/A', empty, '1e3', 'abc'- Row-level checksum before/after (hash of row) to trace changes- Aggregate sanity checks (min, max, mean) vs historical bounds- Schema assertion: no unexpected null rates (alert if > threshold)- Re-run on sample data and compare diffs; store transform metadata (timestamp, rules applied, counts) in a log or audit table.

Notes:- Keep cleaning functions idempotent and deterministic.- Store original raw file and a mapping of fixes for traceability.

Unlock Full Question Bank

Get access to hundreds of End To End Data Analysis Process interview questions and detailed answers.

Join thousands of developers preparing for their dream job.