- (Exam Topic 5)
You have an Azure Synapse Analytics Apache Spark pool named Pool1.
You plan to load JSON files from an Azure Data Lake Storage Gen2 container into the tables in Pool1. The structure and data types vary by file.
You need to load the files into the tables. The solution must maintain the source data types. What should you do?

1. A. Load the data by using PySpark.
2. B. Load the data by using the OPENROWSET Transact-SQL command in an Azure Synapse Analytics serverless SQL pool.
3. C. Use a Get Metadata activity in Azure Data Factory.
4. D. Use a Conditional Split transformation in an Azure Synapse data flow.

Correct Answer: B
Serverless SQL pool can automatically synchronize metadata from Apache Spark. A serverless SQL pool database will be created for each database existing in serverless Apache Spark pools.
Serverless SQL pool enables you to query data in your data lake. It offers a T-SQL query surface area that accommodates semi-structured and unstructured data queries.
To support a smooth experience for in place querying of data that's located in Azure Storage files, serverless SQL pool uses the OPENROWSET function with additional capabilities.
The easiest way to see to the content of your JSON file is to provide the file URL to the OPENROWSET function, specify csv FORMAT.
Reference:
https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/query-json-files https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/query-data-storage

- (Exam Topic 5)
You have an Azure Active Directory (Azure AD) tenant named contoso.com that contains a user named user1@contoso.com and an Azure SQL managed instance named SQLMI1.
You need to ensure that user1@contoso.com can create logins in SQLMI1 that map to Azure AD service principals.
Which three actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.

Solution:
Text Description automatically generated with medium confidence
Reference:
https://docs.microsoft.com/en-us/azure/azure-sql/managed-instance/aad-security-configure-tutorial

Does this meet the goal?

1. A. Yes
2. B. No

Correct Answer: A

- (Exam Topic 5)
You have an Azure subscription that contains a logical SQL server named Serverl. The master database of Serverl contains a user named Userl. You need to ensure that Userl can create databases on Serverl. Which database role should you assign to Userl?

1. A. db_owner
2. B. dbmanager
3. C. dbo
4. D. db_ddladmin

Correct Answer: B

- (Exam Topic 5)
You have a new Azure SQL database. The database contains a column that stores confidential information. You need to track each time values from the column are returned in a query. The tracking information must be stored for 365 days from the date the query was executed.
Which three actions should you perform? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.

1. A. Turn on auditing and write audit logs to an Azure Storage account.
2. B. Add extended properties to the column.
3. C. Turn on Advanced Data Security for the Azure SQL server.
4. D. Apply sensitivity labels named Highly Confidential to the column.
5. E. Turn on Azure Advanced Threat Protection (ATP).

Correct Answer: ACD
C: Advanced Data Security (ADS) is a unified package for advanced SQL security capabilities. ADS is available for Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse Analytics. It includes functionality for discovering and classifying sensitive data
D: You can apply sensitivity-classification labels persistently to columns by using new metadata attributes that have been added to the SQL Server database engine. This metadata can then be used for advanced,
sensitivity-based auditing and protection scenarios.
A: An important aspect of the information-protection paradigm is the ability to monitor access to sensitive data. Azure SQL Auditing has been enhanced to include a new field in the audit log called data_sensitivity_information. This field logs the sensitivity classifications (labels) of the data that was returned by a query. Here's an example:

Reference:
https://docs.microsoft.com/en-us/azure/azure-sql/database/data-discovery-and-classification-overview

- (Exam Topic 5)
You have an Azure SQL database that contains a table named Customer. Customer has the columns shown in the following table.

You plan to implement a dynamic data mask for the Customer_Phone column. The mask must meet the following requirements:
The first six numerals of each customer’s phone number must be masked.
The last four digits of each customer’s phone number must be visible.
Hyphens must be preserved and displayed.
How should you configure the dynamic data mask? To answer, select the appropriate options in the answer area.

Solution:
Box 1: 0
Custom String : Masking method that exposes the first and last letters and adds a custom padding string in the middle. prefix,[padding],suffix
Box 2: xxx-xxx
Box 3: 5 Reference:
https://docs.microsoft.com/en-us/sql/relational-databases/security/dynamic-data-masking

Does this meet the goal?

1. A. Yes
2. B. No

Correct Answer: A