Dashboard and Data Visualization Design Questions

Principles and practices for designing, prototyping, and implementing visual artifacts and interactive dashboards that surface insights and support decision making. Topics include information architecture and layout, chart and visual encoding selection for comparisons trends distributions and relationships, annotation and labeling, effective use of color and white space, and trade offs between overview and detail. The topic covers interactive patterns such as filters drill downs tooltips and bookmarks and decision frameworks for when interactivity adds user value versus complexity. It also encompasses translating analytic questions into metrics grouping related measures, wireframing and prototyping, performance and data latency considerations for large data sets, accessibility and mobile responsiveness, data integrity and maintenance, and how statistical concepts such as statistical significance confidence intervals and effect sizes influence visualization choices.

HardSystem Design

139 practiced

You are asked to support near-real-time dashboards with high-cardinality dimensions such as per-user metrics. Evaluate streaming vs micro-batch architectures, summarization strategies (sketches, approximate counts, rollups), and discuss trade-offs in cost, latency, consistency, and query complexity.

Sample Answer

Requirements & constraints:- Freshness: near-real-time (seconds–low minutes)- High-cardinality: per-user, millions of distinct keys- Queries: interactive dashboards (filters, funnels, cohorts)- Budget & ops: moderate (BI team, not SRE-heavy)- Consistency: eventual ok; occasional bounded error acceptable for aggregated KPIs

Architecture choices — Streaming vs Micro-batch- Streaming (Kafka + stream processor like Flink/ksqlDB/Beam): - Pros: sub-second–second latency, continuous stateful aggregations, can maintain per-user state, supports windowed real-time metrics. - Cons: higher operational complexity, state storage (RocksDB), costly at massive cardinality, harder to support arbitrary ad-hoc queries.- Micro-batch (Spark Structured Streaming / Airflow + frequent batch jobs): - Pros: simpler ops, easier to recompute/correct, cheaper for heavy joins/complex transforms, good for minute-level freshness. - Cons: latency in seconds–minutes depending on batch interval, coarser event-time semantics.

Summarization strategies- Rollups (time×user→pre-aggregates): - Good for common queries (daily active users, per-user totals) - Requires design of rollup granularity; explosion if too many dimensions.- Sketches (HyperLogLog, Count-Min, T-Digest): - HyperLogLog for unique counts (DAU) — tiny memory, probabilistic error. - Count-Min for heavy hitters (top-N) — biased overestimates, tunable error. - T-Digest for quantiles (latency distributions). - Pros: dramatic memory savings, fast updates in streaming. - Cons: approximate answers, complexity when combining with filters.- Stateful per-key stores (Redis, Cassandra, materialized views): - Exact counts per user but costly at scale.

Trade-offs- Latency vs Cost: - Streaming + per-key state = lowest latency but highest cost/complexity. - Micro-batch + rollups/sketches = lower cost, minutes-level freshness.- Consistency vs Complexity: - Streaming offers stronger event-time/window semantics but needs careful watermarking to avoid duplicates. - Micro-batch simplifies correctness (recompute windows) and is easier for ad-hoc corrections.- Query complexity vs Accuracy: - Pre-aggregated rollups enable fast dashboard queries but limit ad-hoc slicing. - Sketches enable scale but require UI to surface "approximate" and error bounds; combining many filters reduces accuracy. - Keeping raw events in a data lake for on-demand exact queries (slower) balances flexibility.

Recommended approach for BI role- Hybrid: - Streaming ingestion to compute time-windowed rollups for common KPIs and HyperLogLog for uniques, materialize to a low-latency store (e.g., ClickHouse, Druid, BigQuery BI Engine). - Micro-batch hourly/daily jobs to rebuild full aggregates and backfill/correct state. - Keep raw events in object storage for exact/research queries. - Surface error bounds in dashboards and provide "refresh" for exact counts when needed.- Operational tips: - Design rollup matrix focusing on most-used filters. - Instrument alerts for sketch drift and state-store sizes. - Provide users with "near-real-time" vs "finalized" labels and last-updated timestamps.

MediumSystem Design

62 practiced

As a BI Analyst, design a prototype for an executive sales dashboard for a global company. Requirements: support multiple currencies, show 30-day trend, YoY comparison, top regions and products, and allow drill-to-transaction. Describe the layout, key visuals, currency handling, interaction patterns, prototyping tools, and validation plan with stakeholders.

Sample Answer

Requirements clarification:- Executive-level: high-level KPIs + ability to drill to transaction.- Functional: multi-currency, 30-day trend, YoY comparison, top regions/products, transaction-level drill.- Non-functional: <2s interactions, secure, mobile-friendly.

Layout (single-screen, top-down):1. Header: company logo, date selector, currency selector (dropdown), territory selector, refresh timestamp.2. KPI strip (big numbers): Total Revenue (selected currency), Gross Margin %, Orders, Avg Order Value — each shows % vs. prior period and YoY sparkline.3. Trend panel (left): 30-day revenue line with 30D rolling average and YoY overlay (same 30-day window previous year).4. Rankings panel (right): Top 5 Regions (map + bar chart) and Top 5 Products (horizontal bars) with contribution %.5. Anomalies/Highlights: callouts for week-over-week spikes, large refunds, or currency revaluations.6. Drill table (bottom): paginated transaction table with filtering; each row expandable to show order details and link to source system.

Key visuals & interactions:- KPI tiles: click to filter dashboard to that metric (e.g., click Revenue -> show revenue breakdown).- 30-day chart: hover tooltip with daily metrics; click a point to open date-scoped drill table.- Map: click region to filter all charts and load region transactions.- Product bars: double-click to open product performance modal and transactions.- Drill-to-transaction: from any visual, “View Transactions” action opens contextual, exportable transaction list with direct links to CRM/ERP records.- Cross-filtering and global drill breadcrumbs to track filter context.- Exports: PDF/CSV and scheduled email reports.

Currency handling (precise rules):- Base reporting currency set by executive (dropdown) with persistent user preference.- Use transaction-level currency and timestamped exchange rates table (daily mid-market or controlled corporate rates). Conversion uses rate with timestamp = transaction_date to maintain historical accuracy (no retrospective revaluation unless explicitly requested).- Show a small toggle: “Report in local currencies” to display alongside totals as stacked bars or per-region KPIs.- Display conversion rate source and snapshot date in footer; flag large FX movements.

Data model & performance:- Pre-aggregate daily revenue by currency-region-product in a BI semantic layer; maintain exchange_rates dimension (date).- Use materialized views for 30-day rolling metrics; limit initial query to top N and lazy-load deep transaction data.

Prototyping tools & tech:- Wireframe & interactions: Figma for clickable prototype (stakeholder walkthrough).- Data prototype: Tableau or Power BI connected to sample sanitized dataset to demo drill behavior and calculated currency conversions.- Final: implement in chosen BI tool (Tableau/Power BI/Looker) with SQL-backed semantic layer (dbt models over warehouse).

Validation plan with stakeholders:1. Workshop 1 (requirements + success metrics): confirm KPIs, currency rules, drill depth, SLAs.2. Low-fidelity review (Figma): confirm layout, interactions, and navigation.3. Data validation sprint: build sample dataset; run reconciliation tests (sum revenue by transaction -> dashboard totals) and edge-case checks (returns, FX gaps).4. UAT session with executives: interactive demo using production-like data; capture feedback and tweak visuals/thresholds.5. Sign-off criteria: accuracy within 0.5%, drill-to-transaction completeness, acceptable latency.6. Post-launch: 2-week hypercare, user analytics to track feature usage, quarterly review for metric adjustments.

Risk & trade-offs:- Pre-aggregations speed up UI but increase ETL latency; choose refresh cadence based on exec needs.- Historical revaluation complexity: avoid automatic revaluation to preserve auditability.

This prototype balances executive clarity with traceability to transactions while ensuring correct, auditable multi-currency reporting and an iterative validation path with stakeholders.

HardSystem Design

66 practiced

As a BI lead, propose a system to instrument dashboard usage (filter changes, clicks, time-on-view) across many dashboards, store telemetry, and analyze it to prioritize UX improvements. Define an event schema, storage strategy, sampling or aggregation approach, privacy controls, and typical analysis queries you'd run.

Sample Answer

Requirements and constraints:- Capture interactions (filter changes, clicks, hover, time-on-view, export/refresh) for many dashboards with low overhead.- Retain raw events for 30–90 days, aggregated summaries for 1–3 years.- Support near-real-time dashboards (minutes), ad-hoc analysis, and ML on usage patterns.- Enforce privacy (PII removal, consent, opt-out), GDPR-compliant retention & deletion.

High-level architecture:Browser/BI client (Tableau/Looker/Power BI embedded JS or proxy) → Event collector (API gateway + Kafka) → Stream processor (Flink/ksqlDB) → Storage:- Hot: partitioned ClickHouse / Druid for fast OLAP (last 30 days)- Cold: Parquet on S3 + Athena/BigQuery for long-term analytics- Metadata/lookup: Postgres/catalog for dashboard definitions and user roles

Event schema (JSON):{ event_id: UUID, ts: ISO8601, app: string, // "tableau", "looker" dashboard_id: string, dashboard_version: string, user_id_hash: string, // hashed/anonymized user_role: string, // coarse-grained session_id: string, event_type: string, // "filter_change","click","hover","view_start","view_end","export" target: string, // element id or filter name prev_value: string|null, new_value: string|null, view_duration_ms: int|null, sample_rate: float, ctx: { screen_w:int, screen_h:int, browser:string, referrer:string }}

Sampling & aggregation:- Deterministic client-side sampling by user_id_hash modulus to preserve consistent sampling per user (e.g., 10% for full-detail events).- For high-frequency events (hover, mousemove) always aggregate on client: emit only summaries (counts, avg duration) per session.- Stream processor produces rollups: hourly/day/per-dashboard/per-element aggregated metrics (counts, medians, P95 times, funnel conversion rates).- Store sampled raw + full aggregates. Include sample_rate in events so queries can scale up estimates.

Privacy & compliance:- Hash user identifiers with HMAC and rotate keys periodically; store mapping only in a secure vault if needed for support.- Drop or redact any free-text inputs before sending.- Respect user-level opt-out flag from auth service; do not emit events for opted-out users.- Provide automated retention/deletion pipelines (S3 lifecycle, DB TTL) and audit logs.

Typical analysis queries:- Filter usage frequency: SELECT dashboard_id, target, COUNT(*) FROM events WHERE event_type='filter_change' GROUP BY dashboard_id,target ORDER BY COUNT DESC LIMIT 50;- Time-on-view: SELECT dashboard_id, percentile_cont(0.5) AS median_ms, percentile_cont(0.95) AS p95_ms FROM events WHERE event_type='view_end' GROUP BY dashboard_id;- Funnel conversion (view -> interaction -> export): compute session-level sequences and conversion rates using window functions.- UX prioritization score: combine impacted_users, frequency, avg_time_loss, and business_impact to rank changes.- A/B impact: join feature flag exposures with events to measure differences in engagement.

Trade-offs:- Sampling reduces storage/cost but loses some granularity—keep a small full-sample cohort for experiments.- Near-real-time OLAP (ClickHouse/Druid) costs more but gives actionable insights quickly; cold Parquet for history.

Operational considerations:- Instrumentation library versioning tied to dashboard versions.- Synthetic smoke tests to validate event pipeline.- Dashboards for BI team: event heatmaps, top slow screens, filters with high toggle rates, and prioritized action list with estimated ROI.

MediumTechnical

90 practiced

In Power BI, create a dynamic report title that updates based on selected slicer values for date range and region. Provide the DAX measure for the title, explain how it handles multiple selections and blanks, and describe how you would localize or shorten titles for small screens.

Sample Answer

Approach: build a DAX measure that reads slicer selections for Start/End Date (or a single Date table) and Region slicer, formats them, handles single/multi/blank selections, and returns a concise title. Use SELECTEDVALUE for single selection, CONCATENATEX for multiple, and fallback text for blanks.

dax

Report Title = 
VAR StartDate = MIN('Date'[Date])        -- or SELECTEDVALUE if using explicit slicers
VAR EndDate   = MAX('Date'[Date])
VAR DateText =
    IF(
        ISBLANK(StartDate),
        "All time",
        IF(StartDate = EndDate, FORMAT(StartDate,"MMM d, yyyy"),
           FORMAT(StartDate,"MMM d, yyyy") & " - " & FORMAT(EndDate,"MMM d, yyyy")
        )
    )
VAR Regions =
    VAR selCount = COUNTROWS(VALUES('Region'[RegionName]))
    RETURN
        SWITCH(
            TRUE(),
            selCount = 0, "All regions",
            selCount = 1, SELECTEDVALUE('Region'[RegionName]),
            selCount <= 3, CONCATENATEX(VALUES('Region'[RegionName]), 'Region'[RegionName], ", "),
            selCount > 3, SELECTEDVALUE('Region'[RegionName], selCount & " regions")
        )
RETURN
    "Sales — " & Regions & " | " & DateText

Key points:- Multiple selections: CONCATENATEX lists up to 3; for >3 it shows count to avoid long titles.- Blanks: fallback to "All regions" / "All time".Localization & small screens:- Use a parameter to detect locale or USERPRINCIPALNAME locale mapping, then FORMAT with locale-aware format strings and translate fixed phrases via a small lookup table (LocaleTable).- For small screens, create a compact measure variant: abbreviate months (Jun), shorten "regions" to "rgns" or show just "X regions" and remove separators. Swap displayed title by a page/bookmark optimized for phone.

HardSystem Design

76 practiced

Architect a BI dashboard platform to serve 500,000 monthly users with mixed workloads: executive dashboards require sub-2s load for common queries, analysts run heavy ad-hoc queries, and ETL runs hourly. Describe components, data flows, caching layers, query routing, multi-tenancy and security considerations, and scalability patterns you would adopt.

Sample Answer

Requirements & constraints- 500k monthly users with mixed SLAs: executives need <2s for common cards; analysts run heavy ad-hoc; ETL hourly; multi-tenant orgs, data access rules, auditability.

High-level architecture- Ingestion: Change Data Capture → Kafka (topic per domain) for streaming + batch landing S3.- ETL/Transformation: Airflow/DBT jobs into a cloud data warehouse (Snowflake/BigQuery/Redshift) for served canonical tables and a separate analytical query engine for ad-hoc (Presto/Trino).- Serving layer: - OLAP store for low-latency dashboards: columnar, pre-aggregated cubes (ClickHouse, Pinot, Druid) or materialized views in Snowflake. - Analytics cluster for ad-hoc: Presto/Trino over warehouse or data lake.- Cache & API: Redis edge cache + CDN for dashboard assets. A stateless API layer (Kubernetes) routes requests to cache or query engine.- Visualization: BI tool (Looker/Tableau/Power BI Embedded) connecting via BI API.

Data flow1. Source -> CDC -> Kafka -> raw S32. Airflow/DBT transform -> warehouse + materialized aggregates3. Materialized aggregates -> OLAP store (near-real-time) for executive dashboards4. Ad-hoc queries -> routed to Trino/warehouse; long-running jobs sandboxed

Caching & performance patterns- Multi-layer caching: - CDN + browser caching for static dashboard files - Redis cache for parametrized query results (TTL and cache keys by user/filters) - Precomputed aggregates (hourly/near-real-time) for common KPI card dimensions- Query acceleration: - Use approximate algorithms (HyperLogLog, sampled tables) for exploratory queries - Query federation with pushdown predicates- Query prioritization & resource isolation: - Separate clusters/warehouses: small, highly provisioned cluster for SLA-critical queries; separate elastic cluster for ad-hoc. - Use workload management (Snowflake Resource Monitors / Redshift WLM / Presto queues) to set concurrency, memory limits, and priority. - Short-circuit queries: first check Redis, then OLAP pre-agg, then warehouse.

Multi-tenancy & security- Logical tenant isolation: - Row-level security (RLS) and column masking enforced at warehouse / BI layer. - Optionally separate schemas/projects per major tenant for compliance.- AuthN/Z: - Single Sign-On (OAuth/OIDC + SAML), role-based access control in BI tool and API gateway. - Fine-grained access: translate app roles to DB roles; use ephemeral DB credentials (Vault).- Data governance: - Catalog (Amundsen/Atlas), lineage from ETL jobs, schema versioning. - Audit logs for query access, data modifications (stored in immutable log).- Encryption & compliance: - TLS in transit, KMS-managed encryption at rest, VPC endpoints for storage. - Mask PII at transform stage and maintain tokenization where necessary.

Scalability & resilience- Autoscaling clusters for ad-hoc compute; separate, fixed capacity for SLA-critical OLAP with hot-replica failover.- Sharding and partitioning on time and tenant for storage and OLAP ingestion.- Backpressure handling on Kafka + dead-letter queues for ETL failures.- Blue/green deploys for BI layer; automated chaos testing and SLO-based alerting.- Cost controls: tiered storage (hot for recent, cold for archive), cost-aware query planning and query quotas per user/team.

Operational considerations- SLOs: 95th-percentile executive queries <2s; background SLA for ad-hoc (eg. 5–60s depending on class).- Monitoring: query latency heatmaps, cache hit ratios, ETL freshness (data freshness SLA), user churn metrics.- UX: expose “data freshness” and fallback messages when using approximate/cached results.

Why this design- Separates workloads (low-latency pre-agg vs flexible ad-hoc) to meet conflicting SLAs.- Multi-layer caching + materialized aggregates achieve sub-2s for executives with predictable cost.- Workload management and multi-tenant controls protect stability and enforce security/compliance.

Unlock Full Question Bank

Get access to hundreds of Dashboard and Data Visualization Design interview questions and detailed answers.

Join thousands of developers preparing for their dream job.