Differential privacy SQL reference¶

This topic provides the following information:

A reference for the SQL functions that are unique to differential privacy.
A list of the Snowflake data types, operators, query syntax, and functions that are supported by differential privacy.

Differential privacy functions¶

The following functions are unique to differential privacy.

Function	Description
DP_INTERVAL_LOW	Returns the lower bound of the noise interval.
DP_INTERVAL_HIGH	Returns the upper bound of the noise interval.

Data types¶

The following data types are supported.

Data type	Notes
BOOLEAN
CHAR, CHARACTER
DATE
DATETIME
DECIMAL, NUMERIC
DOUBLE, DOUBLE PRECISION, REAL
FLOAT, FLOAT4, FLOAT8
INT, INTEGER , BIGINT, SMALLINT, TINYINT, BYTEINT
NUMBER
STRING
TEXT
TIME
TIMESTAMP, TIMESTAMP_NTZ	Time data types with time zones are not supported. Use TIMESTAMP or TIMESTAMP_NTZ.
VARCHAR

Query syntax¶

The following elements of the Snowflake query syntax are supported.

Syntax	Notes
SELECT
SELECT ALL
FROM
INNER JOIN ON	See Supported joins.
INNER JOIN USING	See Supported joins.
LEFT OUTER JOIN ON	See Supported joins.
LEFT OUTER JOIN USING	See Supported joins.
RIGHT OUTER JOIN ON	See Supported joins.
RIGHT OUTER JOIN USING	See Supported joins.
FULL OUTER JOIN ON	See Supported joins.
FULL OUTER JOIN USING	See Supported joins.
NATURAL JOIN USING	See Supported joins.
WHERE
GROUP BY	Aliases are not supported in the GROUP BY clause. For example, `GROUP BY col_a AS column_a` is not supported. When a GROUP BY clause is used in the outermost query, the arguments to each of the aggregates must be the same. For example, `SELECT g, SUM(a), COUNT(a) FROM t GROUP BY g` is supported. In contrast, `SELECT g, SUM(a), COUNT(b) FROM t GROUP BY g` is not supported.

Limitations on query syntax: Quoted identifiers (for example, column, table, schema and database names) are not supported.

Operators¶

Arithmetic operators¶

The following arithmetic operators are supported.

Operator	Notes
`-` (unary)
`-`
`+` (unary)	Does not work with strings.
`+`
`*`
`/`
`%`

Comparison operators¶

The following comparison operators are supported.

Operator	Notes
`=`
`!=`
`<`
`>`
`<=`
`>=`

Logical operators¶

The following logical operators are supported.

Operator	Notes
AND
NOT
OR

Set operators¶

The following set operators are supported.

Operator	Notes
INTERSECT
UNION [ ALL ]
MINUS

Subquery operators¶

Subquery operators are not supported.

Functions¶

Aggregate functions¶

The following aggregate functions are supported.

Function	Notes
ANY_VALUE	Supported only as an aggregate for a subquery with a GROUP BY clause.
AVG
BOOLAND_AGG	Supported only when both of the following are true: It is used as an aggregate for a subquery with a GROUP BY clause. Its argument is a BOOLEAN data type.
BOOLOR_AGG	Supported only when both of the following are true: It is used as an aggregate for a subquery with a GROUP BY clause. Its argument is a BOOLEAN data type.
BOOLXOR_AGG	Supported only when both of the following are true: It is used as an aggregate for a subquery with a GROUP BY clause. Its argument is a BOOLEAN data type.
COUNT
COUNT DISTINCT
MEDIAN	Not supported when used in a subquery with GROUP BY
MIN	Not supported when used in a subquery with GROUP BY
MAX	Not supported when used in a subquery with GROUP BY
SUM
VAR_POP, VARIANCE_POP

Bitwise expression functions¶

Bitwise expression functions are not supported.

Conditional expression functions¶

The following conditional expression functions are supported.

Function	Notes
[ NOT ] IN
CASE
COALESCE
DECODE
EQUAL_NULL
GREATEST
IFF
IS [NOT] NULL
LEAST

Context functions¶

Context functions are not supported.

Conversion functions¶

The following conversion functions are supported.

Function	Notes
CAST, `::`	Columns must be explicitly non-null to be casted. To do this, filter out nulls before casting. Casting other data types to STRING is not supported.
TO_BOOLEAN
TO_CHAR , TO_VARCHAR
TO_DATE , DATE
TO_DECIMAL , TO_NUMBER , TO_NUMERIC
TO_DOUBLE
TO_TIME , TIME
TO_TIMESTAMP , TO_TIMESTAMP_*
TRY_CAST
TRY_TO_BOOLEAN
TRY_TO_DATE
TRY_TO_DECIMAL, TRY_TO_NUMBER, TRY_TO_NUMERIC
TRY_TO_DOUBLE
TRY_TO_TIME
TRY_TO_TIMESTAMP , TRY_TO_TIMESTAMP_*

Data generation functions¶

Data generation functions are not supported.

Data metric functions¶

Data metric functions are not supported. User-defined DMFs are also not supported.

Date & time functions¶

The following date & time functions are supported.

Function	Notes
ADD_MONTHS
DATE_FROM_PARTS
DATE_PART	The following date and time parts are not supported: `dayofweek`, `week`, `yearofweek`, `nanosecond`, `epoch_`, and `timezone_`.
DATE_TRUNC	Only `year` and `month` are supported.
DATEADD	Only `year` and `month` are supported.
DATEDIFF	Only `year` and `month` are supported.
DAYNAME
EXTRACT	The following date and time parts are not supported: `dayofweek`, `week`, `yearofweek`, `nanosecond`, `epoch_`, and `timezone_`.
HOUR
LAST_DAY
MINUTE
MONTHS_BETWEEN
NEXT_DAY
SECOND
TIME_FROM_PARTS
TIMEADD	Only `year` and `month` are supported.
TIMEDIFF	Only `year` and `month` are supported.
TIMESTAMPADD	Only `year` and `month` are supported.
TIMESTAMPDIFF	Only `year` and `month` are supported.
TRUNC
YEAR* / DAY* / WEEK* / MONTH / QUARTER

Encryption functions¶

Encryption functions are not supported.

File functions¶

File functions are not supported.

Geospatial functions¶

Geospatial functions are not supported.

Hash functions¶

Hash functions are not supported.

Metadata functions¶

Metadata functions are not supported.

Numeric functions¶

The following numeric functions are supported.

Function	Notes
ABS
ACOS
ACOSH
ATAN
ATANH
CEIL
COS
COSH
FLOOR
MOD
ROUND	Currently supports only 0 for the scale parameter; default value for rounding mode
SIGN

Regular expression functions¶

Regular expression functions are not supported.

Semi-structured and structured data functions¶

Semi-structured and structured data functions are not supported.

String and binary functions¶

The following string & binary functions are supported.

Function	Notes
CONTAINS
LENGTH , LEN
LOWER
POSITION
SUBSTR , SUBSTRING
UPPER

System functions¶

System functions are not supported.

Table functions¶

Table functions are not supported.