Covers the end to end preparation of raw data for analysis and modeling in machine learning and artificial intelligence. Topics include data collection and ingestion, data quality assessment, detecting and handling missing values with deletion or various imputation strategies, identifying and treating outliers, removing duplicates, and standardizing formats such as dates and categorical labels. Includes data type conversions, categorical variable encoding, feature scaling and normalization, standardization to zero mean and unit variance, and guidance on when each is appropriate given model choice. Covers feature engineering and selection, addressing class imbalance with sampling and weighting methods, and domain specific preprocessing such as data augmentation for computer vision and text preprocessing for natural language processing. Emphasizes correct order of operations, reproducible pipelines, splitting data into training validation and test sets, cross validation practices, and documenting preprocessing decisions and their impact on model performance. Also explains which models are sensitive to feature scale, common pitfalls, and evaluation strategies to ensure preprocessing does not leak information.
HardSystem Design
80 practiced
You must preprocess a 1TB table in a cloud data warehouse (Snowflake/BigQuery) to perform imputations, encodings and aggregate joins before training models in Python. Propose strategies to minimize data egress and compute cost: SQL pushdown operations, materialized views, partitioning, sampling, and integrating warehouse compute with Python training environments (e.g., using BigQuery ML, dbt, or query-export pipelines). Discuss pros/cons and reproducibility implications.
Sample Answer
Requirements & constraints:- 1TB table in warehouse, need imputations/encodings/aggregate joins, minimize egress and compute cost, keep pipelines reproducible for model training in Python.High-level strategy (priority order)1. Push computation into the warehouse (SQL pushdown) - Do imputations, deterministic encodings (one-hot / label maps), heavy aggregations and joins in SQL. Warehouses are optimized for large-set operations and avoid moving data. - Pros: minimal egress, fast bulk operations, lower network cost. - Cons: limited complex ML logic (custom feature transforms), SQL can be verbose.2. Partitioning / clustering / pruning - Partition by date and cluster by join/filter keys so queries scan much less than 1TB. - Pros: big cost savings on scans; faster query runtime. - Cons: requires upfront schema choices; needs maintenance as access patterns change.3. Materialized views / incremental tables - Precompute expensive aggregates and joins as materialized views or incremental (dbt) models. - Pros: amortize cost over many runs; instant reads. - Cons: storage cost; freshness trade-offs.4. Sampling + progressive approach - Use stratified sampling in-warehouse for development and hyperparameter tuning (small subset), then run final training on full features or larger sample. - Pros: fast iteration, lower dev cost. - Cons: sampling bias risk—validate representativeness.5. Integrate compute: in-warehouse ML or tightly-coupled compute - Options: BigQuery ML / Snowpark or using dbt models + BigQuery Storage API / Snowflake Snowpipe or Snowflake's Python UDFs/Snowpark for Python. - Pros: BigQuery ML / Snowpark let you train simple models without egress. BigQuery Storage API and Snowflake Snowpark reduce egress costs and are faster than exporting CSVs. - Cons: feature parity and tooling differences; complex model training often better in Python frameworks.6. Controlled exports when needed - If Python-only libraries are required (e.g., specialized deep learning), export compressed, columnar Parquet/Avro via cloud storage in partitioned chunks; use BigQuery Storage API or Snowflake's external stage to minimize data movement overhead. - Pros: efficient transfer, can parallelize. - Cons: still incurs egress and storage I/O charges.Reproducibility & governance- Use dbt (or SQL files + version control) to codify transforms: each step (impute, encode, aggregate) as versioned models; include tests and docs.- Store deterministic seeds and transformation metadata (encoding maps, imputation rules) in tables so Python code can reproduce exact transforms.- Use materialized views or snapshot tables with timestamps to fix dataset versions used for training; publish DAGs (Airflow, Cloud Composer) for orchestration.- Log query costs and results; include lineage in BI tool / data catalog for auditability.Trade-offs summary- Maximize pushdown + partitioning + materialized/incremental models for cost-efficiency.- Use sampling for dev agility; export only final datasets needed for heavy Python-only training.- Prefer in-warehouse ML or storage APIs to limit egress; use dbt for reproducibility and traceability.
MediumTechnical
76 practiced
Compare common outlier detection techniques useful in BI: IQR-based capping/trimming, z-score and robust z-score, Winsorization, Isolation Forest, Local Outlier Factor (LOF), and DBSCAN-based anomaly detection. For each method describe assumptions about distribution, sensitivity to sample size, computational cost, and scenarios where it is preferable for reporting vs modeling.
Sample Answer
IQR-based capping/trimming- Assumptions: Non-parametric; no normality required. Uses quartiles and IQR.- Sample size sensitivity: Needs moderate sample to estimate quartiles reliably; small samples can be noisy.- Cost: O(n) to compute percentiles.- Use: Reporting — good for simple, explainable dashboards (cap extreme KPI spikes). For modeling — trimming can bias models; capping (Winsorizing) safer.Z-score and Robust z-score (median/MAD)- Assumptions: Z-score assumes approximate normality; robust z (using median and MAD) is distribution-agnostic.- Sample size: Z-score needs enough data for mean/std to be stable; robust z better on small/contaminated samples.- Cost: O(n).- Use: Reporting — robust z for flagging anomalies in skewed metrics; standard z when metric is Gaussian. Modeling — robust z useful for preprocessing; standard z for normalization if normal.Winsorization- Assumptions: No distributional required; replaces tails with threshold values.- Sample size: Works on small/large; choice of percentile matters more than size.- Cost: O(n) (percentile + replace).- Use: Reporting — keeps outliers from distorting summary stats while retaining observations. Modeling — reduces variance influence; preferable when you want to preserve sample size.Isolation Forest- Assumptions: No strict distributional assumptions; exploits how easy an instance is to isolate in random partitions.- Sample size: Performs well on large datasets; small samples reduce reliability.- Cost: Typically O(t * ψ * log ψ) where ψ is subsample size, t trees — scalable with libraries.- Use: Reporting — less transparent, so use with caution and add explanations; Modeling — good for complex, high-dimensional features and unsupervised anomaly detection.Local Outlier Factor (LOF)- Assumptions: Density-based; assumes normal instances lie in dense neighborhoods.- Sample size: Sensitive — small samples or uneven density harm results; high dimensionality reduces neighborhood meaning.- Cost: O(n²) naive (can be improved with KD-trees in low-dim); expensive for large datasets.- Use: Reporting — avoid for executive summaries unless pre-aggregated; Modeling — useful to flag contextual anomalies (local spikes) in low-to-moderate dimensions.DBSCAN-based anomaly detection- Assumptions: Density-based clustering; defines anomalies as low-density points not belonging to clusters.- Sample size: Needs enough points to form clusters; parameters (eps, minPts) depend on scale and density.- Cost: O(n log n) with spatial indexing; otherwise O(n²).- Use: Reporting — useful when clusters correspond to meaningful segments and anomalies are truly noise; Modeling — good to remove noise before modeling or to identify segment-specific anomalies.Practical guidance for BI analysts- Prefer simple, explainable methods (IQR, robust z, Winsorize) for dashboards and stakeholder communication.- Use Isolation Forest or LOF for exploratory modeling or automated anomaly pipelines, but accompany with validation and examples.- Always visualize (boxplots, time series with caps, scatter/UMAP for multivariate) and document method, thresholds, and business rationale.
MediumSystem Design
67 practiced
Design an ETL pipeline to transform raw clickstream JSON logs into analytics-ready tables for BI: sessionized_events(session_id, user_id, session_start, session_end), daily_active_users, and event_counts. Describe ingestion (Kafka or file-based), processing framework (Spark, Beam), sessionization logic (e.g., 30-minute inactivity), idempotency design, partitioning strategy, schema evolution handling, and how to expose materialized tables to BI tools (e.g., BigQuery, Redshift, data lakehouse).
Sample Answer
Requirements & constraints:- Near-real-time (minutes) and batch backfill support- Clickstream JSON varying fields, high volume, per-user sessionization (30-min inactivity)- Downstream artifacts: sessionized_events, daily_active_users (DAU), event_counts- BI-friendly storage with ACID/partitioning for fast reads (e.g., BigQuery, Redshift Spectrum, or Delta Lake on S3)Ingestion- Prefer Kafka for real-time: producers push JSON events keyed by user_id; use Avro/Protobuf + Schema Registry to enforce schemas and handle evolution.- File-based (S3) acceptable for bulk/backfill: land gzipped JSON/Parquet files into an ingest bucket with manifest.Processing framework- Use Spark Structured Streaming (or Apache Beam) for unified streaming + batch semantics.- Read from Kafka with event-time processing, set watermark for late data (e.g., 1 hour).Sessionization logic- Use event_time and user_id keying; apply session windows with 30-minute inactivity gap: - Spark example: groupByKey(user_id).sessionWindow("30 minutes") or use flatMapGroupsWithState for stateful session merging.- For each session produce: session_id (UUID deterministic: hash(user_id + session_start)), user_id, session_start, session_end, event_count, aggregated attributes (pages, durations).- Handle out-of-order events using watermark + update-on-arrival; emit late-window updates up to watermark.Idempotency & exactly-once- Consume Kafka with checkpointing and commit offsets only after successful sink write.- Use idempotent sinks: write to Delta Lake (ACID) or BigQuery with dedup key (session_id) and upsert logic.- For file sinks, write atomic file manifests (write-to-temp + rename).- For streaming writes to Redshift, stage to S3 and COPY with transaction or use MERGE for upserts.Partitioning & storage layout- Store final tables partitioned by event_date (date of session_start) and optionally by user_id bucket for large scale.- For lakehouse: Delta/Parquet files partitioned by yyyy=,mm=,dd= and cluster/ Z-order by user_id/session_start for faster predicate scans.- For BigQuery: partition by DATE(session_start), cluster by user_id,event_type.Schema evolution- Use schema registry + Avro/Protobuf to manage producer changes.- Store canonical event schema in data lake as Parquet/Delta; enable schema merge for additive fields.- Provide backward/forward compatibility rules; in ETL map unknown fields to a JSON metadata column to avoid breakage.Materializing analytics-ready tables- sessionized_events: materialize in Delta/Parquet or directly upsert into BigQuery/Redshift as the canonical table.- daily_active_users: nightly (or streaming) aggregate of unique user_ids per date; maintain as partitioned table for fast queries.- event_counts: rolling window aggregates (hourly, daily) stored as rollup tables.- Maintain incremental refresh jobs (e.g., hourly micro-batch + nightly full reconcile).Exposing to BI tools- If lakehouse (Delta on S3): use Databricks or Presto/Trino to serve tables with a Hive metastore; connect Looker/Tableau/Power BI via JDBC.- If BigQuery/Redshift: load final tables there for direct BI connectivity; create curated views and access-control roles.- For dashboards, expose materialized views for common queries (DAU, funnels) and pre-aggregate heavy metrics to reduce dashboard latency.Observability & quality- Monitor throughput, lateness, failed offsets, watermark lag.- Implement row-level validation and anomaly alerts (schema drift, sudden drop/spike).- Periodic reconciliation: compare event counts between raw and processed sinks.Trade-offs- Kafka+Spark provides low-latency + mature session APIs; greater infra complexity.- Batch-only (S3 -> Spark batch) reduces complexity but increases latency.Why this fits a BI context- Produces deterministic, partitioned, queryable tables optimized for BI tools, supports incremental updates and historical backfills, and enforces idempotency and schema management so dashboards remain reliable.
HardSystem Design
81 practiced
Design a monitoring and alerting system to detect input distribution drift and label drift in production. Specify statistical methods (KS test, PSI, KL divergence), windowing strategy, thresholds for alerts, aggregation across features, and concrete remediation actions (retraining, feature freeze, rollback). Discuss how to balance sensitivity and false positives.
Sample Answer
Requirements and constraints:- Detect input (feature) distribution drift and label (target) drift for production reports/models daily. Low-latency not required; weekly/ daily cadence OK. Support numeric & categorical features, multiple segments (product lines, region).High-level design:- Pipeline: ingest sample of production data → feature preprocessing (binning/standardizing) → compute drift metrics by feature + segment → aggregate and score → alerting dashboard + notifications (Slack/email) → remediation playbook.Statistical methods & usage:- Numeric features: two-sample Kolmogorov–Smirnov (KS) test for distribution shape changes; Population Stability Index (PSI) for magnitude of shift (stable, moderate, severe). Use KL divergence as secondary check when distributions continuous and smoothed.- Categorical: Chi-square or PSI (with category mapping). For small samples use Fisher exact.- Label drift: compare current label distribution vs baseline with PSI and chi-square; monitor model predicted score distribution too.Windowing strategy & baselines:- Baseline: rolling reference window (last 28 days or last stable release period) and fixed historical baseline (e.g., training data snapshot) kept for comparison.- Current window: sliding 1-day and 7-day aggregated windows to capture transient vs persistent shifts. Require persistence across both to escalate.Thresholds & alert rules:- PSI: <0.1 (no action), 0.1–0.25 (warning), >0.25 (alert).- KS p-value: p < 0.01 (strong evidence of change); paired with effect size (D statistic) threshold.- KL: relative increase > X (tunable by feature).- Label PSI >0.1 triggers immediate review; >0.25 triggers high-priority alert.- Require drift on >N related features or on key business KPI features to open incident.Aggregation across features:- Compute per-feature severity score (normalized PSI/KS effect); aggregate by weighted sum where weights = business importance (mapped by BI owners). Produce an overall drift index per segment and global index.- Correlate feature drift with downstream KPIs (conversion, revenue) via quick causality checks (lagged correlation) to prioritize.Remediation actions (escalation playbook):1. Automated: feature freeze (stop using affected feature in live reports/models if critical), switch to fallback model/version or roll back to previous stable pipeline.2. Investigation: sample inspection dashboard, root-cause tags (schema change, upstream pipeline delay, seasonality).3. Retraining: schedule retrain when drift persists >7 days and model performance degradation confirmed on holdout or delayed labels.4. Business intervention: notify product/operations to confirm behavioral changes (campaigns, pricing).5. Postmortem and update baselines.Balancing sensitivity vs false positives:- Use two-stage detection: sensitive detectors (daily) produce warnings; require persistence (7-day window) or cross-feature agreement to escalate. Tune thresholds per-feature using historical backtesting and precision-recall of alerts. Use weightings and business-impact filters to suppress low-value alerts. Provide feedback loop where analysts can label alerts (true/false) to auto-tune thresholds.Implementation notes:- Exposure in BI: integrate drift index and per-feature charts into dashboards (Looker/Tableau) with drilldowns to distributions, sample data, and remediation buttons (acknowledge, assign).- Logging and audit trail for alert decisions. Regular review cadence to adjust thresholds and baselines.
HardTechnical
116 practiced
A key numeric predictor that business stakeholders say is important has 50% missing values across customers. As a BI analyst, outline a decision framework to decide whether to drop the column, impute it, or derive alternative proxies. Consider information value analysis, collection cost, model sensitivity, business impact, and experiments you would run to quantify the decision.
Sample Answer
Framework — stepwise decision process I would present to stakeholders:1) Quantify missingness pattern- Calculate missing rate by customer segment, time, product, geography. Test MCAR/MAR/MNAR (Little’s test, logistic model predicting missingness).- If missingness is informative (correlates with outcome or segment), keep as a feature (use missing indicator or model that handles NaNs).2) Measure predictive information- Compute Information Value (IV) / Weight of Evidence or mutual information of the variable (on non-missing rows) vs target; run simple model (e.g., gradient boosted tree) with and without the variable; record delta AUC/PR and feature importance.- Run ablation: train model on full features, then drop the column; measure change in key metrics (AUC, uplift, revenue proxy). Do this across stratified folds to estimate variance.3) Imputation experiments- Try multiple imputation strategies: mean/median, KNN, MICE, model-based (predictor model), and a “missing” sentinel. For each, evaluate downstream model performance and calibration.- Use cross-validated imputation inside pipeline to avoid leakage. Record metric deltas and bias introduced (e.g., distribution shift, RMSE vs holdout where true values available).4) Proxy derivation- Search for correlated variables or create engineered proxies (aggregates, behavioral signals). Evaluate IV and model lift when replacing original with proxy or combining both.- If easy to compute proxies from existing tables, prioritize low-cost proxies.5) Business impact & collection cost- Estimate revenue/operational impact of model performance lift attributed to the variable (use historical conversions, average order value).- Estimate cost to improve collection (engineering effort, API changes, privacy implications, customer friction). Compute ROI: (expected incremental revenue * probability of success) / cost to collect.6) Decision rules (example thresholds)- If delta AUC (ablation) < X% (e.g., 0.5–1%) and IV is low → drop column; use proxies if similar performance.- If delta AUC >= meaningful threshold or revenue uplift > collection cost ROI → invest in collection.- If missingness is informative or imputation recovers most lift → keep with appropriate imputation + missing flag.7) Operationalize & monitor- Implement chosen approach in pipelines; add data quality dashboard (missing rate by segment) and model-monitoring alerts for performance drift.- Run an experiment: A/B test where a subset of customers get improved collection (or derived proxy) and measure downstream KPIs (conversion, LTV) over a defined window; use sample size calc to power test.Outcome: the approach blends statistical evidence (IV, ablation, imputation) with practical ROI/cost, and uses targeted experiments to resolve ambiguous cases before committing engineering resources.
Unlock Full Question Bank
Get access to hundreds of Data Preprocessing and Handling for AI interview questions and detailed answers.
