Relevant Technical Experience and Projects Questions

Describe hands-on technical work and projects that directly relate to the role you are interviewing for. Cover the specific tools, platforms, or technologies you used, tailored to your own domain (for example: programming languages and frameworks, cloud or infrastructure tooling, data or analytics platforms, security tooling, or specialized hardware and software relevant to your field). For each project, explain your individual role, the scope and scale of the work (team size, data or user volume, timeline), the key technical decisions and trade-offs you made, measurable outcomes or improvements you drove, and what you learned. Include relevant certifications or training when they reinforced your technical skills. Also discuss any process improvements you introduced, the cross-functional collaboration required, and how this project experience demonstrates readiness for the specific role.

HardTechnical

33 practiced

Explain how you'd implement version control and code review workflows for BI artifacts (LookML, Tableau workbooks, Power BI PBIX, SQL scripts). Address diffing of binary files, promoting changes across dev/staging/prod, automated validation (lint/tests), and governance controls to prevent accidental production breaks.

Sample Answer

Situation/Approach (summary):Treat BI artifacts as code: source in Git, peer-reviewed changes, automated validation gates, and controlled deployments through dev→staging→prod. Use artifact-specific serialization/translation for diffing binary files and CI/CD to promote safely.

Branching & review workflow:- Branch-per-feature from main (dev branch) → open PR against staging branch. PR must include screenshots/preview links, change description, affected datasets.- Protected branches: staging and prod require 2 approvals + successful CI.- Use code owners for dashboards, datasets, and sensitive areas.

Diffing & source formats:- LookML: native text files — store in Git, run looker lint (lookml-lint) and unit tests.- Tableau: store workbook as packaged workbook (TWBX) can be converted to TWB (XML) for diffs. Use tableau-tools / TSC to export TWB and resources; commit XML and datasource connections separately. For extracts use TDE/Hyper artifacts stored in artifact repo.- Power BI: PBIX is binary. Use PBIT (template) or extract model metadata with Tabular Editor/ALM Toolkit to serialize model (TMSL/JSON) and report layout via Power BI REST export or Power BI CLI. Store serialized JSON/TMSL + report assets in Git for diffs.- SQL scripts: plain text in folders with naming conventions and migration files; use dbt for transformation code where possible (dbt models are SQL + YAML, great for testing).

Automated validation (CI):- Linting: lookml-lint, sqlfluff for SQL, Prettier or xml/JSON linters for serialized Tableau/PBI artifacts.- Static checks: verify connection strings and credential placeholders; detect hard-coded PII or prod credentials.- Unit/smoke tests: run test queries against a sandbox dataset to validate row counts, key metrics, column types. Use a test DB or a schema copy; for Looker use Looker API to run queries; for Tableau/Power BI use headless rendering or export image tests.- Data regression: Great Expectations or custom tests to compare key metric baselines.- Visual tests: automated screenshot diffs using headless rendering (tableau-server-client, Power BI report render API) for critical dashboards.- Complexity: run tests in CI (GitHub Actions/GitLab CI/Cloud Build). PR blocked if tests fail.

Promotion & deployment:- Merge to staging triggers deployment to staging server (Looker dev mode deploy to staging, Tableau server project, Power BI workspace).- Stakeholder QA in staging; after sign-off, create release PR to prod branch with changelog and automated smoke tests against prod (read-only).- Use atomic deploys and environment-specific parameters (no hard-coded IDs). For Looker use git-managed production deploy flow; for Tableau/Power BI use server APIs to publish with overwrite flags and backup previous versions.

Governance & safeguards:- Require approvals from data steward or owner for schema/semantic changes.- Protected production datasets: any change to authoritative datasets must go through change request ticket and scheduled deployment window.- Rollback: keep versioned backups (Tableau versioning, Power BI export, Looker Git history) and automated rollback scripts.- Monitoring: post-deploy health checks (query latency, null-rate thresholds, dashboard render errors) and alerting (Slack/email).- Access controls: least-privilege on publishing APIs; CI service accounts scoped to staging/prod with audit logs.- Audit & lineage: enforce metadata capture (who changed what, list of affected reports/dashboards), integrate with data catalog (e.g., DataHub, Amundsen).

Example CI job (pseudo):- Checkout branch- Run linters: sqlfluff, lookml-lint- Serialize Tableau/PBIX to XML/JSON and run diffs- Run unit queries against sandbox and compare expected values- Render screenshots for visual diff- If all pass, allow merge; merge to staging triggers publish via APIs

Why this works:- Text-first storage enables code review and diffs.- Serialization of binaries provides meaningful diffs and CI checks.- Automated tests catch data/regression/visual issues before prod.- Branch protections + approvals + scheduled deploys reduce accidental production breaks while keeping an auditable history.

EasyTechnical

35 practiced

Given an orders table (order_id, user_id, order_date, amount), write an SQL query to compute each customer's lifetime value (LTV) as sum(amount) and return the top 5 customers by LTV. State any assumptions (refunds, time window) and how LTV calculations differ for subscription models vs one-time purchases.

Sample Answer

Approach: aggregate orders per user summing amount to compute LTV, handle refunds by treating negative amounts, optionally filter by time window, then order and take top 5.

sql

-- Top 5 customers by lifetime value (LTV)
-- Assumes refunds are negative amounts in `amount`. To restrict to last N years, add WHERE order_date >= ...
SELECT
  user_id,
  SUM(amount) AS lifetime_value,
  COUNT(*) AS order_count,
  MIN(order_date) AS first_order,
  MAX(order_date) AS last_order
FROM orders
-- optional time-window example: uncomment to limit to last 3 years
-- WHERE order_date >= CURRENT_DATE - INTERVAL '3 years'
GROUP BY user_id
ORDER BY lifetime_value DESC
LIMIT 5;

Key points:- Assumptions: refunds are negative rows or separate refunds table; if refunds are separate, join and subtract. Decide time window (all-time vs trailing 12/36 months) based on business need.- Subscription vs one-time: for subscriptions LTV often uses ARR/MRR × expected lifetime (churn-based) or cohort retention; for one-time purchases LTV = sum(amount) or average order value × purchase frequency × retention horizon.- Edge cases: users with only refunds, fraudulent orders, null amounts—clean data beforehand.

HardTechnical

37 practiced

You're given an environment where different teams define the same KPIs differently. Propose a metric governance framework to eliminate inconsistencies: include a central metric catalog, metric ownership, canonical definitions, source-of-truth tables, automated tests, change-management process, rollout plan, and suggested tooling.

Sample Answer

Situation / objective: Multiple teams report conflicting KPIs (e.g., MAU, Conversion Rate), causing bad decisions. I’d implement a metric governance framework that creates a single source of truth, clear ownership, and safe change processes.

Framework overview (high-level steps)1. Governance body- Create a Metrics Council (BI lead + product, finance, ops) to approve definitions, prioritize disputes, and review changes monthly.

2. Central metric catalog- Single searchable catalog (Collibra/Alation or open: a Looker/LookML or dbt + docs site) listing: - Canonical metric name, business description, formula, dimensions, owner, lineage, alerts, last updated, SLA.

3. Metric ownership & RACI- Assign an owner (R) and steward (S) for each metric. Owners approve definition/changes; stewards implement transforms and tests. Stakeholders (C) must be notified for changes.

4. Canonical definitions & examples- Every metric has: natural language definition, SQL canonical formula, aggregation rules, timezone, inclusion/exclusion rules, sample rows. Example: - MAU: "Unique users with >=1 session in 28-day window" plus canonical SQL and filter on bots.

5. Source-of-truth tables- Build curated, versioned metric tables in the warehouse (e.g., metrics marts in Snowflake/BigQuery), populated by tested ETL/ELT. Consumers read these tables instead of ad-hoc joins.

6. Automated tests & CI- Implement data quality tests (dbt tests + custom checks), schema and lineage checks, regression tests (Great Expectations / Soda). Run in CI (GitHub Actions/CI) and block deploys on failures. Add monitoring/alerts for drift.

7. Change-management process- All metric change via pull request with: - Impact analysis (downstream dashboards), - Migration plan, - Backfill approach, - Stakeholder sign-off.- Use semantic versioning for metrics; keep old metric IDs for historical reproducibility.

8. Rollout plan- Phase 1 (0–6 weeks): Inventory + quick wins — identify top 20 high-impact metrics, assign owners, publish definitions.- Phase 2 (6–16 weeks): Implement canonical tables and tests for top metrics; onboard product/finance users.- Phase 3 (3–6 months): Catalog all metrics, integrate with BI tools, enforce read-from-SOT policy.- Phase 4: Ongoing: monthly review, training, and audits.

9. Adoption & change management- Enforce via BI templates/dashboards that surface canonical metric links, provide migration guides, host office hours, and require stakeholders to sign SLAs for use.- Provide dashboards that show metric discrepancies and adoption rates as KPI for governance.

Suggested tooling- Warehouse: Snowflake / BigQuery- Transform & docs: dbt + dbt docs- Catalog: Collibra / Alation / Amundsen / OpenMetadata- Testing/monitoring: Great Expectations, Soda, Monte Carlo (data observability)- CI/CD: GitHub Actions / GitLab CI- Orchestration: Airflow / Prefect- BI: Looker (LookML), Tableau, Power BI with direct links to catalog- Communication: Confluence + Slack + change request via Jira

Success metrics- Reduction in metric definition disputes, % of dashboards using canonical metrics, time-to-resolve metric incidents, and data quality alert rates.

This balances governance with pragmatism: curate high-impact metrics first, automate quality gates, and make it easy for analysts to consume canonical metrics so teams stop reinventing definitions.

EasyTechnical

33 practiced

In Excel Power Query (M), describe how you would unpivot a table that has monthly columns (Jan, Feb, Mar...) into a normalized rows format with columns [Month, Value]. Explain the steps, why unpivoting is preferred for BI workflows, and how it supports downstream aggregations and dashboards.

Sample Answer

Approach: convert wide monthly columns into rows using Unpivot in Power Query so you get a normalized table with [Month, Value]. This makes measures, time intelligence, and filtering consistent for BI.

Steps in Power Query UI (or M):1. Load table into Power Query.2. Select the key identifier columns you want to keep (e.g., Product, Region).3. Right-click the monthly columns (Jan, Feb, Mar...) → Unpivot Columns (or Unpivot Other Columns if you selected keys).4. Rename the generated Attribute column to Month and Value column to Value.5. Optionally transform Month to a proper date (e.g., add year + parse to Date) and change Value type to numeric.

M example:

powerquery

let
  Source = Excel.CurrentWorkbook(){[Name="Sales"]}[Content],
  #"Changed Types" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Region", type text}}),
  #"Unpivoted Months" = Table.UnpivotOtherColumns(#"Changed Types", {"Product","Region"}, "Month", "Value"),
  #"Parsed Month" = Table.AddColumn(#"Unpivoted Months", "MonthDate", each Date.FromText("2025-" & Text.PadStart(Text.From(List.PositionOf({"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"}, Text.Start([Month],3))+1),2,"0") & "-01"), type date)
in
  #"Parsed Month"

Why unpivot is preferred:- Normalized (long) format aligns with star-schema and analytics tools expectations.- Simplifies writing measures/aggregations (SUM, AVERAGE) across time without dynamic column references.- Enables time-series functions, sorting, and proper axis behavior in visuals.- Easier to join with calendar tables, perform month-over-month or YoY calculations, and apply slicers.

Downstream benefits:- Dashboards become dynamic: new months appended as rows auto-flow through transformations without changing query steps.- Performance: aggregation queries operate on a single column rather than many structurally-changing columns.- Maintainability: fewer schema changes, consistent ETL logic, and easier troubleshooting.

HardSystem Design

40 practiced

Design a BI reporting and analytics architecture for an IoT deployment: 100,000 devices, peak 1,000,000 events per minute, dashboards that require <5s latency for operational metrics, and historical analytics queries across months. Describe ingestion, messaging, stream processing, storage (hot vs cold), serving layer for dashboards, cost/performance trade-offs, and how you'd ensure reliability.

Sample Answer

Requirements / constraints:- 100k devices, peak 1,000,000 events/min (~16.7k ev/s)- Dashboards: <5s latency for operational metrics (near real-time)- Historical analytics across months (cost-effective cold store)- BI user role: needs stable, easy-to-query schema for Looker/Tableau/PowerBI

High-level architecture:Devices → Edge collectors / MQTT brokers → Kafka (ingestion) → Stream processing (real-time) → Hot serving + Cold storage

Components & choices:1. Ingestion & messaging- Use MQTT at edge with regional brokers; bridge to Kafka (Confluent/Kafka cluster or managed MSK/Confluent Cloud).- Kafka handles bursts, retention for replays. Partition by device_id/tenant to scale.

2. Stream processing- Stateful stream processors (Apache Flink or Kafka Streams) compute rolling aggregates (1s–1m windows), enrich events (device metadata), detect anomalies, and write: - Hot outputs to fast analytical store (clickhouse, Druid, or materialized views in BigQuery BI Engine/Bigtable). - Raw + transformed events to cold storage (partitioned Parquet in S3/GCS).

3. Storage: hot vs cold- Hot: low-latency OLAP store (ClickHouse or BigQuery with BI Engine) for operational dashboards (<5s). Keep recent 7–30 days of pre-aggregated metrics and raw recent minute-level data.- Cold: columnar files (Parquet) in S3/GCS, partitioned by date/device, for monthly historical analytics. Use an analytical warehouse (BigQuery/Athena/Snowflake) to query months efficiently.

4. Serving layer for dashboards- For operational dashboards: serve queries from hot store / materialized aggregates exposed via SQL to BI tools. Precompute common KPIs (rollups by minute/hour/device-group).- For deep historical: federated queries over cold store or scheduled ETL into warehouse partitions; use scheduled aggregate tables for common dimensions.

5. Cost / performance trade-offs- More pre-aggregation → lower query latency and cost, but higher compute on ingestion and storage of many rollups.- Hot store (ClickHouse) → low latency & lower scan costs; BigQuery → easier maintenance, pay-per-query but higher per-query cost at scale. Choose based on team expertise and cost model.- Retain raw events longer in cold storage to avoid re-ingestion.

6. Reliability & correctness- Kafka with replication, multiple brokers, retention; producers use retries and idempotent writes.- Exactly-once processing semantics in Flink or idempotent upserts to hot store.- Schema registry (Avro/Protobuf) for contract evolution.- Monitor: end-to-end SLAs, lag, throughput, error rates; alerting, automatic consumer restarts.- Backfill: replay from Kafka/Parquet for reprocessing.- Disaster recovery: cross-region replication for Kafka and object storage snapshots.

Operational notes for BI analyst:- Define canonical event models and dimension tables (device metadata, location, firmware).- Provide curated SSAS-like semantic layer or LookML/Power BI datasets exposing pre-aggregated metrics with time grain controls.- Maintain runbook for latency incidents and data quality checks (row counts, nulls, drift).

This design meets <5s latency for operational metrics via hot pre-aggregates, supports month-scale historical analytics cheaply via Parquet+warehouse, and emphasizes reliability via durable messaging, schema governance, exactly-once processing, and monitoring.

Unlock Full Question Bank

Get access to hundreds of Relevant Technical Experience and Projects interview questions and detailed answers.

Join thousands of developers preparing for their dream job.