Assess the candidates practical experience with business intelligence and operational tools, their depth of proficiency, and their ability to learn and apply new systems. Topics to cover include which business intelligence platforms they have used such as Power BI, Tableau, and Looker, the duration and level of hands on experience with each, specific projects where they built dashboards or reports, and the candidates role in data modeling and visualization. Also include familiarity with general operational tools such as spreadsheet software, analytics platforms, project management systems, human resources information systems, and other domain specific software. Candidates should be ready to explain tool selection, how they integrated data sources, any involvement in implementation or configuration, examples of key metrics and dashboards they built, and how they troubleshoot or improve existing reports. For junior level candidates, emphasize practical skills such as creating dashboards, designing reports, basic data modeling, cleaning and preparing data, and demonstrating learning agility for company specific systems. For mid and senior levels, assess deeper topics such as automating extract transform load processes, optimizing data models, writing structured query language queries or scripts for data transformation, governance and sharing practices, and mentoring others on tool usage.
HardSystem Design
47 practiced
Design a scalable ETL architecture to support near-real-time dashboards with sub-minute latency for critical KPIs across multiple regions. Include choices for ingestion (CDC, streaming), stream vs batch transforms, storage layer (columnar analytics vs OLTP), idempotency, schema evolution, and failure handling strategies.
Sample Answer
Requirements & constraints:- Functional: sub-minute latency for critical KPIs across regions, cross-region rollups, near-real-time dashboards, historical analytics.- Non‑functional: high availability, elastic scale (bursty traffic), at-least-once & idempotent correctness, schema evolution support, multi-region failover, cost-awareness.High-level architecture:Users → Source DBs (OLTP) + external events → Change Data Capture (CDC) & event stream → Stream processing → Serving stores (real-time OLAP + long-term analytics) → BI tools (Looker/Tableau/Power BI)1) Ingestion- CDC for transactional systems: Debezium (Kafka Connect) to capture row-level changes with metadata (lsn, op, ts).- Event streaming for app events: Kafka / Amazon MSK / Kinesis for high-throughput streaming and replayability.- Use a single Kafka topic-per-entity with partitioning by region or customer to localize latency.2) Stream vs Batch transforms- Streaming (Kafka Streams/Flink/ksqlDB/Beam): compute rolling aggregates, session/windowed KPIs, denormalization, deduplication — does the sub-minute heavy lifting.- Micro-batch (Spark structured streaming or hourly batch): complex joins across large historical datasets, re-computations, backfills, slowly changing dimensions.- Strategy: stream-first for critical KPIs; batch for enrichments and corrections.3) Storage layer- Real-time serving: a low-latency, read-optimized store (materialized views) — e.g., ClickHouse, Druid, or a purpose-built OLAP cache (Redis for single-value counters, Pinot/Druid/ClickHouse for time-series aggregates). These support sub-second queries for dashboards.- Long-term and ad-hoc analytics: columnar store (Snowflake, BigQuery, Redshift) storing raw + curated tables partitioned by date/region.- Keep canonical raw event lake (S3) for replay/backfill.4) Idempotency & Exactly-once considerations- Use unique event ids (source lsn + table + pk + operation) and include watermark/timestamp.- Stream processors maintain state stores keyed by unique id to dedupe; rely on Kafka's offset management and transactional producers for end-to-end exactly-once semantics where supported.- Design materialized updates to be idempotent: use upserts with last-write-wins based on source lsn.5) Schema evolution- Use a schema registry (Confluent Schema Registry / AWS Glue Schema Registry) with backward/forward compatibility rules.- Use Avro/Protobuf/JSON Schema for typed payloads; processors handle missing/new fields with defaults.- For large structural changes, version topics and run dual-writer migrations (write old + new formats) during rollout.6) Failure handling & monitoring- Automatic retries with exponential backoff; poison message handling paths (dead-letter topics with alerting).- Checkpointing: stream engines persist state and offsets; implement periodic snapshot exports.- End-to-end observability: metrics (lag, throughput, error rates), tracing (correlate event id across pipeline), data quality checks (row counts, thresholds, schema checks).- Recovery: ability to replay raw topics from S3 or Kafka from earliest offset to rebuild materialized views; maintain idempotent rebuilds.Trade-offs & rationale- Kafka + stream processing minimizes latency and supports replays; columnar long-term store optimizes cost and analytics flexibility.- Using specialized OLAP stores (ClickHouse/Pinot/Druid) ensures sub-minute dashboard SLAs; OLTP DBs cannot scale for analytic workloads.- Complexity increases with ops (schema registry, stateful stream jobs) but yields correctness, scalability, and maintainability.Operational best practices- Define SLA tiers: critical KPIs on stream pipelines, non-critical on micro-batch.- Automate deployments, include canary topic migrations, and run regular backfills/drills to validate rebuild processes.- Collaborate with data engineering to expose curated, documented semantic layer (consistent metrics) for BI tools so analysts get reliable, low-latency KPIs.
EasyTechnical
68 practiced
Describe three specific ways you would use Excel (pivot tables, formulas, Power Query) to validate a dataset before building a dashboard. For each check, explain the steps, expected outputs, and how the check prevents common reporting errors.
Sample Answer
1) Pivot-table summary checks (completeness & aggregation)Situation/steps: I create a pivot table from the raw table, placing Date/Region/Product as rows and Sum(Quantity), Sum(Sales) as values. I also add Count of TransactionID to detect missing/duplicate rows.Expected outputs: Totals by dimension, counts per day/region, grand totals that match source.How it prevents errors: Comparing grand totals and counts quickly reveals dropped rows, unexpected zero sums, or duplicated TransactionIDs (if count > distinct expected). It catches aggregation logic errors I might replicate in the dashboard.2) Formula-based row-level validation (consistency & business rules)Steps: Add helper columns with formulas: =IF(ISBLANK([@Sales]),"MISSING", ""), =IF([@Quantity]*[@UnitPrice]<>[@Sales],"MISMATCH","OK"), and =IF(COUNTIFS(Table[TransactionID],[@TransactionID])>1,"DUP","").Expected outputs: Flags per row for MISSING, MISMATCH, DUP.How it prevents errors: Identifies bad or inconsistent calculations, nulls that would distort KPIs, and duplicate transactions that inflate metrics before building visuals.3) Power Query transformations & type/lookup checks (schema & referential integrity)Steps: In Power Query, set strict column data types, remove leading/trailing spaces, trim/case-normalize keys, merge with reference tables (e.g., product master) using Left Anti Join to list unmatched keys, and run Remove Duplicates with grouping.Expected outputs: Cleaned dataset with correct types, a small preview table of unmatched keys or rows removed.How it prevents errors: Enforces schema (prevents text-in-number issues), surfaces orphaned foreign keys that would produce missing labels in visuals, and ensures consistent joins—avoiding misleading blanks or misaggregations in the dashboard.
MediumBehavioral
48 practiced
Describe a time you automated a repetitive report or dashboard refresh. Include which scripting or automation tools you used (PowerShell, Python, REST APIs, scheduled jobs), how you validated the automation, and the measurable business impact such as time saved or reduction in errors.
Sample Answer
Situation: Our monthly executive sales dashboard in Power BI required a manual refresh and data harmonization across three source systems; the process took ~6 hours across the BI team and introduced occasional mismatches from late-arriving CSVs.Task: As the BI analyst owner, I needed to fully automate data ingestion, refresh, and validation so executives had an always-consistent dashboard by 8am on the 1st of each month, and to free analysts for higher-value work.Action:- I built a Python ETL script (pandas + requests) to pull data from the CRM and billing REST APIs, and to ingest nightly CSV exports from SFTP.- Implemented transformations and schema checks, then wrote row-count and checksum validations.- Packaged post-processing in a PowerShell wrapper that: - Runs the Python ETL - Publishes datasets to the Power BI Service using the Power BI REST API - Triggers a dataset refresh and polls refresh status - Sends a success/failure email to stakeholders- Deployed as an Azure DevOps scheduled pipeline (cron) to run at 3:30am.- Added automated unit checks and a fallback alert that attached raw files for debugging.Result:- End-to-end automation reduced manual effort from ~6 person-hours to 10 minutes of monitoring (90% time saved).- Data mismatch incidents dropped from ~3 per quarter to zero within two months.- Executives received dashboards on time 100% of the last six months, improving trust and enabling faster monthly decisions.This taught me to pair robust validation with clear alerting so automation both saves time and increases data confidence.
HardSystem Design
54 practiced
You must migrate hundreds of dashboards from Tableau Server to Power BI Service while preserving logic, metrics, and user permissions. Outline an end-to-end migration plan: inventory, mapping of features, scripts/tools for conversion, testing approach, training plan, and rollback strategy in case issues are found after migration.
Sample Answer
Requirements & scope- Clarify stakeholders, SLAs, retention, compliance, and which dashboards/datasets are in-scope (critical, high, low).- Decide cutover model: big-bang vs phased by business unit.1) Inventory & discovery (automated + manual)- Export full inventory using Tableau REST API / TabCmd and Tableau Catalog: workbooks, data sources, extracts, calculated fields, parameters, schedules, owners, permissions, groups, refresh schedules.- Produce CSV/JSON inventory with metadata and usage metrics (last access, views) to prioritize.2) Feature & semantic mapping- Create a mapping table: - Tableau concepts → Power BI equivalents (workbook → report, data source/extract → dataset, Tableau calc → DAX/M measures, parameter → What-if or slicer, LOD → DAX/aggregations). - Visuals: map common chart types; flag unsupported/special features (Tableau level-of-detail, custom JS, Story points). - Permissions: Tableau users/groups → Azure AD users/groups → Power BI workspace roles (Admin/Member/Contributor/Viewer).3) Conversion approach & tooling- Automated extraction: use Tableau REST API/TabCmd to export data extracts (TDE/Hyper) and workbook JSONs.- Data: migrate extracts to target databases or publish Hyper as source; prefer centralizing datasets in Power BI datasets (Power BI Premium or Embedded) using DirectQuery or scheduled import.- Rebuild logic: - Convert calculated fields and LODs to DAX measures (manual process); use scripts to extract calc definitions to accelerate manual translation. - Use Tabular Editor / ALM Toolkit to manage dataset metadata if migrating semantic model to Analysis Services / Power BI Dataset.- Deployment automation: Power BI REST API + PowerShell (MicrosoftPowerBIMgmt) to create workspaces, set permissions, publish PBIX. Use Azure DevOps pipelines for CI/CD.- Tools: Tableau APIs, Python scripts (tableau-api-lib), Power BI REST, PowerShell, Tabular Editor, DAX formatter, ALM Toolkit, Power BI Desktop.4) Testing & validation- Unit tests: validate each migrated dataset produces identical aggregate numbers for key metrics (row counts, sums, KPIs).- Visual parity tests: sampling of reports; automated screenshot comparisons for high-priority dashboards (tools like Selenium/Playwright) and manual review for interactions.- Data freshness & refresh tests: verify scheduled refreshes and gateways.- Security tests: confirm workspace and dataset permissions match mapped Azure AD groups; validate RLS if used.- UAT: business users sign-off on critical dashboards; maintain an issues log and severity SLA.5) Cutover strategy & rollback- Phased cutover per priority: pilot group → business unit waves. Keep Tableau read-only for a stabilization window.- Parallel-run mode: run both systems for 1–4 weeks; sync ETL and refresh schedules until cutover.- Rollback plan: - If critical issues: revert users to Tableau by switching access links and permissions (preserve current Tableau Server state). - Keep original Tableau content intact and mark migrated dashboards; maintain fallback runbooks to re-enable Tableau refreshes and reassign owners. - Maintain source-of-truth datasets in original DB for quick republishing.6) Training & adoption- Role-based training: admins (workspace, governance), creators (Power BI Desktop, DAX patterns), consumers (Power BI service navigation, subscriptions).- Deliverables: playbooks for conversion patterns (common Tableau calc → DAX), cheat-sheets, recorded webinars, office hours, migration FAQ.- Governance: update BI standards, naming conventions, lifecycle policies, tagging, cataloging (Data Catalog / Purview).7) Governance, monitoring & metrics- Define success metrics: % dashboards migrated, data parity pass rate, user adoption, incident count.- Post-migration monitoring: usage, performance, refresh failures; implement alerts and support channel.Risks & mitigations- Unsupported features → re-engineer UX or provide mitigations.- Performance changes → optimize models (star schema, aggregations), use Premium capacity.- Permission drift → use automated scripts to enforce mappings.Estimate & resources- Pilot (1–2 weeks), migration waves (2–8 weeks per BU depending on volume), total program governance, 1 migration lead, SMEs, devs for scripts, QA, trainers.This plan preserves logic and permissions by extracting metadata, centralizing datasets, using scripted deployments, thorough testing, phased cutover with parallel runs, and explicit rollback paths.
MediumTechnical
40 practiced
A stakeholder requests an ad-hoc KPI report by tomorrow but the underlying dataset is large and not modeled. Walk through how you would rapidly scope, prioritize required fields, create an efficient extraction or sample, and deliver a lightweight but defensible report with clear assumptions documented.
Sample Answer
Situation: A product manager asked me late afternoon for a one-day KPI report (conversion funnel and revenue by channel) but the source is a massive, unmodeled event table.Task: Deliver a lightweight, defensible report by tomorrow that answers the stakeholder’s questions, documents assumptions, and avoids heavy cluster queries.Action:- Clarify scope (30 min): Confirm which KPIs, date range, channel definitions, level of granularity (daily vs aggregate), and acceptable confidence vs completeness. I narrowed it to last 7 days, daily KPIs, top 5 channels.- Prioritize fields: Required = event_time, user_id, event_type, channel, revenue_amount. Optional = device, campaign_id. I explicitly told the stakeholder optional fields could be added later.- Plan efficient extraction: - Use a targeted WHERE on date range and top channels to limit data scan. - Add a preliminary lightweight aggregate query to identify top channels: SELECT channel, COUNT(*) FROM events WHERE event_time >= DATE_SUB(current_date, INTERVAL 30 DAY) GROUP BY channel ORDER BY COUNT(*) DESC LIMIT 5; - Then run a CTAS (CREATE TABLE AS) or materialized temp table with only required columns and filters, partitioned by date to speed subsequent queries. - If full scan still costly, generate a stratified sample by channel and date (e.g., 5% sample using HASH(user_id) % 100 < 5) to approximate metrics and surface trends quickly.- Validate & document assumptions: Compare sample vs small full-day full-scan to estimate sampling error; note date range, channel filter, sampling rate, missing data caveats, and any known ETL lag.- Deliver & communicate: Built simple Power BI dashboard with the temp table aggregates, highlighted margins of error, and sent an executive summary email explaining what’s definitive vs provisional and next steps (full modeled pipeline and automation).Result: Delivered the report the next morning; stakeholder got the actionable insights needed for a planning meeting. They accepted the sampled figures for immediate decisions and scheduled follow-up to implement a persistent modeled dataset. This approach balanced speed, defensibility, and clear communication of limitations.
Unlock Full Question Bank
Get access to hundreds of Technical Tools and Competency interview questions and detailed answers.
