Subscription company, billing migration
A made-up subscription company moves customers, invoices and payments to a new billing database. Every difference the move made is found by SQL, with its cause.
112 differences in 3 tables: 7 rows lost at batch boundaries, 40 amounts truncated, 25 times shifted by the time zone, 10 spaces turned into NULL, 30 codes missing from the map
Bridge: source to target, by table and month
customers
| Month | Step | Rows | Control total |
|---|---|---|---|
| All rows | Source | 1,200 | |
| All rows | Target | 1,200 |
invoices
| Month | Step | Rows | Control total |
|---|---|---|---|
| July 2026 | Source | 1,200 | 129,762.6459 |
| July 2026 | Amount truncated | 0 | −0.0759 |
| July 2026 | Target | 1,200 | 129,762.5700 |
| August 2026 | Source | 1,200 | 127,065.6409 |
| August 2026 | Amount truncated | 0 | −0.0509 |
| August 2026 | Target | 1,200 | 127,065.5900 |
| September 2026 | Source | 1,200 | 126,119.3898 |
| September 2026 | Amount truncated | 0 | −0.0798 |
| September 2026 | Target | 1,200 | 126,119.3100 |
payments
| Month | Step | Rows | Control total |
|---|---|---|---|
| July 2026 | Source | 998 | 108,665.4100 |
| July 2026 | Lost at a batch boundary | −1 | −49.0000 |
| July 2026 | Target | 997 | 108,616.4100 |
| August 2026 | Source | 1,135 | 118,177.5700 |
| August 2026 | Lost at a batch boundary | −4 | −266.0000 |
| August 2026 | Target | 1,131 | 117,911.5700 |
| September 2026 | Source | 1,267 | 136,030.9000 |
| September 2026 | Lost at a batch boundary | −2 | −148.0000 |
| September 2026 | Moved to another month | −2 | −148.0000 |
| September 2026 | Target | 1,263 | 135,734.9000 |
| October 2026 (outside the period) | Moved in from another month | +2 | +148.0000 |
| October 2026 (outside the period) | Target | 2 | 148.0000 |
Task graph of the DAG
Differences
| Table | Cause | Key | Column | Source | Target | Difference | Explanation |
|---|---|---|---|---|---|---|---|
| customers | spaces became NULL | C10355 | address_line2 | ' ' | NULL | A value of spaces became NULL. | |
| customers | spaces became NULL | C10369 | address_line2 | ' ' | NULL | A value of spaces became NULL. | |
| customers | spaces became NULL | C10443 | address_line2 | ' ' | NULL | A value of spaces became NULL. | |
| customers | spaces became NULL | C10501 | address_line2 | ' ' | NULL | A value of spaces became NULL. | |
| customers | spaces became NULL | C10552 | address_line2 | ' ' | NULL | A value of spaces became NULL. | |
| customers | spaces became NULL | C10656 | address_line2 | ' ' | NULL | A value of spaces became NULL. | |
| customers | spaces became NULL | C10923 | address_line2 | ' ' | NULL | A value of spaces became NULL. | |
| customers | spaces became NULL | C11078 | address_line2 | ' ' | NULL | A value of spaces became NULL. | |
| customers | spaces became NULL | C11118 | address_line2 | ' ' | NULL | A value of spaces became NULL. | |
| customers | spaces became NULL | C11190 | address_line2 | ' ' | NULL | A value of spaces became NULL. | |
| invoices | code missing from the map | INV-2026-00045 | status | on_hold | NULL | Code missing from the migration map; the target has NULL. | |
| invoices | code missing from the map | INV-2026-00303 | status | on_hold | NULL | Code missing from the migration map; the target has NULL. | |
| invoices | code missing from the map | INV-2026-00320 | status | on_hold | NULL | Code missing from the migration map; the target has NULL. | |
| invoices | code missing from the map | INV-2026-00357 | status | on_hold | NULL | Code missing from the migration map; the target has NULL. | |
| invoices | code missing from the map | INV-2026-00391 | status | on_hold | NULL | Code missing from the migration map; the target has NULL. | |
| invoices | code missing from the map | INV-2026-00543 | status | on_hold | NULL | Code missing from the migration map; the target has NULL. | |
| invoices | code missing from the map | INV-2026-00606 | status | on_hold | NULL | Code missing from the migration map; the target has NULL. | |
| invoices | code missing from the map | INV-2026-00814 | status | on_hold | NULL | Code missing from the migration map; the target has NULL. | |
| invoices | code missing from the map | INV-2026-01064 | status | on_hold | NULL | Code missing from the migration map; the target has NULL. | |
| invoices | code missing from the map | INV-2026-01085 | status | on_hold | NULL | Code missing from the migration map; the target has NULL. | |
| invoices | code missing from the map | INV-2026-01412 | status | on_hold | NULL | Code missing from the migration map; the target has NULL. | |
| invoices | code missing from the map | INV-2026-01500 | status | on_hold | NULL | Code missing from the migration map; the target has NULL. | |
| invoices | code missing from the map | INV-2026-01567 | status | on_hold | NULL | Code missing from the migration map; the target has NULL. | |
| invoices | code missing from the map | INV-2026-01613 | status | on_hold | NULL | Code missing from the migration map; the target has NULL. | |
| invoices | code missing from the map | INV-2026-01688 | status | on_hold | NULL | Code missing from the migration map; the target has NULL. | |
| invoices | code missing from the map | INV-2026-01717 | status | on_hold | NULL | Code missing from the migration map; the target has NULL. | |
| invoices | code missing from the map | INV-2026-01883 | status | on_hold | NULL | Code missing from the migration map; the target has NULL. | |
| invoices | code missing from the map | INV-2026-01906 | status | on_hold | NULL | Code missing from the migration map; the target has NULL. | |
| invoices | code missing from the map | INV-2026-02103 | status | on_hold | NULL | Code missing from the migration map; the target has NULL. | |
| invoices | code missing from the map | INV-2026-02275 | status | on_hold | NULL | Code missing from the migration map; the target has NULL. | |
| invoices | code missing from the map | INV-2026-02468 | status | on_hold | NULL | Code missing from the migration map; the target has NULL. | |
| invoices | code missing from the map | INV-2026-02564 | status | on_hold | NULL | Code missing from the migration map; the target has NULL. | |
| invoices | code missing from the map | INV-2026-02709 | status | on_hold | NULL | Code missing from the migration map; the target has NULL. | |
| invoices | code missing from the map | INV-2026-02730 | status | on_hold | NULL | Code missing from the migration map; the target has NULL. | |
| invoices | code missing from the map | INV-2026-02836 | status | on_hold | NULL | Code missing from the migration map; the target has NULL. | |
| invoices | code missing from the map | INV-2026-03022 | status | on_hold | NULL | Code missing from the migration map; the target has NULL. | |
| invoices | code missing from the map | INV-2026-03040 | status | on_hold | NULL | Code missing from the migration map; the target has NULL. | |
| invoices | code missing from the map | INV-2026-03049 | status | on_hold | NULL | Code missing from the migration map; the target has NULL. | |
| invoices | code missing from the map | INV-2026-03169 | status | on_hold | NULL | Code missing from the migration map; the target has NULL. | |
| invoices | code missing from the map | INV-2026-03579 | status | on_hold | NULL | Code missing from the migration map; the target has NULL. | |
| invoices | amount truncated | INV-2026-00012 | amount | 845.0914 | 845.09 | −0.0014 | Amount cut to the target's decimals. |
| invoices | amount truncated | INV-2026-00304 | amount | 850.3652 | 850.36 | −0.0052 | Amount cut to the target's decimals. |
| invoices | amount truncated | INV-2026-00360 | amount | 292.0176 | 292.01 | −0.0076 | Amount cut to the target's decimals. |
| invoices | amount truncated | INV-2026-00407 | amount | 929.2268 | 929.22 | −0.0068 | Amount cut to the target's decimals. |
| invoices | amount truncated | INV-2026-00428 | amount | 726.4054 | 726.40 | −0.0054 | Amount cut to the target's decimals. |
| invoices | amount truncated | INV-2026-00522 | amount | 967.0581 | 967.05 | −0.0081 | Amount cut to the target's decimals. |
| invoices | amount truncated | INV-2026-00672 | amount | 39.1147 | 39.11 | −0.0047 | Amount cut to the target's decimals. |
| invoices | amount truncated | INV-2026-00838 | amount | 451.0984 | 451.09 | −0.0084 | Amount cut to the target's decimals. |
| invoices | amount truncated | INV-2026-00843 | amount | 265.9825 | 265.98 | −0.0025 | Amount cut to the target's decimals. |
| invoices | amount truncated | INV-2026-00873 | amount | 148.1135 | 148.11 | −0.0035 | Amount cut to the target's decimals. |
| invoices | amount truncated | INV-2026-00911 | amount | 822.1361 | 822.13 | −0.0061 | Amount cut to the target's decimals. |
| invoices | amount truncated | INV-2026-01011 | amount | 307.8965 | 307.89 | −0.0065 | Amount cut to the target's decimals. |
| invoices | amount truncated | INV-2026-01060 | amount | 870.9806 | 870.98 | −0.0006 | Amount cut to the target's decimals. |
| invoices | amount truncated | INV-2026-01165 | amount | 223.1591 | 223.15 | −0.0091 | Amount cut to the target's decimals. |
| invoices | amount truncated | INV-2026-01554 | amount | 308.1369 | 308.13 | −0.0069 | Amount cut to the target's decimals. |
| invoices | amount truncated | INV-2026-01656 | amount | 656.5999 | 656.59 | −0.0099 | Amount cut to the target's decimals. |
| invoices | amount truncated | INV-2026-01756 | amount | 160.1429 | 160.14 | −0.0029 | Amount cut to the target's decimals. |
| invoices | amount truncated | INV-2026-01860 | amount | 981.6116 | 981.61 | −0.0016 | Amount cut to the target's decimals. |
| invoices | amount truncated | INV-2026-01939 | amount | 247.0291 | 247.02 | −0.0091 | Amount cut to the target's decimals. |
| invoices | amount truncated | INV-2026-01947 | amount | 366.8938 | 366.89 | −0.0038 | Amount cut to the target's decimals. |
| invoices | amount truncated | INV-2026-01969 | amount | 243.1163 | 243.11 | −0.0063 | Amount cut to the target's decimals. |
| invoices | amount truncated | INV-2026-02058 | amount | 161.2214 | 161.22 | −0.0014 | Amount cut to the target's decimals. |
| invoices | amount truncated | INV-2026-02197 | amount | 858.3344 | 858.33 | −0.0044 | Amount cut to the target's decimals. |
| invoices | amount truncated | INV-2026-02394 | amount | 922.5546 | 922.55 | −0.0046 | Amount cut to the target's decimals. |
| invoices | amount truncated | INV-2026-02413 | amount | 393.1891 | 393.18 | −0.0091 | Amount cut to the target's decimals. |
| invoices | amount truncated | INV-2026-02602 | amount | 212.5571 | 212.55 | −0.0071 | Amount cut to the target's decimals. |
| invoices | amount truncated | INV-2026-02649 | amount | 981.9316 | 981.93 | −0.0016 | Amount cut to the target's decimals. |
| invoices | amount truncated | INV-2026-02673 | amount | 96.3212 | 96.32 | −0.0012 | Amount cut to the target's decimals. |
| invoices | amount truncated | INV-2026-02695 | amount | 421.2070 | 421.20 | −0.0070 | Amount cut to the target's decimals. |
| invoices | amount truncated | INV-2026-02844 | amount | 744.8051 | 744.80 | −0.0051 | Amount cut to the target's decimals. |
| invoices | amount truncated | INV-2026-02846 | amount | 50.4985 | 50.49 | −0.0085 | Amount cut to the target's decimals. |
| invoices | amount truncated | INV-2026-02881 | amount | 286.3431 | 286.34 | −0.0031 | Amount cut to the target's decimals. |
| invoices | amount truncated | INV-2026-02927 | amount | 425.1551 | 425.15 | −0.0051 | Amount cut to the target's decimals. |
| invoices | amount truncated | INV-2026-02951 | amount | 107.7133 | 107.71 | −0.0033 | Amount cut to the target's decimals. |
| invoices | amount truncated | INV-2026-03033 | amount | 874.1507 | 874.15 | −0.0007 | Amount cut to the target's decimals. |
| invoices | amount truncated | INV-2026-03353 | amount | 33.6109 | 33.61 | −0.0009 | Amount cut to the target's decimals. |
| invoices | amount truncated | INV-2026-03405 | amount | 14.6866 | 14.68 | −0.0066 | Amount cut to the target's decimals. |
| invoices | amount truncated | INV-2026-03460 | amount | 215.1664 | 215.16 | −0.0064 | Amount cut to the target's decimals. |
| invoices | amount truncated | INV-2026-03470 | amount | 90.3568 | 90.35 | −0.0068 | Amount cut to the target's decimals. |
| invoices | amount truncated | INV-2026-03597 | amount | 235.6973 | 235.69 | −0.0073 | Amount cut to the target's decimals. |
| payments | lost at a batch boundary | P100501 | −1.0000 | Skipped at a batch boundary: the next batch started after this row's time. | |||
| payments | lost at a batch boundary | P101002 | −1.0000 | Skipped at a batch boundary: the next batch started after this row's time. | |||
| payments | lost at a batch boundary | P101503 | −1.0000 | Skipped at a batch boundary: the next batch started after this row's time. | |||
| payments | lost at a batch boundary | P101504 | −1.0000 | Skipped at a batch boundary: the next batch started after this row's time. | |||
| payments | lost at a batch boundary | P102005 | −1.0000 | Skipped at a batch boundary: the next batch started after this row's time. | |||
| payments | lost at a batch boundary | P102506 | −1.0000 | Skipped at a batch boundary: the next batch started after this row's time. | |||
| payments | lost at a batch boundary | P103007 | −1.0000 | Skipped at a batch boundary: the next batch started after this row's time. | |||
| payments | time zone shift | P100210 | paid_at | 2026-07-13 10:20:29 | 2026-07-13 13:20:29 | +3.0000 | Time moved by whole hours: local time loaded as UTC. |
| payments | time zone shift | P100216 | paid_at | 2026-07-13 13:45:32 | 2026-07-13 16:45:32 | +3.0000 | Time moved by whole hours: local time loaded as UTC. |
| payments | time zone shift | P100393 | paid_at | 2026-07-17 18:58:46 | 2026-07-17 21:58:46 | +3.0000 | Time moved by whole hours: local time loaded as UTC. |
| payments | time zone shift | P100710 | paid_at | 2026-07-24 10:13:49 | 2026-07-24 13:13:49 | +3.0000 | Time moved by whole hours: local time loaded as UTC. |
| payments | time zone shift | P100828 | paid_at | 2026-07-27 17:30:41 | 2026-07-27 20:30:41 | +3.0000 | Time moved by whole hours: local time loaded as UTC. |
| payments | time zone shift | P100891 | paid_at | 2026-07-28 12:08:45 | 2026-07-28 15:08:45 | +3.0000 | Time moved by whole hours: local time loaded as UTC. |
| payments | time zone shift | P100902 | paid_at | 2026-07-29 23:41:47 | 2026-07-30 02:41:47 | +3.0000 | Time moved by whole hours: local time loaded as UTC. |
| payments | time zone shift | P100915 | paid_at | 2026-07-29 23:15:26 | 2026-07-30 02:15:26 | +3.0000 | Time moved by whole hours: local time loaded as UTC. |
| payments | time zone shift | P100927 | paid_at | 2026-07-29 21:44:34 | 2026-07-30 00:44:34 | +3.0000 | Time moved by whole hours: local time loaded as UTC. |
| payments | time zone shift | P101023 | paid_at | 2026-08-01 22:50:52 | 2026-08-02 01:50:52 | +3.0000 | Time moved by whole hours: local time loaded as UTC. |
| payments | time zone shift | P101132 | paid_at | 2026-08-05 21:35:09 | 2026-08-06 00:35:09 | +3.0000 | Time moved by whole hours: local time loaded as UTC. |
| payments | time zone shift | P101148 | paid_at | 2026-08-06 17:38:21 | 2026-08-06 20:38:21 | +3.0000 | Time moved by whole hours: local time loaded as UTC. |
| payments | time zone shift | P101181 | paid_at | 2026-08-07 07:23:48 | 2026-08-07 10:23:48 | +3.0000 | Time moved by whole hours: local time loaded as UTC. |
| payments | time zone shift | P101311 | paid_at | 2026-08-11 10:11:11 | 2026-08-11 13:11:11 | +3.0000 | Time moved by whole hours: local time loaded as UTC. |
| payments | time zone shift | P101444 | paid_at | 2026-08-15 18:03:30 | 2026-08-15 21:03:30 | +3.0000 | Time moved by whole hours: local time loaded as UTC. |
| payments | time zone shift | P101795 | paid_at | 2026-08-24 00:01:22 | 2026-08-24 03:01:22 | +3.0000 | Time moved by whole hours: local time loaded as UTC. |
| payments | time zone shift | P101956 | paid_at | 2026-08-27 14:19:46 | 2026-08-27 17:19:46 | +3.0000 | Time moved by whole hours: local time loaded as UTC. |
| payments | time zone shift | P102109 | paid_at | 2026-08-31 11:25:10 | 2026-08-31 14:25:10 | +3.0000 | Time moved by whole hours: local time loaded as UTC. |
| payments | time zone shift | P102193 | paid_at | 2026-09-02 00:03:44 | 2026-09-02 03:03:44 | +3.0000 | Time moved by whole hours: local time loaded as UTC. |
| payments | time zone shift | P102590 | paid_at | 2026-09-15 23:11:41 | 2026-09-16 02:11:41 | +3.0000 | Time moved by whole hours: local time loaded as UTC. |
| payments | time zone shift | P103198 | paid_at | 2026-09-28 10:05:43 | 2026-09-28 13:05:43 | +3.0000 | Time moved by whole hours: local time loaded as UTC. |
| payments | time zone shift | P103225 | paid_at | 2026-09-29 17:17:41 | 2026-09-29 20:17:41 | +3.0000 | Time moved by whole hours: local time loaded as UTC. |
| payments | time zone shift | P103314 | paid_at | 2026-09-30 23:41:04 | 2026-10-01 02:41:04 | +3.0000 | Time moved by whole hours: local time loaded as UTC. |
| payments | time zone shift | P103326 | paid_at | 2026-09-30 07:38:21 | 2026-09-30 10:38:21 | +3.0000 | Time moved by whole hours: local time loaded as UTC. |
| payments | time zone shift | P103340 | paid_at | 2026-09-30 22:11:53 | 2026-10-01 01:11:53 | +3.0000 | Time moved by whole hours: local time loaded as UTC. |
How to run
Both demos from scratch: ./run.sh. The load check as an Airflow Dag, with its environment set up as the README says: airflow dags test load_check.