End To End Data Preprocessing & Exploration Questions

Follow a systematic, tool-agnostic data pipeline before deeper analysis: load the data, check shape and dtypes, identify missing values and duplicates, explore distributions, check for outliers, understand class or category balance where relevant, and summarize key statistics. Document findings and build visualizations that surface relationships in the data. This exploration is the foundation for whatever comes next: feature engineering and model selection for predictive/ML work, or clean aggregations and trustworthy KPIs for dashboards and reporting.

MediumSystem Design

33 practiced

Design an automated ETL pipeline that produces a daily executive report from a transactional database into Power BI. Include steps for extraction, validation (schema and missingness thresholds), transformation (aggregations and feature creation), storage (materialized tables or parquet datasets), scheduling, failure handling, and monitoring/alerts. Mention one or two orchestration tools you would use and why.

Sample Answer

Requirements:- Daily executive report refreshed by 6:00 AM with <15 min SLAs for data availability.- Source: transactional OLTP DB (Postgres/SQL Server). Power BI dataset must be refreshable via gateway.- Non-functional: data quality checks, observable failures, ability to backfill.

High-level architecture:OLTP → Staging (cloud storage / staging DB) → Transform (materialized tables / parquet) → Power BI dataset (DirectQuery or scheduled import) Orchestration: Apache Airflow (for rich DAGs, retries, lineage) or Prefect (easier modern workflows and observability).

Pipeline steps:1. Extraction- Use incremental extraction (last_updated timestamp or CDC). Dump deltas nightly (e.g., 02:00 AM) into staging (S3/ADLS) as parquet or into a staging schema in data warehouse (Snowflake/BigQuery/Redshift).- Keep full extract option for weekly snapshot/backfill.

2. Validation (schema & missingness)- Run schema checks: expected columns, types, new/removed columns → fail DAG if mismatch.- Run missingness thresholds per critical column (e.g., customer_id <1% null, order_amount <0.5% null). Implement as test assertions (Great Expectations or built-in checks).- Record validation results to a checks table; if thresholds exceeded, mark dataset quarantined and alert.

3. Transformation- Use ELT in warehouse: materialized tables or incremental views that compute KPIs (daily revenue, MTD, rolling 30d retention).- Feature creation: customer lifetime value, cohort assignments, churn flags — computed as scheduled SQL jobs; store results in denormalized reporting tables optimized for Power BI ingestion.- Keep intermediate audit columns (source_batch_id, row_loaded_at).

4. Storage- Core reporting tables as materialized tables in warehouse for fast reads (refresh incrementally).- Export stabilized datasets to partitioned parquet in S3/ADLS for cheaper storage and for downstream reprocessing/backups.

5. Scheduling- Orchestrator (Airflow/Prefect) DAG: extract → validate → transform → publish. Use sensors to ensure source availability. Schedule daily at 02:00 with SLA checks; set up dependency windows to ensure table refresh by 06:00.

6. Failure handling- Retry policies with exponential backoff for transient failures; hard fail for schema mismatch.- On failure: DAG marks state, writes error to audit table, triggers alert, and pauses dependent tasks.- Provide manual rerun/backfill operators with parameterized DAG runs.

7. Monitoring & Alerts- Emit metrics to monitoring stack (Prometheus/Grafana or cloud monitoring): run durations, row counts, validation pass rates, SLA misses.- Alerts via Slack/email on: validation failures, SLA breaches, repeated retries, gateway refresh failures.- Daily report of health: last successful run, changes in row counts > X%, validation anomalies.

Power BI integration- Use Import mode with scheduled dataset refresh via On-prem or cloud gateway after reporting tables are ready (or use DirectQuery against warehouse if low-latency required).- Trigger Power BI refresh via REST API at end of DAG; monitor refresh status and fail/alert if refresh fails.

Why Airflow/Prefect- Airflow: mature, wide integration with SQL engines, great for complex DAGs and enterprise scheduling.- Prefect: simpler development, modern observability and local testing, easier to implement health checks and dynamic flows.

Operational best practices- Maintain data catalog and lineage (OpenLineage), keep golden dataset versioned, and run periodic reconciliation jobs comparing daily extracts to source row counts.- Implement RBAC for data access and document SLAs and escalation paths.

This design provides reliable daily delivery, automated quality gates, clear observability, and easy integration into Power BI for executive reporting.

HardSystem Design

25 practiced

You need to join CRM and transaction datasets but there is no shared stable customer ID. Design an EDA-driven entity resolution (record linkage) approach: describe blocking strategies to reduce comparisons, match features to compute similarity (name, email, phone, address), threshold selection, manual review heuristics, and evaluation metrics to measure match quality.

Sample Answer

Requirements & constraints:- Join CRM and transactions to create unified customer views for reporting; tolerate some false positives but prefer high precision for executive metrics; scalable to millions of rows; iterative EDA-driven process with human-in-the-loop.

High-level approach:- EDA to profile fields (completeness, formats, common values, error patterns) → blocking to reduce candidate pairs → compute feature-level similarities → train/threshold or ruleset + manual review → evaluate & iterate.

Blocking strategies (progressive, layered):- Exact-key blocks: normalized email local-part, normalized phone (last 7 digits), hashed postal code + name initial.- Token-based blocks: Soundex/metaphone of last name, name bigrams.- Sorted neighborhood: sort by normalized name/email and compare sliding windows.- Canopy clustering: cheap TF-IDF on name/address to create overlapping canopies.- Use composite blocking: union of multiple blocks to increase recall, track block provenance.

Match features & similarity measures:- Name: normalize (lower, strip titles), compare with Jaro-Winkler and token-set ratio; handle reorderings (first/last).- Email: exact normalized, substring match, domain canonicalization; treat corporate domains differently.- Phone: digits-only, compare last N digits, edit-distance for minor typos.- Address: parse components, compare street using tokenized Jaccard, Levenshtein for unit numbers, geocode where available for distance threshold.- Additional: DOB, loyalty ID fragments, transaction patterns (IP, device fingerprint), frequency-weighted token similarity.- Combine into feature vector; assign weights via logistic regression or gradient-boosted tree trained on labeled samples from manual review.

Threshold selection & calibration:- Use labeled validation set from manual sampling; choose two thresholds: - High-confidence match threshold tuned for high precision (used automated joins). - Non-match threshold tuned for high recall to auto-exclude. - Ambiguous zone between thresholds sent to manual review.- Optimize thresholds based on business cost matrix (FP vs FN costs) and ROC/PR curves.

Manual review heuristics:- Prioritize review by potential impact (high revenue customers, frequent transactors) and by uncertainty score (near decision boundary).- Provide reviewers contextual evidence: fields side-by-side, transaction snippets, block provenance, similarity breakdown.- Capture reviewer decisions as training labels; enforce inter-rater sampling for quality.

Evaluation metrics & monitoring:- Precision, recall, F1 on holdout labeled set; also use precision@k for top merges.- Error-rate by cohort (high-value, region) and drift detection over time.- Downstream impact metrics: duplicated customer counts in dashboards, changes in MAU/ARPU after joining.- Production checks: sampling merged pairs daily, alert if match rate or precision drop beyond threshold.

Operationalization:- Store blocking keys and feature vectors for incremental runs; use scalable frameworks (Spark or a specialized record-linkage lib).- Version rules/models, log decisions, and maintain feedback loop to retrain as data evolves.

MediumTechnical

26 practiced

A categorical field 'product_id' has 2,000,000 unique values and you need to display revenue by product in a dashboard. Propose strategies to handle this high cardinality for both performance and interpretability (pre-aggregation, top-N + 'Other', attribute joins, approximate distinct). For each approach state impact on EDA findings and reporting accuracy.

Sample Answer

Overview: With 2M unique product_ids, show revenue by product while balancing dashboard performance and user interpretability. Below are practical strategies, their implementation notes, and how each affects exploratory analysis (EDA) and reporting accuracy.

1) Pre-aggregation (daily/hourly product revenue materialized table)- Implementation: ETL job aggregates revenue per product per period and stores in a summary table or OLAP cube.- Performance: Excellent — queries hit small table; fast dashboard refresh.- Interpretability: Still can drill to product level if aggregates kept.- EDA impact: Enables time-series and trend analysis; less raw-event noise.- Accuracy: Exact for aggregated metrics; loses transaction-level granularity (can't inspect individual orders).

2) Top-N + “Other” grouping- Implementation: Compute top 100 (or business-driven N) by revenue; group remaining 1,999,900 as “Other”.- Performance: Fast and clear visuals.- Interpretability: High for stakeholders focused on major contributors; “Other” obscures long tail.- EDA impact: EDA will highlight dominant products but miss insights in the tail (niche patterns).- Accuracy: Accurate totals if “Other” shows combined revenue; not accurate for individual low-volume products.

3) Attribute joins / enrich with product metadata- Implementation: Join product_id to category, brand, price band, lifecycle stage and aggregate by those attributes.- Performance: Aggregating on low-cardinality attributes dramatically reduces cardinality; moderate cost for joins.- Interpretability: Much higher — business-friendly buckets (category, brand).- EDA impact: Reveals drivers at categorical levels; can mask variance within categories.- Accuracy: Aggregates accurate; hides product-level outliers.

4) Approximate aggregation (HyperLogLog, count-min, sketches)- Implementation: Use approximate algorithms for distinct counts or heavy-hitter sketches to find high-revenue products.- Performance: Very fast and memory efficient for streaming/real-time.- Interpretability: Good for trend detection and anomaly alerts; less trustable for billing/financial reports.- EDA impact: Useful for exploratory signals; not for final analysis.- Accuracy: Controlled error bounds; not suitable where exactness is required.

5) On-demand detail with lazy-loading / drillthrough- Implementation: Dashboard shows aggregated view and fetches product-level rows only when user drills into a selected bucket; use pagination and server-side filters.- Performance: Keeps initial load light; acceptable latency for focused queries.- Interpretability: Balances overview and detail.- EDA impact: Preserves ability to investigate outliers while keeping EDA performant.- Accuracy: Exact when loaded; requires safeguards for timeouts/partial results.

Recommendations:- Combine approaches: pre-aggregate for standard views, show Top-N + Other, enrich with attributes, and provide drillthrough to exact data. Use approximate methods only for fast exploratory signals or anomaly detection, not financial reporting. Document limitations (what “Other” contains, aggregation window, approximation error) so stakeholders understand trade-offs.

HardSystem Design

44 practiced

An executive dashboard freezes when users select wide date ranges because underlying tables are huge. Propose a multi-step plan to optimize dashboard performance while preserving data accuracy and UX: include pre-aggregation strategies, materialized views, columnar storage, partitioning, incremental refresh, caching, and visualization-level techniques. Mention trade-offs for each choice.

Sample Answer

Requirements:- Fast, interactive executive dashboard for wide date ranges (months→years)- Preserve accuracy (no sampling unless explicit) and UX (sub-second to few-second responses)- Work with existing warehouse + BI tools (Tableau/Power BI)

High-level plan (multi-step):

1. Baseline & measure- Capture slow queries, cardinalities, common filters/aggregations, and refresh SLAs.Trade-off: upfront time but avoids blind changes.

2. Columnar storage + appropriate warehouse- Ensure data in a columnar store (BigQuery, Snowflake, Redshift Spectrum). Columnar reduces I/O for aggregated columns.Trade-off: storage/compute costs vs big perf gains on analytic queries.

3. Partitioning + clustering- Partition by date (day/month) and cluster on high-cardinality filter columns (region, product).Trade-off: too-fine partitions increase metadata; too-coarse reduces prune benefits.

4. Pre-aggregations & materialized views- Create materialized views / aggregate tables at common grain (daily / weekly / monthly) and for common dimension combinations used by execs.- Use BI-layer aggregation tables for “wide-range” queries; source tables for recent/row-level drill.Trade-off: storage and update complexity vs query speed.

5. Incremental refresh & refresh cadence- Implement incremental materialized view refreshes (streaming or delta) and fast refresh policies: near-real-time for last N days, daily/hourly for older aggregates.Trade-off: complexity in ETL; potential lag if refresh windows are long.

6. Caching & BI tool optimizations- Enable query result caching in the warehouse and cache extracts in Tableau/Power BI for static slices.- Use aggregated extracts for executives and live connections for ad-hoc analysts.Trade-off: cache staleness vs UX.

7. Visualization-level techniques- Limit default date range (last 90/180 days) with “load more” for wider ranges.- Use summarized visual types (heatmaps, trends) with ability to drill into pre-agg detail on demand.- Lazy-load heavy viz and use pagination for large tables.Trade-off: slightly changed UX requiring user clicks vs instant performance.

8. Monitoring & Ops- Add dashboards for query latency, refresh success, and top slow queries. Automate alerts for failed refreshes.

Trade-offs summary:- Speed vs freshness (caching, pre-agg)- Storage/complexity vs query latency (materialized views, partitions)- Simpler UX vs defensive UX patterns (default ranges, lazy load)

Implementation note: start by instrumenting queries, build a few critical pre-aggregations, then iterate: add partitioning/clustering and caching; finally optimize visuals and refresh patterns. This staged approach preserves accuracy while delivering measurable UX improvement.

MediumTechnical

24 practiced

Explain when and why you would apply transformations (log, square-root, Box-Cox) to numeric variables before visualization or modeling. Describe how you would choose an appropriate transformation using skewness/kurtosis and diagnostic plots, and how you would reverse-transform results for stakeholder reporting.

Sample Answer

When to apply transformations and why:- Apply when numeric features are highly skewed, have heavy tails, or violate model assumptions (normality, homoscedasticity, linearity). Transformations (log, square-root, Box–Cox) stabilize variance, reduce skewness, make relationships more linear, and improve visualization readability (e.g., long right tail compressed for plots).

Choosing an appropriate transform:1. Quantify: compute skewness and kurtosis. Practical guides: |skewness| < 0.5 ≈ fairly symmetric; 0.5–1 moderate; >1 strong skew. High positive skew → consider log or Box–Cox; moderate → sqrt.2. Diagnostic plots: - Histogram + density: check tail behavior. - Q–Q plot of variable (or model residuals): assess normality. - Residuals vs fitted (for models): look for funnel shapes (heteroscedasticity). - Compare transformed versions side-by-side.3. Try simple transforms first: - log(x) for multiplicative effects and strong right skew (requires x>0; use log1p for zeros). - sqrt(x) for moderate skew/count-like data. - Box–Cox (λ search) when you want an optimal power transform; use Yeo–Johnson if zeros/negatives exist.4. Validate: refit model or re-plot; re-check skewness, residuals, AIC/R², or cross-validated error to choose best transform.

Practical example (BI context):- Revenue per customer is right-skewed. I compute skewness = 2.3, show histogram and Q–Q. I try log1p and Box–Cox; log1p reduces skew to 0.2 and residuals look homoscedastic, so I keep log transform.

Reversing transforms for stakeholder reporting:- For visuals: show both scales (transformed axis for modeling clarity + a secondary axis or tooltip with original-scale values).- For predictions: invert transform. For log models, exponentiate predictions; if you modeled log(y) with Gaussian errors, correct bias: E[Y] ≈ exp(mu + 0.5*sigma^2) where sigma^2 is variance of residuals on log-scale. For Box–Cox, use the inverse Box–Cox formula with the chosen λ. Document assumptions and present intuitive metrics (median often maps directly with monotonic transforms; use medians if bias correction uncertain).Tools: scipy.stats.boxcox / yeojohnson, sklearn PowerTransformer, and plotting in Tableau/Power BI with dual axes or annotation.

Key takeaways:- Use skewness/kurtosis and diagnostic plots to guide choice.- Prefer simple transforms first; use Box–Cox/Yeo–Johnson when needed.- Always back-transform predictions with appropriate bias correction and present original-scale figures for stakeholders.

Unlock Full Question Bank

Get access to hundreds of End To End Data Preprocessing & Exploration interview questions and detailed answers.

Join thousands of developers preparing for their dream job.