BI Engine limitations

BigQuery BI Engine is designed to optimize BI scenarios. As a result, some queries don't benefit from BigQuery BI Engine and transparently fall back to standard BigQuery execution.

Joins

BI Engine accelerates certain types of join queries. Acceleration happens on leaf-level subqueries with INNER and LEFT OUTER JOINS, where a large fact table is joined with up to four smaller, "dimension" tables. Small dimension tables have the following restrictions:

  • Less than 5 million rows
  • Size limit:
    • Unpartitioned tables: 5 GB or less
    • Partitioned tables: Referenced partitions 1 GB or less

Window functions

Window functions, also known as analytical functions, have the following limitations when accelerated by BigQuery BI Engine:

  • The input stages are accelerated by BigQuery BI Engine if they don't have window functions. In this case INFORMATION_SCHEMA.JOBS view reports bi_engine_statistics.acceleration_mode as FULL_INPUT.
  • The input stages of queries with window functions in their input stages are accelerated by BI Engine, but don't have the limitations described in the BI Engine Window functions limitations section. In that case, the input stages or the full query is executed in BI Engine. In this case INFORMATION_SCHEMA.JOBS view reports bi_engine_statistics.acceleration_mode as FULL_INPUT or FULL_QUERY.

For more information about the BiEngineStatistics field, see the Job reference.

BI Engine Window functions limitations

Queries with window functions only run in BI Engine if all of the following conditions are true:

  • The query scans exactly one table.
    • The table is not partitioned.
    • The table has less than 5 million rows.
  • The query has no JOIN operators.
  • The scanned table size times the number of window function operators does not exceed 300 MB.

Two window functions with identical OVER clauses and the same direct inputs can share the same window function operator. For example:

  • SELECT ROW_NUMBER() OVER (ORDER BY x), SUM(x) OVER (ORDER BY x) FROM my_table has only one window function operator.
  • SELECT ROW_NUMBER() OVER (ORDER BY x), SUM(x) OVER (PARTITION BY y ORDER BY x) FROM my_table has two window function operators because the two functions have different OVER clauses.
  • SELECT ROW_NUMBER() OVER (ORDER BY x) FROM (SELECT SUM(x) OVER (ORDER BY x) AS x FROM my_table) has two window function operators because the two functions have different direct inputs although their OVER clauses appear the same.

Supported window functions

The following referenced window functions are supported:

  • ANY_VALUE
  • AVG
  • BIT_AND
  • BIT_OR
  • BIT_XOR
  • CORR
  • COUNT
  • COUNTIF
  • COVAR_POP
  • COVAR_SAMP
  • CUME_DIST
  • DENSE_RANK
  • FIRST_VALUE
  • LAG
  • LAST_VALUE
  • LEAD
  • LOGICAL_AND
  • LOGICAL_OR
  • MAX
  • MIN
  • NTH_VALUE
  • NTILE
  • PERCENT_RANK
  • PERCENTILE_CONT
  • PERCENTILE_DISC
  • RANK
  • ROW_NUMBER
  • ST_CLUSTERDBSCAN
  • STDDEV_POP
  • STDDEV_SAMP
  • STDDEV
  • STRING_AGG
  • SUM
  • VAR_POP
  • VAR_SAMP
  • VARIANCE

If window functions aren't supported, then you might see the following error:

Analytic function is incompatible with other operators or its inputs are too large

Other BI Engine unsupported features

BI Engine acceleration is not available for the following features:

  • JavaScript UDFs
  • External tables
  • Querying native JSON data - Error message: JSON native type is not supported
  • Writing results to a permanent BigQuery table
  • Tables containing upserts that use BigQuery change data capture
  • Transactions
  • Queries that return more than 1 GB of data. For latency-sensitive applications, a response size of less than 1 MB is recommended.
  • Row-level security

Work-around for unsupported features

While some SQL features are not supported in BigQuery BI Engine, there is an available workaround:

  1. Write a query in BigQuery.
  2. Save the results of the query to a table.
  3. Schedule your query to update the table on a regular basis. An hourly or daily refresh rate works best. Refreshing every minute might invalidate the cache too frequently.
  4. Reference this table in your performance-critical queries.