EY ETL Testing Interview Questions

Leave a Comment / Uncategorized / By

What Is ETL Testing? (Definition + EY-Style Real Example)

ETL Testing ensures that data extracted from multiple source systems is correctly transformed according to business rules and accurately loaded into a Data Warehouse (DW) for reporting and analytics.

EY-style real-world example

In large consulting programs (like those handled by Ernst & Young), ETL testing often involves:

  • Multiple source systems (ERP, CRM, flat files)
  • Complex transformations (tax rules, regulatory calculations)
  • Strict audit, reconciliation, and data quality controls

An ETL tester validates that financial or regulatory reports exactly match approved business logic and source data.

Data Warehouse Flow: Source → Staging → Transform → Load → Reporting

  1. Source – OLTP databases, APIs, files
  2. Staging – Raw data landing (no transformation)
  3. Transformation – Business rules, joins, SCD logic
  4. Load – Fact & Dimension tables
  5. Reporting – BI tools, dashboards, regulatory reports

EY ETL Testing Interview Questions & Answers

(Basic → Advanced | Scenario-Based)

🔹 Basic ETL Testing Questions (1–15)

  1. What is ETL testing?
     Validation of extracted, transformed, and loaded data for accuracy and completeness.
  2. Why is ETL testing critical in EY projects?
     Because data is often used for financial, audit, and regulatory reporting.
  3. What is Source-to-Target (S2T) mapping?
     A document defining how each source field maps to target fields with transformations.
  4. What are common ETL testing types?
     Source validation, transformation validation, target validation, reconciliation, performance testing.
  5. What is staging area testing?
     Ensuring staging data exactly matches source data.
  6. What are audit fields?
     load_date, batch_id, created_by, updated_date.
  7. How do you validate record counts?
     Compare source and target counts after filters.
  8. What is data reconciliation?
     Matching totals and counts between source and target.
  9. What is data warehouse testing?
     Testing schemas, facts, dimensions, and reports.
  10. What is a fact table?
     Stores measurable business data (sales, revenue).
  11. What is a dimension table?
     Stores descriptive attributes (customer, product).
  12. Difference between ETL testing and database testing?
     ETL focuses on data movement and transformation.
  13. What is full load vs incremental load?
     Full reloads all data; incremental loads only deltas.
  14. What is reject data testing?
     Validating rejected records and error reasons.
  15. What is data quality testing?
     Checking accuracy, consistency, completeness.

🔹 Intermediate Scenario-Based Questions (16–35)

  1. Scenario: Source has 1M rows, target has 980K. What do you check?
     Filters, rejects, duplicate removal logic.
  2. How do you test NULL handling?
     Validate mandatory fields and default values.
  3. What is SCD Type 1?
     Overwrites old data without history.
  4. What is SCD Type 2?
     Maintains history using effective dates and flags.
  5. Scenario: Customer address changes. What do you test?
     SCD2 history creation.
  6. What is surrogate key testing?
     Ensure uniqueness and non-null keys.
  7. What is late-arriving dimension scenario?
     Fact arrives before dimension; validate dummy keys.
  8. How do you test aggregation logic?
     Compare aggregated source vs target data.
  9. Scenario: Duplicate records in target. Why?
     Incorrect joins or missing dedup logic.
  10. What is hashing used for?
     Change detection and deduplication.
  11. How do you test CDC (Change Data Capture)?
     Validate only changed records are processed.
  12. Scenario: Negative amount in financial report. What test?
     Business rule validation.
  13. What is referential integrity testing?
     Fact keys must exist in dimensions.
  14. Scenario: Data type mismatch error. What do you test?
     Casting and truncation rules.
  15. How do you validate date transformations?
     Timezone and format validation.
  16. What is soft delete testing?
     Validate delete_flag instead of physical delete.
  17. Scenario: Re-run ETL job causes duplicates. What failed?
     Restartability logic.
  18. How do you test data masking?
     Validate PII fields are obfuscated.
  19. What is schema drift?
     Source schema changes; test ETL adaptability.
  20. How do you test BI reports?
     Reconcile report data with DW tables.

🔹 Advanced & EY Real-Time Scenarios (36–55)

  1. Scenario: ETL job misses SLA. What do you test?
     Query plans, indexes, partitions.
  2. How do you test performance tuning?
     Validate join strategy and parallelism.
  3. Scenario: Many-to-many join inflates data. What test?
     Join cardinality validation.
  4. How do you test window functions?
     Validate ranking and dedup logic.
  5. Scenario: Late data impacts aggregates. What do you do?
     Recalculate impacted partitions.
  6. How do you test audit and control tables?
     Validate batch status and row counts.
  7. Scenario: Regulatory report mismatch. What do you check first?
     S2T mapping and aggregation logic.
  8. How do you test ETL rollback?
     Ensure partial loads are reverted.
  9. Scenario: Parallel jobs cause locking. What test?
     Concurrency and isolation.
  10. How do you test historical reloads?
     Data duplication and SCD handling.
  11. Scenario: Source file arrives late. What do you test?
     Dependency and rerun logic.
  12. How do you test checksum/hash totals?
     Compare source vs target hashes.
  13. Scenario: Currency conversion mismatch. What do you validate?
     Exchange rate application.
  14. How do you test cloud DW ETL?
     Cost, scalability, performance checks.
  15. Scenario: NULLs after LEFT JOIN. Why?
     Missing dimension records.
  16. How do you test archival logic?
     Validate data movement to history tables.
  17. Scenario: Incorrect SCD expiry date. What failed?
     Effective date logic.
  18. How do you validate metadata tables?
     Batch IDs, counts, timestamps.
  19. Scenario: Unexpected data spike. What test?
     Source anomaly detection.
  20. How do you perform end-to-end ETL testing?
     Source → DW → Report reconciliation.

Real SQL Query Examples for ETL Validation

Sample Tables

  • src_orders(order_id, cust_id, amount, order_dt)
  • dim_customer(cust_sk, cust_id, current_flag)
  • fact_sales(order_id, cust_sk, amount, load_dt)

1️⃣ Record Count Validation

SELECT COUNT(*) FROM src_orders;

SELECT COUNT(*) FROM fact_sales;

2️⃣ JOIN Validation

SELECT COUNT(*) missing_dim

FROM fact_sales f

LEFT JOIN dim_customer d

ON f.cust_sk = d.cust_sk

WHERE d.cust_sk IS NULL;

3️⃣ GROUP BY Aggregation

SELECT cust_id, SUM(amount)

FROM src_orders

GROUP BY cust_id;

4️⃣ Window Function – Deduplication

SELECT *

FROM (

  SELECT order_id,

         ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY order_dt DESC) rn

  FROM src_orders

) t

WHERE rn = 1;

5️⃣ Performance Tuning Validation

EXPLAIN

SELECT *

FROM fact_sales

WHERE load_dt >= CURRENT_DATE – 1;

ETL Tools Commonly Asked in EY Interviews

  • Informatica
  • Microsoft SQL Server Integration Services
  • Ab Initio
  • Pentaho
  • Talend

ETL Defect Examples + Test Case Sample

Defect Example

Issue: Duplicate fact records in financial report

  • Root Cause: Incorrect join logic
  • Fix: Correct join keys and add deduplication

Sample Test Case

  • Test Case: Validate SCD2 update
  • Expected Result:
    • Old record expired
    • New record inserted with current_flag = ‘Y’

EY ETL Testing Quick Revision Sheet

  • Validate record counts, sums, NULLs, duplicates
  • Check S2T mapping line by line
  • Test SCD1, SCD2, audit fields, hashing
  • Use JOIN, GROUP BY, window functions
  • Always reconcile source vs target vs reports

FAQs (SEO Snippet Friendly)

Q1. What are EY ETL testing interview questions?
 They focus on real-time data validation, reconciliation, and regulatory-grade accuracy.

Q2. Which SQL is important for EY ETL roles?
 JOINs, GROUP BY, window functions, and performance tuning queries.

Q3. How do you test SCD2 in real projects?
 By validating history rows, effective dates, and current flags.

Leave a Comment

Your email address will not be published. Required fields are marked *