Free DP-203 Practice Exam Questions

You have an Azure Synapse Analytics dedicated SQL pool.
You need to ensure that data in the pool is encrypted at rest. The solution must NOT
require modifying applications that query the data.
What should you do?

A.

Enable encryption at rest for the Azure Data Lake Storage Gen2 account.

B.

Enable Transparent Data Encryption (TDE) for the pool.

C.

Use a customer-managed key to enable double encryption for the Azure Synapse workspace.

D.

Create an Azure key vault in the Azure subscription grant access to the pool.

B.

Enable Transparent Data Encryption (TDE) for the pool.

Explanation:
Transparent Data Encryption (TDE) helps protect against the threat of malicious activity by
encrypting and decrypting your data at rest. When you encrypt your database, associated
backups and transaction log files are encrypted without requiring any changes to your
applications. TDE encrypts the storage of an entire database
by using a symmetric key called the database encryption key.
Reference:
https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-datawarehouse-
overviewmanage-security

You have files and folders in Azure Data Lake Storage Gen2 for an Azure Synapse workspace as shown in the following exhibit

You create an external table named ExtTable that has LOCATION='/topfolder/'.
When you query ExtTable by using an Azure Synapse Analytics serverless SQL pool,
which files are returned?

A.

File2.csv and File3.csv only

B.

File1.csv and File4.csv only

C.

File1.csv, File2.csv, File3.csv, and File4.csv

D.

File1.csv only

C.

File1.csv, File2.csv, File3.csv, and File4.csv

Explanation:
To run a T-SQL query over a set of files within a folder or set of folders while treating them
as a single entity or
rowset, provide a path to a folder or a pattern (using wildcards) over a set of files or folders.
Reference:
https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/query-data-storage#querymultiple-
files-or-folders

You have an Azure subscription that contains the following resources:
* An Azure Active Directory (Azure AD) tenant that contains a security group named
Group1.
* An Azure Synapse Analytics SQL pool named Pool1.
You need to control the access of Group1 to specific columns and rows in a table in Pool1
Which Transact-SQL commands should you use? To answer, select the appropriate
options in the answer area.
NOTE: Each appropriate options in the answer area.

You are designing a dimension table for a data warehouse. The table will track the value of
the dimension attributes over time and preserve the history of the data by adding new rows
as the data changes.
Which type of slowly changing dimension (SCD) should use?

A.

Type 0

B.

Type 1

C.

Type 2

D.

Type 3

C.

Type 2

Explanation: Type 2 - Creating a new additional record. In this methodology all history of
dimension changes is kept in the database. You capture attribute change by adding a new
row with a new surrogate key to the dimension table. Both the prior and new rows contain
as attributes the natural key(or other durable identifier). Also 'effective date' and 'current
indicator' columns are used in this method. There could be only one record with current
indicator set to 'Y'. For 'effective date' columns, i.e. start_date and end_date, the end_date
for current record usually is set to value 9999-12-31. Introducing changes to the
dimensional model in type 2 could be very expensive database operation so it is not
recommended to use it in dimensions where a new attribute could be added in the future.
https://www.datawarehouse4u.info/SCD-Slowly-Changing-Dimensions.html

You use Azure Stream Analytics to receive Twitter data from Azure Event Hubs and to output the data to an Azure Blob storage account.
You need to output the count of tweets during the last five minutes every five minutes.
Each tweet must only
be counted once.
Which windowing function should you use?

A.

a five-minute Session window

B.

a five-minute Sliding window

C.

a five-minute Tumbling window

D.

a five-minute Hopping window that has one-minute hop

C.

a five-minute Tumbling window

Explanation:
Tumbling window functions are used to segment a data stream into distinct time segments
and perform a function against them, such as the example below. The key differentiators of
a Tumbling window are that they repeat, do not overlap, and an event cannot belong to
more than one tumbling window.
References:
https://docs.microsoft.com/en-us/azure/stream-analytics/stream-analytics-window-functions

DP-203 Practice Test