Databricks Certified Data Engineer - Associate

Correct Answer: D

Explanation:

In Databricks SQL, you can add comments to tables using the COMMENT clause. The syntax for adding a comment to a table is:

COMMENT 'comment_text'

COMMENT 'comment_text'

In this specific case, the organization policy requires indicating that the table contains personally identifiable information (PII). The correct way to do this is by using:

COMMENT 'Contains PII'

COMMENT 'Contains PII'

This would make the complete CREATE TABLE statement:

CREATE TABLE customersInFrance
COMMENT 'Contains PII' AS
SELECT id,
       firstName,
       lastName,
FROM customerLocations
WHERE country = 'FRANCE';

CREATE TABLE customersInFrance
COMMENT 'Contains PII' AS
SELECT id,
       firstName,
       lastName,
FROM customerLocations
WHERE country = 'FRANCE';

Why other options are incorrect:

• A: Incorrect - There are multiple ways to indicate PII in Databricks, including table comments and TBLPROPERTIES.
• B: Incorrect - The syntax is wrong. It should be COMMENT 'PII' or COMMENT 'Contains PII', not "COMMENT PII".
• C: Incorrect - The syntax is incorrect. TBLPROPERTIES should be followed by parentheses with key-value pairs, like TBLPROPERTIES ('contains_pii'='true').
• E: Incorrect - PII alone is not valid SQL syntax for table creation.

Best Practice: While COMMENT 'Contains PII' works, a more robust approach for PII indication would be to use TBLPROPERTIES with a specific key-value pair, which can be programmatically queried later:

CREATE TABLE customersInFrance
COMMENT 'Contains PII'
TBLPROPERTIES ('contains_pii'='true') AS
SELECT id,
       firstName,
       lastName,
FROM customerLocations
WHERE country = 'FRANCE';

CREATE TABLE customersInFrance
COMMENT 'Contains PII'
TBLPROPERTIES ('contains_pii'='true') AS
SELECT id,
       firstName,
       lastName,
FROM customerLocations
WHERE country = 'FRANCE';