Database and ETL testing interview questions

Database and ETL testing roles (often in banking, insurance, retail analytics and data-platform teams) test the data, not the screen. Interviewers want to see that you can write SQL that proves something, explain where data goes wrong in a pipeline, and say what you would check first when a report does not match. The SQL below was run on SQLite 3.46.1; most of it works unchanged on PostgreSQL and MySQL, and the places where databases differ are pointed out.

The basics

1. What is database testing?

Checking that the application stores, changes and returns data correctly, and that the database itself protects the data. That covers the schema (tables, columns, types), constraints (primary key, foreign key, unique, not null, check), the data an action writes (place an order, then confirm the right rows exist with the right values), transactions, stored procedures and triggers, and access rights. It is mostly done with SQL alongside UI or API tests.

2. What exactly do you check after a user action, for example placing an order?

The rows the action should create or change, and nothing else. For an order: one row in orders with the right user, total, status and timestamp; one row per product in order_items; stock reduced by the right amount; the cart emptied. Then the negative side: no duplicate order on a double click, and no partial order if payment fails.

Likely follow-up: How do you find which tables an action touches? Ask the developer or read the data model; failing that, compare row counts or recent rows in likely tables before and after the action on a quiet test environment.

3. What is ETL?

Extract, Transform, Load: data is read from source systems (an app database, files, an API), changed according to business rules (cleaned, converted, joined, aggregated), and written to a target, usually a data warehouse or a reporting database. ELT is the variant where raw data is loaded first and transformed inside the target. For a tester the questions are the same: did every expected row arrive, is each value right, and did anything arrive twice?

4. How is ETL testing different from database testing?

Database testing looks at one system and its rules. ETL testing looks at data in motion between systems and compares a source with a target through a set of transformation rules. The main document in ETL testing is the mapping document (source column, rule, target column), and most test cases are SQL queries that compare the two sides.

5. What are fact and dimension tables?

In a warehouse, a fact table stores measurable events (an order line with quantity and amount), and dimension tables describe them (customer, product, date, store). Facts hold foreign keys to dimensions. A star schema has a fact table in the middle with dimensions around it. Tests check that every fact row points to a real dimension row and that totals in the fact table match the source.

6. What is the difference between OLTP and OLAP?

OLTP (online transaction processing) systems run the business: many small reads and writes, normalised tables, for example the database behind a booking site. OLAP (online analytical processing) systems answer questions about the business: large reads, aggregated and often denormalised data, for example a sales warehouse. ETL usually moves data from OLTP to OLAP.

7. What is a staging area, and why test it?

A set of tables where extracted data lands before it is transformed. Testing staging separately tells you whether a problem came from the extract (data never arrived) or from the transform (data arrived and was changed wrongly). Without that split, every defect starts with an argument about whose step broke it.

Database checks

8. How do you test database constraints?

Try to break each one and expect the database to refuse. A small pytest file that does that against SQLite:

import sqlite3

import pytest


@pytest.fixture
def db():
    con = sqlite3.connect(":memory:")
    con.execute("PRAGMA foreign_keys = ON")  # SQLite enforces foreign keys only when this is on
    con.executescript("""
        CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT NOT NULL UNIQUE);
        CREATE TABLE orders (
            id INTEGER PRIMARY KEY,
            user_id INTEGER NOT NULL REFERENCES users(id),
            total_paise INTEGER NOT NULL CHECK (total_paise > 0));
        INSERT INTO users VALUES (1, '[email protected]');
    """)
    yield con
    con.close()


def test_duplicate_email_is_rejected(db):
    with pytest.raises(sqlite3.IntegrityError, match="UNIQUE"):
        db.execute("INSERT INTO users VALUES (2, '[email protected]')")


def test_order_for_unknown_user_is_rejected(db):
    with pytest.raises(sqlite3.IntegrityError, match="FOREIGN KEY"):
        db.execute("INSERT INTO orders VALUES (1, 999, 49900)")


def test_zero_total_is_rejected(db):
    with pytest.raises(sqlite3.IntegrityError, match="CHECK"):
        db.execute("INSERT INTO orders VALUES (1, 1, 0)")


def test_failed_transaction_leaves_nothing_behind(db):
    with pytest.raises(sqlite3.IntegrityError):
        with db:  # commits on success, rolls back on an exception
            db.execute("INSERT INTO orders VALUES (1, 1, 49900)")
            db.execute("INSERT INTO orders VALUES (2, 1, -5)")
    assert db.execute("SELECT COUNT(*) FROM orders").fetchone()[0] == 0

All four pass (pytest 9.2.0, Python 3.12). The SQLite foreign-key pragma is a classic trap: without it the second test fails, which is exactly the kind of environment difference an interviewer likes to hear you know about.

9. What are ACID properties, and how would you test one?

Atomicity (all or nothing), Consistency (rules hold before and after), Isolation (concurrent transactions do not see each other's half-done work) and Durability (committed data survives a crash). Atomicity is the easiest to test: make the second step of a two-step operation fail and check the first step was rolled back, as the last test above does. For a money transfer between two UPI wallets, the debit must not survive if the credit fails.

10. How do you test a stored procedure or a trigger?

Like a function: set up known input rows, call it, and check the output and every table it should change. Cover normal input, boundary values, nulls, and the error paths (what it does with a customer that does not exist). For a trigger, perform the event (insert, update, delete) and check the side effect, such as an audit row with the old and new values and the user who made the change.

11. How do you check data that the UI shows comes from the right place?

Pick a record, read it in the UI and query it directly, and compare field by field, including formatting rules: amounts stored in paise and shown in rupees, dates stored in UTC and shown in IST, names trimmed. Mismatches between storage and display are a common source of defects, and interviewers like candidates who know the display layer can be the bug.

12. How would you notice a performance problem in a query as a tester?

Run the slow screen's query with the execution plan (EXPLAIN in PostgreSQL and MySQL, EXPLAIN QUERY PLAN in SQLite) and look for full scans of big tables where an index should be used. Test with realistic data volumes; a query that is instant on 200 test rows can take minutes on production-size data. Report the query, the plan and the data volume; fixing it is the developer's or DBA's job.

13. How do you handle test data that contains personal information?

Do not copy production data with real names, phone numbers, Aadhaar or PAN details into test environments. Use synthetic data, or masked copies where personal fields are replaced while formats and relationships are kept (a masked mobile number is still ten digits and still starts with 6 to 9, so validation tests remain meaningful). Your organisation's data-protection policy decides what is allowed; ask before you copy.

14. How do you test concurrent updates?

Have two sessions change the same row at the same time and check that the result follows the rule: one wins and the other gets a clear error, or the second waits. Classic cases are two users booking the last seat or two payments applying to the same wallet. Lost updates (both succeed, one change silently disappears) are the defect to look for.

Likely follow-up: How would you do that without tools? Open two database sessions, start a transaction in each, update the same row in both, and commit them in turn; note which one blocks or fails.

ETL checks

15. What types of tests do you run on an ETL job?

Completeness (all expected rows arrived), correctness (each transformation rule), uniqueness (no duplicates), data quality (nulls, formats, ranges), referential integrity (facts point to real dimensions), aggregates (totals match), incremental behaviour (only new and changed rows on a delta run), rejects and error handling, re-runnability (running twice does not double the data), and performance within the load window.

The examples that follow use two small tables. The source keeps amounts in rupees and one-letter status codes; the target keeps paise and full words; emails are lower-cased.

CREATE TABLE src_orders (order_id INTEGER PRIMARY KEY, customer_email TEXT, status TEXT, amount_rupees REAL, order_date TEXT);
CREATE TABLE dw_orders  (order_id INTEGER PRIMARY KEY, customer_email TEXT, status TEXT, amount_paise INTEGER, order_date TEXT);

INSERT INTO src_orders VALUES
  (1, '[email protected]',  'P', 499.00,  '2026-11-01'),
  (2, '[email protected]',  'C', 1250.50, '2026-11-01'),
  (3, '[email protected]', 'P', 19.99,   '2026-11-02'),
  (4, '[email protected]',  'X', 300.00,  '2026-11-02');

INSERT INTO dw_orders VALUES
  (1, '[email protected]',  'PLACED',    49900,  '2026-11-01'),
  (2, '[email protected]',  'CANCELLED', 125050, '2026-11-01'),
  (3, '[email protected]', 'PLACED',    1998,   '2026-11-02');

16. How do you check that all rows were loaded?

Start with counts on both sides, then find the actual missing keys, because equal counts can hide one missing row and one extra row.

SELECT
  (SELECT COUNT(*) FROM src_orders) AS source_rows,
  (SELECT COUNT(*) FROM dw_orders)  AS target_rows;
-- 4 | 3

SELECT order_id FROM src_orders
EXCEPT
SELECT order_id FROM dw_orders;
-- 4

Oracle calls EXCEPT MINUS; older MySQL versions have neither, so use LEFT JOIN ... WHERE target.key IS NULL. Run the query in both directions to find extra rows too.

Likely follow-up: Order 4 has status X, which the mapping does not know. Is its absence a defect? It depends on the mapping document. If unknown codes must go to a reject table, check that it is there with a reason; if they must load as UNKNOWN, it is a defect.

17. How do you test a transformation rule?

Write the rule in SQL from the mapping document and compare it with what the job produced. Return only the rows that differ, so an empty result is a pass.

SELECT s.order_id,
       CAST(ROUND(s.amount_rupees * 100) AS INTEGER) AS expected_paise,
       t.amount_paise                                AS actual_paise
FROM src_orders s
JOIN dw_orders t ON t.order_id = s.order_id
WHERE t.amount_paise <> CAST(ROUND(s.amount_rupees * 100) AS INTEGER)
   OR t.customer_email <> LOWER(TRIM(s.customer_email))
   OR t.status <> CASE s.status WHEN 'P' THEN 'PLACED' WHEN 'C' THEN 'CANCELLED' END;
-- 3 | 1999 | 1998

Order 3 shows a real-world defect: ₹19.99 became 1998 paise. In binary floating point, 19.99 times 100 comes out as 1998.9999999999998, and a job that truncates instead of rounding loses a paisa. Money should be stored as a decimal or an integer of the smallest unit in the first place.

18. What is a mapping document, and what do you do when it is unclear?

The specification for each target column: its source columns, the transformation, defaults for nulls, and rules for rejects. Testers derive their queries from it, so ambiguity there becomes defects later. When a rule is unclear (what happens to an unknown status code, how to round), raise it before testing and get the answer written into the document, not agreed on a call.

19. How do you find duplicates?

Group by the business key (not the surrogate key the warehouse generated) and keep groups with more than one row. Decide the key carefully: here two orders by the same customer are fine, but the same query on a customer table would be a defect.

SELECT LOWER(customer_email) AS email, COUNT(*) AS orders
FROM src_orders
GROUP BY LOWER(customer_email)
HAVING COUNT(*) > 1;
-- [email protected] | 2

Note the LOWER: without it, [email protected] and [email protected] look like different customers, which is itself a source-data quality issue worth reporting.

20. How do you test data quality: nulls, formats and ranges?

One query per rule, each counting violations, so the result reads like a checklist where every number should be zero.

SELECT
  SUM(CASE WHEN customer_email IS NULL OR TRIM(customer_email) = '' THEN 1 ELSE 0 END) AS missing_email,
  SUM(CASE WHEN status IS NULL THEN 1 ELSE 0 END) AS missing_status,
  SUM(CASE WHEN amount_paise <= 0 THEN 1 ELSE 0 END) AS non_positive_amount
FROM dw_orders;
-- 0 | 0 | 0

Add format checks your database supports (pattern matching for six-digit PIN codes or ten-digit mobile numbers) and range checks (order dates not in the future).

21. How do you test aggregates such as daily sales totals?

Compute the same total from the source with the same filters and compare per group, not just the grand total, because errors in two groups can cancel out.

SELECT s.order_date,
       CAST(ROUND(SUM(s.amount_rupees) * 100) AS INTEGER) AS source_paise,
       (SELECT SUM(t.amount_paise) FROM dw_orders t WHERE t.order_date = s.order_date) AS target_paise
FROM src_orders s
WHERE s.status IN ('P', 'C')
GROUP BY s.order_date;
-- 2026-11-01 | 174950 | 174950
-- 2026-11-02 |   1999 |   1998

The one-paisa difference on 2 November is the rounding defect from question 17, now visible in a report.

22. What is a slowly changing dimension (SCD), and how do you test type 2?

A dimension whose attributes change over time, like a customer's city. Type 1 overwrites the old value (no history). Type 2 closes the old row and adds a new one with validity dates and a current flag, so old orders still report against the old city. Tests: exactly one current row per customer, no overlapping validity periods, and the old row's end date just before the new row's start date.

CREATE TABLE dim_customer (
  customer_key INTEGER PRIMARY KEY, customer_id INTEGER, city TEXT,
  valid_from TEXT, valid_to TEXT, is_current INTEGER);
INSERT INTO dim_customer VALUES
  (1, 101, 'Pune',      '2025-01-01', '2026-06-30', 0),
  (2, 101, 'Bengaluru', '2026-07-01', '9999-12-31', 1),
  (3, 102, 'Jaipur',    '2025-03-10', '9999-12-31', 1),
  (4, 102, 'Indore',    '2026-02-01', '9999-12-31', 1);

SELECT customer_id, COUNT(*) AS current_rows
FROM dim_customer
WHERE is_current = 1
GROUP BY customer_id
HAVING COUNT(*) > 1;

SELECT a.customer_id, a.customer_key, b.customer_key
FROM dim_customer a
JOIN dim_customer b
  ON a.customer_id = b.customer_id
 AND a.customer_key < b.customer_key
 AND a.valid_from <= b.valid_to
 AND b.valid_from <= a.valid_to;

Both should return nothing. In the sample, customer 101's move from Pune to Bengaluru was loaded correctly, but customer 102's Jaipur row was never closed when they moved to Indore, so the first query returns 102 | 2 and the second returns 102 | 3 | 4.

23. How do you test an incremental (delta) load?

Load a baseline, then change the source in known ways: insert new rows, update some, delete one, and leave most alone. Run the delta and check that only the changed rows were touched (by load timestamp or batch id), updates replaced or versioned the right rows, deletes were handled as the design says (hard delete, soft-delete flag, or ignored), and running the same delta again changes nothing. Also test the boundary: a row changed one second before the last run's cut-off time.

24. How do you test rejected records and error handling?

Feed in rows that break each rule (missing mandatory fields, bad dates, unknown codes, orphan foreign keys) and check that each lands in the reject or error table with a readable reason, that good rows in the same batch still load, and that the job's summary counts add up: rows read equals rows loaded plus rows rejected. A job that silently drops bad rows is a defect even if the warehouse looks clean.

25. How do you compare millions of rows?

Do not eyeball samples alone. Compare counts and per-group totals first (cheap and catches most problems), then compare row fingerprints: a hash of the concatenated columns per key on each side, comparing only the hashes. Where source and target are different databases, extract both sides to files or a common database and compare there. Keep a sample for manual review of formats, but let the full comparison decide pass or fail.

26. How would you automate ETL checks with Python?

Apply the mapping rules to the source in code, read the target, and compare by key. This script runs against the tables from question 15 and exits with code 1 when anything differs, so a CI job can fail on it.

import hashlib
import sqlite3
import sys

STATUS = {"P": "PLACED", "C": "CANCELLED"}


def expected_rows(con):
    """Apply the mapping document's rules to the source, keyed by order_id."""
    rows = con.execute("SELECT order_id, customer_email, status, amount_rupees, order_date FROM src_orders")
    return {
        oid: (email.strip().lower(), STATUS.get(status), round(amount * 100), day)
        for oid, email, status, amount, day in rows
    }


def actual_rows(con):
    rows = con.execute("SELECT order_id, customer_email, status, amount_paise, order_date FROM dw_orders")
    return {oid: tuple(rest) for oid, *rest in rows}


def fingerprint(row):
    return hashlib.sha256("|".join(map(str, row)).encode()).hexdigest()


def reconcile(source, target):
    missing = sorted(source.keys() - target.keys())
    extra = sorted(target.keys() - source.keys())
    changed = sorted(k for k in source.keys() & target.keys() if fingerprint(source[k]) != fingerprint(target[k]))
    return missing, extra, changed


if __name__ == "__main__":
    con = sqlite3.connect(":memory:")
    con.executescript(open("setup.sql").read())  # the CREATE and INSERT statements from question 15
    source, target = expected_rows(con), actual_rows(con)
    missing, extra, changed = reconcile(source, target)
    print(f"source={len(source)} target={len(target)} missing={missing} extra={extra} changed={changed}")
    for k in changed:
        print(f"  order {k}: expected {source[k]} got {target[k]}")
    sys.exit(1 if missing or extra or changed else 0)

Output:

source=4 target=3 missing=[4] extra=[] changed=[3]
  order 3: expected ('[email protected]', 'PLACED', 1999, '2026-11-02') got ('[email protected]', 'PLACED', 1998, '2026-11-02')

In a real project the two connections point at different databases, and the rules come from the mapping document, reviewed by someone other than the person who wrote the ETL.

Scenarios

27. The finance report total does not match the source system. How do you investigate?

Narrow it down instead of guessing. Compare per day, then per branch or category, until you find the group that differs. Then compare row by row in that group: missing rows (extract or filter problem), extra or duplicated rows (join fan-out or a re-run), or same rows with different values (transformation, rounding or currency). Check filters too: cancelled orders, test accounts and time-zone cut-offs are frequent causes. Report the exact rows and the rule they break.

28. Row counts match but users say the data is wrong. What could be happening?

Counts only prove quantity. Values can still be wrong: shifted columns, truncated text, wrong rounding, swapped day and month in dates, a lookup that matched the wrong dimension row, or one missing row offset by one duplicate. This is why counts are the first check, never the only one; follow with key-level and value-level comparison.

29. Orders placed just before midnight show up on the wrong day in the report. Why?

Almost always time zones. The source stores timestamps in UTC, and India is UTC+5:30, so an order at 11:45 pm IST on 1 November is 6:15 pm UTC on 1 November, but an order at 2:00 am IST on 2 November is still 1 November in UTC. If the report groups by the UTC date, early-morning Indian orders land on the previous day. Test with timestamps on either side of midnight in both zones and agree which zone the business day uses.

30. How would you test a data migration from an old system to a new one?

Agree the scope and mapping first (what moves, what is archived, how old codes map to new ones). Then: count and key reconciliation per table, value comparison with the transformation rules, referential integrity in the new system, special cases (nulls, very old records, special characters in names, maximum lengths), and a functional pass where users perform real tasks on migrated data, such as opening an old policy and making a claim. Rehearse the migration at least once with production-sized volume to measure the downtime, and test the rollback plan.

Check yourself

Answer these aloud before reading the answers.

1. Source and target both have 10,000 rows. Can you mark completeness as passed?
2. Which SQL clause keeps only groups with more than one row?
3. In a type 2 dimension, how many rows per customer should have the current flag set?
4. Why should a reconciliation query return only mismatching rows?
5. Rows read are 1,000, rows loaded are 990, and the reject table is empty. What do you report?

Answers. (1) No: equal counts can hide a missing row and an extra row; compare keys. (2) HAVING COUNT(*) > 1 after GROUP BY. (3) Exactly one. (4) So an empty result means pass, which is easy to automate and to read. (5) Ten rows were silently dropped: a defect, with the batch id and the counts as evidence.

Practise it

Run the queries above in SQLite or the ten SQL exercises, then change one row in the target and confirm your checks catch it. For the SQL side of interviews, go through the SQL questions for testers and the SQL query round.