Data Transformation and Preparation Questions

Focuses on the technical skills and judgement required to connect to data sources, clean and shape data, and prepare datasets for analysis and visualization. Includes identifying necessary transformations such as calculations, aggregations, filtering, joins, and type conversions; deciding whether to perform transformations in the business intelligence tool or in the data warehouse or database layer; designing efficient data models and extract transform load workflows; ensuring data quality, lineage, and freshness; applying performance optimization techniques such as incremental refresh and pushdown processing; and familiarity with tools and features such as Power BI Power Query, Tableau data preparation capabilities, and structured query language for database level transformations. Also covers documentation, reproducibility, and testing of data preparation pipelines.

EasyTechnical

78 practiced

When preparing a dataset for dashboards or analysis, how do you decide whether a transformation should happen in SQL or the data warehouse versus in Power BI Power Query or Tableau Prep? Give one example where you would push the logic down for reuse and performance, and one where you would intentionally keep it in the BI layer for flexibility.

MediumTechnical

91 practiced

Write a PostgreSQL query that produces monthly revenue in USD by region from orders(order_id, customer_id, order_ts, status, amount, currency), customers(customer_id, region, signup_date), and fx_rates(currency, rate_to_usd, effective_date). Include only completed orders, ignore null amounts, deduplicate on order_id by keeping the latest order_ts, and use the most recent FX rate on or before the order date. Return month, region, and revenue_usd.

Sample Answer

**Approach**- Deduplicate orders by `order_id`, keeping the latest `order_ts`.- Filter to completed orders with non-null `amount`.- Join customers for region.- Use the most recent FX rate on or before the order date.- Aggregate by month and region.

sql

WITH deduped_orders AS (
  SELECT
    o.*,
    ROW_NUMBER() OVER (
      PARTITION BY o.order_id
      ORDER BY o.order_ts DESC
    ) AS rn
  FROM orders o
  WHERE o.status = 'completed'
    AND o.amount IS NOT NULL
)
SELECT
  date_trunc('month', d.order_ts)::date AS month,
  c.region,
  SUM(d.amount * fx.rate_to_usd) AS revenue_usd
FROM deduped_orders d
JOIN customers c
  ON c.customer_id = d.customer_id
LEFT JOIN LATERAL (
  SELECT f.rate_to_usd
  FROM fx_rates f
  WHERE f.currency = d.currency
    AND f.effective_date <= d.order_ts::date
  ORDER BY f.effective_date DESC
  LIMIT 1
) fx ON true
WHERE d.rn = 1
GROUP BY 1, 2
ORDER BY 1, 2;

**Key points**- The lateral join handles as-of FX lookup cleanly.- If a rate is missing, the row contributes null revenue, which is usually a signal to inspect data quality.

**Complexity**- The expensive parts are the windowed dedupe and the as-of FX lookup; with good indexing, this is practical for warehouse workloads.

**Edge cases**- Multiple FX rows on the same date.- Orders with missing currency or customer records.- Business rules for refunds or partial completions.

MediumTechnical

70 practiced

You're handed a customer transaction extract with inconsistent date formats, duplicate rows, missing category labels, and a few extreme outliers. How would you decide which issues to fix automatically, which to flag for manual review, and which to leave as-is for downstream analysis? What business context do you need before transforming the data?

HardTechnical

88 practiced

Finance and Sales both use dashboards built from the same revenue data, but their numbers differ by 3% before an executive review tomorrow. Walk me through how you would investigate whether the discrepancy comes from transformation logic, time-zone handling, duplicate transactions, or refresh timing, and how you would communicate the root cause and next steps.

HardSystem Design

82 practiced

Design the data preparation layer for a Data Scientist team that needs one curated dataset for BI dashboards and another for model training. The sources include a transactional database, SaaS exports, and event logs. The business expects daily freshness for most metrics and near-real-time updates for a few key indicators. How would you structure ingestion, transformation, validation, storage layers, and refresh strategy?

Unlock Full Question Bank

Get access to hundreds of Data Transformation and Preparation interview questions and detailed answers.

Join thousands of developers preparing for their dream job.