Recommended practices

Queries

• Should be defined in files named as sql/<project>/<dataset>/<table>_<version>/query.sql e.g.
  • <project> defines both where the destination table resides and in which project the query job runs sql/moz-fx-data-shared-prod/telemetry_derived/clients_daily_v7/query.sql
  • Queries that populate tables should always be named with a version suffix; we assume that future optimizations to the data representation may require schema-incompatible changes such as dropping columns
• May be generated using a python script that prints the query to stdout
  • Should save output as sql/<project>/<dataset>/<table>_<version>/query.sql as above
  • Should be named as sql/<project>/query_type.sql.py e.g. sql/moz-fx-data-shared-prod/clients_daily.sql.py
  • May use options to generate queries for different destination tables e.g. using --source telemetry_core_parquet_v3 to generate sql/moz-fx-data-shared-prod/telemetry/core_clients_daily_v1/query.sql and using --source main_summary_v4 to generate sql/moz-fx-data-shared-prod/telemetry/clients_daily_v7/query.sql
  • Should output a header indicating options used e.g.

    -- Query generated by: sql/moz-fx-data-shared-prod/clients_daily.sql.py --source telemetry_core_parquet
    

• For tables in moz-fx-data-shared-prod the project prefix should be omitted to simplify testing. (Other projects do need the project prefix)
• Should be incremental
• Should filter input tables on partition and clustering columns
• Should use _ prefix in generated column names not meant for output
• Should use _bits suffix for any integer column that represents a bit pattern
• Should not use DATETIME type, due to incompatibility with spark-bigquery-connector
• Should read from *_stable tables instead of including custom deduplication
  • Should use the earliest row for each document_id by submission_timestamp where filtering duplicates is necessary
• Should not refer to views in the mozdata project which are duplicates of views in another project (commonly moz-fx-data-shared-prod). Refer to the original view instead.
• Should escape identifiers that match keywords, even if they aren't reserved keywords
• Queries are interpreted as Jinja templates, so it is possible to use Jinja statements and expressions

Querying Metrics

• Queries, views and UDFs can reference metrics and data sources that have been defined in metric-hub
  • To reference metrics use {{ metrics.calculate() }}:

    SELECT
      *
    FROM
      {{ metrics.calculate(
        metrics=['days_of_use', 'active_hours'],
        platform='firefox_desktop',
        group_by={'sample_id': 'sample_id', 'channel': 'application.channel'},
        where='submission_date = "2023-01-01"'
      ) }}
    
    -- this translates to
    SELECT
      *
    FROM
      (
        WITH clients_daily AS (
          SELECT
            client_id AS client_id,
            submission_date AS submission_date,
            COALESCE(SUM(active_hours_sum), 0) AS active_hours,
            COUNT(submission_date) AS days_of_use,
          FROM
            mozdata.telemetry.clients_daily
          GROUP BY
            client_id,
            submission_date
        )
        SELECT
          clients_daily.client_id,
          clients_daily.submission_date,
          active_hours,
          days_of_use,
        FROM
          clients_daily
      )
    

    • metrics: unique reference(s) to metric definition, all metric definitions are aggregations (e.g. SUM, AVG, ...)
    • platform: platform to compute metrics for (e.g. firefox_desktop, firefox_ios, fenix, ...)
    • group_by: fields used in the GROUP BY statement; this is a dictionary where the key represents the alias, the value is the field path; GROUP BY always includes the configured client_id and submission_date fields
    • where: SQL filter clause
    • group_by_client_id: Whether the field configured as client_id (defined as part of the data source specification in metric-hub) should be part of the GROUP BY. True by default
    • group_by_submission_date: Whether the field configured as submission_date (defined as part of the data source specification in metric-hub) should be part of the GROUP BY. True by default
  • To reference data source definitions use {{ metrics.data_source() }}:

    SELECT
      *
    FROM
      {{ metrics.data_source(
        data_source='main',
        platform='firefox_desktop',
        where='submission_date = "2023-01-01"'
      ) }}
    
    -- this translates to
    SELECT
      *
    FROM
      (
        SELECT *
        FROM `mozdata.telemetry.main`
        WHERE submission_date = "2023-01-01"
      )
    

• To render queries that use Jinja expressions or statements use ./bqetl query render path/to/query.sql
• The generated-sql branch has rendered queries/views/UDFs
• ./bqetl query run does support running Jinja queries

Query Metadata

• For each query, a metadata.yaml file should be created in the same directory
• This file contains a description, owners and labels. As an example:

friendly_name: SSL Ratios
description: >
  Percentages of page loads Firefox users have performed that were
  conducted over SSL broken down by country.
owners:
  - example@mozilla.com
labels:
  application: firefox
  incremental: true # incremental queries add data to existing tables
  schedule: daily # scheduled in Airflow to run daily
  public_json: true
  public_bigquery: true
  review_bugs:
    - 1414839 # Bugzilla bug ID of data review
  incremental_export: false # non-incremental JSON export writes all data to a single location

• Queries that only select from legacy telemetry data should be assigned a legacy: true label.
• Queries that are output from code in sql_generators/ should be assigned a sql_generator label set to the name of the generator.
• only labels where value types are eithers integers or strings are published, all other values types are being skipped

Table partitioning/clustering

Table partitioning and/or clustering can be configured under a bigquery metadata section, like so:

bigquery:
  time_partitioning:
    type: day
    field: submission_date
    require_partition_filter: true
    expiration_days: 775
  clustering:
    fields:
      - sample_id

• If there's a primary timestamp or date column like submission_timestamp or submission_date it should generally be used for time partitioning.
• require_partition_filter should be enabled for large tables to avoid queries accidentally scanning the entire table.
• For large tables that contain client IDs it's highly recommended to include the associated sample_id column and cluster on it to allow for more performant sample-specific queries.
• The table partition expiration shouldn't be greater than the partition expiration of the query's upstream source tables unless the query does some form of de-identification (e.g. aggregation).

Dynamic Schemas

For tables whose schemas may evolve over time (e.g., tables that use --schema_update_option=ALLOW_FIELD_ADDITION), you can indicate this in the metadata to ensure schema updates are not skipped:

schema:
  allow_field_addition: true

This setting ensures that when running ./bqetl query schema update --skip-existing, the schema for this query will still be updated even if a schema.yaml file already exists. This is particularly useful for:

• Tables that receive new fields dynamically
• Tables using BigQuery's schema auto-detection
• Tables that use ALLOW_FIELD_ADDITION schema update option

Without this flag, --skip-existing will skip schema updates for queries that already have a schema.yaml file.

Views

• Should be defined in files named as sql/<project>/<dataset>/<table>/view.sql e.g. sql/moz-fx-data-shared-prod/telemetry/core/view.sql
  • Views should generally not be named with a version suffix; a view represents a stable interface for users and whenever possible should maintain compatibility with existing queries; if the view logic cannot be adapted to changes in underlying tables, breaking changes must be communicated to fx-data-dev@mozilla.org
• Must specify project and dataset in all table names
  • Should default to using the moz-fx-data-shared-prod project; the scripts/publish_views tooling can handle parsing the definitions to publish to other projects such as derived-datasets
• Should not refer to views in the mozdata project which are duplicates of views in another project (commonly moz-fx-data-shared-prod). Refer to the original view instead.
• Views are interpreted as Jinja templates, so it is possible to use Jinja statements and expressions

View Metadata

• For each view, a metadata.yaml file may be created in the same directory.
• This file contains a description, owners, and labels (see the query metadata example above).
• Views that only select from legacy telemetry data should be assigned a legacy: true label.
• Views that are output from code in sql_generators/ should be assigned a sql_generator label set to the name of the generator.

UDFs

• Should limit the number of expression subqueries to avoid: BigQuery error in query operation: Resources exceeded during query execution: Not enough resources for query planning - too many subqueries or query is too complex.
• Should be used to avoid code duplication
• Must be named in files with lower snake case names ending in .sql e.g. mode_last.sql
  • Each file must only define effectively private helper functions and one public function which must be defined last
    • Helper functions must not conflict with function names in other files
  • SQL UDFs must be defined in the udf/ directory and JS UDFs must be defined in the udf_js directory
    • The udf_legacy/ directory is an exception which must only contain compatibility functions for queries migrated from Athena/Presto.
• Functions must be defined as persistent UDFs using CREATE OR REPLACE FUNCTION syntax
  • Function names must be prefixed with a dataset of <dir_name>. so, for example, all functions in udf/*.sql are part of the udf dataset
    • The final syntax for creating a function in a file will look like CREATE OR REPLACE FUNCTION <dir_name>.<file_name>
  • We provide tooling in scripts/publish_persistent_udfs for publishing these UDFs to BigQuery
    • Changes made to UDFs need to be published manually in order for the dry run CI task to pass
• In order to run UDFs in private-bigquery-etl in the BigQuery Console, you will need to not only write the UDF but also a dummy/stub file in bigquery-etl.
  • Actual UDF in private-bigquery-etl:

        CREATE OR REPLACE FUNCTION udf.distribution_model_ga_metrics()
        RETURNS STRING AS (
         (SELECT 'non-distribution' AS distribution_model)
        );
    
        SELECT
          mozfun.assert.equals(udf.distribution_model_ga_metrics(), 'non-distribution');
    

  • Stub file in bigquery-etl:

        CREATE OR REPLACE FUNCTION udf.distribution_model_ga_metrics()
        RETURNS STRING AS (
          'helloworld'
        );
    
        SELECT
          mozfun.assert.equals(udf.distribution_model_ga_metrics(), 'helloworld');
    

• Should use SQL over js for performance
• UDFs are interpreted as Jinja templates, so it is possible to use Jinja statements and expressions

Large Backfills

• Should be documented and reviewed by a peer using a new bug that describes the context that required the backfill and the command or script used.
• Frequent backfills should be avoided
  • Backfills may increase storage costs for a table for 90 days by moving data from long-term storage to short-term storage and requiring a production snapshot.
  • Should combine multiple backfills happening around the same time
  • Should delay column deletes until the next other backfill
    • Should use NULL for new data and EXCEPT to exclude from views until dropped
• After running the backfill, it is important to validate that the job(s) ran without errors and the execution times and bytes processed are as expected. Here is a query you may use for this purpose:

  SELECT
    job_type,
    state,
    submission_date,
    destination_dataset_id,
    destination_table_id,
    total_terabytes_billed,
    total_slot_ms,
    error_location,
    error_reason,
    error_message
  FROM
    moz-fx-data-shared-prod.monitoring.bigquery_usage
  WHERE
    submission_date <= CURRENT_DATE()
    AND destination_dataset_id LIKE "%backfills_staging_derived%"
    AND destination_table_id LIKE "%{{ your table name }}%"
  ORDER BY
    submission_date DESC