teardown

Setup/teardown cloud reporting using Python/sqlite

Left: Athena, Right: Snowflake, Teardown: Sqlite (local)

usecase:
    comments: Join Athena and Snowflake datasets.
    left: |
      Select dt, count(1) cnt from athena.table group by 1;
    right: |
      select dt, count(1) cnt from snowflake.table group by 1;
    comparison: |
      SELECT a.dt, a.cnt, b.cnt, a.cnt-b.cnt diff
      FROM left a, right b
      WHERE a.dt=b.dt

Submit

time python3.7 teardown.py  -cf config/DEV.config.yml 

Athena submit: |████████████████████████████████████████| 100.0% Complete

+--------+-------------+--------------------------------------+---------+----------+
| Year   | Month(02)   | Query ID                             | Error   | Status   |
|--------+-------------+--------------------------------------+---------+----------|
| 2019   | 02          | 86a0b9c2-741a-4c26-9beb-b32f6b10ae2f |         | <null>   |
+--------+-------------+--------------------------------------+---------+----------+

Snowflake execute: |████████████████████████████████████████| 100.0% Complete

+--------+-------------+---------------+---------+----------+
| Year   | Month(02)   | JSON length   | Error   | Status   |
|--------+-------------+---------------+---------+----------|
| 2019   | 02          | 361           |         | <null>   |
+--------+-------------+---------------+---------+----------+

Athena download: |████████████████████████████████████████| 100.0% Complete

+--------+-------------+---------------+---------+----------+
| Year   | Month(02)   | JSON length   | Error   | Status   |
|--------+-------------+---------------+---------+----------|
| 2019   | 02          | 8678          |         | <null>   |
+--------+-------------+---------------+---------+----------+

Athena v.s. Snowflake "by day" count difference report.

+--------+-------------+------------+-------------+-------------+------------+
| Year   | Month(02)   | Date       | Athena      | Snowflake   | Diff       |
|--------+-------------+------------+-------------+-------------+------------|
| 2019   | 02          | 2019-02-21 | 1,495,070.0 | 1,495,293.0 | -223.0     |
| 2019   | 02          | 2019-02-22 | 1,462,280.0 | 1,462,860.0 | -580.0     |
| 2019   | 02          | 2019-02-23 | 1,780,682.0 | 1,781,970.0 | -1,288.0   |
| 2019   | 02          | 2019-02-24 | 2,076,891.0 | 2,079,409.0 | -2,518.0   |
| 2019   | 02          | 2019-02-25 | 2,077,098.0 | 2,082,984.0 | -5,886.0   |
| 2019   | 02          | 2019-02-26 | 1,791,450.0 | 1,800,355.0 | -8,905.0   |
| 2019   | 02          | 2019-02-27 | 1,564,619.0 | 1,580,048.0 | -15,429.0  |
| 2019   | 02          | 2019-02-28 | 873,560.0   | 1,510,337.0 | -636,777.0 |
+--------+-------------+------------+-------------+-------------+------------+