dataops-ci-bot
commented
2 weeks ago Integration report for "Add comment"
sql.diff
Click to expand!
```diff diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/accounts_cirrus_derived/baseline_clients_daily_v1/query.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/accounts_cirrus_derived/baseline_clients_daily_v1/query.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/accounts_cirrus_derived/baseline_clients_daily_v1/query.sql 2024-06-19 17:25:03.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/accounts_cirrus_derived/baseline_clients_daily_v1/query.sql 2024-06-19 17:26:51.000000000 +0000 @@ -7,7 +7,9 @@ sample_id, SAFE.PARSE_DATE('%F', SUBSTR(client_info.first_run_date, 1, 10)) AS first_run_date, mozfun.glean.parse_datetime(ping_info.end_time) AS parsed_end_time, - udf.glean_timespan_seconds(metrics.timespan.glean_baseline_duration) AS duration, + `moz-fx-data-shared-prod.udf.glean_timespan_seconds`( + metrics.timespan.glean_baseline_duration + ) AS duration, client_info.android_sdk_version, client_info.app_build, client_info.app_channel, @@ -26,7 +28,7 @@ CAST(NULL AS STRING) AS distribution_id, metadata.geo.subdivision1 AS geo_subdivision, FROM - `accounts_cirrus_stable.baseline_v1` + `moz-fx-data-shared-prod.accounts_cirrus_stable.baseline_v1` -- Baseline pings with 'foreground' reason were first introduced in early April 2020; -- we initially excluded them from baseline_clients_daily so that we could measure -- effects on KPIs. On 2020-08-25, we removed the filter on reason and backfilled. See: @@ -76,23 +78,35 @@ ) OVER w1 AS days_seen_session_end_bits, -- -- For all other dimensions, we use the mode of observed values in the day. - udf.mode_last(ARRAY_AGG(normalized_channel) OVER w1) AS normalized_channel, - udf.mode_last(ARRAY_AGG(normalized_os) OVER w1) AS normalized_os, - udf.mode_last(ARRAY_AGG(normalized_os_version) OVER w1) AS normalized_os_version, - udf.mode_last(ARRAY_AGG(android_sdk_version) OVER w1) AS android_sdk_version, - udf.mode_last(ARRAY_AGG(locale) OVER w1) AS locale, - udf.mode_last(ARRAY_AGG(city) OVER w1) AS city, - udf.mode_last(ARRAY_AGG(country) OVER w1) AS country, - udf.mode_last(ARRAY_AGG(isp) OVER w1) AS isp, - udf.mode_last(ARRAY_AGG(app_build) OVER w1) AS app_build, - udf.mode_last(ARRAY_AGG(app_channel) OVER w1) AS app_channel, - udf.mode_last(ARRAY_AGG(app_display_version) OVER w1) AS app_display_version, - udf.mode_last(ARRAY_AGG(architecture) OVER w1) AS architecture, - udf.mode_last(ARRAY_AGG(device_manufacturer) OVER w1) AS device_manufacturer, - udf.mode_last(ARRAY_AGG(device_model) OVER w1) AS device_model, - udf.mode_last(ARRAY_AGG(telemetry_sdk_build) OVER w1) AS telemetry_sdk_build, - udf.mode_last(ARRAY_AGG(distribution_id) OVER w1) AS distribution_id, - udf.mode_last(ARRAY_AGG(geo_subdivision) OVER w1) AS geo_subdivision, + `moz-fx-data-shared-prod.udf.mode_last`( + ARRAY_AGG(normalized_channel) OVER w1 + ) AS normalized_channel, + `moz-fx-data-shared-prod.udf.mode_last`(ARRAY_AGG(normalized_os) OVER w1) AS normalized_os, + `moz-fx-data-shared-prod.udf.mode_last`( + ARRAY_AGG(normalized_os_version) OVER w1 + ) AS normalized_os_version, + `moz-fx-data-shared-prod.udf.mode_last`( + ARRAY_AGG(android_sdk_version) OVER w1 + ) AS android_sdk_version, + `moz-fx-data-shared-prod.udf.mode_last`(ARRAY_AGG(locale) OVER w1) AS locale, + `moz-fx-data-shared-prod.udf.mode_last`(ARRAY_AGG(city) OVER w1) AS city, + `moz-fx-data-shared-prod.udf.mode_last`(ARRAY_AGG(country) OVER w1) AS country, + `moz-fx-data-shared-prod.udf.mode_last`(ARRAY_AGG(isp) OVER w1) AS isp, + `moz-fx-data-shared-prod.udf.mode_last`(ARRAY_AGG(app_build) OVER w1) AS app_build, + `moz-fx-data-shared-prod.udf.mode_last`(ARRAY_AGG(app_channel) OVER w1) AS app_channel, + `moz-fx-data-shared-prod.udf.mode_last`( + ARRAY_AGG(app_display_version) OVER w1 + ) AS app_display_version, + `moz-fx-data-shared-prod.udf.mode_last`(ARRAY_AGG(architecture) OVER w1) AS architecture, + `moz-fx-data-shared-prod.udf.mode_last`( + ARRAY_AGG(device_manufacturer) OVER w1 + ) AS device_manufacturer, + `moz-fx-data-shared-prod.udf.mode_last`(ARRAY_AGG(device_model) OVER w1) AS device_model, + `moz-fx-data-shared-prod.udf.mode_last`( + ARRAY_AGG(telemetry_sdk_build) OVER w1 + ) AS telemetry_sdk_build, + `moz-fx-data-shared-prod.udf.mode_last`(ARRAY_AGG(distribution_id) OVER w1) AS distribution_id, + `moz-fx-data-shared-prod.udf.mode_last`(ARRAY_AGG(geo_subdivision) OVER w1) AS geo_subdivision, FROM with_date_offsets WHERE @@ -133,7 +147,7 @@ FROM windowed AS cd LEFT JOIN - `accounts_cirrus_derived.baseline_clients_first_seen_v1` AS cfs + `moz-fx-data-shared-prod.accounts_cirrus_derived.baseline_clients_first_seen_v1` AS cfs USING (client_id) WHERE _n = 1 diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/burnham_derived/baseline_clients_daily_v1/query.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/burnham_derived/baseline_clients_daily_v1/query.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/burnham_derived/baseline_clients_daily_v1/query.sql 2024-06-19 17:25:03.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/burnham_derived/baseline_clients_daily_v1/query.sql 2024-06-19 17:26:54.000000000 +0000 @@ -7,7 +7,9 @@ sample_id, SAFE.PARSE_DATE('%F', SUBSTR(client_info.first_run_date, 1, 10)) AS first_run_date, mozfun.glean.parse_datetime(ping_info.end_time) AS parsed_end_time, - udf.glean_timespan_seconds(metrics.timespan.glean_baseline_duration) AS duration, + `moz-fx-data-shared-prod.udf.glean_timespan_seconds`( + metrics.timespan.glean_baseline_duration + ) AS duration, client_info.android_sdk_version, client_info.app_build, client_info.app_channel, @@ -26,7 +28,7 @@ CAST(NULL AS STRING) AS distribution_id, metadata.geo.subdivision1 AS geo_subdivision, FROM - `burnham_stable.baseline_v1` + `moz-fx-data-shared-prod.burnham_stable.baseline_v1` -- Baseline pings with 'foreground' reason were first introduced in early April 2020; -- we initially excluded them from baseline_clients_daily so that we could measure -- effects on KPIs. On 2020-08-25, we removed the filter on reason and backfilled. See: @@ -76,23 +78,35 @@ ) OVER w1 AS days_seen_session_end_bits, -- -- For all other dimensions, we use the mode of observed values in the day. - udf.mode_last(ARRAY_AGG(normalized_channel) OVER w1) AS normalized_channel, - udf.mode_last(ARRAY_AGG(normalized_os) OVER w1) AS normalized_os, - udf.mode_last(ARRAY_AGG(normalized_os_version) OVER w1) AS normalized_os_version, - udf.mode_last(ARRAY_AGG(android_sdk_version) OVER w1) AS android_sdk_version, - udf.mode_last(ARRAY_AGG(locale) OVER w1) AS locale, - udf.mode_last(ARRAY_AGG(city) OVER w1) AS city, - udf.mode_last(ARRAY_AGG(country) OVER w1) AS country, - udf.mode_last(ARRAY_AGG(isp) OVER w1) AS isp, - udf.mode_last(ARRAY_AGG(app_build) OVER w1) AS app_build, - udf.mode_last(ARRAY_AGG(app_channel) OVER w1) AS app_channel, - udf.mode_last(ARRAY_AGG(app_display_version) OVER w1) AS app_display_version, - udf.mode_last(ARRAY_AGG(architecture) OVER w1) AS architecture, - udf.mode_last(ARRAY_AGG(device_manufacturer) OVER w1) AS device_manufacturer, - udf.mode_last(ARRAY_AGG(device_model) OVER w1) AS device_model, - udf.mode_last(ARRAY_AGG(telemetry_sdk_build) OVER w1) AS telemetry_sdk_build, - udf.mode_last(ARRAY_AGG(distribution_id) OVER w1) AS distribution_id, - udf.mode_last(ARRAY_AGG(geo_subdivision) OVER w1) AS geo_subdivision, + `moz-fx-data-shared-prod.udf.mode_last`( + ARRAY_AGG(normalized_channel) OVER w1 + ) AS normalized_channel, + `moz-fx-data-shared-prod.udf.mode_last`(ARRAY_AGG(normalized_os) OVER w1) AS normalized_os, + `moz-fx-data-shared-prod.udf.mode_last`( + ARRAY_AGG(normalized_os_version) OVER w1 + ) AS normalized_os_version, + `moz-fx-data-shared-prod.udf.mode_last`( + ARRAY_AGG(android_sdk_version) OVER w1 + ) AS android_sdk_version, + `moz-fx-data-shared-prod.udf.mode_last`(ARRAY_AGG(locale) OVER w1) AS locale, + `moz-fx-data-shared-prod.udf.mode_last`(ARRAY_AGG(city) OVER w1) AS city, + `moz-fx-data-shared-prod.udf.mode_last`(ARRAY_AGG(country) OVER w1) AS country, + `moz-fx-data-shared-prod.udf.mode_last`(ARRAY_AGG(isp) OVER w1) AS isp, + `moz-fx-data-shared-prod.udf.mode_last`(ARRAY_AGG(app_build) OVER w1) AS app_build, + `moz-fx-data-shared-prod.udf.mode_last`(ARRAY_AGG(app_channel) OVER w1) AS app_channel, + `moz-fx-data-shared-prod.udf.mode_last`( + ARRAY_AGG(app_display_version) OVER w1 + ) AS app_display_version, + `moz-fx-data-shared-prod.udf.mode_last`(ARRAY_AGG(architecture) OVER w1) AS architecture, + `moz-fx-data-shared-prod.udf.mode_last`( + ARRAY_AGG(device_manufacturer) OVER w1 + ) AS device_manufacturer, + `moz-fx-data-shared-prod.udf.mode_last`(ARRAY_AGG(device_model) OVER w1) AS device_model, + `moz-fx-data-shared-prod.udf.mode_last`( + ARRAY_AGG(telemetry_sdk_build) OVER w1 + ) AS telemetry_sdk_build, + `moz-fx-data-shared-prod.udf.mode_last`(ARRAY_AGG(distribution_id) OVER w1) AS distribution_id, + `moz-fx-data-shared-prod.udf.mode_last`(ARRAY_AGG(geo_subdivision) OVER w1) AS geo_subdivision, FROM with_date_offsets WHERE @@ -133,7 +147,7 @@ FROM windowed AS cd LEFT JOIN - `burnham_derived.baseline_clients_first_seen_v1` AS cfs + `moz-fx-data-shared-prod.burnham_derived.baseline_clients_first_seen_v1` AS cfs USING (client_id) WHERE _n = 1 diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/contextual_services_derived/request_payload_suggest_v2/query.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/contextual_services_derived/request_payload_suggest_v2/query.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/contextual_services_derived/request_payload_suggest_v2/query.sql 2024-06-19 17:24:26.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/contextual_services_derived/request_payload_suggest_v2/query.sql 2024-06-19 17:24:22.000000000 +0000 @@ -1,7 +1,9 @@ WITH parsed AS ( SELECT submission_timestamp, - udf_js.parse_sponsored_interaction(udf_js.extract_string_from_bytes(payload)) AS si + `moz-fx-data-shared-prod.udf_js.parse_sponsored_interaction`( + `moz-fx-data-shared-prod.udf_js.extract_string_from_bytes`(payload) + ) AS si FROM `moz-fx-data-shared-prod.payload_bytes_error.contextual_services` WHERE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/contextual_services_derived/request_payload_tiles_v2/query.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/contextual_services_derived/request_payload_tiles_v2/query.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/contextual_services_derived/request_payload_tiles_v2/query.sql 2024-06-19 17:24:26.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/contextual_services_derived/request_payload_tiles_v2/query.sql 2024-06-19 17:24:22.000000000 +0000 @@ -1,7 +1,9 @@ WITH parsed AS ( SELECT submission_timestamp, - udf_js.parse_sponsored_interaction(udf_js.extract_string_from_bytes(payload)) AS si + `moz-fx-data-shared-prod.udf_js.parse_sponsored_interaction`( + `moz-fx-data-shared-prod.udf_js.extract_string_from_bytes`(payload) + ) AS si FROM `moz-fx-data-shared-prod.payload_bytes_error.contextual_services` WHERE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/fenix_derived/android_onboarding_v1/query.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix_derived/android_onboarding_v1/query.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/fenix_derived/android_onboarding_v1/query.sql 2024-06-19 17:25:04.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix_derived/android_onboarding_v1/query.sql 2024-06-19 17:26:58.000000000 +0000 @@ -21,12 +21,19 @@ ac.client_id AS client_id, ac.client_id AS column FROM - fenix.firefox_android_clients ac + `moz-fx-data-shared-prod.fenix.firefox_android_clients` ac LEFT JOIN `moz-fx-data-shared-prod`.fenix_derived.funnel_retention_clients_week_4_v1 r ON ac.client_id = r.client_id LEFT JOIN - (SELECT * FROM fenix.events_unnested eu WHERE DATE(submission_timestamp) = @submission_date) eu + ( + SELECT + * + FROM + `moz-fx-data-shared-prod.fenix.events_unnested` eu + WHERE + DATE(submission_timestamp) = @submission_date + ) eu ON ac.client_id = eu.client_info.client_id LEFT JOIN ( @@ -34,7 +41,7 @@ client_info.client_id, ANY_VALUE(`mozfun.map.get_key`(event_extra, 'sequence_id')) AS funnel_id FROM - fenix.events_unnested + `moz-fx-data-shared-prod.fenix.events_unnested` WHERE `mozfun.map.get_key`(event_extra, 'sequence_id') IS NOT NULL AND DATE(submission_timestamp) = @submission_date @@ -73,12 +80,19 @@ THEN ac.client_id END AS column FROM - fenix.firefox_android_clients ac + `moz-fx-data-shared-prod.fenix.firefox_android_clients` ac LEFT JOIN `moz-fx-data-shared-prod`.fenix_derived.funnel_retention_clients_week_4_v1 r ON ac.client_id = r.client_id LEFT JOIN - (SELECT * FROM fenix.events_unnested eu WHERE DATE(submission_timestamp) = @submission_date) eu + ( + SELECT + * + FROM + `moz-fx-data-shared-prod.fenix.events_unnested` eu + WHERE + DATE(submission_timestamp) = @submission_date + ) eu ON ac.client_id = eu.client_info.client_id LEFT JOIN ( @@ -86,7 +100,7 @@ client_info.client_id, ANY_VALUE(`mozfun.map.get_key`(event_extra, 'sequence_id')) AS funnel_id FROM - fenix.events_unnested + `moz-fx-data-shared-prod.fenix.events_unnested` WHERE `mozfun.map.get_key`(event_extra, 'sequence_id') IS NOT NULL AND DATE(submission_timestamp) = @submission_date @@ -126,12 +140,19 @@ THEN ac.client_id END AS column FROM - fenix.firefox_android_clients ac + `moz-fx-data-shared-prod.fenix.firefox_android_clients` ac LEFT JOIN `moz-fx-data-shared-prod`.fenix_derived.funnel_retention_clients_week_4_v1 r ON ac.client_id = r.client_id LEFT JOIN - (SELECT * FROM fenix.events_unnested eu WHERE DATE(submission_timestamp) = @submission_date) eu + ( + SELECT + * + FROM + `moz-fx-data-shared-prod.fenix.events_unnested` eu + WHERE + DATE(submission_timestamp) = @submission_date + ) eu ON ac.client_id = eu.client_info.client_id LEFT JOIN ( @@ -139,7 +160,7 @@ client_info.client_id, ANY_VALUE(`mozfun.map.get_key`(event_extra, 'sequence_id')) AS funnel_id FROM - fenix.events_unnested + `moz-fx-data-shared-prod.fenix.events_unnested` WHERE `mozfun.map.get_key`(event_extra, 'sequence_id') IS NOT NULL AND DATE(submission_timestamp) = @submission_date @@ -179,12 +200,19 @@ THEN ac.client_id END AS column FROM - fenix.firefox_android_clients ac + `moz-fx-data-shared-prod.fenix.firefox_android_clients` ac LEFT JOIN `moz-fx-data-shared-prod`.fenix_derived.funnel_retention_clients_week_4_v1 r ON ac.client_id = r.client_id LEFT JOIN - (SELECT * FROM fenix.events_unnested eu WHERE DATE(submission_timestamp) = @submission_date) eu + ( + SELECT + * + FROM + `moz-fx-data-shared-prod.fenix.events_unnested` eu + WHERE + DATE(submission_timestamp) = @submission_date + ) eu ON ac.client_id = eu.client_info.client_id LEFT JOIN ( @@ -192,7 +220,7 @@ client_info.client_id, ANY_VALUE(`mozfun.map.get_key`(event_extra, 'sequence_id')) AS funnel_id FROM - fenix.events_unnested + `moz-fx-data-shared-prod.fenix.events_unnested` WHERE `mozfun.map.get_key`(event_extra, 'sequence_id') IS NOT NULL AND DATE(submission_timestamp) = @submission_date @@ -231,12 +259,19 @@ THEN ac.client_id END AS column FROM - fenix.firefox_android_clients ac + `moz-fx-data-shared-prod.fenix.firefox_android_clients` ac LEFT JOIN `moz-fx-data-shared-prod`.fenix_derived.funnel_retention_clients_week_4_v1 r ON ac.client_id = r.client_id LEFT JOIN - (SELECT * FROM fenix.events_unnested eu WHERE DATE(submission_timestamp) = @submission_date) eu + ( + SELECT + * + FROM + `moz-fx-data-shared-prod.fenix.events_unnested` eu + WHERE + DATE(submission_timestamp) = @submission_date + ) eu ON ac.client_id = eu.client_info.client_id LEFT JOIN ( @@ -244,7 +279,7 @@ client_info.client_id, ANY_VALUE(`mozfun.map.get_key`(event_extra, 'sequence_id')) AS funnel_id FROM - fenix.events_unnested + `moz-fx-data-shared-prod.fenix.events_unnested` WHERE `mozfun.map.get_key`(event_extra, 'sequence_id') IS NOT NULL AND DATE(submission_timestamp) = @submission_date @@ -284,12 +319,19 @@ THEN ac.client_id END AS column FROM - fenix.firefox_android_clients ac + `moz-fx-data-shared-prod.fenix.firefox_android_clients` ac LEFT JOIN `moz-fx-data-shared-prod`.fenix_derived.funnel_retention_clients_week_4_v1 r ON ac.client_id = r.client_id LEFT JOIN - (SELECT * FROM fenix.events_unnested eu WHERE DATE(submission_timestamp) = @submission_date) eu + ( + SELECT + * + FROM + `moz-fx-data-shared-prod.fenix.events_unnested` eu + WHERE + DATE(submission_timestamp) = @submission_date + ) eu ON ac.client_id = eu.client_info.client_id LEFT JOIN ( @@ -297,7 +339,7 @@ client_info.client_id, ANY_VALUE(`mozfun.map.get_key`(event_extra, 'sequence_id')) AS funnel_id FROM - fenix.events_unnested + `moz-fx-data-shared-prod.fenix.events_unnested` WHERE `mozfun.map.get_key`(event_extra, 'sequence_id') IS NOT NULL AND DATE(submission_timestamp) = @submission_date @@ -337,12 +379,19 @@ THEN ac.client_id END AS column FROM - fenix.firefox_android_clients ac + `moz-fx-data-shared-prod.fenix.firefox_android_clients` ac LEFT JOIN `moz-fx-data-shared-prod`.fenix_derived.funnel_retention_clients_week_4_v1 r ON ac.client_id = r.client_id LEFT JOIN - (SELECT * FROM fenix.events_unnested eu WHERE DATE(submission_timestamp) = @submission_date) eu + ( + SELECT + * + FROM + `moz-fx-data-shared-prod.fenix.events_unnested` eu + WHERE + DATE(submission_timestamp) = @submission_date + ) eu ON ac.client_id = eu.client_info.client_id LEFT JOIN ( @@ -350,7 +399,7 @@ client_info.client_id, ANY_VALUE(`mozfun.map.get_key`(event_extra, 'sequence_id')) AS funnel_id FROM - fenix.events_unnested + `moz-fx-data-shared-prod.fenix.events_unnested` WHERE `mozfun.map.get_key`(event_extra, 'sequence_id') IS NOT NULL AND DATE(submission_timestamp) = @submission_date @@ -389,12 +438,19 @@ THEN ac.client_id END AS column FROM - fenix.firefox_android_clients ac + `moz-fx-data-shared-prod.fenix.firefox_android_clients` ac LEFT JOIN `moz-fx-data-shared-prod`.fenix_derived.funnel_retention_clients_week_4_v1 r ON ac.client_id = r.client_id LEFT JOIN - (SELECT * FROM fenix.events_unnested eu WHERE DATE(submission_timestamp) = @submission_date) eu + ( + SELECT + * + FROM + `moz-fx-data-shared-prod.fenix.events_unnested` eu + WHERE + DATE(submission_timestamp) = @submission_date + ) eu ON ac.client_id = eu.client_info.client_id LEFT JOIN ( @@ -402,7 +458,7 @@ client_info.client_id, ANY_VALUE(`mozfun.map.get_key`(event_extra, 'sequence_id')) AS funnel_id FROM - fenix.events_unnested + `moz-fx-data-shared-prod.fenix.events_unnested` WHERE `mozfun.map.get_key`(event_extra, 'sequence_id') IS NOT NULL AND DATE(submission_timestamp) = @submission_date @@ -442,12 +498,19 @@ THEN ac.client_id END AS column FROM - fenix.firefox_android_clients ac + `moz-fx-data-shared-prod.fenix.firefox_android_clients` ac LEFT JOIN `moz-fx-data-shared-prod`.fenix_derived.funnel_retention_clients_week_4_v1 r ON ac.client_id = r.client_id LEFT JOIN - (SELECT * FROM fenix.events_unnested eu WHERE DATE(submission_timestamp) = @submission_date) eu + ( + SELECT + * + FROM + `moz-fx-data-shared-prod.fenix.events_unnested` eu + WHERE + DATE(submission_timestamp) = @submission_date + ) eu ON ac.client_id = eu.client_info.client_id LEFT JOIN ( @@ -455,7 +518,7 @@ client_info.client_id, ANY_VALUE(`mozfun.map.get_key`(event_extra, 'sequence_id')) AS funnel_id FROM - fenix.events_unnested + `moz-fx-data-shared-prod.fenix.events_unnested` WHERE `mozfun.map.get_key`(event_extra, 'sequence_id') IS NOT NULL AND DATE(submission_timestamp) = @submission_date @@ -495,12 +558,19 @@ THEN ac.client_id END AS column FROM - fenix.firefox_android_clients ac + `moz-fx-data-shared-prod.fenix.firefox_android_clients` ac LEFT JOIN `moz-fx-data-shared-prod`.fenix_derived.funnel_retention_clients_week_4_v1 r ON ac.client_id = r.client_id LEFT JOIN - (SELECT * FROM fenix.events_unnested eu WHERE DATE(submission_timestamp) = @submission_date) eu + ( + SELECT + * + FROM + `moz-fx-data-shared-prod.fenix.events_unnested` eu + WHERE + DATE(submission_timestamp) = @submission_date + ) eu ON ac.client_id = eu.client_info.client_id LEFT JOIN ( @@ -508,7 +578,7 @@ client_info.client_id, ANY_VALUE(`mozfun.map.get_key`(event_extra, 'sequence_id')) AS funnel_id FROM - fenix.events_unnested + `moz-fx-data-shared-prod.fenix.events_unnested` WHERE `mozfun.map.get_key`(event_extra, 'sequence_id') IS NOT NULL AND DATE(submission_timestamp) = @submission_date @@ -545,12 +615,19 @@ THEN ac.client_id END AS column FROM - fenix.firefox_android_clients ac + `moz-fx-data-shared-prod.fenix.firefox_android_clients` ac LEFT JOIN `moz-fx-data-shared-prod`.fenix_derived.funnel_retention_clients_week_4_v1 r ON ac.client_id = r.client_id LEFT JOIN - (SELECT * FROM fenix.events_unnested eu WHERE DATE(submission_timestamp) = @submission_date) eu + ( + SELECT + * + FROM + `moz-fx-data-shared-prod.fenix.events_unnested` eu + WHERE + DATE(submission_timestamp) = @submission_date + ) eu ON ac.client_id = eu.client_info.client_id LEFT JOIN ( @@ -558,7 +635,7 @@ client_info.client_id, ANY_VALUE(`mozfun.map.get_key`(event_extra, 'sequence_id')) AS funnel_id FROM - fenix.events_unnested + `moz-fx-data-shared-prod.fenix.events_unnested` WHERE `mozfun.map.get_key`(event_extra, 'sequence_id') IS NOT NULL AND DATE(submission_timestamp) = @submission_date @@ -595,12 +672,19 @@ THEN ac.client_id END AS column FROM - fenix.firefox_android_clients ac + `moz-fx-data-shared-prod.fenix.firefox_android_clients` ac LEFT JOIN `moz-fx-data-shared-prod`.fenix_derived.funnel_retention_clients_week_4_v1 r ON ac.client_id = r.client_id LEFT JOIN - (SELECT * FROM fenix.events_unnested eu WHERE DATE(submission_timestamp) = @submission_date) eu + ( + SELECT + * + FROM + `moz-fx-data-shared-prod.fenix.events_unnested` eu + WHERE + DATE(submission_timestamp) = @submission_date + ) eu ON ac.client_id = eu.client_info.client_id LEFT JOIN ( @@ -608,7 +692,7 @@ client_info.client_id, ANY_VALUE(`mozfun.map.get_key`(event_extra, 'sequence_id')) AS funnel_id FROM - fenix.events_unnested + `moz-fx-data-shared-prod.fenix.events_unnested` WHERE `mozfun.map.get_key`(event_extra, 'sequence_id') IS NOT NULL AND DATE(submission_timestamp) = @submission_date @@ -645,12 +729,19 @@ THEN ac.client_id END AS column FROM - fenix.firefox_android_clients ac + `moz-fx-data-shared-prod.fenix.firefox_android_clients` ac LEFT JOIN `moz-fx-data-shared-prod`.fenix_derived.funnel_retention_clients_week_4_v1 r ON ac.client_id = r.client_id LEFT JOIN - (SELECT * FROM fenix.events_unnested eu WHERE DATE(submission_timestamp) = @submission_date) eu + ( + SELECT + * + FROM + `moz-fx-data-shared-prod.fenix.events_unnested` eu + WHERE + DATE(submission_timestamp) = @submission_date + ) eu ON ac.client_id = eu.client_info.client_id LEFT JOIN ( @@ -658,7 +749,7 @@ client_info.client_id, ANY_VALUE(`mozfun.map.get_key`(event_extra, 'sequence_id')) AS funnel_id FROM - fenix.events_unnested + `moz-fx-data-shared-prod.fenix.events_unnested` WHERE `mozfun.map.get_key`(event_extra, 'sequence_id') IS NOT NULL AND DATE(submission_timestamp) = @submission_date diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/fenix_derived/events_daily_v1/query.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix_derived/events_daily_v1/query.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/fenix_derived/events_daily_v1/query.sql 2024-06-19 17:25:04.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix_derived/events_daily_v1/query.sql 2024-06-19 17:26:58.000000000 +0000 @@ -29,7 +29,7 @@ UNNEST(ping_info.experiments) ) AS experiments FROM - org_mozilla_firefox.events e + `moz-fx-data-shared-prod.org_mozilla_firefox.events` e CROSS JOIN UNNEST(e.events) AS event UNION ALL @@ -62,7 +62,7 @@ UNNEST(ping_info.experiments) ) AS experiments FROM - org_mozilla_firefox_beta.events e + `moz-fx-data-shared-prod.org_mozilla_firefox_beta.events` e CROSS JOIN UNNEST(e.events) AS event UNION ALL @@ -95,7 +95,7 @@ UNNEST(ping_info.experiments) ) AS experiments FROM - org_mozilla_fenix.events e + `moz-fx-data-shared-prod.org_mozilla_fenix.events` e CROSS JOIN UNNEST(e.events) AS event ), @@ -113,12 +113,18 @@ ), joined AS ( SELECT - CONCAT(udf.pack_event_properties(events.extra, event_types.event_properties), index) AS index, + CONCAT( + `moz-fx-data-shared-prod.udf.pack_event_properties`( + events.extra, + event_types.event_properties + ), + index + ) AS index, events.* EXCEPT (category, event, extra) FROM events INNER JOIN - fenix.event_types event_types + `moz-fx-data-shared-prod.fenix.event_types` event_types USING (category, event) ) SELECT diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/fenix_derived/event_types_history_v1/query.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix_derived/event_types_history_v1/query.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/fenix_derived/event_types_history_v1/query.sql 2024-06-19 17:25:04.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix_derived/event_types_history_v1/query.sql 2024-06-19 17:26:58.000000000 +0000 @@ -8,7 +8,7 @@ name AS event, extra, FROM - org_mozilla_firefox.events e + `moz-fx-data-shared-prod.org_mozilla_firefox.events` e CROSS JOIN UNNEST(e.events) AS event UNION ALL @@ -19,7 +19,7 @@ name AS event, extra, FROM - org_mozilla_firefox_beta.events e + `moz-fx-data-shared-prod.org_mozilla_firefox_beta.events` e CROSS JOIN UNNEST(e.events) AS event UNION ALL @@ -30,7 +30,7 @@ name AS event, extra, FROM - org_mozilla_fenix.events e + `moz-fx-data-shared-prod.org_mozilla_fenix.events` e CROSS JOIN UNNEST(e.events) AS event ), @@ -115,7 +115,9 @@ ARRAY_AGG( STRUCT( event_property_value AS key, - udf.event_code_points_to_string([event_property_value_index]) AS value, + `moz-fx-data-shared-prod.udf.event_code_points_to_string`( + [event_property_value_index] + ) AS value, event_property_value_index AS index ) ORDER BY @@ -140,7 +142,7 @@ event, first_timestamp, primary_index AS numeric_index, - udf.event_code_points_to_string([primary_index]) AS index, + `moz-fx-data-shared-prod.udf.event_code_points_to_string`([primary_index]) AS index, ARRAY_AGG( IF( event_property IS NULL, @@ -181,7 +183,7 @@ name AS event, extra, FROM - org_mozilla_firefox.events e + `moz-fx-data-shared-prod.org_mozilla_firefox.events` e CROSS JOIN UNNEST(e.events) AS event UNION ALL @@ -192,7 +194,7 @@ name AS event, extra, FROM - org_mozilla_firefox_beta.events e + `moz-fx-data-shared-prod.org_mozilla_firefox_beta.events` e CROSS JOIN UNNEST(e.events) AS event UNION ALL @@ -203,7 +205,7 @@ name AS event, extra, FROM - org_mozilla_fenix.events e + `moz-fx-data-shared-prod.org_mozilla_fenix.events` e CROSS JOIN UNNEST(e.events) AS event ), @@ -219,7 +221,7 @@ SELECT * FROM - fenix_derived.event_types_history_v1 + `moz-fx-data-shared-prod.fenix_derived.event_types_history_v1` WHERE submission_date = DATE_SUB(@submission_date, INTERVAL 1 DAY) ), @@ -402,7 +404,9 @@ ARRAY_AGG( STRUCT( event_property_value AS key, - udf.event_code_points_to_string([event_property_value_index]) AS value, + `moz-fx-data-shared-prod.udf.event_code_points_to_string`( + [event_property_value_index] + ) AS value, event_property_value_index AS index ) ORDER BY @@ -425,7 +429,7 @@ event, first_timestamp, numeric_index, - udf.event_code_points_to_string([numeric_index]) AS index, + `moz-fx-data-shared-prod.udf.event_code_points_to_string`([numeric_index]) AS index, ARRAY_AGG( IF( event_property IS NULL, diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_accounts_derived/events_daily_v1/query.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_accounts_derived/events_daily_v1/query.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_accounts_derived/events_daily_v1/query.sql 2024-06-19 17:25:03.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_accounts_derived/events_daily_v1/query.sql 2024-06-19 17:27:03.000000000 +0000 @@ -3,7 +3,7 @@ SELECT * FROM - firefox_accounts_derived.funnel_events_source_v1 + `moz-fx-data-shared-prod.firefox_accounts_derived.funnel_events_source_v1` ), events AS ( SELECT @@ -19,12 +19,18 @@ ), joined AS ( SELECT - CONCAT(udf.pack_event_properties(events.extra, event_types.event_properties), index) AS index, + CONCAT( + `moz-fx-data-shared-prod.udf.pack_event_properties`( + events.extra, + event_types.event_properties + ), + index + ) AS index, events.* EXCEPT (category, event, extra) FROM events INNER JOIN - firefox_accounts.event_types event_types + `moz-fx-data-shared-prod.firefox_accounts.event_types` event_types USING (category, event) ) SELECT diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_accounts_derived/event_types_history_v1/query.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_accounts_derived/event_types_history_v1/query.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_accounts_derived/event_types_history_v1/query.sql 2024-06-19 17:25:03.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_accounts_derived/event_types_history_v1/query.sql 2024-06-19 17:27:02.000000000 +0000 @@ -4,7 +4,7 @@ SELECT * FROM - firefox_accounts_derived.funnel_events_source_v1 + `moz-fx-data-shared-prod.firefox_accounts_derived.funnel_events_source_v1` ), sample AS ( SELECT @@ -87,7 +87,9 @@ ARRAY_AGG( STRUCT( event_property_value AS key, - udf.event_code_points_to_string([event_property_value_index]) AS value, + `moz-fx-data-shared-prod.udf.event_code_points_to_string`( + [event_property_value_index] + ) AS value, event_property_value_index AS index ) ORDER BY @@ -112,7 +114,7 @@ event, first_timestamp, primary_index AS numeric_index, - udf.event_code_points_to_string([primary_index]) AS index, + `moz-fx-data-shared-prod.udf.event_code_points_to_string`([primary_index]) AS index, ARRAY_AGG( IF( event_property IS NULL, @@ -149,7 +151,7 @@ SELECT * FROM - firefox_accounts_derived.funnel_events_source_v1 + `moz-fx-data-shared-prod.firefox_accounts_derived.funnel_events_source_v1` ), current_events AS ( SELECT @@ -163,7 +165,7 @@ SELECT * FROM - firefox_accounts_derived.event_types_history_v1 + `moz-fx-data-shared-prod.firefox_accounts_derived.event_types_history_v1` WHERE submission_date = DATE_SUB(@submission_date, INTERVAL 1 DAY) ), @@ -346,7 +348,9 @@ ARRAY_AGG( STRUCT( event_property_value AS key, - udf.event_code_points_to_string([event_property_value_index]) AS value, + `moz-fx-data-shared-prod.udf.event_code_points_to_string`( + [event_property_value_index] + ) AS value, event_property_value_index AS index ) ORDER BY @@ -369,7 +373,7 @@ event, first_timestamp, numeric_index, - udf.event_code_points_to_string([numeric_index]) AS index, + `moz-fx-data-shared-prod.udf.event_code_points_to_string`([numeric_index]) AS index, ARRAY_AGG( IF( event_property IS NULL, diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_accounts_derived/fxa_amplitude_export_v1/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_accounts_derived/fxa_amplitude_export_v1/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_accounts_derived/fxa_amplitude_export_v1/metadata.yaml 2024-06-19 17:26:05.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_accounts_derived/fxa_amplitude_export_v1/metadata.yaml 2024-06-19 17:26:14.000000000 +0000 @@ -24,8 +24,8 @@ - workgroup:mozilla-confidential references: query.sql: - - _current.os_used_month - - firefox_accounts_derived.fxa_amplitude_export_v1 + - moz-fx-data-shared-prod._current.os_used_month - moz-fx-data-shared-prod.firefox_accounts_derived.encrypted_keys_v1 + - moz-fx-data-shared-prod.firefox_accounts_derived.fxa_amplitude_export_v1 - moz-fx-dataops-secrets.airflow_query_keys.fxa_prod - moz-fx-fxa-prod-0712.fxa_prod_logs.docker_fxa_auth diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_accounts_derived/fxa_amplitude_export_v1/query.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_accounts_derived/fxa_amplitude_export_v1/query.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_accounts_derived/fxa_amplitude_export_v1/query.sql 2024-06-19 17:24:26.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_accounts_derived/fxa_amplitude_export_v1/query.sql 2024-06-19 17:24:22.000000000 +0000 @@ -39,7 +39,10 @@ -- to prevent weirdness from timestamp field, use provided -- submission date parameter as timestamp TO_HEX( - udf.hmac_sha256((SELECT * FROM hmac_key), CAST(jsonPayload.fields.user_id AS BYTES)) + `moz-fx-data-shared-prod.udf.hmac_sha256`( + (SELECT * FROM hmac_key), + CAST(jsonPayload.fields.user_id AS BYTES) + ) ) AS user_id, MIN(CONCAT(insertId, '-user')) AS insert_id, -- Amplitude properties, scalars @@ -114,22 +117,27 @@ SELECT * EXCEPT (submission_date_pacific) FROM - firefox_accounts_derived.fxa_amplitude_export_v1 + `moz-fx-data-shared-prod.firefox_accounts_derived.fxa_amplitude_export_v1` WHERE submission_date_pacific = DATE_SUB(@submission_date, INTERVAL 1 DAY) - AND udf.shift_28_bits_one_day(days_seen_bits) > 0 + AND `moz-fx-data-shared-prod.udf.shift_28_bits_one_day`(days_seen_bits) > 0 ) SELECT @submission_date AS submission_date_pacific, _current.* REPLACE ( COALESCE(_current.user_id, _previous.user_id) AS user_id, - udf.combine_adjacent_days_28_bits( + `moz-fx-data-shared-prod.udf.combine_adjacent_days_28_bits`( _previous.days_seen_bits, _current.days_seen_bits ) AS days_seen_bits, - udf.combine_days_seen_maps( + `moz-fx-data-shared-prod.udf.combine_days_seen_maps`( _previous.os_used_month, - ARRAY(SELECT STRUCT(key, CAST(TRUE AS INT64) AS value) FROM _current.os_used_month AS key) + ARRAY( + SELECT + STRUCT(key, CAST(TRUE AS INT64) AS value) + FROM + `moz-fx-data-shared-prod._current.os_used_month` AS key + ) ) AS os_used_month ) FROM diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_accounts_derived/fxa_amplitude_user_ids_v1/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_accounts_derived/fxa_amplitude_user_ids_v1/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_accounts_derived/fxa_amplitude_user_ids_v1/metadata.yaml 2024-06-19 17:26:05.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_accounts_derived/fxa_amplitude_user_ids_v1/metadata.yaml 2024-06-19 17:26:14.000000000 +0000 @@ -15,7 +15,7 @@ - workgroup:mozilla-confidential references: query.sql: - - fxa_amplitude_user_ids_v1 - moz-fx-data-shared-prod.firefox_accounts_derived.encrypted_keys_v1 + - moz-fx-data-shared-prod.firefox_accounts_derived.fxa_amplitude_user_ids_v1 - moz-fx-dataops-secrets.airflow_query_keys.fxa_prod - moz-fx-fxa-prod-0712.fxa_prod_logs.docker_fxa_auth diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_accounts_derived/fxa_amplitude_user_ids_v1/query.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_accounts_derived/fxa_amplitude_user_ids_v1/query.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_accounts_derived/fxa_amplitude_user_ids_v1/query.sql 2024-06-19 17:24:26.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_accounts_derived/fxa_amplitude_user_ids_v1/query.sql 2024-06-19 17:24:22.000000000 +0000 @@ -12,7 +12,10 @@ ) SELECT TO_HEX( - udf.hmac_sha256((SELECT * FROM hmac_key), CAST(jsonPayload.fields.user_id AS BYTES)) + `moz-fx-data-shared-prod.udf.hmac_sha256`( + (SELECT * FROM hmac_key), + CAST(jsonPayload.fields.user_id AS BYTES) + ) ) AS user_id FROM `moz-fx-fxa-prod-0712.fxa_prod_logs.docker_fxa_auth` @@ -23,5 +26,5 @@ SELECT user_id FROM - fxa_amplitude_user_ids_v1 + `moz-fx-data-shared-prod.firefox_accounts_derived.fxa_amplitude_user_ids_v1` {% endif %} diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_accounts_derived/fxa_delete_events_v1/query.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_accounts_derived/fxa_delete_events_v1/query.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_accounts_derived/fxa_delete_events_v1/query.sql 2024-06-19 17:24:26.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_accounts_derived/fxa_delete_events_v1/query.sql 2024-06-19 17:24:22.000000000 +0000 @@ -14,7 +14,10 @@ `timestamp` AS submission_timestamp, TO_HEX(SHA256(jsonPayload.fields.uid)) AS user_id, TO_HEX( - udf.hmac_sha256((SELECT * FROM hmac_key), CAST(jsonPayload.fields.uid AS BYTES)) + `moz-fx-data-shared-prod.udf.hmac_sha256`( + (SELECT * FROM hmac_key), + CAST(jsonPayload.fields.uid AS BYTES) + ) ) AS hmac_user_id, FROM `moz-fx-fxa-prod-0712.fxa_prod_logs.docker_fxa_auth` diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_accounts_derived/fxa_delete_events_v2/query.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_accounts_derived/fxa_delete_events_v2/query.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_accounts_derived/fxa_delete_events_v2/query.sql 2024-06-19 17:24:26.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_accounts_derived/fxa_delete_events_v2/query.sql 2024-06-19 17:24:22.000000000 +0000 @@ -14,7 +14,10 @@ MIN(`timestamp`) AS submission_timestamp, TO_HEX(SHA256(jsonPayload.fields.uid)) AS user_id, TO_HEX( - udf.hmac_sha256((SELECT * FROM hmac_key), CAST(jsonPayload.fields.uid AS BYTES)) + `moz-fx-data-shared-prod.udf.hmac_sha256`( + (SELECT * FROM hmac_key), + CAST(jsonPayload.fields.uid AS BYTES) + ) ) AS hmac_user_id, FROM `moz-fx-fxa-prod.gke_fxa_prod_log.stderr` diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_accounts_derived/fxa_log_device_command_events_v1/query.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_accounts_derived/fxa_log_device_command_events_v1/query.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_accounts_derived/fxa_log_device_command_events_v1/query.sql 2024-06-19 17:24:26.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_accounts_derived/fxa_log_device_command_events_v1/query.sql 2024-06-19 17:24:22.000000000 +0000 @@ -15,22 +15,31 @@ `timestamp`, jsonPayload.type, TO_HEX( - udf.hmac_sha256((SELECT * FROM hmac_key), CAST(jsonPayload.fields.uid AS BYTES)) + `moz-fx-data-shared-prod.udf.hmac_sha256`( + (SELECT * FROM hmac_key), + CAST(jsonPayload.fields.uid AS BYTES) + ) ) AS user_id, TO_HEX( - udf.hmac_sha256( + `moz-fx-data-shared-prod.udf.hmac_sha256`( (SELECT * FROM hmac_key), CAST(FORMAT('%d', CAST(jsonPayload.fields.index AS INT64)) AS BYTES) ) ) AS index, jsonPayload.fields.command, TO_HEX( - udf.hmac_sha256((SELECT * FROM hmac_key), CAST(jsonPayload.fields.target AS BYTES)) + `moz-fx-data-shared-prod.udf.hmac_sha256`( + (SELECT * FROM hmac_key), + CAST(jsonPayload.fields.target AS BYTES) + ) ) AS target, jsonPayload.fields.targetOS AS target_os, jsonPayload.fields.targetType AS target_type, TO_HEX( - udf.hmac_sha256((SELECT * FROM hmac_key), CAST(jsonPayload.fields.sender AS BYTES)) + `moz-fx-data-shared-prod.udf.hmac_sha256`( + (SELECT * FROM hmac_key), + CAST(jsonPayload.fields.sender AS BYTES) + ) ) AS sender, jsonPayload.fields.senderOS AS sender_os, jsonPayload.fields.senderType AS sender_type, diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_accounts_derived/fxa_log_device_command_events_v2/query.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_accounts_derived/fxa_log_device_command_events_v2/query.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_accounts_derived/fxa_log_device_command_events_v2/query.sql 2024-06-19 17:24:26.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_accounts_derived/fxa_log_device_command_events_v2/query.sql 2024-06-19 17:24:22.000000000 +0000 @@ -14,22 +14,31 @@ `timestamp`, jsonPayload.type, TO_HEX( - udf.hmac_sha256((SELECT * FROM hmac_key), CAST(jsonPayload.fields.uid AS BYTES)) + `moz-fx-data-shared-prod.udf.hmac_sha256`( + (SELECT * FROM hmac_key), + CAST(jsonPayload.fields.uid AS BYTES) + ) ) AS user_id, TO_HEX( - udf.hmac_sha256( + `moz-fx-data-shared-prod.udf.hmac_sha256`( (SELECT * FROM hmac_key), CAST(FORMAT('%d', CAST(jsonPayload.fields.index AS INT64)) AS BYTES) ) ) AS index, jsonPayload.fields.command, TO_HEX( - udf.hmac_sha256((SELECT * FROM hmac_key), CAST(jsonPayload.fields.target AS BYTES)) + `moz-fx-data-shared-prod.udf.hmac_sha256`( + (SELECT * FROM hmac_key), + CAST(jsonPayload.fields.target AS BYTES) + ) ) AS target, jsonPayload.fields.targetOS AS target_os, jsonPayload.fields.targetType AS target_type, TO_HEX( - udf.hmac_sha256((SELECT * FROM hmac_key), CAST(jsonPayload.fields.sender AS BYTES)) + `moz-fx-data-shared-prod.udf.hmac_sha256`( + (SELECT * FROM hmac_key), + CAST(jsonPayload.fields.sender AS BYTES) + ) ) AS sender, jsonPayload.fields.senderOS AS sender_os, jsonPayload.fields.senderType AS sender_type, diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_accounts_derived/fxa_users_services_first_seen_v1/query.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_accounts_derived/fxa_users_services_first_seen_v1/query.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_accounts_derived/fxa_users_services_first_seen_v1/query.sql 2024-06-19 17:24:26.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_accounts_derived/fxa_users_services_first_seen_v1/query.sql 2024-06-19 17:24:22.000000000 +0000 @@ -25,8 +25,12 @@ user_id, service, -- using mode_last with w1_reversed to get mode_first - udf.mode_last(ARRAY_AGG(`timestamp`) OVER w1_reversed) AS first_service_timestamp_last, - udf.mode_last(ARRAY_AGG(flow_id) OVER w1_reversed) AS first_service_flow, + `moz-fx-data-shared-prod.udf.mode_last`( + ARRAY_AGG(`timestamp`) OVER w1_reversed + ) AS first_service_timestamp_last, + `moz-fx-data-shared-prod.udf.mode_last`( + ARRAY_AGG(flow_id) OVER w1_reversed + ) AS first_service_flow, LOGICAL_OR(IFNULL(event_type = 'fxa_reg - complete', FALSE)) OVER w1_reversed AS did_register FROM fxa_content_auth_oauth @@ -38,7 +42,7 @@ AND DATE(`timestamp`) >= '2019-03-01' AND user_id IS NOT NULL WINDOW - -- We must provide a window with `ORDER BY timestamp DESC` so that udf.mode_last actually aggregates mode first. + -- We must provide a window with `ORDER BY timestamp DESC` so that `moz-fx-data-shared-prod.udf.mode_last` actually aggregates mode first. w1_reversed AS ( PARTITION BY user_id, @@ -172,8 +176,12 @@ user_id, service, -- using mode_last with w1_reversed to get mode_first - udf.mode_last(ARRAY_AGG(`timestamp`) OVER w1_reversed) AS first_service_timestamp_last, - udf.mode_last(ARRAY_AGG(flow_id) OVER w1_reversed) AS first_service_flow, + `moz-fx-data-shared-prod.udf.mode_last`( + ARRAY_AGG(`timestamp`) OVER w1_reversed + ) AS first_service_timestamp_last, + `moz-fx-data-shared-prod.udf.mode_last`( + ARRAY_AGG(flow_id) OVER w1_reversed + ) AS first_service_flow, LOGICAL_OR(IFNULL(event_type = 'fxa_reg - complete', FALSE)) OVER w1_reversed AS did_register FROM fxa_content_auth_oauth @@ -185,7 +193,7 @@ AND DATE(`timestamp`) >= '2019-03-01' AND user_id IS NOT NULL WINDOW - -- We must provide a window with `ORDER BY timestamp DESC` so that udf.mode_last actually aggregates mode first. + -- We must provide a window with `ORDER BY timestamp DESC` so that `moz-fx-data-shared-prod.udf.mode_last` actually aggregates mode first. w1_reversed AS ( PARTITION BY user_id, diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_background_defaultagent_derived/baseline_clients_daily_v1/query.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_background_defaultagent_derived/baseline_clients_daily_v1/query.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_background_defaultagent_derived/baseline_clients_daily_v1/query.sql 2024-06-19 17:25:04.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_background_defaultagent_derived/baseline_clients_daily_v1/query.sql 2024-06-19 17:27:04.000000000 +0000 @@ -7,7 +7,9 @@ sample_id, SAFE.PARSE_DATE('%F', SUBSTR(client_info.first_run_date, 1, 10)) AS first_run_date, mozfun.glean.parse_datetime(ping_info.end_time) AS parsed_end_time, - udf.glean_timespan_seconds(metrics.timespan.glean_baseline_duration) AS duration, + `moz-fx-data-shared-prod.udf.glean_timespan_seconds`( + metrics.timespan.glean_baseline_duration + ) AS duration, client_info.android_sdk_version, client_info.app_build, client_info.app_channel, @@ -26,7 +28,7 @@ CAST(NULL AS STRING) AS distribution_id, metadata.geo.subdivision1 AS geo_subdivision, FROM - `firefox_desktop_background_defaultagent_stable.baseline_v1` + `moz-fx-data-shared-prod.firefox_desktop_background_defaultagent_stable.baseline_v1` -- Baseline pings with 'foreground' reason were first introduced in early April 2020; -- we initially excluded them from baseline_clients_daily so that we could measure -- effects on KPIs. On 2020-08-25, we removed the filter on reason and backfilled. See: @@ -76,23 +78,35 @@ ) OVER w1 AS days_seen_session_end_bits, -- -- For all other dimensions, we use the mode of observed values in the day. - udf.mode_last(ARRAY_AGG(normalized_channel) OVER w1) AS normalized_channel, - udf.mode_last(ARRAY_AGG(normalized_os) OVER w1) AS normalized_os, - udf.mode_last(ARRAY_AGG(normalized_os_version) OVER w1) AS normalized_os_version, - udf.mode_last(ARRAY_AGG(android_sdk_version) OVER w1) AS android_sdk_version, - udf.mode_last(ARRAY_AGG(locale) OVER w1) AS locale, - udf.mode_last(ARRAY_AGG(city) OVER w1) AS city, - udf.mode_last(ARRAY_AGG(country) OVER w1) AS country, - udf.mode_last(ARRAY_AGG(isp) OVER w1) AS isp, - udf.mode_last(ARRAY_AGG(app_build) OVER w1) AS app_build, - udf.mode_last(ARRAY_AGG(app_channel) OVER w1) AS app_channel, - udf.mode_last(ARRAY_AGG(app_display_version) OVER w1) AS app_display_version, - udf.mode_last(ARRAY_AGG(architecture) OVER w1) AS architecture, - udf.mode_last(ARRAY_AGG(device_manufacturer) OVER w1) AS device_manufacturer, - udf.mode_last(ARRAY_AGG(device_model) OVER w1) AS device_model, - udf.mode_last(ARRAY_AGG(telemetry_sdk_build) OVER w1) AS telemetry_sdk_build, - udf.mode_last(ARRAY_AGG(distribution_id) OVER w1) AS distribution_id, - udf.mode_last(ARRAY_AGG(geo_subdivision) OVER w1) AS geo_subdivision, + `moz-fx-data-shared-prod.udf.mode_last`( + ARRAY_AGG(normalized_channel) OVER w1 + ) AS normalized_channel, + `moz-fx-data-shared-prod.udf.mode_last`(ARRAY_AGG(normalized_os) OVER w1) AS normalized_os, + `moz-fx-data-shared-prod.udf.mode_last`( + ARRAY_AGG(normalized_os_version) OVER w1 + ) AS normalized_os_version, + `moz-fx-data-shared-prod.udf.mode_last`( + ARRAY_AGG(android_sdk_version) OVER w1 + ) AS android_sdk_version, + `moz-fx-data-shared-prod.udf.mode_last`(ARRAY_AGG(locale) OVER w1) AS locale, + `moz-fx-data-shared-prod.udf.mode_last`(ARRAY_AGG(city) OVER w1) AS city, + `moz-fx-data-shared-prod.udf.mode_last`(ARRAY_AGG(country) OVER w1) AS country, + `moz-fx-data-shared-prod.udf.mode_last`(ARRAY_AGG(isp) OVER w1) AS isp, + `moz-fx-data-shared-prod.udf.mode_last`(ARRAY_AGG(app_build) OVER w1) AS app_build, + `moz-fx-data-shared-prod.udf.mode_last`(ARRAY_AGG(app_channel) OVER w1) AS app_channel, + `moz-fx-data-shared-prod.udf.mode_last`( + ARRAY_AGG(app_display_version) OVER w1 + ) AS app_display_version, + `moz-fx-data-shared-prod.udf.mode_last`(ARRAY_AGG(architecture) OVER w1) AS architecture, + `moz-fx-data-shared-prod.udf.mode_last`( + ARRAY_AGG(device_manufacturer) OVER w1 + ) AS device_manufacturer, + `moz-fx-data-shared-prod.udf.mode_last`(ARRAY_AGG(device_model) OVER w1) AS device_model, + `moz-fx-data-shared-prod.udf.mode_last`( + ARRAY_AGG(telemetry_sdk_build) OVER w1 + ) AS telemetry_sdk_build, + `moz-fx-data-shared-prod.udf.mode_last`(ARRAY_AGG(distribution_id) OVER w1) AS distribution_id, + `moz-fx-data-shared-prod.udf.mode_last`(ARRAY_AGG(geo_subdivision) OVER w1) AS geo_subdivision, FROM with_date_offsets WHERE @@ -133,7 +147,7 @@ FROM windowed AS cd LEFT JOIN - `firefox_desktop_background_defaultagent_derived.baseline_clients_first_seen_v1` AS cfs + `moz-fx-data-shared-prod.firefox_desktop_background_defaultagent_derived.baseline_clients_first_seen_v1` AS cfs USING (client_id) WHERE _n = 1 diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_background_tasks_derived/baseline_clients_daily_v1/query.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_background_tasks_derived/baseline_clients_daily_v1/query.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_background_tasks_derived/baseline_clients_daily_v1/query.sql 2024-06-19 17:25:03.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_background_tasks_derived/baseline_clients_daily_v1/query.sql 2024-06-19 17:27:04.000000000 +0000 @@ -7,7 +7,9 @@ sample_id, SAFE.PARSE_DATE('%F', SUBSTR(client_info.first_run_date, 1, 10)) AS first_run_date, mozfun.glean.parse_datetime(ping_info.end_time) AS parsed_end_time, - udf.glean_timespan_seconds(metrics.timespan.glean_baseline_duration) AS duration, + `moz-fx-data-shared-prod.udf.glean_timespan_seconds`( + metrics.timespan.glean_baseline_duration + ) AS duration, client_info.android_sdk_version, client_info.app_build, client_info.app_channel, @@ -26,7 +28,7 @@ CAST(NULL AS STRING) AS distribution_id, metadata.geo.subdivision1 AS geo_subdivision, FROM - `firefox_desktop_background_tasks_stable.baseline_v1` + `moz-fx-data-shared-prod.firefox_desktop_background_tasks_stable.baseline_v1` -- Baseline pings with 'foreground' reason were first introduced in early April 2020; -- we initially excluded them from baseline_clients_daily so that we could measure -- effects on KPIs. On 2020-08-25, we removed the filter on reason and backfilled. See: @@ -76,23 +78,35 @@ ) OVER w1 AS days_seen_session_end_bits, -- -- For all other dimensions, we use the mode of observed values in the day. - udf.mode_last(ARRAY_AGG(normalized_channel) OVER w1) AS normalized_channel, - udf.mode_last(ARRAY_AGG(normalized_os) OVER w1) AS normalized_os, - udf.mode_last(ARRAY_AGG(normalized_os_version) OVER w1) AS normalized_os_version, - udf.mode_last(ARRAY_AGG(android_sdk_version) OVER w1) AS android_sdk_version, - udf.mode_last(ARRAY_AGG(locale) OVER w1) AS locale, - udf.mode_last(ARRAY_AGG(city) OVER w1) AS city, - udf.mode_last(ARRAY_AGG(country) OVER w1) AS country, - udf.mode_last(ARRAY_AGG(isp) OVER w1) AS isp, - udf.mode_last(ARRAY_AGG(app_build) OVER w1) AS app_build, - udf.mode_last(ARRAY_AGG(app_channel) OVER w1) AS app_channel, - udf.mode_last(ARRAY_AGG(app_display_version) OVER w1) AS app_display_version, - udf.mode_last(ARRAY_AGG(architecture) OVER w1) AS architecture, - udf.mode_last(ARRAY_AGG(device_manufacturer) OVER w1) AS device_manufacturer, - udf.mode_last(ARRAY_AGG(device_model) OVER w1) AS device_model, - udf.mode_last(ARRAY_AGG(telemetry_sdk_build) OVER w1) AS telemetry_sdk_build, - udf.mode_last(ARRAY_AGG(distribution_id) OVER w1) AS distribution_id, - udf.mode_last(ARRAY_AGG(geo_subdivision) OVER w1) AS geo_subdivision, + `moz-fx-data-shared-prod.udf.mode_last`( + ARRAY_AGG(normalized_channel) OVER w1 + ) AS normalized_channel, + `moz-fx-data-shared-prod.udf.mode_last`(ARRAY_AGG(normalized_os) OVER w1) AS normalized_os, + `moz-fx-data-shared-prod.udf.mode_last`( + ARRAY_AGG(normalized_os_version) OVER w1 + ) AS normalized_os_version, + `moz-fx-data-shared-prod.udf.mode_last`( + ARRAY_AGG(android_sdk_version) OVER w1 + ) AS android_sdk_version, + `moz-fx-data-shared-prod.udf.mode_last`(ARRAY_AGG(locale) OVER w1) AS locale, + `moz-fx-data-shared-prod.udf.mode_last`(ARRAY_AGG(city) OVER w1) AS city, + `moz-fx-data-shared-prod.udf.mode_last`(ARRAY_AGG(country) OVER w1) AS country, + `moz-fx-data-shared-prod.udf.mode_last`(ARRAY_AGG(isp) OVER w1) AS isp, + `moz-fx-data-shared-prod.udf.mode_last`(ARRAY_AGG(app_build) OVER w1) AS app_build, + `moz-fx-data-shared-prod.udf.mode_last`(ARRAY_AGG(app_channel) OVER w1) AS app_channel, + `moz-fx-data-shared-prod.udf.mode_last`( + ARRAY_AGG(app_display_version) OVER w1 + ) AS app_display_version, + `moz-fx-data-shared-prod.udf.mode_last`(ARRAY_AGG(architecture) OVER w1) AS architecture, + `moz-fx-data-shared-prod.udf.mode_last`( + ARRAY_AGG(device_manufacturer) OVER w1 + ) AS device_manufacturer, + `moz-fx-data-shared-prod.udf.mode_last`(ARRAY_AGG(device_model) OVER w1) AS device_model, + `moz-fx-data-shared-prod.udf.mode_last`( + ARRAY_AGG(telemetry_sdk_build) OVER w1 + ) AS telemetry_sdk_build, + `moz-fx-data-shared-prod.udf.mode_last`(ARRAY_AGG(distribution_id) OVER w1) AS distribution_id, + `moz-fx-data-shared-prod.udf.mode_last`(ARRAY_AGG(geo_subdivision) OVER w1) AS geo_subdivision, FROM with_date_offsets WHERE @@ -133,7 +147,7 @@ FROM windowed AS cd LEFT JOIN - `firefox_desktop_background_tasks_derived.baseline_clients_first_seen_v1` AS cfs + `moz-fx-data-shared-prod.firefox_desktop_background_tasks_derived.baseline_clients_first_seen_v1` AS cfs USING (client_id) WHERE _n = 1 diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_background_update_derived/baseline_clients_daily_v1/query.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_background_update_derived/baseline_clients_daily_v1/query.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_background_update_derived/baseline_clients_daily_v1/query.sql 2024-06-19 17:25:04.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_background_update_derived/baseline_clients_daily_v1/query.sql 2024-06-19 17:27:04.000000000 +0000 @@ -7,7 +7,9 @@ sample_id, SAFE.PARSE_DATE('%F', SUBSTR(client_info.first_run_date, 1, 10)) AS first_run_date, mozfun.glean.parse_datetime(ping_info.end_time) AS parsed_end_time, - udf.glean_timespan_seconds(metrics.timespan.glean_baseline_duration) AS duration, + `moz-fx-data-shared-prod.udf.glean_timespan_seconds`( + metrics.timespan.glean_baseline_duration + ) AS duration, client_info.android_sdk_version, client_info.app_build, client_info.app_channel, @@ -26,7 +28,7 @@ CAST(NULL AS STRING) AS distribution_id, metadata.geo.subdivision1 AS geo_subdivision, FROM - `firefox_desktop_background_update_stable.baseline_v1` + `moz-fx-data-shared-prod.firefox_desktop_background_update_stable.baseline_v1` -- Baseline pings with 'foreground' reason were first introduced in early April 2020; -- we initially excluded them from baseline_clients_daily so that we could measure -- effects on KPIs. On 2020-08-25, we removed the filter on reason and backfilled. See: @@ -76,23 +78,35 @@ ) OVER w1 AS days_seen_session_end_bits, -- -- For all other dimensions, we use the mode of observed values in the day. - udf.mode_last(ARRAY_AGG(normalized_channel) OVER w1) AS normalized_channel, - udf.mode_last(ARRAY_AGG(normalized_os) OVER w1) AS normalized_os, - udf.mode_last(ARRAY_AGG(normalized_os_version) OVER w1) AS normalized_os_version, - udf.mode_last(ARRAY_AGG(android_sdk_version) OVER w1) AS android_sdk_version, - udf.mode_last(ARRAY_AGG(locale) OVER w1) AS locale, - udf.mode_last(ARRAY_AGG(city) OVER w1) AS city, - udf.mode_last(ARRAY_AGG(country) OVER w1) AS country, - udf.mode_last(ARRAY_AGG(isp) OVER w1) AS isp, - udf.mode_last(ARRAY_AGG(app_build) OVER w1) AS app_build, - udf.mode_last(ARRAY_AGG(app_channel) OVER w1) AS app_channel, - udf.mode_last(ARRAY_AGG(app_display_version) OVER w1) AS app_display_version, - udf.mode_last(ARRAY_AGG(architecture) OVER w1) AS architecture, - udf.mode_last(ARRAY_AGG(device_manufacturer) OVER w1) AS device_manufacturer, - udf.mode_last(ARRAY_AGG(device_model) OVER w1) AS device_model, - udf.mode_last(ARRAY_AGG(telemetry_sdk_build) OVER w1) AS telemetry_sdk_build, - udf.mode_last(ARRAY_AGG(distribution_id) OVER w1) AS distribution_id, - udf.mode_last(ARRAY_AGG(geo_subdivision) OVER w1) AS geo_subdivision, + `moz-fx-data-shared-prod.udf.mode_last`( + ARRAY_AGG(normalized_channel) OVER w1 + ) AS normalized_channel, + `moz-fx-data-shared-prod.udf.mode_last`(ARRAY_AGG(normalized_os) OVER w1) AS normalized_os, + `moz-fx-data-shared-prod.udf.mode_last`( + ARRAY_AGG(normalized_os_version) OVER w1 + ) AS normalized_os_version, + `moz-fx-data-shared-prod.udf.mode_last`( + ARRAY_AGG(android_sdk_version) OVER w1 + ) AS android_sdk_version, + `moz-fx-data-shared-prod.udf.mode_last`(ARRAY_AGG(locale) OVER w1) AS locale, + `moz-fx-data-shared-prod.udf.mode_last`(ARRAY_AGG(city) OVER w1) AS city, + `moz-fx-data-shared-prod.udf.mode_last`(ARRAY_AGG(country) OVER w1) AS country, + `moz-fx-data-shared-prod.udf.mode_last`(ARRAY_AGG(isp) OVER w1) AS isp, + `moz-fx-data-shared-prod.udf.mode_last`(ARRAY_AGG(app_build) OVER w1) AS app_build, + `moz-fx-data-shared-prod.udf.mode_last`(ARRAY_AGG(app_channel) OVER w1) AS app_channel, + `moz-fx-data-shared-prod.udf.mode_last`( + ARRAY_AGG(app_display_version) OVER w1 + ) AS app_display_version, + `moz-fx-data-shared-prod.udf.mode_last`(ARRAY_AGG(architecture) OVER w1) AS architecture, + `moz-fx-data-shared-prod.udf.mode_last`( + ARRAY_AGG(device_manufacturer) OVER w1 + ) AS device_manufacturer, + `moz-fx-data-shared-prod.udf.mode_last`(ARRAY_AGG(device_model) OVER w1) AS device_model, + `moz-fx-data-shared-prod.udf.mode_last`( + ARRAY_AGG(telemetry_sdk_build) OVER w1 + ) AS telemetry_sdk_build, + `moz-fx-data-shared-prod.udf.mode_last`(ARRAY_AGG(distribution_id) OVER w1) AS distribution_id, + `moz-fx-data-shared-prod.udf.mode_last`(ARRAY_AGG(geo_subdivision) OVER w1) AS geo_subdivision, FROM with_date_offsets WHERE @@ -133,7 +147,7 @@ FROM windowed AS cd LEFT JOIN - `firefox_desktop_background_update_derived.baseline_clients_first_seen_v1` AS cfs + `moz-fx-data-shared-prod.firefox_desktop_background_up ```⚠️ Only part of the diff is displayed.
scholtzan
This implements https://docs.google.com/document/d/1S4e42e4tbhjJuRsKlhTsxzPAYk7QNefKC7Hq1OJdDxA/edit
bqetl formatwill now also fully-qualify table and UDF references and check for them in the CI.Next steps:
skip_formatthat should be formatteddry-runis failing due tometrics_clients_last_seen_v1referencing themselves and having to explicit schema. So stage cannot deploy the table which is referenced in the dry run. This should be fixed in a follow-up PRChecklist for reviewer:
<username>:<branch>of the fork as parameter. The parameter will also show up in the logs of themanual-trigger-required-for-forkCI task together with more detailed instructions.For modifications to schemas in restricted namespaces (see
CODEOWNERS):┆Issue is synchronized with this Jira Task