Comments (2)
I'm going to start working on this.
from bigquery-etl.
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 |
from bigquery-etl.
Related Issues (20)
- Add custom ping into Shredder HOT 4
- Change broken_site_report view to only use historical tables
- Add a separate view for live user_reports
- Improve dryrun skip logic for stage deploys
- Encode Derived Funnel Dimensions into UDF HOT 3
- Adding the ability to override scheduling parameters on backfill
- Schema updates for tables with not associated query.sql HOT 3
- Support publishing only authorized views
- Use schema.yaml for static tables instead of schema.json HOT 1
- [Data Checks Bug]: Apple CPUs are represented as empty string HOT 1
- Python 3.12 bootstrap - ModuleNotFoundError: No module named 'distutils'
- Create new derived query for Firefox-CI worker costs
- Support secrets in DAG generation
- event_monitoring_live generation is skipping apps that only have events defined in dependencies HOT 2
- materialized views don't get updated after deploy HOT 2
- Recreating materialized views may cause occasional dry run failures on main HOT 1
- Union default_table_workgroup_access with table-level workgroup_access in metadata generation logic
- `event` column from `events_stream` inaccessible in Looker HOT 2
- CI may be running stale tests in some cases HOT 1
- Monitor plus Attribution: Update ETL to store the channel for relevant Bigquery Tables and Looker explore HOT 2
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from bigquery-etl.