Reconcile totals with the source

How to do it

Tests inside the warehouse cannot see rows that never arrived. Count rows and sum amounts per day in the source and in the mart, then compare the two. For analytics events, Record business facts on the server already compares orders with events. Do the same for every table behind a financial report. Leave out the last day, which may still be loading.

Examples

Python example

def reconcile_orders(source, warehouse):
    sql = """SELECT CAST(created_at AS date) AS day, count(*) AS orders, sum(total_cents) AS cents
             FROM {table} WHERE created_at < CURRENT_DATE GROUP BY 1"""
    expected = {row["day"]: row for row in source.query(sql.format(table="orders"))}
    actual = {row["day"]: row for row in warehouse.query(sql.format(table="marts.orders"))}
    return [day for day in expected.keys() | actual.keys() if expected.get(day) != actual.get(day)]

JavaScript example

const PER_DAY = table => `SELECT CAST(created_at AS date) AS day, count(*) AS orders, sum(total_cents) AS cents
  FROM ${table} WHERE created_at < CURRENT_DATE GROUP BY 1`;

export async function reconcileOrders(source, warehouse) {
  const byDay = rows => new Map(rows.map(row => [String(row.day), `${row.orders}:${row.cents}`]));
  const expected = byDay(await source.query(PER_DAY('orders')));
  const actual = byDay(await warehouse.query(PER_DAY('marts.orders')));
  return [...new Set([...expected.keys(), ...actual.keys()])].filter(day => expected.get(day) !== actual.get(day));
}

How to check

Delete one loaded row from the mart on staging. The next daily check reports the day and the difference.

Related rules

• DAT-52 Record business facts on the server in Data and databases