Exam DP-203 topic 1 question 4 discussion

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

"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

because we have not given wildcard expression

you do not need to use LOCATION='/topfolder/*' to read files in subfolders when using external tables in serverless SQL pools. You use wildcards like '/topfolder/*' when querying directly using OPENROWSET

Correct answer is 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 (.).

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 (.).

When you query ExtTable with LOCATION='/topfolder/', only the files directly inside /topfolder/ will be returned.

the answer should be B

The answer is A, the answer C corresponds to LOCATION='/topfolder/**'

https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/query-folders-multiple-csv-files#traverse-folders-recursively

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

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.

(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

"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

I believe that the right answer is B, because from the root directory, there will be listed two files and two folders

CREATE EXTERNAL TABLE ExtTable ( EmployeeId INT, EmployeeName VARCHAR(100), EmployeeStartDate DATE ) WITH ( LOCATION = '/topfolder/**', DATA_SOURCE = your_data_source, FILE_FORMAT = your_file_format );