Decentriq’s Data Clean Rooms are equipped with a own SQL engine, powered by confidential computing to guarantee a high level of security. You can query on provisioned datasets, and only results will be available. Sensitive data is never revealed to anyone. Results of SQL computations can be used as input to other computations such as Python, R and Synthetic Data Generation.
NULLvalues are supported
NULLliteral in expressions not yet supported
NOT NULLannotation supported
Window and ranking functions
PERCENTILE_CONT(can be used for MEAN)
RANDOM() OVER()(To generate a random value)
LENGTH(string)-> To get the number of characters of a string
ROUND( float_number , n_of_decimals[OPTIONAL] )→ To round a float number to a give n. of decimals. If the second parameter is not given, it returns an integer. Please note that it can be applied only on columns and cannot be used on aggregation functions
Supported syntax clauses table
|SELECT & FROM||Full support||SELECT a, b, c FROM TEST|
|WILDCARD operator||supports "*"||SELECT * FROM test|
|WHERE clause||Full support for WHERE clause||SELECT a |
WHERE b >= 4
|GROUP BY||GROUP BY full support||SELECT column1, column 2, COUNT(*) |
GROUP BY column1, column 2
|ORDER BY||You can now order the output rows by one or more columns||SELECT column1, column2, ... |
ORDER BY column1, column2, ...
|JOINs: INNER, LEFT, RIGHT, FULL OUTER, CROSS||INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, CROSS JOIN for joining tables. only supports equality conditions for the ON operator. Rows with NULL keys are treated as if there was no match.||SELECT table1.column, table2.column |
FROM table1 LEFT JOIN table2
ON table1.key = table2.key
- If unique = false, all rows match where column1 and column2 have Levenshtein distance of at most length.
- If unique = true, then for each element in column1 only the element in column2 with the lowest Levenshtein distance matches. If multiple have the same lowest Levenshtein distance, only the first match is returned. If all are larger than length, no element matches.
|SELECT * |
FROM table1 INNER JOIN table2
ON table1.last_name = table2.last_name AND
fuzzystrmatch(table1.first_name, table2.first_name, 4, true)
|UNION & UNION ALL||It is possible to combine different SELECT statement with same column number and similar column type||SELECT FROM table1 |
SELECT FROM table 2
|Aliasing of columns, tables and subqueries||Columns, tables and queries can be renamed with the 'AS' clause||SELECT a AS alias |
|CAST operator||Cast operator - limited for: BIG_INT, DOUBLE and VARCHAR||SELECT CAST(10 as DOUBLE) |
|CASE operator||CASE operator in SELECT clauses (must include an ELSE clause)||SELECT CASE WHEN a = '1' THEN 'it is one' WHEN a = '2' THEN 'it is two' ELSE 'i do not know' END |
|IN operator||IN operator in the where clause - no support for subquery||SELECT a |
WHERE b IN (1, 2, 3)
|BETWEEN operator||BETWEEN operator in the where clause||SELECT a |
WHERE b BETWEEN 10 AND 20
|Subquery in FROM statement||SELECT a FROM |
(SELECT b FROM test)
|Subquery||Subquery using WITH clause||WITH subquery_table as (... SUBQUERY ....) |
SELECT ... FROM subquery_table
|DISTINCT||Support for the DISTINCT clause on the SELECT||SELECT DISTINCT a |
Decentriq supports a k-anonymity filter feature for the SQL queries that are run in the Data Clean Rooms.
The K-anonymity technology forces the queries to output only aggregated values over a minimum amount of rows (the K parameter). All the other results will not be returned to the users and will remain unaccessible within the confidential computing environment.
Querying computation results
SQL queries can refer to the output of another computation in the same Data Clean Room in the
from statement, to simplify the writing of complex and nested queries.
SELECT sum(col_a) AS a
Create a SQL computation in the Decentriq UI
Follow the Decentriq UI tutorial
Create a SQL computation using the Python SDK
Follow the Python SDK tutorial