Technical Foundation and Self Assessment Questions

Covers baseline technical knowledge and the candidate's ability to honestly assess and communicate their technical strengths and weaknesses. Topics include fundamental infrastructure and networking concepts, operating system and protocol basics, core development and platform concepts relevant to the role, and the candidate's candid self evaluation of their depth in specific technologies. Interviewers use this to calibrate how technical the candidate is expected to be, identify areas for growth, and ensure alignment of expectations between product and engineering for collaboration.

HardTechnical

33 practiced

As a senior BI analyst, how would you coach nontechnical stakeholders to understand confidence intervals, model uncertainty, and probabilistic outcomes shown on dashboards? Provide concrete examples, visual aids you would use, and how you'd measure whether the coaching improved stakeholder decisions.

Sample Answer

Situation: At my last company we rolled out a conversion-forecast dashboard that showed probabilistic outcomes (P50/P90) and 95% confidence intervals; stakeholders found the intervals confusing and either ignored them or misinterpreted them as “wrong.”

Task: As senior BI analyst, I needed to coach product and marketing stakeholders to correctly interpret confidence intervals, model uncertainty, and probabilistic forecasts so decisions (budgeting, campaign launches) used uncertainty appropriately.

Action:- Short workshop (30–45 min) with live dashboard: start with an intuitive framing—“interval = plausible range” and “probability = how often this would happen if we repeated the situation many times.”- Concrete, domain examples: show monthly conversions forecasted at 1,000 (P50) with 90% CI [900, 1,100]. Translate: “There’s a 90% chance actual will fall in this range; there’s a 10% chance it will be outside.”- Visual aids on dashboard: - fan chart over time (shaded bands for 50%, 80%, 95% intervals) so widening bands communicate increased uncertainty; - probability density curve for a single metric with markers at P10/P50/P90; - simple scenario buttons that show decisions under optimistic/pessimistic draws (e.g., allocate +10% vs hold).- Interactive exercise: run 1000 Monte Carlo draws, show distribution of outcomes, and simulate decision outcomes (e.g., expected revenue if we invest vs not).- One-pagers and glossary: short visuals explaining confidence interval vs. prediction interval vs. model error; rule-of-thumb guidance (e.g., use P50 for planning, P10/P90 for risk buffers).- Embed explanations in tooltip text and conditional formatting (red when lower CI breaches target).

Result / Measurement:- Tracked behavioral metrics over 3 months: - Pre/post quiz on concept understanding (target +40% correct). - Dashboard usage patterns: increased use of scenario buttons and views showing CI (target +30%). - Decision quality proxies: fewer post-hoc budget reversals due to “unexpected” outcomes (target 25% reduction), and decisions that incorporated risk buffers aligned with nominal CI sizes.- Qualitative feedback via short surveys and decision postmortems confirmed clearer reasoning in stakeholder notes and fewer “surprised” escalations.

This approach focuses on concrete, repeatable visuals, active learning (simulations), and measurable outcomes so stakeholders both understand and apply probabilistic information in decisions.

EasyTechnical

41 practiced

Given these PostgreSQL tables: users(user_id INT PRIMARY KEY, name TEXT) and orders(order_id INT PRIMARY KEY, user_id INT, amount NUMERIC, order_date DATE), write a SQL query that returns user_id, name, and total_amount for orders in the last 30 days, including users with zero orders. Use standard SQL/Postgres and order by total descending.

Sample Answer

Approach: Use a LEFT JOIN from users to orders, filter orders to the last 30 days inside the aggregate (not in the WHERE, to keep users with zero orders), then GROUP BY user to compute total_amount. Order by total descending and coalesce null totals to 0.

sql

SELECT
  u.user_id,
  u.name,
  COALESCE(SUM(o.amount), 0) AS total_amount
FROM users u
LEFT JOIN orders o
  ON o.user_id = u.user_id
  AND o.order_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY u.user_id, u.name
ORDER BY total_amount DESC;

Key points:- Filtering on order_date is applied in the JOIN to avoid turning the LEFT JOIN into an inner join.- COALESCE ensures users with no orders show 0 instead of NULL.- Uses CURRENT_DATE for “last 30 days” relative to today; replace with a fixed date if reproducing historical reports.Edge cases:- If you need to include time components, use order_date >= NOW() - INTERVAL '30 days' with a timestamp column.- Large tables: ensure an index on orders(user_id, order_date) for performance.

EasyTechnical

38 practiced

Explain the difference between OLTP and OLAP systems and, as a BI analyst, give one concrete example of when you would query each. Discuss typical schema design, query patterns, and trade-offs (latency, normalization, indexing, update frequency) when choosing where to run heavy analytics versus reporting snapshots.

Sample Answer

OLTP vs OLAP — core difference:- OLTP (Online Transaction Processing) is optimized for fast, consistent transactional workloads: many short reads/writes, low latency, high concurrency (e.g., order creation, payment). - OLAP (Online Analytical Processing) is optimized for complex read-heavy analytics: large scans, aggregations, historical analysis, lower concurrency but heavier CPU I/O.

Concrete BI examples:- Query OLTP: “Fetch current order status and available inventory for order #12345” — a single-row lookup or small join used in an operational dashboard or support tool.- Query OLAP: “Monthly revenue by product category for the last 24 months, with YoY growth and rolling averages” — large aggregation across historical data for executive dashboards.

Typical schema design:- OLTP: Highly normalized (3NF+), lots of foreign keys to avoid redundancy; optimized for consistent updates and small transactions.- OLAP: Denormalized star or snowflake schema (fact tables + dimension tables), pre-aggregated summary tables or cubes to speed queries.

Query patterns:- OLTP: Short, indexed point lookups or small-range queries; frequent inserts/updates/deletes.- OLAP: Full table scans, joins between fact and dimensions, GROUP BY, window functions, time-series aggregations.

Trade-offs & guidance on where to run heavy analytics vs reporting snapshots:- Latency: OLTP gives sub-second transactional latency; OLAP accepts higher query latency but pre-aggregations/materialized views reduce it. Don’t run long aggregations on OLTP — they slow transactions.- Normalization: Normalized OLTP simplifies updates and maintains consistency; denormalized OLAP speeds reads at cost of storage and ETL complexity.- Indexing: OLTP uses many narrow indexes for point queries; OLAP benefits from composite, columnar storage, and bitmap or columnstore indexes for analytical scans.- Update frequency: OLTP updates continuously; OLAP is updated via ETL/CDC—can be near-real-time but often batch (hourly/daily). Frequent heavy writes in OLAP reduce performance of analytical queries if not managed.

Practical recommendation:- Offload heavy analytics to a separate data warehouse or read-replica (columnar storage or cloud DW like Snowflake/Redshift/BigQuery). Use ETL/CDC to populate denormalized fact tables and materialized aggregates for dashboards. For near-real-time operational reports, use a lightweight replica or purpose-built real-time layer to avoid impacting OLTP transactional throughput.

EasyBehavioral

42 practiced

Self-assessment: Rate your SQL skill level from 1 (novice) to 5 (expert) and provide three recent, concrete examples of SQL tasks you can perform confidently (e.g., window functions, query tuning, complex joins) and two technical areas you'd like to improve. Describe one concrete step you will take to improve each weak area.

HardTechnical

33 practiced

Explain the CAP theorem in your own words. How does it guide design choices for a distributed cache used to accelerate BI dashboards (choosing consistency vs availability vs partition-tolerance)? Provide specific cache invalidation and update patterns and explain their trade-offs.

Sample Answer

CAP theorem: in a distributed system you can only simultaneously guarantee two of Consistency, Availability, and Partition-tolerance. Practically: if network partitions happen (they will), you must choose between always returning up-to-date data (Consistency) or always responding (Availability).

For a distributed cache for BI dashboards this guides trade-offs:

- Prefer CP (Consistency + Partition-tolerance) when dashboards power decisions that require strict correctness (e.g., finance close, regulatory reports). On partition, the cache may reject reads/writes or serve stale error—better to delay than show wrong numbers.

- Prefer AP (Availability + Partition-tolerance) for interactive operational dashboards where low latency matters and eventual correctness is acceptable (e.g., marketing dashboards). On partition, serve possibly stale data to keep the UI responsive.

Cache invalidation/update patterns and trade-offs:- Time-to-live (TTL/expire): simple, AP-friendly. Pros: low complexity, always available. Cons: data can be stale up to TTL; picking TTL is hard.- Write-through: write updates go to cache and primary synchronously. Pros: strong consistency for reads; CP-leaning. Cons: higher write latency and complexity.- Write-behind (async writeback): cache accepts writes and asynchronously syncs to DB. Pros: fast writes (better UX), AP-leaning. Cons: risk of data loss on failure, eventual consistency.- Cache-aside: application reads cache, on miss reads DB and populates cache; on DB writes evict/refresh cache. Pros: flexible, commonly used for BI. Cons: race conditions cause brief staleness; requires careful invalidation.- Explicit invalidation (pub/sub): DB change stream notifies cache to invalidate/refresh. Pros: low staleness, good for CP if applied synchronously; scalable. Cons: adds infrastructure and complexity; still subject to network partitions.- Versioning / ETags + conditional refresh: clients detect staleness and refresh. Pros: safe, minimizes unnecessary refreshes. Cons: more round-trips.

Recommendation for BI role:- For executive/financial reports: Use cache-aside + short TTL and strong invalidation (DB changefeed or synchronous invalidation) → favors consistency.- For fast exploratory dashboards: Use longer TTLs with background refresh and allow eventual consistency → favors availability.- Monitor staleness (timestamps, freshness indicators) in the UI so stakeholders know data recency; choose pattern per dashboard SLA.

Unlock Full Question Bank

Get access to hundreds of Technical Foundation and Self Assessment interview questions and detailed answers.

Join thousands of developers preparing for their dream job.