SQL capabilities of AWS Clean Rooms Differential Privacy - AWS Clean Rooms
Common alternatives for unsupported SQL constructs

SQL capabilities of AWS Clean Rooms Differential Privacy

AWS Clean Rooms Differential Privacy uses a general-purpose query structure to support complex SQL queries. Custom analysis templates are validated against this structure to ensure that they can run on tables protected by differential privacy. The following table indicates which functions are supported. See Query structure and syntax for more information.

Category SQL constructs supported in the Spark analytics engine SQL constructs supported in the AWS Clean Rooms SQL analytics engine Common table expressions (CTEs) Final SELECT clause
Aggregate functions

  • ANY_VALUE function

  • APPROXIMATE PERCENTILE_DISC function

  • AVG function

  • COUNT and COUNT DISTINCT functions

  • MAX function

  • MEDIAN function

  • MIN function

  • PERCENTILE_CONT function

  • STDDEV_SAMP and STDDEV_POP functions

  • SUM and SUM DISTINCT functions

  • VAR_SAMP and VAR_POP functions

  • ANY_VALUE function

  • APPROXIMATE PERCENTILE_DISC function

  • AVG function

  • COUNT and COUNT DISTINCT functions

  • LISTAGG function

  • MAX function

  • MEDIAN function

  • MIN function

  • PERCENTILE_CONT function

  • STDDEV_SAMP and STDDEV_POP functions

  • SUM and SUM DISTINCT functions

  • VAR_SAMP and VAR_POP functions

 Supported with the condition that CTEs using differential privacy protected tables must result in data with user-level records. You should write the SELECT expression in those CTEs using `SELECT userIdentifierColumn...' format. Supported aggregations: AVG, COUNT, COUNT DISTINCT, STDDEV, and SUM.
CTEs WITH clause, WITH clause subquery WITH clause, WITH clause subquery Supported with the condition that CTEs using differential privacy protected tables must result in data with user-level records. You should write the SELECT expression in those CTEs using `SELECT userIdentifierColumn...' format. N/A
Subqueries

  • SELECT

  • HAVING

  • JOIN

  • JOIN condition

  • FROM

  • WHERE

  • SELECT

  • HAVING

  • JOIN

  • JOIN condition

  • FROM

  • WHERE

 You can have any subquery that doesn't reference differential privacy relations in these constructs. You can have any subquery that references differential privacy relations in a FROM and JOIN clause only.
Join clauses

  • INNER JOIN

  • LEFT JOIN

  • LEFT SEMI JOIN

  • LEFT ANTI JOIN

  • RIGHT JOIN

  • FULL JOIN

  • [JOIN] OR operator

  • CROSS JOIN

  • INNER JOIN

  • LEFT JOIN

  • RIGHT JOIN

  • FULL JOIN

  • [JOIN] OR operator

  • CROSS JOIN

Supported with the condition that only JOIN functions that are equi-joins on user identifier columns are supported and are mandatory when querying two or more tables with differential privacy turned on. Ensure that the mandatory equi-join conditions are correct. Confirm that the table owner has configured the same user identifier column in all tables so that the definition of a user remains consistent across tables.

CROSS JOIN functions are not supported when combining two or more relations with differential privacy turned on.
Set operators UNION, UNION ALL, INTERSECT, EXCEPT | MINUS (these are synonyms) UNION, UNION ALL, INTERSECT, EXCEPT | MINUS (these are synonyms) All are supported Not supported
Window functions

Aggregate functions

  • AVG window function

  • COUNT window function

  • CUME_DIST window function

  • DENSE_RANK window function

  • FIRST_VALUE window function

  • LAG window function

  • LAST_VALUE window function

  • LEAD window function

  • MAX window functions

  • MEDIAN window functions

  • MIN window functions

  • NTH_VALUE window function

  • STDDEV_SAMP and STDDEV_POP window function (STDDEV_SAMP and STDDEV are synonyms)

  • SUM window functions

  • VAR_SAMP and VAR_POP window functions (VAR_SAMP and VARIANCE are synonyms)

Ranking functions

  • DENSE_RANK window function

  • NTILE window function

  • PERCENT_RANK window function

  • RANK window function

  • ROW_NUMBER window function

Aggregate functions

  • AVG window function

  • COUNT window function

  • CUME_DIST window function

  • DENSE_RANK window function

  • FIRST_VALUE window function

  • LAG window function

  • LAST_VALUE window function

  • LEAD window function

  • MAX window functions

  • MEDIAN window functions

  • MIN window functions

  • NTH_VALUE window function

  • RATIO_TO_REPORT window function

  • STDDEV_SAMP and STDDEV_POP window function (STDDEV_SAMP and STDDEV are synonyms)

  • SUM window functions

  • VAR_SAMP and VAR_POP window functions (VAR_SAMP and VARIANCE are synonyms)

Ranking functions

  • DENSE_RANK window function

  • NTILE window function

  • PERCENT_RANK window function

  • RANK window function

  • ROW_NUMBER window function

 All are supported with the condition that the user identifier column in the window function's partition clause is required when you query a relation with differential privacy turned on. Not supported
Conditional expressions

  • CASE condition expression

  • COALESCE expression

  • GREATEST and LEAST functions

  • NVL and COALESCE functions

  • NVL2 function

  • NULLIF function

  • CASE condition expression

  • COALESCE expression

  • GREATEST and LEAST functions

  • NVL and COALESCE functions

  • NVL2 function

  • NULLIF function

 All are supported All are supported
Conditions

  • Comparison condition

  • Logical conditions

  • Pattern-matching conditions

  • BETWEEN range conditions

  • Null condition

  • Comparison condition

  • Logical conditions

  • Pattern-matching conditions

  • BETWEEN range conditions

  • Null condition

 EXISTS and IN can't be used because they require subqueries. All others are supported. All are supported
Date-time functions

  • Date and time functions in transactions

  • Concatenation operator

  • ADD_MONTHS functions

  • CONVERT_TIMEZONE function

  • CURRENT_DATE function

  • DATEADD function

  • DATEDIFF function

  • DATE_PART functions

  • DATE_TRUNC function

  • EXTRACT function

  • TO_TIMESTAMP function

  • Date parts for date or timestamp functions

  • Date and time functions in transactions

  • Concatenation operator

  • ADD_MONTHS functions

  • CONVERT_TIMEZONE function

  • CURRENT_DATE function

  • DATEADD function

  • DATEDIFF function

  • DATE_PART functions

  • DATE_TRUNC function

  • EXTRACT function

  • GETDATE function

  • TIMEOFDAY functions

  • TO_TIMESTAMP function

  • Date parts for date or timestamp functions

 All are supported All are supported
String functions

  • || (concatenation) operator

  • BTRIM function

  • CHAR_LENGTH function

  • CHARACTER_LENGTH function

  • CONCAT function

  • LEFT and RIGHT functions

  • LEN function

  • LENGTH function

  • LOWER function

  • LPAD and RPAD functions

  • LTRIM function

  • POSITION functions

  • REGEXP_COUNT function

  • REGEXP_INSTR function

  • REGEXP_REPLACE function

  • REGEXP_SUBSTR function

  • REPEAT function

  • REPLACE function

  • REVERSE function

  • RTRIM function

  • SPLIT_PART function

  • SUBSTRING function

  • TRANSLATE function

  • TRIM functions

  • UPPER function

  • || (concatenation) operator

  • BTRIM function

  • CHAR_LENGTH function

  • CHARACTER_LENGTH function

  • CONCAT function

  • LEFT and RIGHT functions

  • LEN function

  • LENGTH function

  • LOWER function

  • LPAD and RPAD functions

  • LTRIM function

  • POSITION functions

  • REGEXP_COUNT function

  • REGEXP_INSTR function

  • REGEXP_REPLACE function

  • REGEXP_SUBSTR function

  • REPEAT function

  • REPLACE function

  • REPLICATE function

  • REVERSE function

  • RTRIM function

  • SOUNDEX function

  • SPLIT_PART function

  • STRPOS function

  • SUBSTRING function

  • TEXTLEN function

  • TRANSLATE function

  • TRIM functions

  • UPPER function

 All are supported All are supported
Data type formatting functions

  • CAST function

  • TO_CHAR

  • TO_DATE function

  • TO_NUMBER

  • Datetime format strings

  • Numeric format strings

  • CAST function

  • TO_CHAR

  • TO_DATE function

  • TO_NUMBER

  • Datetime format strings

  • Numeric format strings

 All are supported All are supported
Hash functions

  • AES_ENCRYPT

  • AES_DECRYPT

  • ENCODE

  • DECODE

  • MD5 function

  • SHA1 function

  • SHA2 function

  • XX_HASH64

  • MD5 function

  • SHA function

  • SHA1 function

  • SHA2 function

  • MURMUR3_32_HASH

 All are supported All are supported
Mathematical operator symbols +, -, *, /, %, and @ +, -, *, /, %, and @ All are supported All are supported
Math functions

  • ABS function

  • ACOS function

  • ASIN function

  • ATAN function

  • ATAN2 function

  • CBRT function

  • CEILING (or CEIL) function

  • COS function

  • COT function

  • DEGREES function

  • LTRIM function

  • EXP function

  • FLOOR function

  • LN function

  • LOG function

  • MOD function

  • PI function

  • POWER function

  • RADIANS function

  • RANDOM function

  • ROUND function

  • SIGN function

  • SIN function

  • SQRT functions

  • TRUNC function

  • ABS function

  • ACOS function

  • ASIN function

  • ATAN function

  • ATAN2 function

  • CBRT function

  • CEILING (or CEIL) function

  • COS function

  • COT function

  • DEGREES function

  • DEXP function

  • LTRIM function

  • DLOG1 function

  • DLOG10 function

  • EXP function

  • FLOOR function

  • LN function

  • LOG function

  • MOD function

  • PI function

  • POWER function

  • RADIANS function

  • RANDOM function

  • ROUND function

  • SIGN function

  • SIN function

  • SQRT functions

  • TRUNC function

 All are supported All are supported
VARBYTE functions

  • UNHEX,

  • UNBASE64

  • HEX

  • HLL_SKETCH_AGG,

  • HLL_SKETCH_ESTIMATE

  • HLL_UNION

  • HLL_UNION_AGG

  • FROM_HEX function

  • FROM_VARBYTE function

  • TO_HEX function

  • TO_VARBYTE function

 All are supported All are supported
JSON

  • TO_JSON

  • GET_JSON_OBJECT

  • CAN_JSON_PARSE function

  • JSON_EXTRACT_ARRAY_ELEMENT_TEXT function

  • JSON_EXTRACT_PATH_TEXT function

  • JSON_PARSE function

  • JSON_SERIALIZE function

  • JSON_SERALIZE_TO_VARBYTE function

 All are supported All are supported
Array functions

  • ARRAY_CONTAINS

  • ARRAY_DISTINCT

  • ARRAY_EXCEPT

  • ARRAY_INTERSECT

  • ARRAY_JOIN

  • ARRAY_REMOVE

  • ARRAY_SORT

  • ARRAY_UNION

  • array function

  • array_concat function

  • array_flatten function

  • get_array_length function

  • split_to_array function

  • subarray function

 Not supported Not supported
Extended GROUP BY GROUPING SETS, ROLLUP, CUBE GROUPING SETS, ROLLUP, CUBE Not supported Not supported
Sort operation ORDER BY ORDER BY Supported with the condition that an ORDER BY clause is only supported in a window function's partition clause when querying tables with differential privacy turned on. Supported
Row limits LIMIT, OFFSET LIMIT, OFFSET Not supported in CTEs using differential privacy protected tables All are supported
Table and column aliasing Supported Supported
Math functions on aggregate functions Supported Supported
Scalar functions within aggregate functions Supported Supported

Common alternatives for unsupported SQL constructs

Category SQL construct Alternative

Window functions

  • LISTAGG

  • PERCENTILE_CONT

  • PERCENTILE_DISC

 You can use the equivalent aggregate function with GROUP BY.
Mathematical operator symbols

  • $column ||/ 2

  • $column |/ 2

  • $column ^ 2

  • CBRT

  • SQRT

  • POWER($column, 2)
Scalar functions

  • SYSDATE

  • $column::integer

  • convert(type, $column)

  • CURRENT_DATE

  • CAST $column AS integer

  • CAST $column AS type
Literals INTERVAL ‘1 SECOND' INTERVAL '1' SECOND
Row limiting TOP n LIMIT n
Join

  • USING

  • NATURAL

 ON clause should explicitly contain a join criterion.