Exam DP-203 topic 5 question 2 discussion

Given answers are correct Box 1: CREATE EXTERNAL DATA SOURCE Box 2: CREATE EXTERNAL FILE FORMAT Box 3: CREATE EXTERNAL TABLE AS SELECT Requirements: Allow Contoso users to use PolyBase in an Azure Synapse Analytics dedicated SQL pool to query the content of the data records that host the Twitter feeds. Data must be protected by using row-level security (RLS). The users must be authenticated by using their own Azure AD credentials. Why CREAT DATABSE SCOPED CREDENTIAL is not required? Requirement: The users must be authenticated by using their own Azure AD credentials Why not CREATE EXTERNAL TABLE? Requirement: Allow Contoso users to use PolyBase ... to query ... PolyBase has limitations. CREATE EXTERNAL TABLE AS SELECT stored the data within the SQL pool and avoids those limitations. https://learn.microsoft.com/en-us/sql/relational-databases/polybase/polybase-versioned-feature-summary?view=sql-server-ver16

1. Scoped Database Credencial 2. External Data Source 3 External File Format

Here is the correct order: CREATE EXTERNAL DATA SOURCE CREATE EXTERNAL FILE FORMAT CREATE EXTERNAL TABLE

I think that the correct order is : create external data source create external file format create external table we don't need to create database scoped credential, since the users are already using an AAD to authenticate to the storage, if there were no mention of AAD usage then we should have created the scoped credential to specify a type of authentication Also here there is no mention of applying transformation on the data, we are only required to read the tweeter data, so create table an external table, CETAS is used to create an external table by exporting the result of a SELECT statement to an external data source, which is not the case here

Create external data source. Create external file format. Use CETAS statement https://learn.microsoft.com/en-us/training/modules/use-azure-synapse-serverless-sql-pools-for-transforming-data-lake/2-transform-data-using-create-external-table-select-statement

twitter feeds are going to be stored in azure storage which also going to need data life cycle management. If we are not storing the data in the dedicated sql pool table then we do not use CETAS we only create an external table to query the data in the azure storage.

DS,format,CETAS

According to Microsoft documentation: You can create external tables in Synapse SQL pools via the following steps: CREATE EXTERNAL DATA SOURCE to reference an external Azure storage and specify the credential that should be used to access the storage. CREATE EXTERNAL FILE FORMAT to describe format of CSV or Parquet files. CREATE EXTERNAL TABLE on top of the files placed on the data source with the same file format. https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/develop-tables-external-tables?tabs=hadoop#external-tables-in-dedicated-sql-pool-and-serverless-sql-pool

Concerning not needing CREATE DATABASE SCOPED CREDENTIAL for CREATE EXTERNAL DATA SOURCE: "External data source without credential can access public storage account or use the caller's Azure AD identity to access files on Azure storage." Ref: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-external-data-source-transact-sql?view=azure-sqldw-latest&tabs=dedicated

Box 1: CREATE EXTERNAL DATA SOURCE Box 2: CREATE EXTERNAL FILE FORMAT Box 3: CREATE EXTERNAL TABLE

The reason you use CTAS is that you must implement row level security.

Starting with SQL Server 2022 (16.x), Create External Table as Select (CETAS) is supported to create an external table and then export, in parallel, the result of a Transact-SQL SELECT statement to Azure Data Lake Storage (ADLS) Gen2, Azure Storage Account V2, and S3-compatible object storage. So shouldnt third be Create External TABLE ? We dont want to write data to ADLS. We want to read. https://learn.microsoft.com/en-us/sql/t-sql/statements/create-external-table-as-select-transact-sql?view=azure-sqldw-latest&preserve-view=true

PolyBase is a technology that accesses external data stored in Azure Blob storage or Azure Data Lake Store via the T-SQL language. So no need to copy table into Dedicated SQL Pool.

why use CETAS instead of Create External Table?

CREATE DATABASE SCOPED CREDENTIALS should be run before all other steps in the given answer

External file format is required when external table needs to refer to Hadoop files