DP-203 Exam - Question 2

Question

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?

Examice · Accepted Answer

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.

dmitriypo · Answer

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].

gerrie1979 · Answer

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

Alongi · Answer

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

Dusica · Answer

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

fahfouhi94 · Answer

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

Nadine_nm · Answer

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

Charley92 · Answer

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

dgerok · Answer

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

dgerok · Answer

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

datanerdChen · Answer

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

slamcity · Answer

error becouse of reference to dbo in  select

ageorgieva · Answer

I believe it is B.

shivanishans · Answer

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)

learnwell · Answer

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

ah1scpsj7k · Answer

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

juanluisacebal · Answer

i think

rajasuman · Answer

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

DP-203 Exam - Question 2

Discussion