Script for populating raw deduplicated tables from live tables

[mozilla]

Script for populating raw deduplicated tables from live tables

[jklukas]

I'm going to start working on this.

[jklukas]

This is now deployed in Airflow and running daily (for prod tables). The stable tables now contain 2 days of data. By comparing live to stable, we can see a fairly consistent dupe rate of ~0.01% in the fenix live tables, so our pipeline deduping is probably performing quite well:

WITH
  stable AS (
  SELECT
    DATE(submission_timestamp) AS submission_date,
    _TABLE_SUFFIX AS table_name,
    COUNT(*) AS n
  FROM
    `moz-fx-data-shared-prod.org_mozilla_fenix_stable.*`
  WHERE
    DATE(submission_timestamp) >= "2019-07-31"
  GROUP BY
    1,
    2 ),
  --
  live AS (
  SELECT
    DATE(submission_timestamp) AS submission_date,
    _TABLE_SUFFIX AS table_name,
    COUNT(*) AS n
  FROM
    `moz-fx-data-shared-prod.org_mozilla_fenix_live.*`
  WHERE
    DATE(submission_timestamp) >= "2019-07-31"
  GROUP BY
    1,
    2 )
  --
SELECT
  table_name,
  submission_date,
  stable.n AS stable_n,
  live.n AS live_n,
  (1 - (stable.n / live.n)) * 100 AS pct_dupes
FROM
  live
JOIN
  stable
USING
  (submission_date,
    table_name)
ORDER BY 1, 2

Row	table_name	submission_date	stable_n	live_n	pct_dupes
1	activation_v1	2019-07-31	2731	2733	0.07317965605562193
2	activation_v1	2019-08-01	3911	3911	0.0
3	baseline_v1	2019-07-31	442118	442178	0.013569196115592597
4	baseline_v1	2019-08-01	444724	444755	0.006970129621930443
5	bookmarks_sync_v1	2019-07-31	141702	141708	0.004234058768737281
6	events_v1	2019-07-31	282118	282170	0.0184286068682038
7	events_v1	2019-08-01	286195	286211	0.005590281295964061
8	history_sync_v1	2019-07-31	141626	141635	0.0063543615631767025
9	metrics_v1	2019-07-31	48321	48323	0.004138815884779934
10	metrics_v1	2019-08-01	47474	47476	0.004212654815061168