3 / 5
SQL (SQLite engine)

Test a data backfill migration

Hard120 pts~45 min
  • Backfill
  • Correlated UPDATE
  • Denormalisation
Practice app · Acme Commerce DB

A real SQL database (SQLite engine) seeded with an e-commerce and HR dataset, with schema browser and query runner.

Test URL
/lab/database-testing-test-a-data-backfill-migration

Your starter code already declares TEST_URL — never hardcode a host.

Objective

Add a denormalised customers.order_count column, backfill it from orders, and verify no customer's count drifted.

Your task

  1. 1ALTER TABLE customers ADD COLUMN order_count INTEGER NOT NULL DEFAULT 0.
  2. 2Backfill with a correlated UPDATE: order_count = the number of orders for that customer.
  3. 3Verify: SELECT SUM(CASE WHEN c.order_count <> (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) THEN 1 ELSE 0 END) AS mismatches, SUM(c.order_count) AS backfilled_orders FROM customers c.

Acceptance criteria

  • Every customer's order_count equals their real order count
  • The last result set is (0, 60)
  • The backfill uses UPDATE

Constraints

  • Every run starts from a freshly seeded database, so your script must include its own setup statements.
  • Dialect: SQLite. ALTER TABLE supports ADD COLUMN, DROP COLUMN and RENAME; other type changes need a table rebuild.
  • Only the last result set of your script is compared, so finish with the verification query.

SQL (SQLite engine) · Database Testing · Migrations & schema