Technology Implementation and Change Management Questions

Addresses the lifecycle of selecting, implementing, and driving adoption of technology solutions, including needs assessment, vendor selection, data migration, integration planning, testing strategies, cutover planning, training, postlaunch support, and optimization. Also covers balancing innovation with operational stability, handling failed technology choices, and measuring technology adoption and business impact. Emphasis is on pragmatic execution, minimizing downtime and data loss, and ensuring end user adoption.

HardSystem Design

85 practiced

Design a governance model for self-service BI that balances analyst agility with data control. Cover roles and responsibilities (data owners, stewards, certifiers), certified dataset lifecycle, naming conventions, change control for metric definitions, access policies, automation to enforce standards, and success metrics for the governance program.

Sample Answer

Requirements / constraints:- Enable fast analyst-led exploration and dashboarding- Ensure trustable, auditable metrics and datasets for executive use- Low friction for certified dataset creation; strong controls for production metrics- Scale across teams and BI tools

High-level model:- Roles: - Data Owner (product/LOB): accountable for dataset/business metric correctness and approvals. - Data Steward (central data platform team): enforces standards, curates metadata, runs data quality checks. - Dataset Certifier (trusted analytics SMEs): validates business logic, signs off on certified datasets/metrics. - BI Analyst (you): builds dashboards, requests certifications, uses certified datasets for production reports. - Platform Engineer: implements automation, RBAC, lineage capture.

Certified dataset lifecycle:1. Draft (analyst builds in sandbox; tagged “draft”)2. Review (submit PR: SQL/LookML/metrics + tests + documentation)3. Certify (steward + owner + certifier sign-off; moves to “certified”)4. Publish (registered in catalog with version, lineage, SLA)5. Deprecate (change-control workflow; deprecation window and migration guidance)

Naming & metadata conventions:- Dataset: {domain}.{product}.{entity}_{granularity}_v{major}- Metric: {domain}.{metric_name}_{aggregation}_{cohort}- Mandatory metadata: owner, steward, certifier, description, business definition, calculation SQL, last certified, SLA, sensitivity label- Enforce via templates and catalog UI

Change control for metric definitions:- All metric changes require a change request linked to dataset version with: - Semantic diff of SQL/logic - Impact analysis (downstream dashboards, consumers) - Testing (unit tests, row-level comparisons, historical backfill checks) - Approval: owner + certifier - Rollout strategy: parallel run, feature-flagged switch, or phased adoption- Maintain immutable metric versions; dashboards reference specific metric version to avoid silent drift

Access policies:- Principle of least privilege via role-based access in BI tools and warehouses- Certified datasets: read access for broad analyst population; write/update only for stewards/owners- Sensitive columns masked by policy; PII access requires explicit justification and auditing- Temporary elevated access via timeboxed tickets and automated logging

Automation & enforcement:- CI/CD for metrics and dataset definitions (linting, SQL style, unit tests)- Automated lineage extraction and impact analysis (hooked into catalog)- Auto-enforce naming/metadata via commit hooks and catalog validators- Data quality jobs with SLA alerts; auto-notify owners when thresholds breach- Auto-deprecation reminders and dashboard impact reports

Success metrics:- Adoption: % of production dashboards using certified datasets- Time-to-certify: median time from draft to certified- Trust: # incidents where metric definitions changed without approval (target = 0)- Quality: pass rate of data quality checks; MTTR for data incidents- Productivity: median time for new dashboard from request to delivery- ROI: reduction in duplicated work (measured via dataset reuse), analyst satisfaction surveys

Operational notes:- Keep sandbox freedom: analysts can prototype but must register artifacts intended for reuse.- Provide training, templates, and office hours to lower certification friction.- Iterate governance based on metrics quarterly; balance control with speed.

MediumBehavioral

79 practiced

Tell me about a time when you built a business case to invest in data quality processes or tools. Explain the problem, how you quantified the cost of poor data, the stakeholders you engaged, recommended solution, and eventual outcomes including metrics or savings realized.

MediumTechnical

78 practiced

Write a SQL query using window functions to detect anomalies in a daily metrics table metrics(date, metric_name, value) such that a row is flagged if its value is greater than mean+3*stddev for the rolling 30-day window for that metric or if it changes more than 50% compared to the 7-day moving average. Show how you'd present the output with metric, date, value, anomaly_type, and supporting stat values.

Sample Answer

Approach: compute rolling 30-day mean and stddev and a 7-day moving average per metric using window functions, then flag rows where value > mean30 + 3*std30 or absolute change vs ma7 > 50%. Present supporting stats and anomaly_type.

sql

WITH stats AS (
  SELECT
    metric_name AS metric,
    date,
    value,
    -- 30-day rolling statistics (current day + previous 29 days)
    AVG(value) OVER (
      PARTITION BY metric_name
      ORDER BY date
      ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
    ) AS mean_30,
    STDDEV_POP(value) OVER (
      PARTITION BY metric_name
      ORDER BY date
      ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
    ) AS stddev_30,
    -- 7-day moving average
    AVG(value) OVER (
      PARTITION BY metric_name
      ORDER BY date
      ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS ma_7
  FROM metrics
)
SELECT
  metric,
  date,
  value,
  mean_30,
  stddev_30,
  ma_7,
  -- percent change vs 7-day MA; null-safe
  CASE
    WHEN ma_7 IS NULL OR ma_7 = 0 THEN NULL
    ELSE (value - ma_7) / ma_7
  END AS pct_change_vs_ma7,
  -- classify anomaly: can be 'spike_30sd', 'sudden_change_50pct', or both
  CONCAT_WS(
    '; ',
    CASE WHEN stddev_30 IS NOT NULL AND value > mean_30 + 3 * stddev_30 THEN 'spike_30sd' END,
    CASE WHEN ma_7 IS NOT NULL AND ma_7 <> 0 AND ABS((value - ma_7) / ma_7) > 0.5 THEN 'sudden_change_50pct' END
  ) AS anomaly_type
FROM stats
WHERE
  -- keep only rows flagged as anomalies
  (stddev_30 IS NOT NULL AND value > mean_30 + 3 * stddev_30)
  OR (ma_7 IS NOT NULL AND ma_7 <> 0 AND ABS((value - ma_7) / ma_7) > 0.5)
ORDER BY metric, date;

Key points:- Use ROWS frame for fixed-day windows; if dates have gaps and you want calendar days use RANGE or join to a dates table.- Handle ma_7 = 0 to avoid division by zero.- Consider using STDDEV_SAMP vs STDDEV_POP per your statistical choice.- For dashboards, surface anomaly_type and supporting stats for drill-down; add previous_day value for context.

MediumTechnical

78 practiced

A Tableau workbook is slow: large extracts, many LOD calculations, and slow filters. Describe practical optimizations you would apply to improve dashboard load and interactivity, including extract strategies, aggregate tables, pre-computed metrics, filter design, and whether to move to a live connection for some sources.

Sample Answer

Start by measuring: run Tableau’s Performance Recorder and examine query durations to identify whether slowness is from extracts, SQL, LODs, or rendering. Then apply targeted optimizations.

Extract strategies- Use Hyper extracts with incremental refreshes to avoid rebuilding large files nightly. Incremental extracts reduce ETL time and keep extracts small.- Apply data source filters at extract time (date ranges, active customers) so the extract only contains needed rows/columns.- Publish extracts to Tableau Server/Online and let server handle refreshes and caching.

Aggregate tables & pre-computed metrics- Create aggregate tables or materialized views in the database for common grain (daily/monthly customer metrics). Point Tableau at these instead of raw transaction-level data.- Pre-compute expensive metrics (running totals, cohort assignments, attribution) in the ETL layer or DB. This removes heavy per-visualization computation.- Example: instead of computing month-to-date via LODs on the fly, store MTD per customer per day in a table refreshed daily.

Reduce LOD/calculation cost- Replace complex FIXED LODs with aggregations on aggregate tables or window functions in the DB where possible.- Where LODs are required, limit their domain using data source filters or context filters so they run on fewer rows.- Convert table calculations that require full data scans into database-processed calculations.

Filter design & dashboard interactivity- Use context filters for high-cost filters that should reduce the domain for subsequent filters (e.g., set date as context before other filters).- Avoid high-cardinality quick filters (unique IDs). Use single-value dropdowns, search-only filters, or parameters instead.- Set “Apply” buttons for multi-select filters to prevent every selection from triggering queries.- Limit global filters’ scope—apply filters only to necessary worksheets rather than globally.- Replace multiple independent filters with a single combined filter table when suitable.

Rendering & visualization optimizations- Reduce mark counts: aggregate data, use bins, or show summary visuals instead of millions of marks.- Limit number of worksheets on a dashboard, use navigation and show/hide containers for secondary views.- Optimize map layers, avoid high-precision geocoding when unnecessary.

Data modeling & connections- Prefer extracts for large, complex join queries—extracts are faster and avoid repeated DB hits.- Consider live connections for small, well-indexed, real-time sources where freshness matters and queries are cheap.- Avoid data blending; instead, join at the DB layer or use relationships (Tableau 2020.2+), and ensure joined fields are indexed.- Push computation to the database (SQL, views, stored procs) where DB is optimized for set operations.

Operational & caching- Configure server caching and tune refresh schedules to align with usage patterns (refresh before business hours).- Use Tableau Prep/ETL to simplify data shape and reduce columns, nulls, and string cardinality.

Trade-offs and governance- Live connections give real-time data but can overload production DBs—coordinate with DBAs and use read replicas or query limits.- Precomputed aggregates increase storage/ETL complexity but dramatically improve interactivity.- Document which sources are extracts vs live and why, so stakeholders understand freshness vs performance trade-offs.

This combination—measure, reduce data volume, push work to the right layer (DB/ETL), simplify LODs & filters, and smartly choose extracts vs live—will improve both dashboard load time and user interactivity.

EasyTechnical

80 practiced

Given the source table schema source_sales(sale_id bigint PK, customer_id bigint, sale_amount numeric, sale_date timestamp, product_id bigint) that will be migrated to a data warehouse, write SQL (Postgres/ANSI) to (a) flag rows with null customer_id or sale_amount, (b) identify potential duplicate rows by customer_id/product_id/sale_date, and (c) count sales that reference non-existent product_id assuming products(product_id) exists. Provide queries and brief explanation of assumptions.

Sample Answer

Assumptions:- "sale_date" duplicates are checked at the DATE level (same calendar day = potential duplicate). If you need exact-timestamp duplicates, remove the DATE(...) truncation.- product_id lookup uses products(product_id) primary key.- sale_amount NULL means missing/invalid amount; negative amounts are not checked here but could be.

a) Flag rows with null customer_id or sale_amount

sql

SELECT
  sale_id,
  customer_id,
  sale_amount,
  sale_date,
  product_id,
  CASE
    WHEN customer_id IS NULL AND sale_amount IS NULL THEN 'missing_customer_and_amount'
    WHEN customer_id IS NULL THEN 'missing_customer'
    WHEN sale_amount IS NULL THEN 'missing_amount'
    ELSE 'ok'
  END AS validation_flag
FROM source_sales;

Explanation: creates a validation_flag per row to identify required-field nulls for downstream ETL.

b) Identify potential duplicate rows by customer_id/product_id/sale_date (by date)

sql

WITH keyed AS (
  SELECT
    sale_id,
    customer_id,
    product_id,
    DATE(sale_date) AS sale_day
  FROM source_sales
)
SELECT
  customer_id,
  product_id,
  sale_day,
  COUNT(*) AS duplicate_count,
  STRING_AGG(sale_id::text, ',') AS sale_ids
FROM keyed
GROUP BY customer_id, product_id, sale_day
HAVING COUNT(*) > 1
ORDER BY duplicate_count DESC;

Explanation: groups by customer/product/day and returns groups with >1 row; STRING_AGG helps investigators locate all conflicting sale_ids.

c) Count sales referencing non-existent product_id

sql

SELECT COUNT(*) AS missing_product_count
FROM source_sales s
LEFT JOIN products p ON s.product_id = p.product_id
WHERE s.product_id IS NOT NULL
  AND p.product_id IS NULL;

Optional: list sample offending rows:

sql

SELECT s.*
FROM source_sales s
LEFT JOIN products p ON s.product_id = p.product_id
WHERE s.product_id IS NOT NULL
  AND p.product_id IS NULL
LIMIT 100;

Explanation: LEFT JOIN + IS NULL finds sales whose product_id has no matching product. Filtering out NULL product_id separately lets you treat truly-missing product_id via (a).

Unlock Full Question Bank

Get access to hundreds of Technology Implementation and Change Management interview questions and detailed answers.

Join thousands of developers preparing for their dream job.