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.

HardTechnical

76 practiced

Explain DirectQuery (Power BI) / Live connection (Tableau) pushdown strategies: which transformations and aggregations typically push down to the source, which complex M or calculated fields do not push down, performance pitfalls of DirectQuery, and recommended workarounds (creating warehouse views, materialized/pre-aggregated tables, or stored procedures) to regain performance while preserving freshness.

Sample Answer

Brief summary: DirectQuery (Power BI) and Live Connection (Tableau) attempt to "push down" work to the source SQL engine. Simple operations (filters, projections, joins that map to source tables, basic aggregates, group by, sorts with limits) usually push down. Complex M queries, custom Power Query transformations, table-level calculated columns, many DAX measures with row-wise context, level-of-detail expressions, or client-side pivots often do NOT push down and are evaluated client-side or by the BI engine — causing row-by-row operations and many small queries.

What typically pushes down:- Column projection (SELECT columns)- WHERE filters, simple AND/OR predicates- JOINs expressed as model relationships if supported- GROUP BY + basic aggregations (SUM, COUNT, MIN, MAX, AVG)- ORDER BY with TOP / LIMIT (depending on connector)- Simple parameterized queries

What typically does NOT push down:- Complex M (Power Query) steps: custom functions, table.Buffer, List transformations, unpivot/pivot implemented client-side- DAX measures involving row context, iterative functions (SUMX, FILTER over large tables), complex time-intelligence that requires model state- Tableau LOD expressions that are computed client-side- Multi-step calculated columns that rely on other calculated columns or model metadata- Formatting, visual-level calculations, and many window functions if connector lacks support

Performance pitfalls:- High query latency due to many small queries (visual interactions trigger dozens of queries)- Source DB not tuned for OLAP patterns (no indexes, missing statistics)- Network latency between BI tool and source- Blocking/locking and concurrency on transactional DBs- Default single-node query limits or timeouts leading to partial results- Heavy client-side computation causing memory/CPU pressure in the BI layer

Recommended workarounds:- Build a semantic layer in the warehouse: create views that encapsulate expensive joins/filters so BI issues a single, optimized SQL.- Materialized / pre-aggregated tables for common aggregation grains (daily totals, monthly cohorts). Refresh cadence balances freshness and performance.- Use indexed/materialized views or summary tables maintained by ETL jobs or DB native features.- Implement stored procedures that accept parameters for common queries (e.g., date range) and return pre-joined, pre-filtered results; expose via query folding-friendly connectors.- Push transformations into ELT pipelines (dbt, Spark) so DirectQuery only reads ready-to-query tables.- Add appropriate indexes, partitioning, and query hints on the source for common access patterns.- Where interactivity demands and freshness allow, use hybrid models: DirectQuery for near-real-time tables + imported mode for large, static reference data.- Throttle visuals: reduce cross-filtering, use drill-throughs, increase default aggregation levels.

Practical guidance: first instrument slow reports to see most frequent SQL patterns, enable query folding diagnostics (Power Query's view native query; Tableau’s Performance Recorder), then prioritize creating warehouse views/materializations for the top N expensive queries. Use pre-aggregation plus reasonably frequent refreshes to preserve freshness while restoring interactive performance.

EasyTechnical

74 practiced

Explain the difference between dimensions and measures in a star schema. For an e-commerce sales reporting dataset give at least three example dimensions and three example measures. Discuss how granularity (grain) affects join strategy, aggregation correctness, and performance in dashboards.

HardTechnical

77 practiced

Design a testing strategy and implement example test cases for ETL pipelines: include unit tests for transformation logic, integration tests for end-to-end loads (row counts and checksum comparisons), data-quality assertions (null ratio thresholds, referential integrity), and CI integration. Provide one example test in SQL or pseudo-code demonstrating how you'd assert row counts and sums between source and target.

Sample Answer

Approach: split testing into (1) unit tests for pure transformation logic, (2) integration/end-to-end tests for loads, (3) data-quality assertions (DQ), and (4) CI automation. Use pytest + dbt/test framework or custom SQL tests run by CI against isolated dev/staging schemas.

Example components:- Unit tests: test SQL snippets or Python UDFs with small controlled inputs. Use pytest to assert expected rows/values.- Integration tests: load source→staging→warehouse in CI, then compare row counts, checksums, aggregated sums between source and target.- DQ assertions: null ratio thresholds, referential integrity checks, uniqueness, value ranges. Fail pipeline if thresholds exceeded.- CI: run unit tests on PR, run integration & DQ tests on merge to staging, promote on success. Store test artifacts and diffs.

Example SQL integration test (row counts + sum of amount):

sql

-- Compare source S (raw schema) vs target T (warehouse schema)
WITH src AS (
  SELECT COUNT(*) AS src_count, SUM(amount) AS src_sum
  FROM raw.payments
  WHERE load_date = '{{ test_date }}'
),
tgt AS (
  SELECT COUNT(*) AS tgt_count, SUM(amount) AS tgt_sum
  FROM warehouse.fact_payments
  WHERE load_date = '{{ test_date }}'
)
SELECT
  CASE WHEN s.src_count = t.tgt_count AND s.src_sum = t.tgt_sum THEN 'PASS'
       ELSE json_build_object(
         'status','FAIL',
         'src_count',s.src_count,'tgt_count',t.tgt_count,
         'src_sum',s.src_sum,'tgt_sum',t.tgt_sum
       )
  END AS result
FROM src s CROSS JOIN tgt t;

Key points:- Allow small acceptable diffs (rounding) via tolerance in assertions.- Run referential integrity checks: ensure foreign keys in fact have matching dimension keys.- Automate: pytest fixtures to provision test schemas, load sample data, run ETL, run SQL assertions, and teardown.- Alerting/logging: store failed rows and sample diffs for debugging.

HardTechnical

70 practiced

Walk through implementing Slowly Changing Dimensions (SCD) Type 2 for a customer dimension to preserve history. Cover schema (surrogate key, natural key, effective_date, end_date, current_flag), ETL logic to detect changes and insert new rows, handling backfills or corrections to history, and how to expose current vs historical views to BI consumers.

Sample Answer

Schema (core columns)- surrogate_key (BIGINT, PK, auto-increment) — unique row id- natural_key (VARCHAR) — business key (customer_id)- name, email, address, etc. — dimension attributes- effective_date (DATE/TIMESTAMP) — row start- end_date (DATE/TIMESTAMP, NULLable) — row end; NULL = current- current_flag (BOOLEAN) — quick filter for current row- source_system, record_hash (optional) — for lineage & change detection

ETL logic to detect changes (batch / incremental)1. Source extract: load new snapshot or change feed with natural_key + attributes + source_ts.2. Compute a hash of tracked attributes in source.3. Join source to current dimension rows on natural_key where current_flag = true.

SQL-like pseudocode:

sql

WITH src AS (
  SELECT natural_key, attr1, attr2, MD5(CONCAT(attr1,attr2)) AS src_hash, source_ts
  FROM staging_customers
),
cur AS (
  SELECT * FROM dim_customer WHERE current_flag = true
)
-- rows that changed
SELECT s.*, c.surrogate_key AS old_sk
FROM src s
LEFT JOIN cur c ON s.natural_key = c.natural_key
WHERE c.surrogate_key IS NULL OR s.src_hash <> c.record_hash;

4. For matched but changed rows: update existing row set end_date = source_ts, current_flag = false; insert new row with new surrogate_key, effective_date=source_ts, end_date=NULL, current_flag=true, record_hash=src_hash.5. For new natural_keys: insert new current rows.6. For deletions: optionally set end_date and current_flag=false with a tombstone reason.

Handling backfills / corrections- Use backdated effective_date from source or audit log; when inserting historical rows, ensure no overlapping intervals: find affected rows and split/adjust intervals.- Implement idempotent upserts using natural_key + effective_date or full-row hash + source_ts.- Keep an audit table of changes (CDC) with operation type, reason, and user to allow manual correction.- Example: to insert backfilled segment, set end_date of surrounding rows appropriately and insert new historical rows with the correct effective/end dates; wrap in transaction.

Exposing to BI consumers- Create two views: - current_dim_view: SELECT * FROM dim_customer WHERE current_flag = true (fast, used by dashboards) - dim_history_view: SELECT * FROM dim_customer (or with lived_date filters) to allow time-travel joins- For time-aware reporting provide a historical join pattern: join fact(event_date) to dim_history_view WHERE event_date BETWEEN effective_date AND COALESCE(end_date, '9999-12-31').- Document recommended join (include SQL template) and expose in semantic layer (Looker view, Power BI composite model) so report authors get correct point-in-time attributes.

Key considerations & trade-offs- Use surrogate keys to avoid FK churn in facts: facts should reference surrogate_key at event ingestion time.- Use HASH to detect changes efficiently and avoid false positives from whitespace/order.- Performance: index on natural_key + current_flag, partition by date if large.- Governance: audit trail, reconciliation jobs, and tests to ensure no overlapping intervals and complete coverage.

HardTechnical

84 practiced

Compare performing heavy JSON-nested transformations inside a cloud data warehouse (BigQuery/Snowflake) versus using Spark/Databricks. Discuss developer productivity, support for nested types, query performance, cost models, scaling behavior, and typical BI access patterns that influence the decision.

Sample Answer

Situation: As a BI analyst deciding where to do heavy JSON-nested transformations, you weigh developer productivity, nested-type support, query performance, cost, scaling, and how BI tools access the data.

Developer productivity:- Cloud warehouse (BigQuery/Snowflake): SQL-first, fast iteration for analysts who know SQL; built-in JSON functions let you prototype quickly without managing clusters. Fewer moving parts: schedule SQL jobs or views.- Spark/Databricks: Better for complex programmatic ETL (PySpark/Scala). More setup (notebooks, clusters, job orchestration) but stronger for reusable data pipelines and unit testing.

Support for nested types:- BigQuery: Native RECORD/ARRAY types, easy UNNEST and repeated fields — excellent for storing and querying nested JSON directly.- Snowflake: VARIANT with FLATTEN() — flexible but sometimes more verbose; semi-structured optimizations improve performance.- Spark: Strong support via DataFrame schema for nested structs/arrays; you can explode and manipulate arbitrarily, but schema enforcement matters.

Query performance:- Warehouses: Optimized for SQL, with vectorized execution and automatic optimizations; ad-hoc nested queries are performant for moderate transformations. BigQuery’s columnar storage and Snowflake’s micro-partitions help.- Spark: Can outperform for very large, complex transformations due to distributed execution and custom partitioning; but interactive query latency higher unless using Databricks SQL endpoints.

Cost models:- BigQuery/Snowflake: Pay-per-query (scan bytes) or compute credits; heavy nested processing in-place may increase query costs but avoids ETL pipeline costs. Snowflake offers warehouse sizing control; BigQuery has on-demand and flat-rate options.- Databricks/Spark: Cluster compute cost (per-second/hour) for transformations; cheaper for large, repeated batch workloads if optimized; additional operational costs (jobs, maintenance).

Scaling behavior:- Warehouses: Seamless autoscaling for concurrency (Snowflake multi-cluster, BigQuery serverless), predictable for BI concurrency patterns.- Spark/Databricks: Horizontal scaling but requires cluster tuning; excellent for massive scale and complex joins across huge datasets.

Typical BI access patterns:- If dashboards need low-latency interactive queries over modeled tables, pre-flatten nested JSON into wide, columnar tables in the warehouse (materialized views/tables) for best UX.- For one-off complex transformations or heavy enrichment joining many external sources, transform in Databricks then load curated tables into the warehouse for BI consumption.

Recommendation (BI-focused):- Prefer doing exploratory and final modeling in the warehouse (use native nested support when it’s simple). For very large volumes, complex stateful transformations, or when you need reusable programmatic pipelines, do heavy ETL in Spark/Databricks, then publish optimized, denormalized tables in the warehouse for BI tools.

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.