Data Driven Decision Making Questions

Using metrics and analytics to inform operational and strategic decisions. Topics include defining and interpreting operational measures such as throughput cycle time error rates resource utilization cost per unit quality measures and on time delivery, as well as growth and lifecycle metrics across acquisition activation retention and revenue. Emphasis is on building audience segmented dashboards and reports presenting insights to influence stakeholders diagnosing problems through variance analysis and performance analytics identifying bottlenecks measuring campaign effectiveness and guiding resource allocation and investment decisions. Also covers how metric expectations change with seniority and how to shape organizational metric strategy and scorecards to drive accountability.

HardTechnical

32 practiced

You need to establish a single source of truth for metric definitions across decentralized business units. Propose a governance model (roles, approvals), metric registry schema, technical enforcement mechanisms (semantic layer, protected views), onboarding and change-control processes, and how to measure trust and adoption.

Sample Answer

Situation: Multiple business units define the same KPIs differently (e.g., "active user", "ARR"), causing conflicting dashboards and decisions. My goal would be to establish a single source of truth (SSOT) for metric definitions with governance, technical enforcement, onboarding, change control, and adoption measurement.

Governance model (roles & approvals)- Metric Steward (one per domain): owns definitions, lineage, calculation logic, tests.- Central Data Council (cross-functional): approves new canonical metrics, resolves conflicts, enforces policies. Composed of BI leads, product finance, engineering, and legal/privacy.- Data Platform Engineer: implements semantic layer and protected artifacts.- Metric Requestor / Consumer: proposes metrics and documents use cases.Approval flow: proposer → Metric Steward review (5 business days) → if conflict or cross-domain impact, escalate to Data Council (7 business days). Emergency hotfix path with post-approval audit.

Metric registry schema (canonical fields)- metric_id (UUID)- name (human-friendly)- short_description- formal_definition (pseudocode / SQL)- numerator, denominator (if ratio)- grain (row-level, user-day, account-month)- aggregation (sum, distinct_count)- time_zone & business day convention- owner (Metric Steward)- source_tables & column_lineage- sampling / refresh_frequency- last_validated (date), validation_queries (links)- tags (finance, product, retention)- sensitivity / PII_flag- version & change_notes- approved_by & approval_dateExample: metric_id=mx-001, name="Monthly Active User", grain=user-month, definition=COUNT(DISTINCT user_id) WHERE event_date BETWEEN month_start AND month_end AND event_type IN ('login','purchase').

Technical enforcement mechanisms- Semantic layer (LookML/ dbt + metrics layer): implement canonical metrics as parameterized models or dbt models that expose one SQL definition. All BI tools must reference this layer.- Protected views: create read-only, audited views in the warehouse (schema canonical_metrics) that encapsulate metric SQL. Grant access to views, not base tables.- Metric registry API: programmatic registry for discovery; tools integrate to fetch definitions and SQL.- CI/CD & tests: dbt tests (freshness, null checks, row counts) run on PRs. Pre-merge checks block changes failing tests.- Access controls & lineage: use data catalog (Collibra/Alation) to show lineage and enforce PII handling.- Monitoring: auto-alerts when metric freshness or validation queries deviate.

Onboarding & change-control process- Onboarding: 1) Training for stakeholders on registry + semantic layer; 2) Template for metric proposals (use case, consumers, owner, SLA); 3) Mentored first submission with Metric Steward.- Change control: versioned changes via Git + Pull Request to metric repo (dbt models). PR must include: updated registry entry, updated tests, impact analysis (list of dashboards referencing metric via automated dependency scan). Approval gates: Metric Steward sign-off + automated test pass → merge → staged rollout (canary dashboards) → production after 48h monitoring.- Rollback: maintain prior metric version and automate rollback path if anomalies observed.

Measuring trust and adoption- Adoption KPIs: - % of dashboards using canonical metrics (tracked via lineage scanner) - % of BI queries referencing protected views vs raw tables - Time-to-resolution for metric disputes- Trust KPIs: - Metric validation pass rate (automated tests) - Freshness SLA compliance - Consumer trust survey (quarterly): Net Trust Score asking stakeholders if metrics are accurate/reliable (target >70%). - Number of metric definition exceptions / shadow metrics created outside registry (goal: decreasing trend).Operationalize measurement in a BI dashboard showing adoption, test pass rates, freshness, and trust survey results; present monthly to Data Council.

Why this works- Clear roles + SLAs create accountability.- Registry + semantic layer provides single-authoritative SQL and discoverability.- Protected views + CI enforce technical correctness and prevent ad-hoc divergence.- Onboarding and automated impact analysis reduce friction for adopters.- Quantitative and qualitative metrics let leadership track trust and iterate.

Example quick win: convert top 10 most-used metrics to canonical views first; require new dashboards to use them. Track % usage monthly — early wins build momentum and reduce shadow reporting.

HardTechnical

32 practiced

Your nightly ETL window currently takes 8 hours but the business requires dashboards updated within 1 hour after midnight. Propose a detailed optimization plan including CDC (change data capture), incremental transforms, partition pruning, parallel processing, materialized views, and an approach to estimate cost trade-offs on cloud providers.

Sample Answer

Requirements & goal:- Deliver dashboards with data available within 1 hour after midnight (SLI: data freshness <= 1h).- Current full ETL = 8 hours; must reduce to <1h reliably.

Optimization plan (phased, low-risk -> higher change):

1) Measure baseline & define SLAs- Instrument current pipeline: per-stage runtimes, I/O, CPU, memory, data volumes, row-level change rates.- Identify longest stages and downstream consumers; set success/failure alerts.

2) Adopt CDC for source ingestion- Use log-based CDC (Debezium, GoldenGate, Cloud Dataflow/Datastream) to capture row-level changes continuously.- Persist changes in a change table or streaming topic (Kafka, Kinesis, Pub/Sub) with schema and watermark/offsets.- Benefits: avoids full table scans; provides event-time for incremental downstream processing.

3) Move to incremental transforms- Implement incremental ETL jobs that process only changed rows since last successful run using CDC offsets or last_updated timestamps.- Use idempotent, upsert logic (MERGE INTO) to safely apply changes to dimensional and fact tables.- For complex transformations, use stream/batch hybrid: micro-batches (e.g., 5–15 min windows) to aggregate events and then materialize nightly summary.

4) Partitioning and pruning- Partition large tables by date (ingest_date/event_date) and frequently filtered columns.- Ensure partition pruning: queries must include partition column predicates; propagate partition filters from BI layer.- Use bucketing/clustering (e.g., BigQuery clustering, Redshift sort keys) on high-selectivity join keys for faster scans.

5) Parallelism & resource scaling- Break jobs into independent tasks (per-source, per-table, per-partition) and execute in parallel using orchestration (Airflow, Cloud Composer).- Use scalable compute: autoscaling Spark/Dataproc, EMR, Databricks jobs; prefer short-lived clusters or serverless SQL where available.- Tune concurrency to balance I/O limits (DB connections, S3/Cloud Storage throughput).

6) Materialized views and pre-aggregation- Identify heavy dashboard queries and create materialized views / aggregated tables refreshed incrementally (on-change or scheduled micro-batches).- Use OLAP cubes or BI extracts for ultra-low-latency needs.- Configure lightning-fast serving layer (read replicas, columnar stores).

7) Testing, data quality, and rollback- Implement backfill, dry-run, and reconciliation jobs comparing aggregate checksums and row counts.- Maintain audit logs of CDC offsets and job checkpoints for safe rollback/replay.

Cost trade-off estimation approach (cloud)- Model costs per component: storage (per GB-month), ingestion egress, compute (vCPU-hour), managed services (per-query or per-node), and operational overhead.- Estimate three scenarios: baseline (current full-load), optimized (CDC + incremental + materialized views), and aggressive (real-time/streaming).- For each scenario: - Calculate expected compute hours per night (batch + micro-batches), storage for change streams, and query costs for materialized view refreshes. - Apply provider pricing (on-demand vs spot/preemptible; reserved instances where stable); factor in data egress and monitoring costs. - Compute ROI: (reduced latency benefits quantified as business value — e.g., faster decision cycles, reduced SLA penalties) vs incremental monthly cloud cost.- Sensitivity analysis: vary change-rate, concurrency, and retention windows to find cost-optimal operating point.

KPIs & rollout- Target: first incremental data within 10–15 minutes after midnight; full dashboard consistency within 60 minutes.- Track freshness, success rate, end-to-end latency, compute cost per day.- Rollout: pilot on high-value dashboards -> extend to all. Maintain fall-back nightly full-run until incremental runs reach reliability.

Trade-offs- CDC and incremental complexity vs dramatic cost/time savings.- Materialized views add storage and refresh cost but give fastest dashboard response.- Higher parallelism increases peak compute cost; mitigate with autoscaling/spot instances.

This plan delivers sub-1-hour freshness by eliminating full reprocessing, processing only deltas, parallelizing tasks, and precomputing aggregates—balanced against cloud cost via scenario-based modeling.

HardTechnical

36 practiced

A highly interactive dashboard is slow and costly. Evaluate the trade-offs between interactivity and query performance, and outline concrete solutions: pre-aggregation tables, materialized views, OLAP cubes, streaming or incremental updates, client-side caching, and reduced interactivity patterns. Provide criteria to choose the right approach for different dashboards.

Sample Answer

Framework: clarify requirements (who uses dashboard, SLA for response, data freshness, cardinality/complexity of queries, cost constraints). Then evaluate trade-offs and map concrete solutions to scenarios.

Trade-offs between interactivity and performance:- High interactivity (ad-hoc filters, many joins, row-level security) => slower, higher compute cost.- Faster queries require pre-computation or reduced flexibility; trade business agility for latency and cost.

Concrete solutions and when to use them:- Pre-aggregation tables: build aggregated tables by common dimensions/time buckets. Good for KPI-heavy dashboards with predictable query patterns. Lowers query cost drastically; needs ETL and storage.- Materialized views: DB-managed precomputed results, refreshed on schedule or incrementally. Use when DB supports fast refresh and underlying data changes moderately.- OLAP cubes: multidimensional models (e.g., SSAS, BigQuery BI Engine) for very fast slice-and-dice at scale and many users. Best when many pre-defined dimensions/measures and low refresh frequency.- Streaming/incremental updates: push near-real-time delta updates to aggregates (Kafka, CDC). Use when freshness matters (<1 min) and dataset is large.- Client-side caching: cache user session results or browser storage for repeated interactions (paging, toggles). Low-cost for personalization and transient filters.- Reduced interactivity patterns: limit number of concurrent filters, replace expensive visuals with summary + drilldowns, use parameterized controls. Use when user workflows tolerate staged exploration.

Implementation checklist:- Profile queries and identify hotspots (slow SQL, big joins, high-cardinality filters).- Classify dashboards by usage: Executive (low-frequency, real-time not required) → pre-agg/materialized; Operational (real-time, high frequency) → streaming + OLAP/BI engine; Exploratory (ad-hoc analysts) → direct access but with quota and sandbox environments.- Monitor cost and add automated refresh strategies (e.g., incremental nightly + on-demand refresh).- Add UX constraints: default filters, note data freshness, progressive disclosure (summary then drill).

Decision criteria:- Query complexity & cardinality- Required freshness (seconds vs hours)- Number of users and concurrency- Predictability of access patterns- Cost tolerance and infra capabilities

Outcome example: for a slow executive dashboard I replaced live joins with daily pre-aggregations and enabled client caching for 80% of interactions—latency fell from 6s to 400ms and compute costs dropped 60% while preserving necessary drillpaths.

MediumTechnical

26 practiced

Write a PostgreSQL query to compute RFM (recency, frequency, monetary) for active users over the past 365 days using tables:

users(user_id bigint, created_at timestamp);
transactions(order_id bigint, user_id bigint, amount numeric, order_ts timestamp);

Flag users at high churn risk where recency > 90 days, frequency <= 1, and monetary < median. Use window functions or aggregates efficiently and explain any assumptions.

Sample Answer

Approach:- Define "active" as users with at least one transaction in the past 365 days (state this assumption; see alternatives below).- Aggregate transactions in the 365-day window per user to compute recency (days since last order), frequency (count), and monetary (total spend).- Compute cohort median monetary across those active users using PostgreSQL percentile_cont(0.5) as a window aggregate.- Flag churn risk where recency > 90, frequency <= 1, and monetary < median.

SQL (Postgres):

sql

WITH recent_tx AS (
  SELECT
    t.user_id,
    t.order_ts,
    t.amount
  FROM transactions t
  WHERE t.order_ts >= now() - interval '365 days'
),
user_agg AS (
  SELECT
    u.user_id,
    -- days since last transaction in the 365-day window
    EXTRACT(day FROM (now() - MAX(r.order_ts)))::int AS recency_days,
    COUNT(r.order_ts) AS frequency,
    SUM(r.amount)::numeric(18,2) AS monetary
  FROM users u
  JOIN recent_tx r ON u.user_id = r.user_id
  GROUP BY u.user_id
),
monetary_median AS (
  SELECT
    percentile_cont(0.5) WITHIN GROUP (ORDER BY monetary) AS median_monetary
  FROM user_agg
)
SELECT
  ua.user_id,
  ua.recency_days,
  ua.frequency,
  ua.monetary,
  mm.median_monetary,
  (ua.recency_days > 90 AND ua.frequency <= 1 AND ua.monetary < mm.median_monetary) AS high_churn_risk
FROM user_agg ua
CROSS JOIN monetary_median mm
ORDER BY high_churn_risk DESC, ua.monetary DESC;

Key points / reasoning:- recency computed from max(order_ts) within the 365-day window; users without transactions in window are excluded (per assumption).- frequency is number of orders in window; monetary is total spend in window.- median computed over aggregated per-user monetary to compare users fairly.- CROSS JOIN monetary_median attaches the single median value to every user.

Complexity & performance:- Aggregation cost O(#transactions in 365d). Ensure index on transactions(user_id, order_ts) to speed filtering and grouping.- percentile_cont computes median over number of active users (not raw transactions) — efficient.

Edge cases & alternatives:- If "active" should include users with no transactions but created in last 365 days, change user_agg to LEFT JOIN recent_tx and treat frequency=0, monetary=0, recency as NULL or days since created_at.- For ties or different median semantics, use percentile_disc(0.5) for discrete median.- For large datasets, pre-aggregate nightly into a summary table to avoid scanning full transactions in dashboards.

EasyTechnical

44 practiced

Write a PostgreSQL query to compute Monthly Active Users (MAU) for the last 6 months given the table:

events(
  event_id bigint PRIMARY KEY,
  user_id bigint,
  event_type text,
  event_ts timestamp with time zone
)

Return rows with columns: month (YYYY-MM) and mau_count. Use calendar months and ensure each user is counted once per month. Explain any timezone assumptions.

Sample Answer

Approach: generate the last 6 calendar-month start dates (including current month), aggregate events by month (count distinct user_id) using a fixed timezone for month boundaries, then left-join so months with zero users are shown. I assume month boundaries are defined in UTC (change timezone string if you want business-local boundaries).

sql

WITH months AS (
  -- generate last 6 calendar months (month starts) in UTC
  SELECT generate_series(
    date_trunc('month', now() AT TIME ZONE 'UTC') - interval '5 months',
    date_trunc('month', now() AT TIME ZONE 'UTC'),
    '1 month'
  ) AS month_start
),
mau AS (
  -- count distinct users per month, using UTC month boundaries
  SELECT
    date_trunc('month', event_ts AT TIME ZONE 'UTC') AS month_start,
    COUNT(DISTINCT user_id) AS mau_count
  FROM events
  WHERE event_ts >= date_trunc('month', now() AT TIME ZONE 'UTC') - interval '5 months'
    AND user_id IS NOT NULL
  GROUP BY 1
)
SELECT
  to_char(m.month_start, 'YYYY-MM') AS month,
  COALESCE(a.mau_count, 0) AS mau_count
FROM months m
LEFT JOIN mau a ON a.month_start = m.month_start
ORDER BY m.month_start;

Key points / assumptions:- I used UTC for month boundaries: event_ts AT TIME ZONE 'UTC'. If your business uses a local timezone (e.g., 'America/Los_Angeles'), swap 'UTC' for that.- COUNT(DISTINCT user_id) ensures each user is counted once per month.- We filter events to the 6-month window for performance.- Exclude NULL user_id to avoid counting anonymous IDs; adapt if you want to include them.- Months with no activity return 0 due to LEFT JOIN + COALESCE.

Unlock Full Question Bank

Get access to hundreds of Data Driven Decision Making interview questions and detailed answers.

Join thousands of developers preparing for their dream job.