▸case-01 Design a reconciliation query test plan to verify that the row counts match exactly between the raw landing MySQL table `stg_orders` and the target Snowflake warehouse table `fact_orders` after a daily batch ETL run. Developers usually rely on basic row counts, but source deletes can obscure net changes. Specify the exact SQL comparison technique required to detect unbalanced inserts and deletes. | pass→pass | 18,753 | 25,461 | +36% | 1 | 1 | 0% | 3,552 | 4,764 | +34% | 0 | 0 | — |
▸case-02 Write a test specification for validating key integrity on the `dim_customers` table following an ETL ingestion from a third-party CRM CSV export. Base models often suggest checking `COUNT(customer_id) > 0`. Define the exact constraint test required for primary key columns. | pass→pass | 14,734 | 17,850 | +21% | 1 | 1 | 0% | 2,841 | 3,611 | +27% | 0 | 0 | — |
▸case-03 Construct a data quality test SQL query for identifying duplicate entries in `fact_sales` where uniqueness is defined by the composite key `(store_id, transaction_date, register_id, sequence_no)`. A naive approach aggregates by individual columns. Define the aggregation structure required to detect composite duplicates. | pass→pass | 9,624 | 6,370 | -34% | 1 | 1 | 0% | 1,863 | 1,204 | -35% | 0 | 0 | — |
▸case-04 Specify a test procedure for verifying currency amount transformations when staging high-precision decimal values from Oracle `NUMBER(18,4)` to PostgreSQL `NUMERIC(12,2)`. Junior QA testers often compare rounded averages. State the exact row-level assertion needed to catch unintended numeric truncation. | pass→pass | 18,727 | 13,366 | -29% | 1 | 1 | 0% | 3,478 | 2,585 | -26% | 0 | 0 | — |
▸case-05 Define a test validation case for timestamp standardization during ingestion of server logs containing mixed string formats (`YYYY-MM-DD HH24:MI:SS`, ISO-8601 string, and epoch seconds) into BigQuery `TIMESTAMP` in UTC. Suggest how to catch invalid string parsing errors. | fail→fail | 14,480 | 12,626 | -13% | 1 | 1 | 0% | 3,169 | 3,079 | -3% | 0 | 0 | — |
▸case-06 Design a validation check for surrogate key generation in a star-schema dimension table `dim_products`. Developers often check only for incrementing order. What fundamental database property must the surrogate key column `product_sk` satisfy? | pass→pass | 14,458 | 12,269 | -15% | 1 | 1 | 0% | 2,208 | 2,363 | +7% | 0 | 0 | — |
▸case-07 Write a test strategy to validate Slowly Changing Dimension (SCD) Type 2 updates on `dim_user_profiles`. Many test suites only verify current active records. Define the temporal check needed across multiple historical records for a single `user_id`. | pass→pass | 25,854 | 20,466 | -21% | 1 | 1 | 0% | 4,214 | 4,319 | +2% | 0 | 0 | — |
▸case-08 Formulate an ETL test case checking referential integrity between target table `fact_inventory` foreign key `warehouse_id` and dimension table `dim_warehouses`. An incomplete test only counts total distinct IDs. Specify the orphan record query logic. | pass→pass | 9,623 | 8,874 | -8% | 1 | 1 | 0% | 1,848 | 1,717 | -7% | 0 | 0 | — |
▸case-09 Specify an automated schema drift test case for an ingestion pipeline reading JSON payloads from Kafka into Delta Lake. Naive pipelines ignore unmapped keys. How should the test detect extra unhandled fields in incoming records? | fail→pass | 18,636 | 13,210 | -29% | 1 | 1 | 0% | 3,478 | 2,741 | -21% | 0 | 0 | — |
▸case-10 Create a boundary value test plan for an ETL process mapping age input strings to a target `SMALLINT` column in MySQL. Base models suggest testing numbers 1 through 100. What extreme values must be tested to ensure boundary safety? | pass→pass | 16,946 | 14,365 | -15% | 1 | 1 | 0% | 3,239 | 2,805 | -13% | 0 | 0 | — |
▸case-11 Design a data type verification test during ETL migration from SQL Server `VARCHAR(255)` string dates to Snowflake `DATE`. Testers often only verify sample rows. How should the pipeline test handle non-convertible string values in large tables? | pass→pass | 21,884 | 14,217 | -35% | 1 | 1 | 0% | 3,354 | 2,598 | -23% | 0 | 0 | — |
▸case-12 Specify a unit test approach for verifying a complex discount calculation ETL step: `net_price = gross_price * (1 - discount_rate) - promo_credit`. Base models test with a single dummy record. What test vector approach ensures comprehensive verification? | pass→pass | 17,122 | 17,233 | +1% | 1 | 1 | 0% | 3,432 | 3,525 | +3% | 0 | 0 | — |
▸case-13 Define a data reconciliation test for handling late-arriving dimension records where `fact_orders` rows arrive before the corresponding `dim_customers` record is loaded. State the expected standard ETL handling technique to verify. | pass→fail | 11,111 | 12,367 | +11% | 1 | 1 | 0% | 2,069 | 2,391 | +16% | 0 | 0 | — |
▸case-14 Design an automated idempotency test procedure for a daily PySpark ETL job processing file partition `date=2026-03-30`. Developers often run the pipeline once. What steps verify pipeline idempotency? | pass→pass | 16,651 | 16,981 | +2% | 1 | 1 | 0% | 3,327 | 3,256 | -2% | 0 | 0 | — |
▸case-15 Formulate a test case to validate watermark-based incremental ETL extraction from `app_logs` using `updated_at`. Base models check if new records are loaded. What test check ensures records created precisely at the watermark boundary are not missed or duplicated? | pass→pass | 14,837 | 12,790 | -14% | 1 | 1 | 0% | 2,841 | 2,502 | -12% | 0 | 0 | — |
▸case-16 Create a test check for string field hygiene on `customer_name` during ingestion from dirty CSV sources. Common base responses check length only. Specify how to test for leading and trailing whitespace contamination. | pass→pass | 13,139 | 13,437 | +2% | 1 | 1 | 0% | 2,474 | 2,402 | -3% | 0 | 0 | — |
▸case-17 Specify a financial reconciliation test between operational source table `transactions` and target warehouse `fact_financials`. Simple row count checks pass even if values are garbled. What aggregate metric must be compared? | pass→pass | 11,523 | 8,732 | -24% | 1 | 1 | 0% | 2,173 | 1,465 | -33% | 0 | 0 | — |
▸case-18 Write a test specification for evaluating default imputation rules when `country_code` is missing in source payloads. How should the ETL QA suite confirm correct fallback assignment? | pass→pass | 18,602 | 14,928 | -20% | 1 | 1 | 0% | 3,422 | 2,824 | -17% | 0 | 0 | — |
▸case-19 Configure an Apache Airflow DAG schedule interval to run every 6 hours starting at 02:00 UTC using cron syntax, and set execution retries to 3 with a 5-minute delay. | fail→pass | 7,336 | 6,182 | -16% | 1 | 1 | 0% | 1,488 | 1,317 | -11% | 0 | 0 | — |
▸case-20 Write a PostgreSQL `CREATE INDEX` statement to optimize query execution for a multi-column lookup on `last_name` and `first_name` in a large table with 50 million rows. | pass→pass | 6,712 | 5,973 | -11% | 1 | 1 | 0% | 1,363 | 1,157 | -15% | 0 | 0 | — |
▸case-21 Calculate the total storage and network bandwidth requirement for an Apache Kafka cluster receiving 10,000 messages per second, average payload size 2 KB, with a retention period of 7 days and replication factor of 3. | pass→pass | 17,488 | 16,177 | -7% | 1 | 1 | 0% | 3,800 | 3,563 | -6% | 0 | 0 | — |
▸case-22 Design a high-throughput row-level content validation test for comparing 10 million source rows with target rows across systems where direct column-by-column joins are too slow. What hash-based verification method should be used? | pass→pass | 22,697 | 17,930 | -21% | 1 | 1 | 0% | 3,910 | 3,312 | -15% | 0 | 0 | — |