Exam DP-203 topic 1 question 2 discussion

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

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

A is correct dbo is default schema for a table.

The clue is in the name apache cluster database. The database is mytestdb . Basically you can create a table with <db>.<tablename> . it will default to the dbo schema

Since there's no configuration in the given scenario to link the Spark-managed Parquet table to the serverless SQL pool, an error will occur when attempting to execute the SQL query.

I think A is the right answer. Firstly, dbo is added automatically (see examples in documentation). Furthermore, even though the created table in the spark pool will be saved with the name in lower case (as is stated in the documentation), SQL serverless pool in Synapse is case insensitive per default wehn it comes to table names and column names.

I believe A is correct because the selection query occurs after the insertion operation.

dbo should not be there.

option A is correct tested it , there is no issue with the case , since the table and db names are saved in the lowercase , what ever case we use in the query azure / sql engine convert that into lower case before performing the query . 2. issue with .dbo the table is created in the spark pool , the table is store in the hive metadata . so without using the .dbo (schema layer ) the serverless sql pool , cannot access the table and gives error . since we are using mytestdb.dbo.myparquetTable --> the serverless poll serches for the table in both its schema layer and hive schema layer giving the output 24 . Also , once a table is created in spark pool , it can be accessed in serverless sql Pool without any issues; there is no need for an external table to be created .

The query will fail because the serverless SQL pool cannot directly access Spark-managed tables without additional configuration. Use an external table or expose the data via a view to make it accessible.

The example here shows https://learn.microsoft.com/en-us/azure/synapse-analytics/metadata/table#create-an-external-table-in-spark-and-query-from-serverless-sql-pool

Selected Answer: A

Option A: dbo is the default schema where the object gets created in synapse if not specified explicitly.

Does the order of insertion doesn't matter here? As the column order is different in the table than the one used in insertion.

24 is correct answer Link : https://learn.microsoft.com/en-us/azure/synapse-analytics/metadata/table#create-a-managed-table-in-spark-and-query-from-serverless-sql-pool

As per ChatGPT, The query will fail with an error message as the table myParquetTable is created using Spark and the USING Parquet option, which means it is stored in a Parquet file format. Serverless SQL pool does not support querying Parquet files directly, so it cannot query myParquetTable in its current form. To make the table accessible from the serverless SQL pool, you need to create an external table that references the Parquet file. Then, you can query the external table instead. Assuming you have created an external table named myExternalParquetTable that references the Parquet file containing the data in myParquetTable, the query to select EmployeeID where EmployeeName is 'Alice' would be: SELECT EmployeeID FROM myExternalParquetTable WHERE EmployeeName = 'Alice';

The query will fail with an error because mytestdb.myParquetTable is a Spark table, not a SQL table. When you created the table using Spark, you used the Spark SQL syntax, and the table is stored in the Spark engine's metadata. Serverless SQL pool in Azure Synapse Analytics cannot directly query Spark tables; it can only query SQL tables. If you want to query the data stored in the mytestdb.myParquetTable table using a serverless SQL pool, you need to create an external table that maps to the same Parquet file. You can do this by using the CREATE EXTERNAL TABLE statement in a SQL pool.