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

You have an Azure Synapse workspace named MyWorkspace that contains an Apache Spark database named mytestdb.

You run the following command in an Azure Synapse Analytics Spark pool in MyWorkspace.

CREATE TABLE mytestdb.myParquetTable(

EmployeeID int,

EmployeeName string,

EmployeeStartDate date)

USING Parquet -

You then use Spark to insert a row into mytestdb.myParquetTable. The row contains the following data.

One minute later, you execute the following query from a serverless SQL pool in MyWorkspace.

SELECT EmployeeID -

FROM mytestdb.dbo.myParquetTable

WHERE EmployeeName = 'Alice';

What will be returned by the query?

    Correct Answer: B

    The correct answer is an error. The issue arises because the query references the table as 'mytestdb.dbo.myParquetTable', but the table was created as 'mytestdb.myParquetTable' without specifying a schema. In the absence of an explicit schema, the table will not be associated with the 'dbo' schema. Therefore, when the query attempts to access 'mytestdb.dbo.myParquetTable', it cannot find the table, resulting in an error. This discrepancy of schema reference causes the failure of the query execution.

Discussion
dmitriypoOption: B

Answer is B, but not because of the lowercase. The case has nothing to do with the error. If you look attentively, you will notice that we create table mytestdb.myParquetTable, but the select statement contains the reference to table mytestdb.dbo.myParquetTable (!!! - dbo). Here is the error message I got: Error: spark_catalog requires a single-part namespace, but got [mytestdb, dbo].

psicktrick

But if you look at the docs, that's exactly what has been done https://learn.microsoft.com/en-us/azure/synapse-analytics/metadata/table#expose-a-spark-table-in-sql:~:text=mytestdb.myparquettable%22)%3B-,Now%20you%20can%20read%20the%20data%20from%20your%20serverless%20SQL%20pool%20as%20follows%3A,-SQL

yogiazaad

Unless you use Either USING CVS or USING PARQUET While you create the table in the Spark. You will get error. If you use the below code and then query in SQL pool it works with out error. DROP TABLE IF EXISTS mytestdb.myParquetTable; CREATE TABLE IF NOT EXISTS mytestdb.myParquetTable( EmployeeID int, EmployeeName string, EmployeeStartDate date) USING PARQUET; INSERT INTO mytestdb.myParquetTable VALUES (24, 'Alice',DATE'2023-01-01'); SELECT * FROM mytestdb.myParquetTable

goldy29

Thanks @psicktrick for the link

Shaik_Shahul

i think you don't about sql server bro, Dbo means database object so it is not a issue for this the correct answer is A

__Tom

Dbo means database owner actually bro

devnginx

i think B option is the correct too

SenMia

kindly clarify, which can be the right option? the conversations are confusing. :( any explanations are appreciated. thank you!!

gerrie1979Option: A

I did a test, waited for one minute and tried the query in a serverless sql pool and received 24 as the result, so I don't understand that B has been voted so much because the answer is A) 24 without a doubt

maximilianogarcia6

Did you tried the same query that is presented here? with "mytestdb.dbo.myParquetTable"??

yogiazaad

The table and Column names are case insensitive.

Virul

I tried with all upper case, and it still return record for name Alice. Answer is A

cecbc1f

i test too and confirm that the right answer is A

AlongiOption: A

A is correct, is explained there: https://learn.microsoft.com/en-us/azure/synapse-analytics/metadata/table#expose-a-spark-table-in-sql

Nadine_nmOption: A

There is exactly the same example in MS docs, and the think you need to focus on, is that u are executing the query from a serverless SQL pool, so the query SELECT * FROM mytestdb.dbo.myparquettable WHERE name = 'Alice'; is correct

fahfouhi94Option: A

the same example here https://learn.microsoft.com/en-us/azure/synapse-analytics/metadata/table#create-a-managed-table-in-spark-and-query-from-serverless-sql-pool

DusicaOption: A

dbo is the default schema so table will be created in it. Answer is A

rajasumanOption: A

https://learn.microsoft.com/en-us/azure/synapse-analytics/metadata/table#examples

juanluisacebalOption: B

i think

ah1scpsj7kOption: A

A is correct, is explained there: ITexamslab https://learn.microsoft.com/en-us/azure/synapse-analytics/metadata/table#expose-a-spark-table-in-sql

learnwellOption: A

Correct answer is A. Clearly explained in the link https://learn.microsoft.com/en-us/azure/synapse-analytics/metadata/table#create-a-managed-table-in-spark-and-query-from-serverless-sql-pool

shivanishansOption: A

I believe the correct answer is option A as we can use a different schema which in this case is .dbo to access a database. Whether it be .db or .dbo it wouldn't make a difference(unless there is a specific configurational change in the environment and connection settings which hinders this process)

ageorgievaOption: B

I believe it is B.

slamcityOption: B

error becouse of reference to dbo in select

datanerdChenOption: A

Answer is A， check this out https://learn.microsoft.com/en-us/azure/synapse-analytics/metadata/table#expose-a-spark-table-in-sql

dgerokOption: B

EmployeeName string ???? Should be EmployeeName VARCHAR or NVARCHAR I think, the answer should be B

dgerokOption: B

there is no STRING format... VARCHAR or NVARCHAR should be used instead, while you create a table... So, the answer is B

Charley92Option: B

SELECT * FROM mytestdb.dbo.myparquettable WHERE name = 'Alice';