Supported BigQuery functions in dbt v2

dbt v2 can validate that your BigQuery function arguments match expected types directly in dbt platform, the CLI, or using the dbt VS Code extension. To enable this, run dbt login and set static_analysis: strict in your project configuration.

The following table lists every BigQuery built-in function and its typechecking support status. Use the table's search and filters to find specific functions.

Refreshed weekly

This table is updated weekly from the BigQuery SQL function reference and cross-referenced with dbt v2's support list. If you spot a discrepancy, we'd love for you to contribute and open an issue in the dbt v1 repository!

Function	Category	v2 typechecking
ABS	Built-in	✓
ACOS	Built-in	✓
ACOSH	Built-in	✓
AEAD.DECRYPT_BYTES	Built-in	✓
AEAD.DECRYPT_STRING	Built-in	✓
AEAD.ENCRYPT	Built-in	✓
AGG	Built-in	—
ANY_VALUE	Built-in	✓
APPENDS	Built-in	—
APPROX_COUNT_DISTINCT	Built-in	✓
APPROX_QUANTILES	Built-in	✓
APPROX_TOP_COUNT	Built-in	✓
APPROX_TOP_SUM	Built-in	✓
ARRAY	Built-in	—
ARRAY_AGG	Built-in	✓
ARRAY_CONCAT	Built-in	✓
ARRAY_CONCAT_AGG	Built-in	✓
ARRAY_FIRST	Built-in	✓
ARRAY_LAST	Built-in	✓
ARRAY_LENGTH	Built-in	✓
ARRAY_REVERSE	Built-in	✓
ARRAY_SLICE	Built-in	✓
ARRAY_TO_STRING	Built-in	✓
ASCII	Built-in	✓
ASIN	Built-in	✓
ASINH	Built-in	✓
ATAN	Built-in	✓
ATAN2	Built-in	✓
ATANH	Built-in	✓
AVG	Built-in	✓
AVG (Differential Privacy)	Built-in	✓
BAG_OF_WORDS	Built-in	—
BIT_AND	Built-in	✓
BIT_COUNT	Built-in	✓
BIT_OR	Built-in	✓
BIT_XOR	Built-in	✓
BOOL	Built-in	✓
BYTE_LENGTH	Built-in	✓
CAST	Built-in	—
CBRT	Built-in	✓
CEIL	Built-in	✓
CEILING	Built-in	—
CHANGES	Built-in	—
CHAR_LENGTH	Built-in	✓
CHARACTER_LENGTH	Built-in	✓
CHR	Built-in	✓
CODE_POINTS_TO_BYTES	Built-in	✓
CODE_POINTS_TO_STRING	Built-in	✓
COLLATE	Built-in	✓
CONCAT	Built-in	✓
CONTAINS_SUBSTR	Built-in	✓
CORR	Built-in	✓
COS	Built-in	✓
COSH	Built-in	✓
COSINE_DISTANCE	Built-in	—
COT	Built-in	✓
COTH	Built-in	✓
COUNT	Built-in	✓
COUNT (Differential Privacy)	Built-in	✓
COUNTIF	Built-in	✓
COVAR_POP	Built-in	✓
COVAR_SAMP	Built-in	✓
CSC	Built-in	✓
CSCH	Built-in	✓
CUME_DIST	Built-in	✓
CURRENT_DATE	Built-in	✓
CURRENT_DATETIME	Built-in	✓
CURRENT_TIME	Built-in	✓
CURRENT_TIMESTAMP	Built-in	✓
DATE	Built-in	✓
DATE_ADD	Built-in	✓
DATE_BUCKET	Built-in	—
DATE_DIFF	Built-in	✓
DATE_FROM_UNIX_DATE	Built-in	✓
DATE_SUB	Built-in	✓
DATE_TRUNC	Built-in	✓
DATETIME	Built-in	✓
DATETIME_ADD	Built-in	✓
DATETIME_BUCKET	Built-in	—
DATETIME_DIFF	Built-in	✓
DATETIME_SUB	Built-in	✓
DATETIME_TRUNC	Built-in	✓
DENSE_RANK	Built-in	✓
DESTINATION_NODE_ID	Built-in	—
DETERMINISTIC_DECRYPT_BYTES	Built-in	✓
DETERMINISTIC_DECRYPT_STRING	Built-in	✓
DETERMINISTIC_ENCRYPT	Built-in	✓
DIV	Built-in	✓
DLP_DETERMINISTIC_DECRYPT	Built-in	—
DLP_DETERMINISTIC_ENCRYPT	Built-in	—
DLP_KEY_CHAIN	Built-in	—
EDGES	Built-in	—
EDIT_DISTANCE	Built-in	✓
ELEMENT_ID	Built-in	—
ENDS_WITH	Built-in	✓
ERROR	Built-in	✓
EUCLIDEAN_DISTANCE	Built-in	—
EXP	Built-in	✓
EXTERNAL_OBJECT_TRANSFORM	Built-in	—
EXTERNAL_QUERY	Built-in	✓
EXTRACT (Date)	Built-in	—
EXTRACT (Datetime)	Built-in	—
EXTRACT (Interval)	Built-in	—
EXTRACT (Time)	Built-in	—
EXTRACT (Timestamp)	Built-in	—
FARM_FINGERPRINT	Built-in	✓
FIRST_VALUE	Built-in	✓
FLOAT64	Built-in	✓
FLOOR	Built-in	✓
FORMAT	Built-in	✓
FORMAT_DATE	Built-in	✓
FORMAT_DATETIME	Built-in	✓
FORMAT_TIME	Built-in	✓
FORMAT_TIMESTAMP	Built-in	✓
FROM_BASE32	Built-in	✓
FROM_BASE64	Built-in	✓
FROM_HEX	Built-in	✓
GAP_FILL	Built-in	✓
GENERATE_ARRAY	Built-in	✓
GENERATE_DATE_ARRAY	Built-in	✓
GENERATE_RANGE_ARRAY	Built-in	—
GENERATE_TIMESTAMP_ARRAY	Built-in	✓
GENERATE_UUID	Built-in	✓
GREATEST	Built-in	✓
GROUPING	Built-in	✓
HLL_COUNT.EXTRACT	Built-in	✓
HLL_COUNT.INIT	Built-in	✓
HLL_COUNT.MERGE	Built-in	✓
HLL_COUNT.MERGE_PARTIAL	Built-in	✓
IEEE_DIVIDE	Built-in	✓
INITCAP	Built-in	✓
INSTR	Built-in	✓
INT64	Built-in	✓
IS_ACYCLIC	Built-in	—
IS_INF	Built-in	✓
IS_NAN	Built-in	✓
IS_SIMPLE	Built-in	—
IS_TRAIL	Built-in	—
JSON_ARRAY	Built-in	✓
JSON_ARRAY_APPEND	Built-in	✓
JSON_ARRAY_INSERT	Built-in	✓
JSON_EXTRACT	Built-in	—
JSON_EXTRACT_ARRAY	Built-in	✓
JSON_EXTRACT_SCALAR	Built-in	✓
JSON_EXTRACT_STRING_ARRAY	Built-in	✓
JSON_FLATTEN	Built-in	✓
JSON_KEYS	Built-in	✓
JSON_OBJECT	Built-in	✓
JSON_QUERY	Built-in	✓
JSON_QUERY_ARRAY	Built-in	✓
JSON_REMOVE	Built-in	✓
JSON_SET	Built-in	✓
JSON_STRIP_NULLS	Built-in	✓
JSON_TYPE	Built-in	✓
JSON_VALUE	Built-in	✓
JSON_VALUE_ARRAY	Built-in	✓
JUSTIFY_DAYS	Built-in	✓
JUSTIFY_HOURS	Built-in	✓
JUSTIFY_INTERVAL	Built-in	✓
KEYS.ADD_KEY_FROM_RAW_BYTES	Built-in	✓
KEYS.KEYSET_CHAIN	Built-in	✓
KEYS.KEYSET_FROM_JSON	Built-in	✓
KEYS.KEYSET_LENGTH	Built-in	✓
KEYS.KEYSET_TO_JSON	Built-in	✓
KEYS.NEW_KEYSET	Built-in	✓
KEYS.NEW_WRAPPED_KEYSET	Built-in	✓
KEYS.REWRAP_KEYSET	Built-in	✓
KEYS.ROTATE_KEYSET	Built-in	✓
KEYS.ROTATE_WRAPPED_KEYSET	Built-in	✓
KLL_QUANTILES.EXTRACT_FLOAT64	Built-in	—
KLL_QUANTILES.EXTRACT_INT64	Built-in	—
KLL_QUANTILES.EXTRACT_POINT_FLOAT64	Built-in	—
KLL_QUANTILES.EXTRACT_POINT_INT64	Built-in	—
KLL_QUANTILES.INIT_FLOAT64	Built-in	—
KLL_QUANTILES.INIT_INT64	Built-in	—
KLL_QUANTILES.MERGE_FLOAT64	Built-in	—
KLL_QUANTILES.MERGE_INT64	Built-in	—
KLL_QUANTILES.MERGE_PARTIAL	Built-in	—
KLL_QUANTILES.MERGE_POINT_FLOAT64	Built-in	—
KLL_QUANTILES.MERGE_POINT_INT64	Built-in	—
LABELS	Built-in	—
LAG	Built-in	✓
LAST_DAY (Date)	Built-in	✓
LAST_DAY (Datetime)	Built-in	✓
LAST_VALUE	Built-in	✓
LAX_BOOL	Built-in	✓
LAX_FLOAT64	Built-in	✓
LAX_INT64	Built-in	✓
LAX_STRING	Built-in	✓
LEAD	Built-in	✓
LEAST	Built-in	✓
LEFT	Built-in	✓
LENGTH	Built-in	✓
LN	Built-in	✓
LOG	Built-in	✓
LOG10	Built-in	✓
LOGICAL_AND	Built-in	✓
LOGICAL_OR	Built-in	✓
LOWER	Built-in	✓
LPAD	Built-in	✓
LTRIM	Built-in	✓
MAKE_INTERVAL	Built-in	✓
MAX	Built-in	✓
MAX_BY	Built-in	✓
MD5	Built-in	✓
MIN	Built-in	✓
MIN_BY	Built-in	✓
MOD	Built-in	✓
NET.HOST	Built-in	✓
NET.IP_FROM_STRING	Built-in	✓
NET.IP_NET_MASK	Built-in	✓
NET.IP_TO_STRING	Built-in	✓
NET.IP_TRUNC	Built-in	✓
NET.IPV4_FROM_INT64	Built-in	✓
NET.IPV4_TO_INT64	Built-in	✓
NET.PUBLIC_SUFFIX	Built-in	✓
NET.REG_DOMAIN	Built-in	✓
NET.SAFE_IP_FROM_STRING	Built-in	✓
NODES	Built-in	—
NORMALIZE	Built-in	—
NORMALIZE_AND_CASEFOLD	Built-in	✓
NTH_VALUE	Built-in	✓
NTILE	Built-in	✓
OBJ.FETCH_METADATA	Built-in	—
OBJ.GET_ACCESS_URL	Built-in	—
OBJ.GET_READ_URL	Built-in	—
OBJ.MAKE_REF	Built-in	—
OCTET_LENGTH	Built-in	✓
PARSE_BIGNUMERIC	Built-in	✓
PARSE_DATE	Built-in	✓
PARSE_DATETIME	Built-in	✓
PARSE_JSON	Built-in	✓
PARSE_NUMERIC	Built-in	✓
PARSE_TIME	Built-in	✓
PARSE_TIMESTAMP	Built-in	✓
PATH_FIRST	Built-in	—
PATH_LAST	Built-in	—
PATH_LENGTH	Built-in	—
PERCENT_RANK	Built-in	✓
PERCENTILE_CONT (Differential Privacy)	Built-in	✓
PERCENTILE_CONT (Navigation)	Built-in	✓
PERCENTILE_DISC	Built-in	✓
POW	Built-in	✓
POWER	Built-in	✓
RAND	Built-in	✓
RANGE	Built-in	—
RANGE_BUCKET	Built-in	✓
RANGE_CONTAINS	Built-in	—
RANGE_END	Built-in	—
RANGE_INTERSECT	Built-in	—
RANGE_OVERLAPS	Built-in	—
RANGE_SESSIONIZE	Built-in	—
RANGE_START	Built-in	—
RANK	Built-in	✓
REGEXP_CONTAINS	Built-in	✓
REGEXP_EXTRACT	Built-in	✓
REGEXP_EXTRACT_ALL	Built-in	✓
REGEXP_INSTR	Built-in	✓
REGEXP_REPLACE	Built-in	✓
REGEXP_SUBSTR	Built-in	✓
REPEAT	Built-in	✓
REPLACE	Built-in	✓
REVERSE	Built-in	✓
RIGHT	Built-in	✓
ROUND	Built-in	✓
ROW_NUMBER	Built-in	✓
RPAD	Built-in	✓
RTRIM	Built-in	✓
S2_CELLIDFROMPOINT	Built-in	✓
S2_COVERINGCELLIDS	Built-in	✓
SAFE_ADD	Built-in	✓
SAFE_CAST	Built-in	—
SAFE_CONVERT_BYTES_TO_STRING	Built-in	✓
SAFE_DIVIDE	Built-in	✓
SAFE_MULTIPLY	Built-in	✓
SAFE_NEGATE	Built-in	✓
SAFE_SUBTRACT	Built-in	✓
SEARCH	Built-in	✓
SEC	Built-in	✓
SECH	Built-in	✓
SESSION_USER	Built-in	✓
SHA1	Built-in	✓
SHA256	Built-in	✓
SHA512	Built-in	✓
SIGN	Built-in	✓
SIN	Built-in	✓
SINH	Built-in	✓
SOUNDEX	Built-in	✓
SOURCE_NODE_ID	Built-in	—
SPLIT	Built-in	✓
SQRT	Built-in	✓
ST_ANGLE	Built-in	✓
ST_AREA	Built-in	✓
ST_ASBINARY	Built-in	✓
ST_ASGEOJSON	Built-in	✓
ST_ASTEXT	Built-in	✓
ST_AZIMUTH	Built-in	✓
ST_BOUNDARY	Built-in	✓
ST_BOUNDINGBOX	Built-in	✓
ST_BUFFER	Built-in	✓
ST_BUFFERWITHTOLERANCE	Built-in	✓
ST_CENTROID	Built-in	✓
ST_CENTROID_AGG	Built-in	✓
ST_CLOSESTPOINT	Built-in	✓
ST_CLUSTERDBSCAN	Built-in	✓
ST_CONTAINS	Built-in	✓
ST_CONVEXHULL	Built-in	✓
ST_COVEREDBY	Built-in	✓
ST_COVERS	Built-in	✓
ST_DIFFERENCE	Built-in	✓
ST_DIMENSION	Built-in	✓
ST_DISJOINT	Built-in	✓
ST_DISTANCE	Built-in	✓
ST_DUMP	Built-in	✓
ST_DWITHIN	Built-in	✓
ST_ENDPOINT	Built-in	✓
ST_EQUALS	Built-in	✓
ST_EXTENT	Built-in	—
ST_EXTERIORRING	Built-in	✓
ST_GEOGFROM	Built-in	✓
ST_GEOGFROMGEOJSON	Built-in	✓
ST_GEOGFROMTEXT	Built-in	✓
ST_GEOGFROMWKB	Built-in	✓
ST_GEOGPOINT	Built-in	✓
ST_GEOGPOINTFROMGEOHASH	Built-in	✓
ST_GEOHASH	Built-in	✓
ST_GEOMETRYTYPE	Built-in	✓
ST_HAUSDORFFDISTANCE	Built-in	—
ST_HAUSDORFFDWITHIN	Built-in	—
ST_INTERIORRINGS	Built-in	✓
ST_INTERSECTION	Built-in	✓
ST_INTERSECTS	Built-in	✓
ST_INTERSECTSBOX	Built-in	✓
ST_ISCLOSED	Built-in	✓
ST_ISCOLLECTION	Built-in	✓
ST_ISEMPTY	Built-in	✓
ST_ISRING	Built-in	✓
ST_LENGTH	Built-in	✓
ST_LINEINTERPOLATEPOINT	Built-in	—
ST_LINELOCATEPOINT	Built-in	✓
ST_LINESUBSTRING	Built-in	—
ST_MAKELINE	Built-in	✓
ST_MAKEPOLYGON	Built-in	✓
ST_MAKEPOLYGONORIENTED	Built-in	✓
ST_MAXDISTANCE	Built-in	✓
ST_NPOINTS	Built-in	✓
ST_NUMGEOMETRIES	Built-in	✓
ST_NUMPOINTS	Built-in	✓
ST_PERIMETER	Built-in	✓
ST_POINTN	Built-in	✓
ST_REGIONSTATS	Built-in	—
ST_SIMPLIFY	Built-in	✓
ST_SNAPTOGRID	Built-in	✓
ST_STARTPOINT	Built-in	✓
ST_TOUCHES	Built-in	✓
ST_UNION	Built-in	✓
ST_UNION_AGG	Built-in	✓
ST_WITHIN	Built-in	✓
ST_X	Built-in	✓
ST_Y	Built-in	✓
STARTS_WITH	Built-in	✓
STDDEV	Built-in	✓
STDDEV_POP	Built-in	✓
STDDEV_SAMP	Built-in	✓
STRING (JSON)	Built-in	✓
STRING (Timestamp)	Built-in	✓
STRING_AGG	Built-in	✓
STRPOS	Built-in	✓
SUBSTR	Built-in	✓
SUBSTRING	Built-in	—
SUM	Built-in	✓
SUM (Differential Privacy)	Built-in	✓
TAN	Built-in	✓
TANH	Built-in	✓
TEXT_ANALYZE	Built-in	—
TF_IDF	Built-in	—
TIME	Built-in	✓
TIME_ADD	Built-in	✓
TIME_DIFF	Built-in	✓
TIME_SUB	Built-in	✓
TIME_TRUNC	Built-in	✓
TIMESTAMP	Built-in	✓
TIMESTAMP_ADD	Built-in	✓
TIMESTAMP_BUCKET	Built-in	—
TIMESTAMP_DIFF	Built-in	✓
TIMESTAMP_MICROS	Built-in	✓
TIMESTAMP_MILLIS	Built-in	✓
TIMESTAMP_SECONDS	Built-in	✓
TIMESTAMP_SUB	Built-in	✓
TIMESTAMP_TRUNC	Built-in	✓
TO_BASE32	Built-in	✓
TO_BASE64	Built-in	✓
TO_CODE_POINTS	Built-in	✓
TO_HEX	Built-in	✓
TO_JSON	Built-in	✓
TO_JSON_STRING	Built-in	✓
TRANSLATE	Built-in	✓
TRIM	Built-in	✓
TRUNC	Built-in	✓
TYPEOF	Built-in	—
UNICODE	Built-in	✓
UNIX_DATE	Built-in	✓
UNIX_MICROS	Built-in	✓
UNIX_MILLIS	Built-in	✓
UNIX_SECONDS	Built-in	✓
UPPER	Built-in	✓
VAR_POP	Built-in	✓
VAR_SAMP	Built-in	✓
VARIANCE	Built-in	✓
VECTOR_INDEX.STATISTICS	Built-in	—
VECTOR_SEARCH	Built-in	—

Was this page helpful?

Privacy policyCreate a GitHub issue

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.