Exam DP-203 All QuestionsBrowse all questions from this exam
Go to Exam
Question 359

DRAG DROP -

You need to ensure that the Twitter feed data can be analyzed in the dedicated SQL pool. The solution must meet the customer sentiment analytics requirements.

Which three Transact-SQL DDL commands should you run in sequence? To answer, move the appropriate commands from the list of commands to the answer area and arrange them in the correct order.

NOTE: More than one order of answer choices is correct. You will receive credit for any of the correct orders you select.

Select and Place:

    Correct Answer:

    Scenario: 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.

    Box 1: CREATE EXTERNAL DATA SOURCE

    External data sources are used to connect to storage accounts.

    Box 2: CREATE EXTERNAL FILE FORMAT

    CREATE EXTERNAL FILE FORMAT creates an external file format object that defines external data stored in Azure Blob Storage or Azure Data Lake Storage.

    Creating an external file format is a prerequisite for creating an external table.

    Box 3: CREATE EXTERNAL TABLE AS SELECT

    When used in conjunction with the CREATE TABLE AS SELECT statement, selecting from an external table imports data into a table within the SQL pool. In addition to the COPY statement, external tables are useful for loading data.

    Incorrect Answers:

    CREATE EXTERNAL TABLE -

    The CREATE EXTERNAL TABLE command creates an external table for Synapse SQL to access data stored in Azure Blob Storage or Azure Data Lake Storage.

    Reference:

    https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/develop-tables-external-tables

Discussion
AzureJobsTillRetire

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

JasonVu

CETAS is not available in dedicated SQL pool

AzureJobsTillRetire

Please see below. CREATE TABLE AS SELECT (Azure Synapse Analytics) https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-as-select-azure-sql-data-warehouse?view=aps-pdw-2016-au7

AzureJobsTillRetire

Also this one. CREATE EXTERNAL TABLE AS SELECT (Transact-SQL) Applies to: SQL Server 2022 (16.x) and later, Azure Synapse Analytics, Analytics Platform System (PDW) https://learn.microsoft.com/en-us/sql/t-sql/statements/create-external-table-as-select-transact-sql?view=aps-pdw-2016-au7

Ram9198

https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/develop-tables-external-tables?tabs=hadoop external tables are supported in both SQL pools

vrodriguesp

are you sure we can create EXTERNAL DATA SOURCE without DATABSE SCOPED CREDENTIAL?

JG1984

It is not necessary if the users are already authenticated by using their own Azure AD credentials.

juanlu46

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

OldSchool

Correct

scarycat

Scoped Database Credencial is a DCL command, not DDL

BPW

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

Jerrie86

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

JitBiswas

You are right. The question is asking to "read" the tweeter feed stored as parquet file in ADLS via PolyBase. This is supported with CREATE EXTERNAL TABLE - which in turn reads data from ADLS. Please refer https://learn.microsoft.com/en-us/sql/t-sql/statements/create-external-table-transact-sql?view=sql-server-ver16&tabs=dedicated It is mentioned - "This command creates an external table for PolyBase to access data stored in a Hadoop cluster or Azure Blob Storage PolyBase external table that references data stored in a Hadoop cluster or Azure Blob Storage."

patjoo

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

Azure_2023

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

Ram9198

https://learn.microsoft.com/en-us/answers/questions/739341/rowlevelsecurity-on-external-table. RLS is not supported on an external table, then how CETAS be an answer

MartianNC

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

evangelist

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

Nadine_nm

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

Abdulwahab1983

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.

kkk5566

DS,format,CETAS

Ram9198

Answer should CET - RLS is supported on external tables and you do not need CETAS to implement RLS refer https://learn.microsoft.com/en-us/sql/relational-databases/security/row-level-security?view=sql-server-ver16

Matt2000

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

youngbug

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.

bigw

why use CETAS instead of Create External Table?

Pais

https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-as-select-azure-sql-data-warehouse?toc=%2Fazure%2Fsynapse-analytics%2Fsql-data-warehouse%2Ftoc.json&bc=%2Fazure%2Fsynapse-analytics%2Fsql-data-warehouse%2Fbreadcrumb%2Ftoc.json&view=azure-sqldw-latest&preserve-view=true#examples-using-ctas-to-replace-sql-server-code

JasonVu

your link points to CTAS, which is a different topic

Igor85

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