General Technical Tool Proficiency Questions

Practical fluency with the technical productivity, analysis, and reporting tools used in your day-to-day work: spreadsheet and reporting software (for example Excel or Google Sheets), scripting or query languages (for example SQL, Python, R), data visualization or BI platforms (for example Tableau, Power BI, Looker), and domain-specific applications (for example CRM, ERP, contract or case management, marketing automation) where relevant to the role. Candidates should be able to describe their depth of expertise, typical use cases and real world examples, automation or scripting practices, and how they choose the right tool for a given problem. This topic also covers reproducible workflows, data preparation and cleaning, presenting findings clearly, and integrating tools into cross functional projects.

EasyTechnical

76 practiced

You're delivering dashboards in Tableau or Power BI. Explain how you'd set up user access and row-level security so managers see only their region's data. Describe how this impacts data source configuration, extracts vs live connections, and dashboard performance.

Sample Answer

Start by defining the access rule: managers should see only rows where Region = their region. Implement row-level security (RLS) centrally and keep user-to-region mapping in a secure table (e.g., AD group membership, O365 groups, or a mapping table keyed by user email).

Tableau (server/online):- Create a users table (UserEmail, Region). In the data model join it or use a published data source.- Use Tableau’s User Functions (USERNAME()/FULLNAME()) to filter: [Region] = LOOKUP([Users.Region], USERNAME()) or create a calculated field that checks the mapping.- Publish the data source with the mapping to Tableau Server and set permissions at project/data source level.

Power BI:- Add a mapping table (UserEmail, Region) in the model. Create a role with a DAX filter: [Region] = LOOKUPVALUE(Users[Region], Users[UserEmail], USERPRINCIPALNAME()).- Deploy to Power BI Service and assign users/groups to the role (or use Azure AD groups for scale).

Data source configuration, extracts vs live:- If using live connections to a secure database, prefer implementing RLS at the database (preferred for sensitive data) via views or session-based filters—this centralizes security and reduces data transfer.- Extracts (Tableau) / Imported model (Power BI) improve performance and allow offline role filtering, but require secure refresh pipelines and careful access control on the published dataset since the full data may be stored in the service.- For very large datasets, use incremental extracts or DirectQuery/Live with database-side RLS to avoid pulling full datasets into the BI layer.

Performance and best practices:- Push filters down to the source where possible (database RLS, parameterized queries) to minimize data transfer.- Avoid complex per-row calculations in the BI tool; precompute or index Region lookups.- Cache or use extracts for dashboards that don’t need real-time data; set refresh cadence aligned with business needs.- Use AD groups to manage role membership at scale and audit access via server logs.- Test with accounts from multiple regions, monitor query plans and dashboard load times, and document security design for compliance.

MediumTechnical

56 practiced

Create a plan for a KPI dashboard showing daily active users (DAU), retention curves, and cohort analysis for product growth. Describe recommended chart types, layout and navigation, interactive filters, and data preparation steps such as aggregation frequency, smoothing, cohort definitions, and sample-size considerations before building in Tableau or Power BI.

Sample Answer

Goal: Provide a dashboard that surfaces DAU trends, retention behavior, and cohort drivers so product and growth teams can monitor health and test initiatives.

High-level layout & navigation:- Top row: KPI summary cards (DAU today vs. 7/28d, weekly active users WAU, 28-day MAU, % change).- Middle left: DAU time series (line with daily points + 7‑day moving average). Middle right: Rolling activation funnel / top conversion rates.- Bottom left: Retention curves panel (selectable cohort type). Bottom right: Cohort heatmap and cohort size table with metrics (LTV proxy, average sessions).- Navigation: Tabs for Overview, Cohorts, Segment Deep-dive. Breadcrumbs + export buttons.

Chart types & interactions:- DAU: line chart with area shading; tooltip with exact counts and annotation capability.- Retention curves: multi-line (day 0..N) with log-scale toggle for long tails.- Cohort analysis: heatmap (color by % retained) with hover details and conditional formatting.- Funnel / conversion: stacked bar or sankey for step drop-offs.- Filters: date range, cohort window (weekly/monthly), acquisition source, platform, geography, user-level properties. Cross-filtering enabled.

Data preparation:- Aggregation frequency: store daily aggregates as canonical table; keep raw event table for ad‑hoc. For DAU use UTC-normalized day buckets; compute WAU/MAU from daily flags.- Smoothing: show raw daily + centered 7‑day MA to reveal signal vs noise; avoid smoothing for cohort retention curves.- Cohort definitions: support multiple definitions — acquisition date (first install/first purchase), activation event, and experiment assignment. Use cohort start = day of first qualifying event.- Sample-size & significance: display cohort size and mask cells with N < 30 (or configurable) and show confidence intervals for retention rates; for experiments compute binomial CIs.- Pipeline: ETL to precompute cohort membership, per-user daily activity flag, and aggregated retention table (cohort_day, users_started, users_retained). Schedule daily refresh; keep historical snapshots for reproducibility.

Implementation tips:- Precompute heavy joins upstream to keep Tableau/Power BI responsive; use extracts/aggregations and incremental refresh. Document definitions and edge-cases (timezones, duplicate users, bots).

MediumTechnical

67 practiced

In PySpark, joining a 1B-row events table with a small lookup table causes heavy shuffles. Explain strategies to speed up the join such as broadcasting the small table, repartitioning, salting for skewed keys, bucketing, and using join hints. Provide a short code example showing how to broadcast a small DataFrame.

Sample Answer

When joining a very large events table (1B rows) to a small lookup, the default shuffle join can be costly. Here are practical strategies to speed it up and when to use each:

- Broadcast the small table: best when lookup fits in executor memory (typically < a few hundred MB). Spark sends it to all executors to avoid shuffling the big table. Use spark.sql.autoBroadcastJoinThreshold or spark.broadcast manually. - Pros: minimal shuffle, fast. - Cons: OOM if too large.

- Repartition the big table on join key: co-locate matching keys to reduce shuffle volume. Use df.repartition("key") for large-scale controlled parallelism. - Pros: predictable partitions. - Cons: expensive if done unnecessarily.

- Salting for skewed keys: if some keys dominate, add a random salt to the skewed key on both sides and expand small table accordingly to spread load. - Pros: avoids single-partition hotspots. - Cons: increases data size and complexity.

- Bucketing + sort-merge: write big tables as bucketed by join key and use spark.sql.sources.bucketing.enabled. When both sides are bucketed with same numBuckets, Spark can avoid shuffle. - Pros: efficient repeated joins. - Cons: requires pre-processing and Hive metastore/catalog support.

- Join hints: give Spark hints like broadcast(df_small) or repartitionByRange to guide planner when auto decisions are wrong. - Pros: simple and explicit. - Cons: operator must ensure hint correctness.

Short code example — broadcasting small DataFrame:

python

from pyspark.sql import SparkSession
from pyspark.sql.functions import broadcast

spark = SparkSession.builder.getOrCreate()

# large events DF and small lookup DF
events = spark.read.parquet("/data/events")      # ~1B rows
lookup = spark.read.csv("/data/lookup.csv", header=True)

# explicit broadcast join
joined = events.join(broadcast(lookup), on="user_id", how="left")
joined.count()  # triggers job

Key checks: verify lookup size with lookup.rdd.map(lambda r: len(str(r))).take(1) or lookup.persist().count(); monitor executors' memory; test with EXPLAIN to confirm BroadcastHashJoin. Use salting only when EXPLAIN shows skewed task durations.

HardTechnical

80 practiced

Model accuracy dropped by 12% over two weeks. Describe a triage plan to determine whether this is due to data drift, label drift, concept drift, feature engineering regressions, or an operational issue. List hypotheses to test, quick statistical checks and visualizations, logs and artifacts to examine, and immediate mitigation steps to reduce business impact.

Sample Answer

Triage plan — quick, prioritized, repeatable checklist to isolate root cause and mitigate business impact.

Hypotheses (rank by likelihood/impact)1. Data drift: input distributions shifted (population or upstream ETL change).2. Label drift: target distribution changed (seasonality, promotion, fraud).3. Concept drift: relationship between X and y changed (user behavior change).4. Feature-regression: feature pipeline/encoding bug or schema change.5. Operational issue: model serving mismatch (wrong model version), inference data munging, latency/timezones.

Quick statistical checks & visualizations (execute immediately)- Compare feature distributions: KS test for continuous, chi-square for categorical; monitor means/STD, percentiles.- PSI (Population Stability Index) per feature over recent vs baseline window.- Label distribution delta and KL divergence.- Feature-target correlation heatmap and feature importance comparisons (SHAP): has importance reordered?- Performance by cohort/time: rolling AUC/accuracy vs baseline; confusion matrix drift.- Scatter plots/time-series of key features and predictions; calibration curve.

Logs & artifacts to inspect- Model version and deployment logs: confirm served model matches expected artifact (checksum).- ETL/job logs for recent failures, schema changes, null rates.- Feature-store telemetry: missing value rates, cardinality growth, encoding exceptions.- Inference input examples and raw request traces, timestamps/timezones, sampling bias.- Training pipeline artifacts: last training data snapshot, label generation code, data sampling scripts.- Feature transformation code commits and infra deploys in last 2–3 weeks.

Targeted tests / experiments- Replay: score a saved baseline batch (from before drop) through current serving pipeline — if predictions change, it's a serving/feature pipeline issue.- Score recent batch with archived model in a controlled environment — if performance stays high, serving regression suspected.- A/B test rollback: route small % to previous model version to measure delta.- Retrain quickly on most recent labeled data (if labels available) to see if performance recovers -> suggests concept/label drift.

Immediate mitigations to reduce business impact- If serving mismatch: rollback to last-known-good model artifact.- If feature pipeline bug: switch to fallback features (precomputed snapshots) or null-safe encodings; rate-limit online inference.- If label drift/concept drift and retrain not feasible: degrade gracefully (use simpler rules/heuristics), increase human review for high-risk cases.- Raise monitoring alerts and pause automated downstream actions using model outputs.- Communicate incident to stakeholders with ETA for fix and temporary mitigations.

Follow-up actions (root-cause & prevention)- Root-cause RCA: tie failing cohort + feature + commit/deploy timeline.- Add automated checks: PSI thresholds, schema validators, checksum verification, shadow deployment scoring, continuous SHAP drift detection.- Improve retraining cadence, add fallback rules, and runbooks for future incidents.

This plan prioritizes quick tests (replay, rollback), statistical diagnostics (PSI, KS, SHAP), and operational checks to rapidly isolate if the issue is data, labels, concept, feature pipeline, or serving.

MediumTechnical

56 practiced

In Python using pandas, given a DataFrame events(user_id, event_type, value, event_time), write efficient code to compute per-user features: total_events, unique_event_types, and mean_value over the last 30 days per user. Provide the code snippet and explain strategies to scale when the DataFrame does not fit in memory (chunking, Dask, or Spark).

Sample Answer

Approach: convert event_time to datetime, filter events in the last 30 days (relative to now or a reference date), then groupby user_id and aggregate count, nunique(event_type), and mean(value). For datasets that don't fit in memory, compute partial aggregates by chunking or use Dask/Spark which provide distributed groupby/agg.

python

import pandas as pd

# df: DataFrame with columns ['user_id','event_type','value','event_time']
df['event_time'] = pd.to_datetime(df['event_time'])

# choose reference time (now) or a provided cutoff
cutoff = pd.Timestamp.now() - pd.Timedelta(days=30)
recent = df[df['event_time'] >= cutoff]

# per-user features
features = recent.groupby('user_id').agg(
    total_events=('event_type','size'),
    unique_event_types=('event_type','nunique'),
    mean_value=('value','mean')
).reset_index()

Key points:- Ensure event_time dtype to avoid wrong filtering.- Use .size for total events (faster than count on a column with NaNs).- Reset index to get user_id back as a column.

Scaling strategies:- Chunking (single-machine, streaming): read in chunks, maintain partial aggregates: - total_events: sum counts per chunk. - mean_value: keep sum and count per user; final mean = sum_total / count_total. - unique_event_types: keep per-user set of event types (use dict[user_id] = set()) and merge sets across chunks; convert sets to sizes at the end.- Dask: use dask.dataframe to read large CSV/Parquet and run the same groupby().agg() (Dask handles partitioning, out-of-core compute).- Spark: use PySpark DataFrame; filter by timestamp, groupBy('user_id').agg(count('*'), approx_count_distinct('event_type') or collect_set for exact, avg('value')). Use approx_count_distinct for lower memory & faster.Trade-offs:- Keeping exact unique sets in chunking can be memory-heavy; use HyperLogLog/approx_count_distinct for cardinality at scale.- Dask is simpler to adopt for Pythonic workloads; Spark is better for heavy ETL, many partitions, or production pipelines.

Unlock Full Question Bank

Get access to hundreds of General Technical Tool Proficiency interview questions and detailed answers.

Join thousands of developers preparing for their dream job.