validate.log

+ bq query '--destination_table=relud-17123:test.clients_daily_v6$20210105' --project_id=moz-fx-data-shared-prod --parameter=submission_date:DATE:2021-01-05 --time_partitioning_field=submission_date --clustering_fields=sample_id --replace
Waiting on bqjob_r3a6a16833c9cc1c0_00000176f8395bca_1 ... (285s) Current status: DONE
+ bq query --dataset_id=relud-17123:test --max_rows=1 --format=prettyjson --parameter=submission_date:DATE:2021-01-05
Waiting on bqjob_r72f38ead5639c19b_00000176f83dcf69_1 ... (48s) Current status: DONE
[
  {
    "mismatched_aborts_content_sum": "0",
    "mismatched_aborts_gmplugin_sum": "0",
    "mismatched_aborts_plugin_sum": "0",
    "mismatched_active_addons": "0",
    "mismatched_active_addons_count_mean": "0",
    "mismatched_active_experiment_branch": "0",
    "mismatched_active_experiment_id": "0",
    "mismatched_active_hours_sum": "0",
    "mismatched_ad_clicks_count_all": "0",
    "mismatched_addon_compatibility_check_enabled": "0",
    "mismatched_app_build_id": "0",
    "mismatched_app_display_version": "0",
    "mismatched_app_name": "0",
    "mismatched_app_version": "0",
    "mismatched_attribution": "0",
    "mismatched_blocklist_enabled": "0",
    "mismatched_channel": "0",
    "mismatched_city": "0",
    "mismatched_client_clock_skew_mean": "0",
    "mismatched_client_id": "0",
    "mismatched_client_submission_latency_mean": "0",
    "mismatched_country": "0",
    "mismatched_cpu_cores": "0",
    "mismatched_cpu_count": "0",
    "mismatched_cpu_family": "0",
    "mismatched_cpu_l2_cache_kb": "0",
    "mismatched_cpu_l3_cache_kb": "1",
    "mismatched_cpu_model": "0",
    "mismatched_cpu_speed_mhz": "0",
    "mismatched_cpu_stepping": "0",
    "mismatched_cpu_vendor": "0",
    "mismatched_crash_submit_attempt_content_sum": "0",
    "mismatched_crash_submit_attempt_main_sum": "0",
    "mismatched_crash_submit_attempt_plugin_sum": "0",
    "mismatched_crash_submit_success_content_sum": "0",
    "mismatched_crash_submit_success_main_sum": "0",
    "mismatched_crash_submit_success_plugin_sum": "0",
    "mismatched_crashes_detected_content_sum": "0",
    "mismatched_crashes_detected_gmplugin_sum": "0",
    "mismatched_crashes_detected_plugin_sum": "0",
    "mismatched_default_search_engine": "0",
    "mismatched_default_search_engine_data_load_path": "0",
    "mismatched_default_search_engine_data_name": "0",
    "mismatched_default_search_engine_data_origin": "0",
    "mismatched_default_search_engine_data_submission_url": "0",
    "mismatched_devtools_toolbox_opened_count_sum": "0",
    "mismatched_distribution_id": "0",
    "mismatched_e10s_enabled": "0",
    "mismatched_env_build_arch": "0",
    "mismatched_env_build_id": "0",
    "mismatched_env_build_version": "0",
    "mismatched_environment_settings_intl_accept_languages": "0",
    "mismatched_environment_settings_intl_app_locales": "0",
    "mismatched_environment_settings_intl_available_locales": "0",
    "mismatched_environment_settings_intl_regional_prefs_locales": "0",
    "mismatched_environment_settings_intl_requested_locales": "0",
    "mismatched_environment_settings_intl_system_locales": "0",
    "mismatched_experiments": "0",
    "mismatched_first_paint_mean": "0",
    "mismatched_flash_version": "0",
    "mismatched_fxa_configured": "0",
    "mismatched_geo_subdivision1": "0",
    "mismatched_geo_subdivision2": "0",
    "mismatched_gfx_features_advanced_layers_status": "0",
    "mismatched_gfx_features_d2d_status": "0",
    "mismatched_gfx_features_d3d11_status": "0",
    "mismatched_gfx_features_gpu_process_status": "0",
    "mismatched_histogram_parent_devtools_aboutdebugging_opened_count_sum": "0",
    "mismatched_histogram_parent_devtools_animationinspector_opened_count_sum": "0",
    "mismatched_histogram_parent_devtools_browserconsole_opened_count_sum": "0",
    "mismatched_histogram_parent_devtools_canvasdebugger_opened_count_sum": "0",
    "mismatched_histogram_parent_devtools_computedview_opened_count_sum": "0",
    "mismatched_histogram_parent_devtools_custom_opened_count_sum": "0",
    "mismatched_histogram_parent_devtools_developertoolbar_opened_count_sum": "0",
    "mismatched_histogram_parent_devtools_dom_opened_count_sum": "0",
    "mismatched_histogram_parent_devtools_eyedropper_opened_count_sum": "0",
    "mismatched_histogram_parent_devtools_fontinspector_opened_count_sum": "0",
    "mismatched_histogram_parent_devtools_inspector_opened_count_sum": "0",
    "mismatched_histogram_parent_devtools_jsbrowserdebugger_opened_count_sum": "0",
    "mismatched_histogram_parent_devtools_jsdebugger_opened_count_sum": "0",
    "mismatched_histogram_parent_devtools_jsprofiler_opened_count_sum": "0",
    "mismatched_histogram_parent_devtools_layoutview_opened_count_sum": "0",
    "mismatched_histogram_parent_devtools_memory_opened_count_sum": "0",
    "mismatched_histogram_parent_devtools_menu_eyedropper_opened_count_sum": "0",
    "mismatched_histogram_parent_devtools_netmonitor_opened_count_sum": "0",
    "mismatched_histogram_parent_devtools_options_opened_count_sum": "0",
    "mismatched_histogram_parent_devtools_paintflashing_opened_count_sum": "0",
    "mismatched_histogram_parent_devtools_picker_eyedropper_opened_count_sum": "0",
    "mismatched_histogram_parent_devtools_responsive_opened_count_sum": "0",
    "mismatched_histogram_parent_devtools_ruleview_opened_count_sum": "0",
    "mismatched_histogram_parent_devtools_scratchpad_opened_count_sum": "0",
    "mismatched_histogram_parent_devtools_scratchpad_window_opened_count_sum": "0",
    "mismatched_histogram_parent_devtools_shadereditor_opened_count_sum": "0",
    "mismatched_histogram_parent_devtools_storage_opened_count_sum": "0",
    "mismatched_histogram_parent_devtools_styleeditor_opened_count_sum": "0",
    "mismatched_histogram_parent_devtools_webaudioeditor_opened_count_sum": "0",
    "mismatched_histogram_parent_devtools_webconsole_opened_count_sum": "0",
    "mismatched_histogram_parent_devtools_webide_opened_count_sum": "0",
    "mismatched_install_year": "0",
    "mismatched_is_default_browser": "0",
    "mismatched_is_wow64": "0",
    "mismatched_isp_name": "0",
    "mismatched_isp_organization": "0",
    "mismatched_locale": "0",
    "mismatched_memory_mb": "0",
    "mismatched_normalized_channel": "0",
    "mismatched_normalized_os_version": "0",
    "mismatched_os": "0",
    "mismatched_os_service_pack_major": "0",
    "mismatched_os_service_pack_minor": "0",
    "mismatched_os_version": "0",
    "mismatched_pings_aggregated_by_this_row": "0",
    "mismatched_places_bookmarks_count_mean": "0",
    "mismatched_places_pages_count_mean": "0",
    "mismatched_plugin_hangs_sum": "0",
    "mismatched_plugins_infobar_allow_sum": "0",
    "mismatched_plugins_infobar_block_sum": "0",
    "mismatched_plugins_infobar_shown_sum": "0",
    "mismatched_plugins_notification_shown_sum": "0",
    "mismatched_previous_build_id": "0",
    "mismatched_push_api_notify_sum": "0",
    "mismatched_sample_id": "0",
    "mismatched_sandbox_effective_content_process_level": "0",
    "mismatched_scalar_combined_webrtc_nicer_stun_retransmits_sum": "0",
    "mismatched_scalar_combined_webrtc_nicer_turn_401s_sum": "0",
    "mismatched_scalar_combined_webrtc_nicer_turn_403s_sum": "0",
    "mismatched_scalar_combined_webrtc_nicer_turn_438s_sum": "0",
    "mismatched_scalar_content_navigator_storage_estimate_count_sum": "0",
    "mismatched_scalar_content_navigator_storage_persist_count_sum": "0",
    "mismatched_scalar_content_telemetry_event_counts_sum": "0",
    "mismatched_scalar_parent_aushelper_websense_reg_version": "0",
    "mismatched_scalar_parent_browser_engagement_max_concurrent_tab_count_max": "0",
    "mismatched_scalar_parent_browser_engagement_max_concurrent_window_count_max": "0",
    "mismatched_scalar_parent_browser_engagement_tab_open_event_count_sum": "0",
    "mismatched_scalar_parent_browser_engagement_total_uri_count_sum": "0",
    "mismatched_scalar_parent_browser_engagement_unfiltered_uri_count_sum": "0",
    "mismatched_scalar_parent_browser_engagement_unique_domains_count_max": "0",
    "mismatched_scalar_parent_browser_engagement_unique_domains_count_mean": "0",
    "mismatched_scalar_parent_browser_engagement_window_open_event_count_sum": "0",
    "mismatched_scalar_parent_devtools_accessibility_node_inspected_count_sum": "0",
    "mismatched_scalar_parent_devtools_accessibility_opened_count_sum": "0",
    "mismatched_scalar_parent_devtools_accessibility_picker_used_count_sum": "0",
    "mismatched_scalar_parent_devtools_accessibility_select_accessible_for_node_sum": "0",
    "mismatched_scalar_parent_devtools_accessibility_service_enabled_count_sum": "0",
    "mismatched_scalar_parent_devtools_copy_full_css_selector_opened_sum": "0",
    "mismatched_scalar_parent_devtools_copy_unique_css_selector_opened_sum": "0",
    "mismatched_scalar_parent_devtools_toolbar_eyedropper_opened_sum": "0",
    "mismatched_scalar_parent_dom_contentprocess_troubled_due_to_memory_sum": "0",
    "mismatched_scalar_parent_navigator_storage_estimate_count_sum": "0",
    "mismatched_scalar_parent_navigator_storage_persist_count_sum": "0",
    "mismatched_scalar_parent_storage_sync_api_usage_extensions_using_sum": "0",
    "mismatched_scalar_parent_telemetry_event_counts_sum": "0",
    "mismatched_scalar_parent_urlbar_searchmode_handoff_sum": "0",
    "mismatched_scalar_parent_urlbar_searchmode_keywordoffer_sum": "0",
    "mismatched_scalar_parent_urlbar_searchmode_oneoff_sum": "0",
    "mismatched_scalar_parent_urlbar_searchmode_shortcut_sum": "0",
    "mismatched_scalar_parent_urlbar_searchmode_tabtosearch_onboard_sum": "0",
    "mismatched_scalar_parent_urlbar_searchmode_tabtosearch_sum": "0",
    "mismatched_scalar_parent_urlbar_searchmode_topsites_newtab_sum": "0",
    "mismatched_scalar_parent_urlbar_searchmode_topsites_urlbar_sum": "0",
    "mismatched_scalar_parent_urlbar_searchmode_touchbar_sum": "0",
    "mismatched_scalar_parent_urlbar_searchmode_typed_sum": "0",
    "mismatched_search_cohort": "0",
    "mismatched_search_count_abouthome": "0",
    "mismatched_search_count_all": "0",
    "mismatched_search_count_contextmenu": "0",
    "mismatched_search_count_newtab": "0",
    "mismatched_search_count_organic": "0",
    "mismatched_search_count_searchbar": "0",
    "mismatched_search_count_system": "0",
    "mismatched_search_count_tagged_follow_on": "0",
    "mismatched_search_count_tagged_sap": "0",
    "mismatched_search_count_urlbar": "0",
    "mismatched_search_with_ads_count_all": "0",
    "mismatched_session_restored_mean": "0",
    "mismatched_sessions_started_on_this_day": "0",
    "mismatched_shutdown_kill_sum": "0",
    "mismatched_ssl_handshake_result_failure_sum": "0",
    "mismatched_ssl_handshake_result_success_sum": "0",
    "mismatched_submission_date": "0",
    "mismatched_submission_timestamp_min": "0",
    "mismatched_subsession_hours_sum": "0",
    "mismatched_sync_configured": "0",
    "mismatched_sync_count_desktop_mean": "0",
    "mismatched_sync_count_desktop_sum": "0",
    "mismatched_sync_count_mobile_mean": "0",
    "mismatched_sync_count_mobile_sum": "0",
    "mismatched_telemetry_enabled": "0",
    "mismatched_timezone_offset": "0",
    "mismatched_total_hours_sum": "0",
    "mismatched_trackers_blocked_sum": "0",
    "mismatched_update_auto_download": "0",
    "mismatched_update_channel": "0",
    "mismatched_update_enabled": "0",
    "mismatched_vendor": "0",
    "mismatched_web_notification_shown_sum": "0",
    "mismatched_windows_build_number": "0",
    "mismatched_windows_ubr": "0",
    "total_rows": "84763936"
  }
]
+ bq query --dataset_id=relud-17123:test --max_rows=1 --format=prettyjson --parameter=submission_date:DATE:2021-01-05
Waiting on bqjob_r2fd2f41a1ce3067a_00000176f845b83a_1 ... (65s) Current status: DONE
[
  {
    "mismatched_profile_age_in_days": "0",
    "mismatched_profile_creation_date": "0",
    "total_rows": "84763936"
  }
]

validate.sh

bq query < sql/moz-fx-data-shared-prod/telemetry_derived/clients_daily_v6/query.sql --destination_table='relud-17123:test.clients_daily_v6$20210105' --project_id=moz-fx-data-shared-prod --parameter=submission_date:DATE:2021-01-05 --time_partitioning_field=submission_date --clustering_fields=sample_id --replace
bq query < validate.sql --dataset_id=relud-17123:test --max_rows=1 --format=prettyjson --parameter=submission_date:DATE:2021-01-05
bq query < validate_any_value_aggregates.sql --dataset_id=relud-17123:test --max_rows=1 --format=prettyjson --parameter=submission_date:DATE:2021-01-05

validate.sql

CREATE TEMPORARY FUNCTION udf_float_not_equal(a ANY TYPE, b ANY TYPE) AS (
  a != b
  -- not equal only when at least one isn't approximately 0
  AND (ABS(a) > 1e-9 OR ABS(b) > 1e-9)
  -- not equal only when different by more than 0.01%
  AND ABS((a - b)/((a + b)/2)) > 0.0001
);
CREATE TEMPORARY FUNCTION udf_map_not_equal(a ANY TYPE, b ANY TYPE) AS (
  (
    WITH b AS (
      SELECT * FROM UNNEST(b)
    )
    SELECT
      -- compare using TO_JSON_STRING so that NULL = NULL
      LOGICAL_OR(
        TO_JSON_STRING(a.key) != TO_JSON_STRING(b.key)
        OR TO_JSON_STRING(a.value) != TO_JSON_STRING(b.value)
      )
    FROM
      UNNEST(a) AS a
    FULL JOIN
      b
    ON
      -- join using TO_JSON_STRING to match NULL
      (TO_JSON_STRING(a.key) = TO_JSON_STRING(b.key))
  )
);
SELECT
  COUNT(*) AS total_rows,
  COUNTIF(TO_JSON_STRING(via_ms.submission_date) != TO_JSON_STRING(direct.submission_date)) AS mismatched_submission_date,
  COUNTIF(TO_JSON_STRING(via_ms.client_id) != TO_JSON_STRING(direct.client_id)) AS mismatched_client_id,
  COUNTIF(TO_JSON_STRING(via_ms.aborts_content_sum) != TO_JSON_STRING(direct.aborts_content_sum)) AS mismatched_aborts_content_sum,
  COUNTIF(TO_JSON_STRING(via_ms.aborts_gmplugin_sum) != TO_JSON_STRING(direct.aborts_gmplugin_sum)) AS mismatched_aborts_gmplugin_sum,
  COUNTIF(TO_JSON_STRING(via_ms.aborts_plugin_sum) != TO_JSON_STRING(direct.aborts_plugin_sum)) AS mismatched_aborts_plugin_sum,
  COUNTIF(udf_float_not_equal(via_ms.active_addons_count_mean, direct.active_addons_count_mean)) AS mismatched_active_addons_count_mean,
  COUNTIF(TO_JSON_STRING(via_ms.active_addons) != TO_JSON_STRING(direct.active_addons)) AS mismatched_active_addons,
  COUNTIF(TO_JSON_STRING(via_ms.active_experiment_branch) != TO_JSON_STRING(direct.active_experiment_branch)) AS mismatched_active_experiment_branch,
  COUNTIF(TO_JSON_STRING(via_ms.active_experiment_id) != TO_JSON_STRING(direct.active_experiment_id)) AS mismatched_active_experiment_id,
  COUNTIF(udf_float_not_equal(via_ms.active_hours_sum, direct.active_hours_sum)) AS mismatched_active_hours_sum,
  COUNTIF(TO_JSON_STRING(via_ms.addon_compatibility_check_enabled) != TO_JSON_STRING(direct.addon_compatibility_check_enabled)) AS mismatched_addon_compatibility_check_enabled,
  COUNTIF(TO_JSON_STRING(via_ms.app_build_id) != TO_JSON_STRING(direct.app_build_id)) AS mismatched_app_build_id,
  COUNTIF(TO_JSON_STRING(via_ms.app_display_version) != TO_JSON_STRING(direct.app_display_version)) AS mismatched_app_display_version,
  COUNTIF(TO_JSON_STRING(via_ms.app_name) != TO_JSON_STRING(direct.app_name)) AS mismatched_app_name,
  COUNTIF(TO_JSON_STRING(via_ms.app_version) != TO_JSON_STRING(direct.app_version)) AS mismatched_app_version,
  COUNTIF(TO_JSON_STRING(via_ms.attribution) != TO_JSON_STRING(direct.attribution)) AS mismatched_attribution,
  COUNTIF(TO_JSON_STRING(via_ms.blocklist_enabled) != TO_JSON_STRING(direct.blocklist_enabled)) AS mismatched_blocklist_enabled,
  COUNTIF(TO_JSON_STRING(via_ms.channel) != TO_JSON_STRING(direct.channel)) AS mismatched_channel,
  COUNTIF(udf_float_not_equal(via_ms.client_clock_skew_mean, direct.client_clock_skew_mean)) AS mismatched_client_clock_skew_mean,
  COUNTIF(udf_float_not_equal(via_ms.client_submission_latency_mean, direct.client_submission_latency_mean)) AS mismatched_client_submission_latency_mean,
  COUNTIF(TO_JSON_STRING(via_ms.cpu_cores) != TO_JSON_STRING(direct.cpu_cores)) AS mismatched_cpu_cores,
  COUNTIF(TO_JSON_STRING(via_ms.cpu_count) != TO_JSON_STRING(direct.cpu_count)) AS mismatched_cpu_count,
  COUNTIF(TO_JSON_STRING(via_ms.cpu_family) != TO_JSON_STRING(direct.cpu_family)) AS mismatched_cpu_family,
  COUNTIF(TO_JSON_STRING(via_ms.cpu_l2_cache_kb) != TO_JSON_STRING(direct.cpu_l2_cache_kb)) AS mismatched_cpu_l2_cache_kb,
  COUNTIF(TO_JSON_STRING(via_ms.cpu_l3_cache_kb) != TO_JSON_STRING(direct.cpu_l3_cache_kb)) AS mismatched_cpu_l3_cache_kb,
  COUNTIF(TO_JSON_STRING(via_ms.cpu_model) != TO_JSON_STRING(direct.cpu_model)) AS mismatched_cpu_model,
  COUNTIF(TO_JSON_STRING(via_ms.cpu_speed_mhz) != TO_JSON_STRING(direct.cpu_speed_mhz)) AS mismatched_cpu_speed_mhz,
  COUNTIF(TO_JSON_STRING(via_ms.cpu_stepping) != TO_JSON_STRING(direct.cpu_stepping)) AS mismatched_cpu_stepping,
  COUNTIF(TO_JSON_STRING(via_ms.cpu_vendor) != TO_JSON_STRING(direct.cpu_vendor)) AS mismatched_cpu_vendor,
  COUNTIF(TO_JSON_STRING(via_ms.crashes_detected_content_sum) != TO_JSON_STRING(direct.crashes_detected_content_sum)) AS mismatched_crashes_detected_content_sum,
  COUNTIF(TO_JSON_STRING(via_ms.crashes_detected_gmplugin_sum) != TO_JSON_STRING(direct.crashes_detected_gmplugin_sum)) AS mismatched_crashes_detected_gmplugin_sum,
  COUNTIF(TO_JSON_STRING(via_ms.crashes_detected_plugin_sum) != TO_JSON_STRING(direct.crashes_detected_plugin_sum)) AS mismatched_crashes_detected_plugin_sum,
  COUNTIF(TO_JSON_STRING(via_ms.crash_submit_attempt_content_sum) != TO_JSON_STRING(direct.crash_submit_attempt_content_sum)) AS mismatched_crash_submit_attempt_content_sum,
  COUNTIF(TO_JSON_STRING(via_ms.crash_submit_attempt_main_sum) != TO_JSON_STRING(direct.crash_submit_attempt_main_sum)) AS mismatched_crash_submit_attempt_main_sum,
  COUNTIF(TO_JSON_STRING(via_ms.crash_submit_attempt_plugin_sum) != TO_JSON_STRING(direct.crash_submit_attempt_plugin_sum)) AS mismatched_crash_submit_attempt_plugin_sum,
  COUNTIF(TO_JSON_STRING(via_ms.crash_submit_success_content_sum) != TO_JSON_STRING(direct.crash_submit_success_content_sum)) AS mismatched_crash_submit_success_content_sum,
  COUNTIF(TO_JSON_STRING(via_ms.crash_submit_success_main_sum) != TO_JSON_STRING(direct.crash_submit_success_main_sum)) AS mismatched_crash_submit_success_main_sum,
  COUNTIF(TO_JSON_STRING(via_ms.crash_submit_success_plugin_sum) != TO_JSON_STRING(direct.crash_submit_success_plugin_sum)) AS mismatched_crash_submit_success_plugin_sum,
  COUNTIF(TO_JSON_STRING(via_ms.default_search_engine) != TO_JSON_STRING(direct.default_search_engine)) AS mismatched_default_search_engine,
  COUNTIF(TO_JSON_STRING(via_ms.default_search_engine_data_load_path) != TO_JSON_STRING(direct.default_search_engine_data_load_path)) AS mismatched_default_search_engine_data_load_path,
  COUNTIF(TO_JSON_STRING(via_ms.default_search_engine_data_name) != TO_JSON_STRING(direct.default_search_engine_data_name)) AS mismatched_default_search_engine_data_name,
  COUNTIF(TO_JSON_STRING(via_ms.default_search_engine_data_origin) != TO_JSON_STRING(direct.default_search_engine_data_origin)) AS mismatched_default_search_engine_data_origin,
  COUNTIF(TO_JSON_STRING(via_ms.default_search_engine_data_submission_url) != TO_JSON_STRING(direct.default_search_engine_data_submission_url)) AS mismatched_default_search_engine_data_submission_url,
  COUNTIF(TO_JSON_STRING(via_ms.devtools_toolbox_opened_count_sum) != TO_JSON_STRING(direct.devtools_toolbox_opened_count_sum)) AS mismatched_devtools_toolbox_opened_count_sum,
  COUNTIF(TO_JSON_STRING(via_ms.distribution_id) != TO_JSON_STRING(direct.distribution_id)) AS mismatched_distribution_id,
  COUNTIF(TO_JSON_STRING(via_ms.e10s_enabled) != TO_JSON_STRING(direct.e10s_enabled)) AS mismatched_e10s_enabled,
  COUNTIF(TO_JSON_STRING(via_ms.env_build_arch) != TO_JSON_STRING(direct.env_build_arch)) AS mismatched_env_build_arch,
  COUNTIF(TO_JSON_STRING(via_ms.env_build_id) != TO_JSON_STRING(direct.env_build_id)) AS mismatched_env_build_id,
  COUNTIF(TO_JSON_STRING(via_ms.env_build_version) != TO_JSON_STRING(direct.env_build_version)) AS mismatched_env_build_version,
  COUNTIF(TO_JSON_STRING(via_ms.environment_settings_intl_accept_languages) != TO_JSON_STRING(direct.environment_settings_intl_accept_languages)) AS mismatched_environment_settings_intl_accept_languages,
  COUNTIF(TO_JSON_STRING(via_ms.environment_settings_intl_app_locales) != TO_JSON_STRING(direct.environment_settings_intl_app_locales)) AS mismatched_environment_settings_intl_app_locales,
  COUNTIF(TO_JSON_STRING(via_ms.environment_settings_intl_available_locales) != TO_JSON_STRING(direct.environment_settings_intl_available_locales)) AS mismatched_environment_settings_intl_available_locales,
  COUNTIF(TO_JSON_STRING(via_ms.environment_settings_intl_requested_locales) != TO_JSON_STRING(direct.environment_settings_intl_requested_locales)) AS mismatched_environment_settings_intl_requested_locales,
  COUNTIF(TO_JSON_STRING(via_ms.environment_settings_intl_system_locales) != TO_JSON_STRING(direct.environment_settings_intl_system_locales)) AS mismatched_environment_settings_intl_system_locales,
  COUNTIF(TO_JSON_STRING(via_ms.environment_settings_intl_regional_prefs_locales) != TO_JSON_STRING(direct.environment_settings_intl_regional_prefs_locales)) AS mismatched_environment_settings_intl_regional_prefs_locales,
  COUNTIF(TO_JSON_STRING(via_ms.experiments) != TO_JSON_STRING(direct.experiments)) AS mismatched_experiments,
  COUNTIF(udf_float_not_equal(via_ms.first_paint_mean, direct.first_paint_mean)) AS mismatched_first_paint_mean,
  COUNTIF(TO_JSON_STRING(via_ms.flash_version) != TO_JSON_STRING(direct.flash_version)) AS mismatched_flash_version,
  COUNTIF(TO_JSON_STRING(via_ms.country) != TO_JSON_STRING(direct.country)) AS mismatched_country,
  COUNTIF(TO_JSON_STRING(via_ms.city) != TO_JSON_STRING(direct.city)) AS mismatched_city,
  COUNTIF(TO_JSON_STRING(via_ms.geo_subdivision1) != TO_JSON_STRING(direct.geo_subdivision1)) AS mismatched_geo_subdivision1,
  COUNTIF(TO_JSON_STRING(via_ms.geo_subdivision2) != TO_JSON_STRING(direct.geo_subdivision2)) AS mismatched_geo_subdivision2,
  COUNTIF(TO_JSON_STRING(via_ms.isp_name) != TO_JSON_STRING(direct.isp_name)) AS mismatched_isp_name,
  COUNTIF(TO_JSON_STRING(via_ms.isp_organization) != TO_JSON_STRING(direct.isp_organization)) AS mismatched_isp_organization,
  COUNTIF(TO_JSON_STRING(via_ms.gfx_features_advanced_layers_status) != TO_JSON_STRING(direct.gfx_features_advanced_layers_status)) AS mismatched_gfx_features_advanced_layers_status,
  COUNTIF(TO_JSON_STRING(via_ms.gfx_features_d2d_status) != TO_JSON_STRING(direct.gfx_features_d2d_status)) AS mismatched_gfx_features_d2d_status,
  COUNTIF(TO_JSON_STRING(via_ms.gfx_features_d3d11_status) != TO_JSON_STRING(direct.gfx_features_d3d11_status)) AS mismatched_gfx_features_d3d11_status,
  COUNTIF(TO_JSON_STRING(via_ms.gfx_features_gpu_process_status) != TO_JSON_STRING(direct.gfx_features_gpu_process_status)) AS mismatched_gfx_features_gpu_process_status,
  COUNTIF(TO_JSON_STRING(via_ms.histogram_parent_devtools_aboutdebugging_opened_count_sum) != TO_JSON_STRING(direct.histogram_parent_devtools_aboutdebugging_opened_count_sum)) AS mismatched_histogram_parent_devtools_aboutdebugging_opened_count_sum,
  COUNTIF(TO_JSON_STRING(via_ms.histogram_parent_devtools_animationinspector_opened_count_sum) != TO_JSON_STRING(direct.histogram_parent_devtools_animationinspector_opened_count_sum)) AS mismatched_histogram_parent_devtools_animationinspector_opened_count_sum,
  COUNTIF(TO_JSON_STRING(via_ms.histogram_parent_devtools_browserconsole_opened_count_sum) != TO_JSON_STRING(direct.histogram_parent_devtools_browserconsole_opened_count_sum)) AS mismatched_histogram_parent_devtools_browserconsole_opened_count_sum,
  COUNTIF(TO_JSON_STRING(via_ms.histogram_parent_devtools_canvasdebugger_opened_count_sum) != TO_JSON_STRING(direct.histogram_parent_devtools_canvasdebugger_opened_count_sum)) AS mismatched_histogram_parent_devtools_canvasdebugger_opened_count_sum,
  COUNTIF(TO_JSON_STRING(via_ms.histogram_parent_devtools_computedview_opened_count_sum) != TO_JSON_STRING(direct.histogram_parent_devtools_computedview_opened_count_sum)) AS mismatched_histogram_parent_devtools_computedview_opened_count_sum,
  COUNTIF(TO_JSON_STRING(via_ms.histogram_parent_devtools_custom_opened_count_sum) != TO_JSON_STRING(direct.histogram_parent_devtools_custom_opened_count_sum)) AS mismatched_histogram_parent_devtools_custom_opened_count_sum,
  COUNTIF(TO_JSON_STRING(via_ms.histogram_parent_devtools_developertoolbar_opened_count_sum) != TO_JSON_STRING(direct.histogram_parent_devtools_developertoolbar_opened_count_sum)) AS mismatched_histogram_parent_devtools_developertoolbar_opened_count_sum,
  COUNTIF(TO_JSON_STRING(via_ms.histogram_parent_devtools_dom_opened_count_sum) != TO_JSON_STRING(direct.histogram_parent_devtools_dom_opened_count_sum)) AS mismatched_histogram_parent_devtools_dom_opened_count_sum,
  COUNTIF(TO_JSON_STRING(via_ms.histogram_parent_devtools_eyedropper_opened_count_sum) != TO_JSON_STRING(direct.histogram_parent_devtools_eyedropper_opened_count_sum)) AS mismatched_histogram_parent_devtools_eyedropper_opened_count_sum,
  COUNTIF(TO_JSON_STRING(via_ms.histogram_parent_devtools_fontinspector_opened_count_sum) != TO_JSON_STRING(direct.histogram_parent_devtools_fontinspector_opened_count_sum)) AS mismatched_histogram_parent_devtools_fontinspector_opened_count_sum,
  COUNTIF(TO_JSON_STRING(via_ms.histogram_parent_devtools_inspector_opened_count_sum) != TO_JSON_STRING(direct.histogram_parent_devtools_inspector_opened_count_sum)) AS mismatched_histogram_parent_devtools_inspector_opened_count_sum,
  COUNTIF(TO_JSON_STRING(via_ms.histogram_parent_devtools_jsbrowserdebugger_opened_count_sum) != TO_JSON_STRING(direct.histogram_parent_devtools_jsbrowserdebugger_opened_count_sum)) AS mismatched_histogram_parent_devtools_jsbrowserdebugger_opened_count_sum,
  COUNTIF(TO_JSON_STRING(via_ms.histogram_parent_devtools_jsdebugger_opened_count_sum) != TO_JSON_STRING(direct.histogram_parent_devtools_jsdebugger_opened_count_sum)) AS mismatched_histogram_parent_devtools_jsdebugger_opened_count_sum,
  COUNTIF(TO_JSON_STRING(via_ms.histogram_parent_devtools_jsprofiler_opened_count_sum) != TO_JSON_STRING(direct.histogram_parent_devtools_jsprofiler_opened_count_sum)) AS mismatched_histogram_parent_devtools_jsprofiler_opened_count_sum,
  COUNTIF(TO_JSON_STRING(via_ms.histogram_parent_devtools_layoutview_opened_count_sum) != TO_JSON_STRING(direct.histogram_parent_devtools_layoutview_opened_count_sum)) AS mismatched_histogram_parent_devtools_layoutview_opened_count_sum,
  COUNTIF(TO_JSON_STRING(via_ms.histogram_parent_devtools_memory_opened_count_sum) != TO_JSON_STRING(direct.histogram_parent_devtools_memory_opened_count_sum)) AS mismatched_histogram_parent_devtools_memory_opened_count_sum,
  COUNTIF(TO_JSON_STRING(via_ms.histogram_parent_devtools_menu_eyedropper_opened_count_sum) != TO_JSON_STRING(direct.histogram_parent_devtools_menu_eyedropper_opened_count_sum)) AS mismatched_histogram_parent_devtools_menu_eyedropper_opened_count_sum,
  COUNTIF(TO_JSON_STRING(via_ms.histogram_parent_devtools_netmonitor_opened_count_sum) != TO_JSON_STRING(direct.histogram_parent_devtools_netmonitor_opened_count_sum)) AS mismatched_histogram_parent_devtools_netmonitor_opened_count_sum,
  COUNTIF(TO_JSON_STRING(via_ms.histogram_parent_devtools_options_opened_count_sum) != TO_JSON_STRING(direct.histogram_parent_devtools_options_opened_count_sum)) AS mismatched_histogram_parent_devtools_options_opened_count_sum,
  COUNTIF(TO_JSON_STRING(via_ms.histogram_parent_devtools_paintflashing_opened_count_sum) != TO_JSON_STRING(direct.histogram_parent_devtools_paintflashing_opened_count_sum)) AS mismatched_histogram_parent_devtools_paintflashing_opened_count_sum,
  COUNTIF(TO_JSON_STRING(via_ms.histogram_parent_devtools_picker_eyedropper_opened_count_sum) != TO_JSON_STRING(direct.histogram_parent_devtools_picker_eyedropper_opened_count_sum)) AS mismatched_histogram_parent_devtools_picker_eyedropper_opened_count_sum,
  COUNTIF(TO_JSON_STRING(via_ms.histogram_parent_devtools_responsive_opened_count_sum) != TO_JSON_STRING(direct.histogram_parent_devtools_responsive_opened_count_sum)) AS mismatched_histogram_parent_devtools_responsive_opened_count_sum,
  COUNTIF(TO_JSON_STRING(via_ms.histogram_parent_devtools_ruleview_opened_count_sum) != TO_JSON_STRING(direct.histogram_parent_devtools_ruleview_opened_count_sum)) AS mismatched_histogram_parent_devtools_ruleview_opened_count_sum,
  COUNTIF(TO_JSON_STRING(via_ms.histogram_parent_devtools_scratchpad_opened_count_sum) != TO_JSON_STRING(direct.histogram_parent_devtools_scratchpad_opened_count_sum)) AS mismatched_histogram_parent_devtools_scratchpad_opened_count_sum,
  COUNTIF(TO_JSON_STRING(via_ms.histogram_parent_devtools_scratchpad_window_opened_count_sum) != TO_JSON_STRING(direct.histogram_parent_devtools_scratchpad_window_opened_count_sum)) AS mismatched_histogram_parent_devtools_scratchpad_window_opened_count_sum,
  COUNTIF(TO_JSON_STRING(via_ms.histogram_parent_devtools_shadereditor_opened_count_sum) != TO_JSON_STRING(direct.histogram_parent_devtools_shadereditor_opened_count_sum)) AS mismatched_histogram_parent_devtools_shadereditor_opened_count_sum,
  COUNTIF(TO_JSON_STRING(via_ms.histogram_parent_devtools_storage_opened_count_sum) != TO_JSON_STRING(direct.histogram_parent_devtools_storage_opened_count_sum)) AS mismatched_histogram_parent_devtools_storage_opened_count_sum,
  COUNTIF(TO_JSON_STRING(via_ms.histogram_parent_devtools_styleeditor_opened_count_sum) != TO_JSON_STRING(direct.histogram_parent_devtools_styleeditor_opened_count_sum)) AS mismatched_histogram_parent_devtools_styleeditor_opened_count_sum,
  COUNTIF(TO_JSON_STRING(via_ms.histogram_parent_devtools_webaudioeditor_opened_count_sum) != TO_JSON_STRING(direct.histogram_parent_devtools_webaudioeditor_opened_count_sum)) AS mismatched_histogram_parent_devtools_webaudioeditor_opened_count_sum,
  COUNTIF(TO_JSON_STRING(via_ms.histogram_parent_devtools_webconsole_opened_count_sum) != TO_JSON_STRING(direct.histogram_parent_devtools_webconsole_opened_count_sum)) AS mismatched_histogram_parent_devtools_webconsole_opened_count_sum,
  COUNTIF(TO_JSON_STRING(via_ms.histogram_parent_devtools_webide_opened_count_sum) != TO_JSON_STRING(direct.histogram_parent_devtools_webide_opened_count_sum)) AS mismatched_histogram_parent_devtools_webide_opened_count_sum,
  COUNTIF(TO_JSON_STRING(via_ms.install_year) != TO_JSON_STRING(direct.install_year)) AS mismatched_install_year,
  COUNTIF(TO_JSON_STRING(via_ms.is_default_browser) != TO_JSON_STRING(direct.is_default_browser)) AS mismatched_is_default_browser,
  COUNTIF(TO_JSON_STRING(via_ms.is_wow64) != TO_JSON_STRING(direct.is_wow64)) AS mismatched_is_wow64,
  COUNTIF(TO_JSON_STRING(via_ms.locale) != TO_JSON_STRING(direct.locale)) AS mismatched_locale,
  COUNTIF(TO_JSON_STRING(via_ms.memory_mb) != TO_JSON_STRING(direct.memory_mb)) AS mismatched_memory_mb,
  COUNTIF(TO_JSON_STRING(via_ms.normalized_channel) != TO_JSON_STRING(direct.normalized_channel)) AS mismatched_normalized_channel,
  COUNTIF(TO_JSON_STRING(via_ms.normalized_os_version) != TO_JSON_STRING(direct.normalized_os_version)) AS mismatched_normalized_os_version,
  COUNTIF(TO_JSON_STRING(via_ms.os) != TO_JSON_STRING(direct.os)) AS mismatched_os,
  COUNTIF(TO_JSON_STRING(via_ms.os_service_pack_major) != TO_JSON_STRING(direct.os_service_pack_major)) AS mismatched_os_service_pack_major,
  COUNTIF(TO_JSON_STRING(via_ms.os_service_pack_minor) != TO_JSON_STRING(direct.os_service_pack_minor)) AS mismatched_os_service_pack_minor,
  COUNTIF(TO_JSON_STRING(via_ms.os_version) != TO_JSON_STRING(direct.os_version)) AS mismatched_os_version,
  COUNTIF(TO_JSON_STRING(via_ms.pings_aggregated_by_this_row) != TO_JSON_STRING(direct.pings_aggregated_by_this_row)) AS mismatched_pings_aggregated_by_this_row,
  COUNTIF(udf_float_not_equal(via_ms.places_bookmarks_count_mean, direct.places_bookmarks_count_mean)) AS mismatched_places_bookmarks_count_mean,
  COUNTIF(udf_float_not_equal(via_ms.places_pages_count_mean, direct.places_pages_count_mean)) AS mismatched_places_pages_count_mean,
  COUNTIF(TO_JSON_STRING(via_ms.plugin_hangs_sum) != TO_JSON_STRING(direct.plugin_hangs_sum)) AS mismatched_plugin_hangs_sum,
  COUNTIF(TO_JSON_STRING(via_ms.plugins_infobar_allow_sum) != TO_JSON_STRING(direct.plugins_infobar_allow_sum)) AS mismatched_plugins_infobar_allow_sum,
  COUNTIF(TO_JSON_STRING(via_ms.plugins_infobar_block_sum) != TO_JSON_STRING(direct.plugins_infobar_block_sum)) AS mismatched_plugins_infobar_block_sum,
  COUNTIF(TO_JSON_STRING(via_ms.plugins_infobar_shown_sum) != TO_JSON_STRING(direct.plugins_infobar_shown_sum)) AS mismatched_plugins_infobar_shown_sum,
  COUNTIF(TO_JSON_STRING(via_ms.plugins_notification_shown_sum) != TO_JSON_STRING(direct.plugins_notification_shown_sum)) AS mismatched_plugins_notification_shown_sum,
  COUNTIF(TO_JSON_STRING(via_ms.previous_build_id) != TO_JSON_STRING(direct.previous_build_id)) AS mismatched_previous_build_id,
  -- profile_age_in_days and profile_creation_date both use ANY_VALUE so they can't be compared directly
  -- COUNTIF(TO_JSON_STRING(via_ms.profile_age_in_days) != TO_JSON_STRING(direct.profile_age_in_days)) AS mismatched_profile_age_in_days,
  -- COUNTIF(TO_JSON_STRING(via_ms.profile_creation_date) != TO_JSON_STRING(direct.profile_creation_date)) AS mismatched_profile_creation_date,
  COUNTIF(TO_JSON_STRING(via_ms.push_api_notify_sum) != TO_JSON_STRING(direct.push_api_notify_sum)) AS mismatched_push_api_notify_sum,
  COUNTIF(TO_JSON_STRING(via_ms.sample_id) != TO_JSON_STRING(direct.sample_id)) AS mismatched_sample_id,
  COUNTIF(TO_JSON_STRING(via_ms.sandbox_effective_content_process_level) != TO_JSON_STRING(direct.sandbox_effective_content_process_level)) AS mismatched_sandbox_effective_content_process_level,
  COUNTIF(TO_JSON_STRING(via_ms.scalar_combined_webrtc_nicer_stun_retransmits_sum) != TO_JSON_STRING(direct.scalar_combined_webrtc_nicer_stun_retransmits_sum)) AS mismatched_scalar_combined_webrtc_nicer_stun_retransmits_sum,
  COUNTIF(TO_JSON_STRING(via_ms.scalar_combined_webrtc_nicer_turn_401s_sum) != TO_JSON_STRING(direct.scalar_combined_webrtc_nicer_turn_401s_sum)) AS mismatched_scalar_combined_webrtc_nicer_turn_401s_sum,
  COUNTIF(TO_JSON_STRING(via_ms.scalar_combined_webrtc_nicer_turn_403s_sum) != TO_JSON_STRING(direct.scalar_combined_webrtc_nicer_turn_403s_sum)) AS mismatched_scalar_combined_webrtc_nicer_turn_403s_sum,
  COUNTIF(TO_JSON_STRING(via_ms.scalar_combined_webrtc_nicer_turn_438s_sum) != TO_JSON_STRING(direct.scalar_combined_webrtc_nicer_turn_438s_sum)) AS mismatched_scalar_combined_webrtc_nicer_turn_438s_sum,
  COUNTIF(TO_JSON_STRING(via_ms.scalar_content_navigator_storage_estimate_count_sum) != TO_JSON_STRING(direct.scalar_content_navigator_storage_estimate_count_sum)) AS mismatched_scalar_content_navigator_storage_estimate_count_sum,
  COUNTIF(TO_JSON_STRING(via_ms.scalar_content_navigator_storage_persist_count_sum) != TO_JSON_STRING(direct.scalar_content_navigator_storage_persist_count_sum)) AS mismatched_scalar_content_navigator_storage_persist_count_sum,
  COUNTIF(TO_JSON_STRING(via_ms.scalar_parent_aushelper_websense_reg_version) != TO_JSON_STRING(direct.scalar_parent_aushelper_websense_reg_version)) AS mismatched_scalar_parent_aushelper_websense_reg_version,
  COUNTIF(TO_JSON_STRING(via_ms.scalar_parent_browser_engagement_max_concurrent_tab_count_max) != TO_JSON_STRING(direct.scalar_parent_browser_engagement_max_concurrent_tab_count_max)) AS mismatched_scalar_parent_browser_engagement_max_concurrent_tab_count_max,
  COUNTIF(TO_JSON_STRING(via_ms.scalar_parent_browser_engagement_max_concurrent_window_count_max) != TO_JSON_STRING(direct.scalar_parent_browser_engagement_max_concurrent_window_count_max)) AS mismatched_scalar_parent_browser_engagement_max_concurrent_window_count_max,
  COUNTIF(TO_JSON_STRING(via_ms.scalar_parent_browser_engagement_tab_open_event_count_sum) != TO_JSON_STRING(direct.scalar_parent_browser_engagement_tab_open_event_count_sum)) AS mismatched_scalar_parent_browser_engagement_tab_open_event_count_sum,
  COUNTIF(TO_JSON_STRING(via_ms.scalar_parent_browser_engagement_total_uri_count_sum) != TO_JSON_STRING(direct.scalar_parent_browser_engagement_total_uri_count_sum)) AS mismatched_scalar_parent_browser_engagement_total_uri_count_sum,
  COUNTIF(TO_JSON_STRING(via_ms.scalar_parent_browser_engagement_unfiltered_uri_count_sum) != TO_JSON_STRING(direct.scalar_parent_browser_engagement_unfiltered_uri_count_sum)) AS mismatched_scalar_parent_browser_engagement_unfiltered_uri_count_sum,
  COUNTIF(TO_JSON_STRING(via_ms.scalar_parent_browser_engagement_unique_domains_count_max) != TO_JSON_STRING(direct.scalar_parent_browser_engagement_unique_domains_count_max)) AS mismatched_scalar_parent_browser_engagement_unique_domains_count_max,
  COUNTIF(udf_float_not_equal(via_ms.scalar_parent_browser_engagement_unique_domains_count_mean, direct.scalar_parent_browser_engagement_unique_domains_count_mean)) AS mismatched_scalar_parent_browser_engagement_unique_domains_count_mean,
  COUNTIF(TO_JSON_STRING(via_ms.scalar_parent_browser_engagement_window_open_event_count_sum) != TO_JSON_STRING(direct.scalar_parent_browser_engagement_window_open_event_count_sum)) AS mismatched_scalar_parent_browser_engagement_window_open_event_count_sum,
  COUNTIF(TO_JSON_STRING(via_ms.scalar_parent_devtools_accessibility_node_inspected_count_sum) != TO_JSON_STRING(direct.scalar_parent_devtools_accessibility_node_inspected_count_sum)) AS mismatched_scalar_parent_devtools_accessibility_node_inspected_count_sum,
  COUNTIF(TO_JSON_STRING(via_ms.scalar_parent_devtools_accessibility_opened_count_sum) != TO_JSON_STRING(direct.scalar_parent_devtools_accessibility_opened_count_sum)) AS mismatched_scalar_parent_devtools_accessibility_opened_count_sum,
  COUNTIF(TO_JSON_STRING(via_ms.scalar_parent_devtools_accessibility_picker_used_count_sum) != TO_JSON_STRING(direct.scalar_parent_devtools_accessibility_picker_used_count_sum)) AS mismatched_scalar_parent_devtools_accessibility_picker_used_count_sum,
  COUNTIF(TO_JSON_STRING(via_ms.scalar_parent_devtools_accessibility_select_accessible_for_node_sum) != TO_JSON_STRING(direct.scalar_parent_devtools_accessibility_select_accessible_for_node_sum)) AS mismatched_scalar_parent_devtools_accessibility_select_accessible_for_node_sum,
  COUNTIF(TO_JSON_STRING(via_ms.scalar_parent_devtools_accessibility_service_enabled_count_sum) != TO_JSON_STRING(direct.scalar_parent_devtools_accessibility_service_enabled_count_sum)) AS mismatched_scalar_parent_devtools_accessibility_service_enabled_count_sum,
  COUNTIF(TO_JSON_STRING(via_ms.scalar_parent_devtools_copy_full_css_selector_opened_sum) != TO_JSON_STRING(direct.scalar_parent_devtools_copy_full_css_selector_opened_sum)) AS mismatched_scalar_parent_devtools_copy_full_css_selector_opened_sum,
  COUNTIF(TO_JSON_STRING(via_ms.scalar_parent_devtools_copy_unique_css_selector_opened_sum) != TO_JSON_STRING(direct.scalar_parent_devtools_copy_unique_css_selector_opened_sum)) AS mismatched_scalar_parent_devtools_copy_unique_css_selector_opened_sum,
  COUNTIF(TO_JSON_STRING(via_ms.scalar_parent_devtools_toolbar_eyedropper_opened_sum) != TO_JSON_STRING(direct.scalar_parent_devtools_toolbar_eyedropper_opened_sum)) AS mismatched_scalar_parent_devtools_toolbar_eyedropper_opened_sum,
  COUNTIF(TO_JSON_STRING(via_ms.scalar_parent_dom_contentprocess_troubled_due_to_memory_sum) != TO_JSON_STRING(direct.scalar_parent_dom_contentprocess_troubled_due_to_memory_sum)) AS mismatched_scalar_parent_dom_contentprocess_troubled_due_to_memory_sum,
  COUNTIF(TO_JSON_STRING(via_ms.scalar_parent_navigator_storage_estimate_count_sum) != TO_JSON_STRING(direct.scalar_parent_navigator_storage_estimate_count_sum)) AS mismatched_scalar_parent_navigator_storage_estimate_count_sum,
  COUNTIF(TO_JSON_STRING(via_ms.scalar_parent_navigator_storage_persist_count_sum) != TO_JSON_STRING(direct.scalar_parent_navigator_storage_persist_count_sum)) AS mismatched_scalar_parent_navigator_storage_persist_count_sum,
  COUNTIF(TO_JSON_STRING(via_ms.scalar_parent_storage_sync_api_usage_extensions_using_sum) != TO_JSON_STRING(direct.scalar_parent_storage_sync_api_usage_extensions_using_sum)) AS mismatched_scalar_parent_storage_sync_api_usage_extensions_using_sum,
  COUNTIF(TO_JSON_STRING(via_ms.search_cohort) != TO_JSON_STRING(direct.search_cohort)) AS mismatched_search_cohort,
  COUNTIF(TO_JSON_STRING(via_ms.search_count_abouthome) != TO_JSON_STRING(direct.search_count_abouthome)) AS mismatched_search_count_abouthome,
  COUNTIF(TO_JSON_STRING(via_ms.search_count_contextmenu) != TO_JSON_STRING(direct.search_count_contextmenu)) AS mismatched_search_count_contextmenu,
  COUNTIF(TO_JSON_STRING(via_ms.search_count_newtab) != TO_JSON_STRING(direct.search_count_newtab)) AS mismatched_search_count_newtab,
  COUNTIF(TO_JSON_STRING(via_ms.search_count_searchbar) != TO_JSON_STRING(direct.search_count_searchbar)) AS mismatched_search_count_searchbar,
  COUNTIF(TO_JSON_STRING(via_ms.search_count_system) != TO_JSON_STRING(direct.search_count_system)) AS mismatched_search_count_system,
  COUNTIF(TO_JSON_STRING(via_ms.search_count_urlbar) != TO_JSON_STRING(direct.search_count_urlbar)) AS mismatched_search_count_urlbar,
  COUNTIF(TO_JSON_STRING(via_ms.search_count_all) != TO_JSON_STRING(direct.search_count_all)) AS mismatched_search_count_all,
  COUNTIF(TO_JSON_STRING(via_ms.search_count_tagged_sap) != TO_JSON_STRING(direct.search_count_tagged_sap)) AS mismatched_search_count_tagged_sap,
  COUNTIF(TO_JSON_STRING(via_ms.search_count_tagged_follow_on) != TO_JSON_STRING(direct.search_count_tagged_follow_on)) AS mismatched_search_count_tagged_follow_on,
  COUNTIF(TO_JSON_STRING(via_ms.search_count_organic) != TO_JSON_STRING(direct.search_count_organic)) AS mismatched_search_count_organic,
  COUNTIF(udf_float_not_equal(via_ms.session_restored_mean, direct.session_restored_mean)) AS mismatched_session_restored_mean,
  COUNTIF(TO_JSON_STRING(via_ms.sessions_started_on_this_day) != TO_JSON_STRING(direct.sessions_started_on_this_day)) AS mismatched_sessions_started_on_this_day,
  COUNTIF(TO_JSON_STRING(via_ms.shutdown_kill_sum) != TO_JSON_STRING(direct.shutdown_kill_sum)) AS mismatched_shutdown_kill_sum,
  COUNTIF(TO_JSON_STRING(via_ms.subsession_hours_sum) != TO_JSON_STRING(direct.subsession_hours_sum)) AS mismatched_subsession_hours_sum,
  COUNTIF(TO_JSON_STRING(via_ms.ssl_handshake_result_failure_sum) != TO_JSON_STRING(direct.ssl_handshake_result_failure_sum)) AS mismatched_ssl_handshake_result_failure_sum,
  COUNTIF(TO_JSON_STRING(via_ms.ssl_handshake_result_success_sum) != TO_JSON_STRING(direct.ssl_handshake_result_success_sum)) AS mismatched_ssl_handshake_result_success_sum,
  COUNTIF(TO_JSON_STRING(via_ms.sync_configured) != TO_JSON_STRING(direct.sync_configured)) AS mismatched_sync_configured,
  COUNTIF(udf_float_not_equal(via_ms.sync_count_desktop_mean, direct.sync_count_desktop_mean)) AS mismatched_sync_count_desktop_mean,
  COUNTIF(udf_float_not_equal(via_ms.sync_count_mobile_mean, direct.sync_count_mobile_mean)) AS mismatched_sync_count_mobile_mean,
  COUNTIF(TO_JSON_STRING(via_ms.sync_count_desktop_sum) != TO_JSON_STRING(direct.sync_count_desktop_sum)) AS mismatched_sync_count_desktop_sum,
  COUNTIF(TO_JSON_STRING(via_ms.sync_count_mobile_sum) != TO_JSON_STRING(direct.sync_count_mobile_sum)) AS mismatched_sync_count_mobile_sum,
  COUNTIF(TO_JSON_STRING(via_ms.telemetry_enabled) != TO_JSON_STRING(direct.telemetry_enabled)) AS mismatched_telemetry_enabled,
  COUNTIF(TO_JSON_STRING(via_ms.timezone_offset) != TO_JSON_STRING(direct.timezone_offset)) AS mismatched_timezone_offset,
  COUNTIF(TO_JSON_STRING(via_ms.total_hours_sum) != TO_JSON_STRING(direct.total_hours_sum)) AS mismatched_total_hours_sum,
  COUNTIF(TO_JSON_STRING(via_ms.update_auto_download) != TO_JSON_STRING(direct.update_auto_download)) AS mismatched_update_auto_download,
  COUNTIF(TO_JSON_STRING(via_ms.update_channel) != TO_JSON_STRING(direct.update_channel)) AS mismatched_update_channel,
  COUNTIF(TO_JSON_STRING(via_ms.update_enabled) != TO_JSON_STRING(direct.update_enabled)) AS mismatched_update_enabled,
  COUNTIF(TO_JSON_STRING(via_ms.vendor) != TO_JSON_STRING(direct.vendor)) AS mismatched_vendor,
  COUNTIF(TO_JSON_STRING(via_ms.web_notification_shown_sum) != TO_JSON_STRING(direct.web_notification_shown_sum)) AS mismatched_web_notification_shown_sum,
  COUNTIF(TO_JSON_STRING(via_ms.windows_build_number) != TO_JSON_STRING(direct.windows_build_number)) AS mismatched_windows_build_number,
  COUNTIF(TO_JSON_STRING(via_ms.windows_ubr) != TO_JSON_STRING(direct.windows_ubr)) AS mismatched_windows_ubr,
  COUNTIF(TO_JSON_STRING(via_ms.fxa_configured) != TO_JSON_STRING(direct.fxa_configured)) AS mismatched_fxa_configured,
  COUNTIF(TO_JSON_STRING(via_ms.trackers_blocked_sum) != TO_JSON_STRING(direct.trackers_blocked_sum)) AS mismatched_trackers_blocked_sum,
  COUNTIF(TO_JSON_STRING(via_ms.submission_timestamp_min) != TO_JSON_STRING(direct.submission_timestamp_min)) AS mismatched_submission_timestamp_min,
  COUNTIF(TO_JSON_STRING(via_ms.ad_clicks_count_all) != TO_JSON_STRING(direct.ad_clicks_count_all)) AS mismatched_ad_clicks_count_all,
  COUNTIF(TO_JSON_STRING(via_ms.search_with_ads_count_all) != TO_JSON_STRING(direct.search_with_ads_count_all)) AS mismatched_search_with_ads_count_all,
  COUNTIF(udf_map_not_equal(via_ms.scalar_parent_telemetry_event_counts_sum, direct.scalar_parent_telemetry_event_counts_sum)) AS mismatched_scalar_parent_telemetry_event_counts_sum,
  COUNTIF(udf_map_not_equal(via_ms.scalar_content_telemetry_event_counts_sum, direct.scalar_content_telemetry_event_counts_sum)) AS mismatched_scalar_content_telemetry_event_counts_sum,
  COUNTIF(udf_map_not_equal(via_ms.scalar_parent_urlbar_searchmode_handoff_sum, direct.scalar_parent_urlbar_searchmode_handoff_sum)) AS mismatched_scalar_parent_urlbar_searchmode_handoff_sum,
  COUNTIF(udf_map_not_equal(via_ms.scalar_parent_urlbar_searchmode_keywordoffer_sum, direct.scalar_parent_urlbar_searchmode_keywordoffer_sum)) AS mismatched_scalar_parent_urlbar_searchmode_keywordoffer_sum,
  COUNTIF(udf_map_not_equal(via_ms.scalar_parent_urlbar_searchmode_oneoff_sum, direct.scalar_parent_urlbar_searchmode_oneoff_sum)) AS mismatched_scalar_parent_urlbar_searchmode_oneoff_sum,
  COUNTIF(udf_map_not_equal(via_ms.scalar_parent_urlbar_searchmode_shortcut_sum, direct.scalar_parent_urlbar_searchmode_shortcut_sum)) AS mismatched_scalar_parent_urlbar_searchmode_shortcut_sum,
  COUNTIF(udf_map_not_equal(via_ms.scalar_parent_urlbar_searchmode_tabtosearch_sum, direct.scalar_parent_urlbar_searchmode_tabtosearch_sum)) AS mismatched_scalar_parent_urlbar_searchmode_tabtosearch_sum,
  COUNTIF(udf_map_not_equal(via_ms.scalar_parent_urlbar_searchmode_tabtosearch_onboard_sum, direct.scalar_parent_urlbar_searchmode_tabtosearch_onboard_sum)) AS mismatched_scalar_parent_urlbar_searchmode_tabtosearch_onboard_sum,
  COUNTIF(udf_map_not_equal(via_ms.scalar_parent_urlbar_searchmode_topsites_newtab_sum, direct.scalar_parent_urlbar_searchmode_topsites_newtab_sum)) AS mismatched_scalar_parent_urlbar_searchmode_topsites_newtab_sum,
  COUNTIF(udf_map_not_equal(via_ms.scalar_parent_urlbar_searchmode_topsites_urlbar_sum, direct.scalar_parent_urlbar_searchmode_topsites_urlbar_sum)) AS mismatched_scalar_parent_urlbar_searchmode_topsites_urlbar_sum,
  COUNTIF(udf_map_not_equal(via_ms.scalar_parent_urlbar_searchmode_touchbar_sum, direct.scalar_parent_urlbar_searchmode_touchbar_sum)) AS mismatched_scalar_parent_urlbar_searchmode_touchbar_sum,
  COUNTIF(udf_map_not_equal(via_ms.scalar_parent_urlbar_searchmode_typed_sum, direct.scalar_parent_urlbar_searchmode_typed_sum)) AS mismatched_scalar_parent_urlbar_searchmode_typed_sum,
FROM
  `moz-fx-data-shared-prod`.telemetry_derived.clients_daily_v6 AS via_ms
FULL JOIN
  clients_daily_v6 AS direct
USING
  (client_id)
WHERE
  via_ms.submission_date = DATE "2021-01-05"
  AND direct.submission_date = DATE "2021-01-05"

validate_any_value_aggregates.sql

WITH array_aggregates AS (
  SELECT
    client_id,
    ARRAY_AGG(DISTINCT UNIX_DATE(DATE(SAFE.TIMESTAMP(payload.info.subsession_start_date))) RESPECT NULLS) AS subsession_start_dates,
    ARRAY_AGG(DISTINCT SAFE_CAST(environment.profile.creation_date AS INT64) RESPECT NULLS) AS profile_creation_dates,
    ANY_VALUE(sample_id) AS sample_id,
  FROM
    `moz-fx-data-shared-prod`.telemetry_stable.main_v4
  WHERE
    DATE(submission_timestamp) = @submission_date
    AND normalized_app_name = 'Firefox'
    AND document_id IS NOT NULL
  GROUP BY
    client_id
),
possible_values AS (
  SELECT
    client_id,
    ARRAY(
      SELECT DISTINCT
        TO_JSON_STRING(subsession_start_date - profile_creation_date)
      FROM
        UNNEST(subsession_start_dates) AS subsession_start_date
      CROSS JOIN
        UNNEST(profile_creation_dates) AS profile_creation_date
    ) AS profile_age_in_days,
    ARRAY(
      SELECT
        TO_JSON_STRING(
          FORMAT_DATE(
            "%F 00:00:00",
            SAFE.DATE_FROM_UNIX_DATE(profile_creation_date)
          )
        )
      FROM
        UNNEST(profile_creation_dates) AS profile_creation_date
    ) AS profile_creation_date,
  FROM
    array_aggregates
)
SELECT
  COUNT(*) AS total_rows,
  COUNTIF(
    TO_JSON_STRING(via_ms.profile_creation_date) NOT IN UNNEST(possible_values.profile_creation_date)
    OR TO_JSON_STRING(direct.profile_creation_date) NOT IN UNNEST(possible_values.profile_creation_date)
  ) AS mismatched_profile_creation_date,
  COUNTIF(
    TO_JSON_STRING(via_ms.profile_age_in_days) NOT IN UNNEST(possible_values.profile_age_in_days)
    OR TO_JSON_STRING(direct.profile_age_in_days) NOT IN UNNEST(possible_values.profile_age_in_days)
  ) AS mismatched_profile_age_in_days,
FROM
  `moz-fx-data-shared-prod`.telemetry_derived.clients_daily_v6 AS via_ms
FULL JOIN
  clients_daily_v6 AS direct
USING
  (client_id)
LEFT JOIN
  possible_values
USING
  (client_id)
WHERE
  via_ms.submission_date = @submission_date
  AND direct.submission_date = @submission_date