Back to API Testing
Q3Question 3 of 3

Validating that UI-displayed data matches the database

Key Concepts
  • The workflow is extract UI values, query the database as source of truth, normalize both sides, compare, and report mismatches
  • Currency and number formatting is the biggest pitfall: strip symbols, compare as BigDecimal with defined scale, never as strings or floats
  • Date formatting differs between UI and database: parse both to a canonical date or instant, account for time zone
  • Null handling requires a rule: UI may show empty string, dash, or 'null' while the database returns actual null
  • Pagination means comparing the visible page against the full dataset requires either paginating the UI or constraining the DB query
  • Always treat the database as the source of truth and key comparisons on a stable identifier so order does not create noise

Definition

Extract the UI values, query the database as the source of truth, normalize both sides for formatting, nulls, and case, then compare and report mismatches, while handling pagination and large datasets so you compare complete, fairly formatted data.
  • Extract – Read the displayed values from the UI using stable locators.
  • Query – Run the source-of-truth query against the database for the same entity.
  • Normalize – Convert both sides to a canonical comparable form: strip currency symbols, parse dates, handle nulls.
  • Compare – Compare the normalized values, keyed on a stable identifier.
  • Report – Show precise mismatches with the field name, UI value, and database value.

When Rajesh opens his statement on the National Bank of India app and sees a balance of nine hundred fifty thousand rupees, a tester must be sure the screen is telling the truth. So the tester reads the numbers off the screen, asks the database directly for the same account, and compares. The trap is that the screen shows "9,50,000.00" while the database stores 950000.00, or the date as "30 Oct 2026" while the database stores a timestamp.

Understanding the Concepts

What is the core workflow?

The workflow is extract, query, normalize, compare, report. The database is always the authority because the UI is just a view over it.

TYPESCRIPT
// Playwright + JDBC reconciliation test
import { test, expect } from '@playwright/test';
import { Pool } from 'pg';

const pool = new Pool({ connectionString: process.env.DB_URL });

test('Statement balance matches database', async ({ page }) => {
  // 1. Extract UI value
  await page.goto('/accounts/ACC-001/statement');
  const uiBalance = await page.getByTestId('account-balance').textContent();

  // 2. Query database
  const result = await pool.query(
    'SELECT balance FROM accounts WHERE id = $1', ['ACC-001']
  );
  const dbBalance = result.rows[0].balance;

  // 3. Normalize: strip currency symbol and separators
  const normalizedUI = parseFloat(
    uiBalance!.replace(/[^0-9.-]/g, '')
  );

  // 4. Compare
  expect(normalizedUI).toBeCloseTo(parseFloat(dbBalance), 2);
});

How do you handle currency and number formatting?

Currency and number formatting is the biggest pitfall. The UI shows grouping separators and a currency symbol while the database stores a plain decimal.

JAVA
// Java: normalize and compare money as BigDecimal
package com.bank.qa;

import java.math.BigDecimal;
import org.junit.jupiter.api.Test;
import static org.junit.jupiter.api.Assertions.assertEquals;

public class BalanceReconciliationTest {

    @Test
    void uiBalanceMatchesDatabase() {
        // UI shows: "Rs 9,50,000.00"
        String uiText = "Rs 9,50,000.00";
        // Strip everything except digits and decimal point
        String cleaned = uiText.replaceAll("[^0-9.]", "");
        BigDecimal uiValue = new BigDecimal(cleaned);

        // Database returns: 950000.00
        BigDecimal dbValue = new BigDecimal("950000.00");

        // Compare as BigDecimal with scale
        assertEquals(0, uiValue.compareTo(dbValue),
            "UI: " + uiValue + " DB: " + dbValue);
    }
}
TEXT
Wrong: Compare as strings
  "9,50,000.00" != "950000.00"  -> false mismatch

Wrong: Compare as double
  950000.00 == 950000.0000000001  -> floating point error

Correct: Compare as BigDecimal with defined scale
  950000.00 compareTo 950000.00  -> 0 (equal)

How do you handle date and time formatting?

The UI shows a localized date while the database stores a timestamp, possibly in a different time zone.

JAVA
import java.time.*;
import java.time.format.DateTimeFormatter;

@Test
void transactionDateMatchesDatabase() {
    // UI shows: "30 Oct 2026"
    String uiDate = "30 Oct 2026";
    LocalDate uiParsed = LocalDate.parse(
        uiDate, DateTimeFormatter.ofPattern("dd MMM yyyy")
    );

    // DB stores: 2026-10-30T18:30:00Z (UTC)
    Instant dbTimestamp = Instant.parse("2026-10-30T18:30:00Z");
    LocalDate dbDate = dbTimestamp
        .atZone(ZoneId.of("Asia/Kolkata"))
        .toLocalDate();

    assertEquals(uiParsed, dbDate);
}

How do you handle nulls?

The UI may render a null as an empty string, a dash, or the literal text "null." Define a single mapping rule per side.

JAVA
@Test
void nullMiddleNameHandledCorrectly() {
    String uiMiddleName = "-"; // UI shows a dash for null
    String dbMiddleName = null; // DB returns null

    String normalizedUI = uiMiddleName.equals("-") ? null : uiMiddleName;

    assertEquals(normalizedUI, dbMiddleName);
}

How do you handle case sensitivity?

The UI may title-case a name the database stores in upper case. Decide whether the comparison is case-sensitive and normalize accordingly.

JAVA
@Test
void accountHolderNameMatches() {
    String uiName = "Rajesh Kumar Singh"; // Title case
    String dbName = "RAJESH KUMAR SINGH"; // Upper case

    // Case-insensitive comparison
    assertEquals(
        uiName.toUpperCase(), dbName.toUpperCase()
    );
}

How do you handle pagination and large datasets?

The UI shows one page of a long list. You either paginate through the UI to assemble the complete set, or constrain the database query to the same page and ordering.

TYPESCRIPT
// Approach 1: Paginate through the UI
async function extractAllTransactions(page: Page): Promise<string[]> {
  const allIds: string[] = [];
  while (true) {
    const rows = await page.getByTestId('txn-row').all();
    for (const row of rows) {
      allIds.push(await row.getAttribute('data-txn-id') || '');
    }
    const nextButton = page.getByRole('button', { name: 'Next' });
    if (await nextButton.isDisabled()) break;
    await nextButton.click();
  }
  return allIds;
}

// Approach 2: Constrain the DB query to match the UI page
const dbResult = await pool.query(
  'SELECT id FROM transactions WHERE account_id = $1 ORDER BY date DESC LIMIT 20',
  ['ACC-001']
);

How do you compare large datasets efficiently?

For large data, extract the UI data into a structured collection, pull the database result into the same structure, and compare as keyed maps.

JAVA
// Compare transactions keyed by ID
Map<String, String> uiTransactions = extractUITransactions();
Map<String, String> dbTransactions = queryDBTransactions();

for (String txnId : dbTransactions.keySet()) {
    String dbAmount = dbTransactions.get(txnId);
    String uiAmount = uiTransactions.get(txnId);
    assertEquals(dbAmount, uiAmount,
        "Mismatch for txn " + txnId + ": UI=" + uiAmount + " DB=" + dbAmount);
}

// Also check counts match
assertEquals(dbTransactions.size(), uiTransactions.size(),
    "Transaction count mismatch");

What are common interview follow-up questions?

  • "What if the UI shows a cached value?" This is a real defect. The reconciliation test catches it, which is exactly its value.
  • "Should you always compare against the database?" For end-to-end tests, yes. For unit tests, mock the data layer. The reconciliation test is specifically about verifying the full stack.
  • "How do you handle real-time data that changes during the test?" Use a snapshot approach: read the DB first, then the UI immediately, or use a test account with frozen data.

Visual Overview

DIAGRAM
  UI-to-Database Reconciliation Workflow
  ──────────────────────────────────────

  ┌──────────────┐
  │  1. EXTRACT  │  Read UI values with stable locators
  │  UI Data     │  balance, transactions, dates
  └──────┬───────┘
         │
  ┌──────▼───────┐
  │  2. QUERY    │  Run source-of-truth DB query
  │  Database    │  SELECT balance, transactions
  └──────┬───────┘
         │
  ┌──────▼───────┐
  │  3. NORMALIZE│  Strip currency symbols
  │  Both Sides  │  Parse dates to canonical form
  │              │  Map nulls consistently
  │              │  Handle case sensitivity
  └──────┬───────┘
         │
  ┌──────▼───────┐
  │  4. COMPARE  │  Key by stable identifier
  │  As Maps     │  Compare field by field
  └──────┬───────┘
         │
  ┌──────▼───────┐
  │  5. REPORT   │  Field: UI value vs DB value
  │  Mismatches  │  Actionable for developers
  └──────────────┘
DIAGRAM
  Normalization Pitfalls
  ──────────────────────

  Pitfall           │ UI Shows        │ DB Stores     │ Solution
  ──────────────────┼─────────────────┼───────────────┼─────────────
  Currency format   │ Rs 9,50,000.00  │ 950000.00     │ BigDecimal
  Date format       │ 30 Oct 2026     │ 2026-10-30T.. │ Parse + TZ
  Null display      │ "-" or ""       │ NULL          │ Map rule
  Case              │ Rajesh Kumar    │ RAJESH KUMAR  │ Normalize
  Pagination        │ Page 1 of 5     │ All 100 rows  │ Match scope

Key Takeaway

Validating that UI data matches the database is a reconciliation task with a fixed workflow: extract UI values, query the database as source of truth, normalize both sides into a comparable canonical form, compare keyed on a stable identifier, and report precise mismatches. The normalization step is what separates a reliable check from a noisy one.

The pitfalls are exactly where naive comparisons fail. Currency formatting requires stripping symbols and comparing as BigDecimal with defined scale. Dates require parsing both sides to a canonical form and accounting for time zone. Nulls require a consistent mapping rule. Case sensitivity requires an explicit decision. And pagination requires either paginating the UI or constraining the database query to the same scope and order.

At the National Bank of India I built a Playwright plus JDBC reconciliation test for the statement screen that normalized money, dates, and names and compared transactions keyed by transaction identifier. It caught a real defect where the UI showed a stale cached balance that did not match the ledger after a late-settling transaction.

Remember one sentence: extract from UI, query the database, normalize both sides for formatting and nulls, compare keyed on a stable identifier, and report mismatches, because the database is always the source of truth.

---

Share
Use ← → arrow keys to navigate