exam questions

Exam DP-203 All Questions

View all questions & answers for the DP-203 exam

Exam DP-203 topic 1 question 4 discussion

Actual exam question from Microsoft's DP-203
Question #: 4
Topic #: 1
[All DP-203 Questions]

You have files and folders in Azure Data Lake Storage Gen2 for an Azure Synapse workspace as shown in the following exhibit.

You create an external table named ExtTable that has LOCATION='/topfolder/'.
When you query ExtTable by using an Azure Synapse Analytics serverless SQL pool, which files are returned?

  • A. File2.csv and File3.csv only
  • B. File1.csv and File4.csv only
  • C. File1.csv, File2.csv, File3.csv, and File4.csv
  • D. File1.csv only
Show Suggested Answer Hide Answer
Suggested Answer: B 🗳️

Comments

Chosen Answer:
This is a voting comment (?). It is better to Upvote an existing comment if you don't have anything to add.
Switch to a voting comment New
Chillem1900
Highly Voted 5 months, 4 weeks ago
I believe the answer should be B. In case of a serverless pool a wildcard should be added to the location. https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/develop-tables-external-tables?tabs=hadoop#arguments-create-external-table
upvoted 124 times
anthony854
10 months ago
Serverless SQL pool can recursively traverse folders if you specify /** at the end of path. The following query will read all files from all folders and subfolders located in the csv/taxi folder. https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/query-folders-multiple-csv-files
upvoted 5 times
...
captainpike
3 years, 5 months ago
I tested and proove you right, the answer is B. Remind the question is referring to serverless SQL and not dedicated SQL pool. "Unlike Hadoop external tables, native external tables don't return subfolders unless you specify /** at the end of path. In this example, if LOCATION='/webdata/', a serverless SQL pool query, will return rows from mydata.txt. It won't return mydata2.txt and mydata3.txt because they're located in a subfolder. Hadoop tables will return all files within any subfolder."
upvoted 41 times
...
...
alain2
Highly Voted 5 months, 4 weeks ago
"Serverless SQL pool can recursively traverse folders only if you specify /** at the end of path." https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/query-folders-multiple-csv-files
upvoted 31 times
captainpike
3 years, 5 months ago
The answer is B however. I could not make "/**" to work. somebody?
upvoted 4 times
...
Preben
3 years, 9 months ago
When you are quoting from Microsoft documentation, do not ADD in words to the sentence. 'Only' is not used.
upvoted 22 times
...
...
AMJB
Most Recent 3 days, 1 hour ago
Selected Answer: C
If you specify LOCATION to be a folder, a PolyBase query that selects from the external table will retrieve files from the folder and all of its subfolders. Just like Hadoop, PolyBase doesn't return hidden folders. It also doesn't return files for which the file name begins with an underline (_) or a period (.).
upvoted 1 times
...
AMJB
3 days, 1 hour ago
Selected Answer: B
If you specify LOCATION to be a folder, a PolyBase query that selects from the external table will retrieve files from the folder and all of its subfolders. Just like Hadoop, PolyBase doesn't return hidden folders. It also doesn't return files for which the file name begins with an underline (_) or a period (.).
upvoted 1 times
...
Pey1nkh
1 month ago
Selected Answer: B
When you query ExtTable with LOCATION='/topfolder/', only the files directly inside /topfolder/ will be returned.
upvoted 1 times
...
krishna1303
2 months ago
Selected Answer: B
the answer should be B
upvoted 1 times
...
EmnCours
3 months, 3 weeks ago
Selected Answer: B
The answer is A, the answer C corresponds to LOCATION='/topfolder/**'
upvoted 1 times
...
seranvijay
4 months, 1 week ago
Selected Answer: B
https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/query-folders-multiple-csv-files#traverse-folders-recursively
upvoted 1 times
...
medsimus
5 months, 3 weeks ago
Correct answer , I test it and it works : CREATE EXTERNAL TABLE ext2 ([User] varchar(40), Feature varchar(40), Event varchar(40) , Time varchar(40)) WITH ( LOCATION = '/topfolder', DATA_SOURCE = ds2, FILE_FORMAT = csvFormat ) GO SELECT COUNT(*) as cnt FROM ext2 GO
upvoted 2 times
captainpike
3 years, 5 months ago
This is for serverless SQL. What you showed is for dedicated SQL, behaviour is different as explained here: "Unlike Hadoop external tables, native external tables don't return subfolders unless you specify /** at the end of path. In this example, if LOCATION='/webdata/', a serverless SQL pool query, will return rows from mydata.txt. It won't return mydata2.txt and mydata3.txt because they're located in a subfolder. Hadoop tables will return all files within any subfolder."
upvoted 2 times
...
...
poundmanluffy
5 months, 3 weeks ago
Selected Answer: B
Option is definitely "B" Below is the documentation given on MS Docs: Recursive data for external tables Unlike Hadoop external tables, native external tables don't return subfolders unless you specify /** at the end of path. In this example, if LOCATION='/webdata/', a serverless SQL pool query, will return rows from mydata.txt. It won't return mydata2.txt and mydata3.txt because they're located in a subfolder. Hadoop tables will return all files within any sub-folder.
upvoted 2 times
...
Raza12
5 months, 3 weeks ago
(Seems from this document that Answer is "C")In SQL Server, the CREATE EXTERNAL TABLE statement creates the path and folder if it doesn't already exist. You can then use INSERT INTO to export data from a local SQL Server table to the external data source. For more information, see PolyBase Queries. If you specify LOCATION to be a folder, a PolyBase query that selects from the external table will retrieve files from the folder and all of its subfolders. Just like Hadoop, PolyBase doesn't return hidden folders. It also doesn't return files for which the file name begins with an underline (_) or a period (.). In this example, if LOCATION='/webdata/', a PolyBase query will return rows from mydata.txt and mydata2.txt. It won't return mydata3.txt because it's a file in a hidden folder. And it won't return _hidden.txt because it's a hidden file. Recursive data for external tables https://docs.microsoft.com/en-us/sql/t-sql/statements/create-external-table-transact-sql?view=sql-server-ver15&tabs=dedicated
upvoted 1 times
...
rohitbinnani
5 months, 3 weeks ago
Selected Answer: B
"native external tables don't return subfolders unless you specify /** at the end of path. In this example, if LOCATION='/webdata/', a serverless SQL pool query, will return rows from mydata.txt. It won't return mydata2.txt and mydata3.txt because they're located in a subfolder. Hadoop tables will return all files within any sub-folder." https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/develop-tables-external-tables?tabs=hadoop#arguments-create-external-table
upvoted 2 times
...
lesardinha
8 months, 2 weeks ago
Selected Answer: B
I believe that the right answer is B, because from the root directory, there will be listed two files and two folders
upvoted 1 times
...
evangelist
8 months, 3 weeks ago
Selected Answer: B
CREATE EXTERNAL TABLE ExtTable ( EmployeeId INT, EmployeeName VARCHAR(100), EmployeeStartDate DATE ) WITH ( LOCATION = '/topfolder/**', DATA_SOURCE = your_data_source, FILE_FORMAT = your_file_format );
upvoted 1 times
...
evangelist
8 months, 3 weeks ago
Selected Answer: C
choice is C, I dont understand why such a simple question trigger such many discussions
upvoted 2 times
...
Nadine_nm
8 months, 3 weeks ago
I think the answer should be B, because there are only two possibilites that would allow to return all the subfolders : - A hadoop external table - The query contains LOCATION='/webdata/** if this is a native external tables, they don't return subfolders
upvoted 1 times
...
KeiNek
9 months ago
Selected Answer: B
https://learn.microsoft.com/en-us/sql/t-sql/statements/create-external-table-transact-sql?view=azure-sqldw-latest&preserve-view=true&tabs=dedicated#location--folder_or_filepath-1
upvoted 3 times
...
Community vote distribution
A (35%)
C (25%)
B (20%)
Other
Most Voted
A voting comment increases the vote count for the chosen answer by one.

Upvoting a comment with a selected answer will also increase the vote count towards that answer by one. So if you see a comment that you already agree with, you can upvote it instead of posting a new comment.

SaveCancel
Loading ...
exam
Someone Bought Contributor Access for:
SY0-701
London, 1 minute ago