Free Data Engineering on Microsoft Azure (DP-203) Questions and Answers — Questions and Answers
Question 1: You are creating a container's folder structure for Azure Data Lake Storage Gen2. <br>
Several services, such as Azure Databricks and Azure Synapse Analytics serverless SQL pools, will be used by users to query data. Subject area will secure the data. The majority of inquiries will contain information from the current month or year. <br>
Which folder organization would you suggest supporting quick queries and streamlined folder security?
- /{DD}/{MM}/{YYYY}/{SubjectArea}/{DataSource}/{FileData}_{YYYY}_{MM}_{DD}.csv
- /{SubjectArea}/{DataSource}/{YYYY}/{MM}/{DD}/{FileData}_{YYYY}_{MM}_{DD}.csv (Correct answer)
- /{SubjectArea}/{DataSource}/{DD}/{MM}/{YYYY}/{FileData}_{YYYY}_{MM}_{DD}.csv
- /{YYYY}/{MM}/{DD}/{SubjectArea}/{DataSource}/{FileData}_{YYYY}_{MM}_{DD}.csv
Correct answer: /{SubjectArea}/{DataSource}/{YYYY}/{MM}/{DD}/{FileData}_{YYYY}_{MM}_{DD}.csv
The suggested folder organization, /{SubjectArea}/{DataSource}/{YYYY}/{MM}/{DD}/{FileData}..., is ideal for both quick queries and streamlined security. Placing `SubjectArea` and `DataSource` at the top allows for easy application of security controls (like POSIX ACLs or RBAC) at a high level, securing entire data domains. Partitioning data by `YYYY/MM/DD` (year, month, day) enables query engines to efficiently prune data, significantly speeding up queries that filter by time, especially for current month/year data, as specified in the requirements.
Question 2: You must create a specific SQL pool for Azure Synapse Analytics that complies with the following criteria: <br>
✑ can provide a record of an employee from a specific time. <br>
✑ keeps up with employee details. <br>
✑ reduces the complexity of the query. <br>
How should the personnel data be modeled
- as a SQL graph table
- as a degenerate dimension table
- as a Type 2 slowly changing dimension (SCD) table (Correct answer)
- as a temporal table
Correct answer: as a Type 2 slowly changing dimension (SCD) table
A Type 2 Slowly Changing Dimension (SCD) table is the appropriate modeling technique. This method tracks historical changes to dimension attributes, such as employee details, by creating a new record for each change while preserving the previous versions. This allows you to retrieve an employee's record as it appeared at any specific point in time, fulfilling the requirement to 'provide a record of an employee from a specific time' and 'keeps up with employee details' while simplifying historical analysis queries.
Question 3: Your Azure Data Lake Storage Gen2 account is corporate-wide. The only way to reach the data lake in Azure is through the virtual network VNET1. <br>
In Azure Synapse, you are creating a SQL pool that will take advantage of the data lake. <br>
Your business has a sales force. The Sales group in Azure Active Directory contains each and every member of the sales team. The Sales group is given access to the files in the data lake through POSIX controls.<br>
Every hour, you intend to load data into the SQL pool. <br>
The SQL pool has to be able to load the sales data from the data lake. <br>
What steps should you take?
- Create a managed identity
- Add the managed identity to the Sales group
- Use the managed identity as the credentials for the data load process
- All of the above (Correct answer)
Correct answer: All of the above
To securely load data from Azure Data Lake Storage Gen2 into an Azure Synapse SQL pool within a VNET, all the listed steps are necessary. First, create a managed identity (A) for the SQL pool, providing it with an Azure Active Directory identity. Next, add this managed identity to the 'Sales' group (B), which has POSIX access to the data lake files, granting the SQL pool the necessary permissions. Finally, use the managed identity as the credentials for the data load process (C) to ensure secure and authorized access to the data lake. Therefore, 'All of the above' is the correct solution.
Question 4: 100 TB of data are present in an Azure Data Lake Storage Gen2 container that you have. <br>
If there is a problem with the primary region, you must make sure that the data in the container is accessible for read workloads in a backup region. The answer must reduce expenses. <br>
What kind of data redundancy ought to you employ?
- locally-redundant storage (LRS)
- geo-redundant storage (GRS)
- read-access geo-redundant storage (RA-GRS) (Correct answer)
- zone-redundant storage (ZRS)
Correct answer: read-access geo-redundant storage (RA-GRS)
To ensure data accessibility for read workloads in a backup region during a primary region problem while reducing expenses, read-access geo-redundant storage (RA-GRS) is the ideal choice. RA-GRS replicates your data to a secondary region and provides read access to that secondary copy. This fulfills the requirement for cross-region data availability for reads at a lower cost than geo-zone-redundant storage (GZRS), which offers write access to the secondary region.
Question 5: You want to set up a Gen 2 storage account for Azure Data Lake. <br>
You must make sure that the data lake will still be accessible if a data center in the main Azure region fails. The answer must reduce expenses. <br>
Which replication type ought to be applied to the storage account?
- locally-redundant storage (LRS)
- zone-redundant storage (ZRS) (Correct answer)
- geo-zone-redundant storage (GZRS)
- geo-redundant storage (GRS)
Correct answer: zone-redundant storage (ZRS)
To ensure the data lake remains accessible if a data center in the main Azure region fails, while reducing expenses, zone-redundant storage (ZRS) should be applied. ZRS synchronously replicates your data across three Azure availability zones within a single region. This protects against data center outages within that region, providing high availability and durability at a lower cost than geo-redundant options like GRS or GZRS, which replicate data across different regions.
Question 6: In a special SQL pool for Azure Synapse Analytics, you create a data warehouse. <br>
To modify data for use in inventory reports, analysts create an intricate SELECT query that includes numerous JOIN and CASE expressions. Depending on the report, the inventory reports will use the data and extra WHERE parameters. Once every day, the reports will be generated. <br>
You must put a plan in place to make the dataset accessible for reporting. The answer must reduce query times. <br>
What actions should you take?
- a replicated table
- result set caching
- an ordered clustered columnstore index
- a materialized view (Correct answer)
Correct answer: a materialized view
For complex SELECT queries that are run daily for reporting and need reduced query times, a materialized view is the most effective solution. A materialized view pre-computes and stores the results of a query, including complex JOINs and CASE expressions. When analysts query the materialized view, they access the pre-calculated data directly, significantly accelerating query performance. Since reports are generated daily, the view can be refreshed once a day, aligning with the update frequency and optimizing subsequent report generation.
Question 7: You have an Apache Spark pool called Pool1 in your WS1 Azure Synapse Analytics workspace. <br>
You want to set up a database in Pool1 called DB1. <br>
You must make sure that when tables are created in DB1, they become instantly accessible to the internal serverless SQL pool as external tables. <br>
Which format ought to be applied to the DB1 tables?
- Parquet (Correct answer)
- CSV
- ORC
- JSON
Correct answer: Parquet
To ensure tables created in an Apache Spark pool (Pool1) become instantly accessible to the internal serverless SQL pool as external tables, the tables should be in Parquet format. Parquet is a columnar storage format highly optimized for analytical queries and is natively supported by both Apache Spark and Azure Synapse serverless SQL pools. This native compatibility allows for seamless integration and direct querying of Spark-generated Parquet files by the serverless SQL pool without any additional conversion or complex setup.
