Technical Problem Solving and Learning Agility Questions

Evaluates a candidates ability to diagnose and resolve technical challenges while rapidly learning new technologies and concepts. Topics include systematic troubleshooting approaches, root cause analysis, debugging strategies, how the candidate breaks down ambiguous problems, and examples of self directed learning such as studying new frameworks, libraries, or application programming interfaces through documentation, courses, blogs, or side projects. Also covers intellectual curiosity, baseline technical comfort, the ability to learn from peers and feedback, and collaborating with engineers to understand architectures and tradeoffs. Interviewers may probe how the candidate acquires new skills under time pressure, transfers knowledge across domains, and applies new tools to deliver outcomes.

MediumTechnical

72 practiced

Explain differences between INNER JOIN, LEFT JOIN, and SEMI-JOIN. For a BI query where a large fact table needs to be filtered by a relatively small dimension of 'active promotions', describe when using a semi-join (existence check) can improve performance and why.

HardSystem Design

68 practiced

Design an analytics platform that supports both ad-hoc SQL exploration for analysts and pre-computed executive dashboards. Requirements: support 10k concurrent users, 10TB of cold+hot data, sub-second dashboard responses, and reasonable cost. Sketch architecture components (storage, query engine, caching, routing), data organization, and how you'd route queries differently for ad-hoc vs dashboards.

Sample Answer

Requirements (clarified):- Functional: ad‑hoc SQL for analysts; highly interactive executive dashboards.- Scale: 10k concurrent users, 10TB total (hot + cold), sub‑second dashboard responses, cost-conscious.

High-level architecture:- Ingestion: CDC stream (Debezium/Kafka) + batch ETL (Airflow).- Storage: Hot store = columnar OLAP (ClickHouse or Snowflake/BigQuery with hot cluster) for recent/high‑QPS data; Cold store = object storage (S3) with Parquet/ORC partitioned files.- Serving/query engine: Precompute layer (Materialized views / aggregate tables) in OLAP; Ad‑hoc engine = Presto/Trino or BigQuery for large scans.- Cache & acceleration: Distributed results cache (Redis or CDN for dashboard tiles), query result cache for parameterized queries, and a query accelerator (Dremel/OLAP node pool) for sub‑second.- Routing & gateway: API tier (Auth, RBAC) routes queries: dashboard traffic → planner that hits materialized aggregates + cache; ad‑hoc → routed to ad‑hoc cluster with resource isolation (YARN/Kubernetes namespaces + query concurrency limits).

Data organization:- Time-partitioned Parquet on S3, partition keys: date, region, product. Maintain rollups (hourly, daily) and dimensional star schema for fast joins. Keep last 30–90 days in hot OLAP; older data in S3 with external table mapping.

Query routing policies:- Dashboards: only allowed against validated parameterized queries / materialized aggregates. Check cache first (tile cache in Redis/edge), then aggregate tables in hot OLAP; fall back to precomputed rollups. Use pinned compute nodes for SLA.- Ad‑hoc: routed to separate cluster with lower priority; allow sandboxed full scans, with query limits (time, bytes), and recommend using sampled datasets or precompute jobs. Long/expensive queries can be auto-scheduled as background precomputes.

Scalability & cost controls:- Autoscale ad‑hoc fleet; keep hot nodes right‑sized and warm for dashboards; use S3 + Parquet to minimize storage cost. Use tiered retention to purge or archive cold data.- Observability: query metrics, cache hit rates, per-user quotas, cost dashboards.

Trade-offs:- Sub‑second SLA requires materialized aggregates & aggressive caching; this increases ETL complexity and storage duplication but keeps cost moderate compared to keeping all data hot. Ad‑hoc flexibility preserved by separate scalable engine.

MediumTechnical

68 practiced

A SQL view used by many dashboards started returning NULL for some fields after a schema migration. Describe a debugging plan to find when/where the nulls appeared, how to mitigate current user impact, and changes to your migration process to prevent recurrence.

Sample Answer

1) Find when/where nulls appeared (root-cause timeline)- Reproduce: run the view and confirm NULL columns and which dashboards use it.- Time-window: identify migration time and compare results before/after using historical table snapshots or backups.- Drill-down queries:

sql

-- check number of NULLs over time (if timestamp available)
SELECT DATE(event_time) dt, COUNT(*) nulls
FROM source_table
WHERE important_col IS NULL
GROUP BY dt
ORDER BY dt;

sql

-- compare view output before/after migration if audit table exists
SELECT COUNT(*) FROM view_name WHERE important_col IS NULL AND created_at < '2025-11-01';
SELECT COUNT(*) FROM view_name WHERE important_col IS NULL AND created_at >= '2025-11-01';

- Schema diff: inspect DDL changes (column renames, type changes, NULLABLE flag, join keys) and migration scripts. Check downstream ETL jobs that populate fields.

2) Mitigate user impact (short-term)- Create a patch view that coalesces nulls to previous values or fallbacks and point dashboards to it:

sql

CREATE OR REPLACE VIEW view_patch AS
SELECT a.*, COALESCE(a.important_col, b.important_col_old) important_col
FROM new_source a
LEFT JOIN historical_source b USING (id);

- Rollback option: if migration reversible quickly, restore previous schema in a maintenance window.- Communicate: notify stakeholders, mark dashboards with Known Issue banner, and freeze scheduled reports.- Add monitoring: alert on >X% NULLs.

3) Prevent recurrence (process changes)- Pre-migration checks: automated schema-diff, nullability and join-key validation, and sample-data comparison (row counts, key uniqueness).- Add integration tests that run dashboards' critical queries on a staging copy and assert no unexpected NULLs or data loss.- Migration runbook: include rollback steps, maintenance window, stakeholder notification.- Post-migration validation: automated data-quality suite (unit tests, anomaly detection) and temporary feature flagging to switch dashboards.- Ownership: require sign-off from BI and data-engineering for schema changes that affect views/dashboards.

Result: This approach finds the breakage window, restores safe output quickly, and closes process gaps so future migrations trigger early detection and safe rollout.

MediumBehavioral

65 practiced

A colleague's exploratory dashboard runs a heavy live query every morning at 9am and slows the warehouse. How would you approach resolving this while balancing technical solutions and interpersonal dynamics? Outline concrete steps: measurement, coordination, technical mitigations, and how you'd propose a change without creating friction.

Sample Answer

Situation: Our warehouse was consistently degraded each morning at 9am because a colleague’s exploratory Looker/Power BI dashboard ran a heavy live query for ad-hoc analysis, impacting scheduled ETL and other teams’ reports.

Task: As the BI analyst responsible for reliability and cross-team reporting, I needed to stop the disruption while preserving my colleague’s ability to explore and get the data they need — without creating interpersonal friction.

Action:- Measurement - Reviewed warehouse query history and monitoring (query times, CPU, bytes scanned) for the 8–10am window to quantify impact and capture screenshots/metrics. - Identified the specific query, its frequency, and what objects/tables it hit.- Coordination - Reached out to the colleague with data-first, non-accusatory language: “I noticed heavy warehouse usage at 9am that’s affecting scheduled jobs — can we look at this together?” - Scheduled a short working session to understand their use case and urgency.- Technical mitigations (worked together) - Proposed and implemented lower-friction options: - Create a summarized/materialized view or pre-aggregated table refreshed before 9am to satisfy the dashboard’s needs. - Convert the dashboard to use extracts/cached tiles refreshed during off-peak hours (e.g., 2am) or on demand. - Add appropriate indexes/clustering or rewrite the query to reduce scanned bytes. - If exploratory work is required, move it to a development schema or a replica read-only cluster where it won’t impact production workloads. - Piloted the materialized view and scheduled a backfill; monitored warehouse metrics to confirm improvement.- Proposing change without friction - Framed recommendations around shared benefits (faster dashboards, reliable ETLs) and offered to implement the change and document it. - Offered training and a simple playbook for safe exploratory queries (time-of-day best practices, using lighter filters, using smaller sample datasets). - Agreed on an SLA: heavy exploratory runs should be scheduled or run on the replica; production dashboards use cached sources.

Result: The 9am bottleneck disappeared; ETL and downstream reports ran reliably. The colleague retained full analytical ability via the dev replica and faster dashboard responses via cached/materialized data. The relationship improved because the solution was collaborative, data-driven, and low-friction; the approach framed performance as a shared problem and provided practical, immediate fixes.

MediumSystem Design

65 practiced

Design an incremental ETL strategy that refreshes daily dashboards sourced from 100M-row transactional tables. Requirements: low latency (dashboard reflects yesterday's data by 6am), minimal compute cost, ability to reprocess late-arriving records, and reliable failure recovery. Describe components, change-capture method, and checkpointing considerations.

Sample Answer

Requirements clarification:- Daily dashboards must reflect all transactions through "yesterday" by 06:00 daily.- Source tables ~100M rows; minimize compute/cost, support late-arriving records, and reliable recovery.

High-level approach:- Use incremental CDC + daily micro-batch refresh. Maintain a small, queryable reporting table (partitioned by date) that is updated each day with only changed/ new rows.

Components:1. Source DB (OLTP) exposing CDC (transaction log).2. Lightweight CDC capture (Debezium, cloud-native CDC, or timestamp-based delta if no log access).3. Staging area (object storage like S3 / GCS) for daily change files (parquet).4. Batch processing job (Airflow / cloud scheduler) that runs at 02:00 — reads CDC since last checkpoint, applies transformations, writes/merges into report table (data warehouse like BigQuery/Redshift/Snowflake).5. Reporting layer (BI tool pointing to the report table with partition pruning).6. Metadata store & checkpoint table recording last processed LSN/timestamp, job status, and watermarks.

Change-capture method:- Prefer log-based CDC (LSN/offset): captures inserts/updates/deletes efficiently and avoids full scans.- If log-based not available, use source-side incremental column (updated_at) with careful watermarking and idempotent upserts.

Checkpointing & idempotency:- Store the last processed CDC offset (LSN or max updated_at) in a durable checkpoint table.- Process changes in batches and write a transactionally consistent marker: only advance checkpoint after successful commit of target merges.- Use idempotent upserts/merge statements (MERGE INTO with primary key) so retries are safe.

Late-arriving records & reprocessing:- Keep daily partitions immutable after initial load but allow occasional backfills: - Maintain a rolling “reprocess window” (e.g., 30 days). If late record arrives for partition P, schedule a partition-level reprocess: re-run transformation for that partition using source+CDC and overwrite partition atomically. - For out-of-window corrections, support ad-hoc backfills.

Failure recovery & reliability:- Each run writes status to metadata (started, success, failed) and retains raw CDC batches until success.- On failure, use checkpoint + persisted CDC files to retry from last committed offset.- Alerting and automatic retries with exponential backoff; manual backfill workflow for persistent failures.

Cost optimization:- Use compressed columnar storage (parquet) and partition pruning.- Process only CDC deltas, not full table scans.- Schedule compute off-peak and use serverless/cloud-managed compute (BigQuery, Snowflake auto-suspend, or spot instances) to reduce cost.- Keep staging retention short (e.g., 7–14 days) but retain checkpoints longer.

Operational notes:- SLA: schedule daily job at ~02:00 -> completes <4 hours; ensures dashboards reflect yesterday by 06:00 with buffer for retries.- Monitor key metrics: CDC lag, job duration, rows processed, checkpoint age.- Document and test reprocess/backfill procedures regularly.

Unlock Full Question Bank

Get access to hundreds of Technical Problem Solving and Learning Agility interview questions and detailed answers.

Join thousands of developers preparing for their dream job.