You have a file named File1.txt that has the following characteristics:-A header row-Tab delimited values-UNIX-style line endingsYou need to read File1.txt by using an Azure Synapse Analytics serverless SQL pool.Which query should you execute?
Answer(s): A
Use FIELDTERMINATOR ='\t' for tab.Use ROWTERMINATOR ='\0x0A ' for UNIX-style line endingsUse FIRSTROW= 2 for a header rowNote: Using Row TerminatorsThe row terminator can be the same character as the terminator for the last field. Generally, however, a distinct row terminator is useful. For example, to produce tabular output, terminate the last field in each row with the newline character (\n) and all other fields with the tab character (\t).If you want to output a line feed character only (LF) as the row terminator - as is typical on Unix and Linux computers - use hexadecimal notation to specify the LF row terminator. For example:bcp -r '0x0A'FIRSTROWFIRSTROW =first_row Specifies the number of the first row to load. The default is 1. This indicates the first row in the specified data file. The row numbers are determined by counting the row terminators. FIRSTROW is 1-based.
https://docs.microsoft.com/en-us/sql/relational-databases/import-export/specify-field-and-row-terminators-sql-serverhttps://docs.microsoft.com/en-us/sql/t-sql/functions/openrowset-transact-sql
HOTSPOT (Drag and Drop is not supported)You have a Power BI dataset that has the query dependencies shown in the following exhibit. Use the drop-down menus to select the answer choice that completes each statement based on the information presented in the graphic.NOTE: Each correct selection is worth one point.
Box 1: 3Power Query doesn't start at the first query and work down, it starts at the bottom (last) query and works backwards, so 3 tables from 1 will cause it to process that first source table 3 times.Incorrect:Not 0:Disabling Load doesn’t mean the query won’t be refreshed, it only means the query won’t be loaded into the memory. When you click on Refresh model in Power BI, or when a scheduled refresh happens even queries marked as Disable Load will be refreshed, but their data will be used as intermediate source for other queries instead of loading directly into the model.Box 2: Using Table.Buffer in the Orders queryTable.Buffer buffers a table in memory, isolating it from external changes during evaluation. Buffering is shallow. It forces the evaluation of any scalar cell values, but leaves non-scalar values (records, lists, tables, and so on) as-is.Note that using this function might or might not make your queries run faster. In some cases, it can make your queries run more slowly due to the added cost of reading all the data and storing it in memory, as well as the fact that buffering prevents downstream folding.Example 1Load all the rows of a SQL table into memory, so that any downstream operations will no longer be able to query the SQL server.Usagelet Source = Sql.Database("SomeSQLServer", "MyDb"), MyTable = Source{[Item="MyTable"]}[Data], BufferMyTable = Table.Buffer(dbo_MyTable)in BufferMyTableOutputtable
https://radacad.com/performance-tip-for-power-bi-enable-load-sucks-memory-uphttps://docs.microsoft.com/en-us/powerquery-m/table-buffer
You are creating an external table by using an Apache Spark pool in Azure Synapse Analytics. The table will contain more than 20 million rows partitioned by date. The table will be shared with the SQL engines.You need to minimize how long it takes for a serverless SQL pool to execute a query data against the table.In which file format should you recommend storing the table data?
Answer(s): D
Prepare files for queryingIf possible, you can prepare files for better performance:* Convert large CSV and JSON files to Parquet. Parquet is a columnar format. Because it's compressed, its file sizes are smaller than CSV or JSON files that contain the same data. Serverless SQL pool skips the columns and rows that aren't needed in a query if you're reading Parquet files. Serverless SQL pool needs less time and fewer storage requests to read it.Incorrect:Not B: Delta is storing the data as parquet, just has an additional layer over it with advanced features, providing history of events, (transaction log) and more flexibility on changing the content like, update, delete and merge capabilities. This link delta explains quite good how the files organized.One drawback that it can get very fragmented on lots of updates, which could be harmful for performance.Use delta, just for the advanced features. It is very handy if there is a scenario where the data is updating over time, not just appending. Especially nice feature that you can read the delta tables as of a given point in time they existed.
https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/best-practices-serverless-sql-poolhttps://stackoverflow.com/questions/65320949/parquet-vs-delta-format-in-azure-data-lake-gen-2-store
You are running a diagnostic against a query as shown in the following exhibit. What can you identify from the diagnostics query?
Understanding folding with Query DiagnosticsOne of the most common reasons to use Query Diagnostics is to have a better understanding of what operations were 'pushed down' by Power Query to be performed by the back-end data source, which is also known as 'folding'. If we want to see what folded, we can look at what is the 'most specific' query, or queries, that get sent to the back-end data source. We can look at this for both ODATA and SQL.
https://docs.microsoft.com/en-us/power-query/querydiagnosticsfolding
Post your Comments and Discuss Microsoft DP-500 exam with other Community members:
Summer commented on July 28, 2024 Wonderful site. It helped me pass my exam. Way to go guys! UNITED STATES upvote
Siyya commented on January 19, 2024 might help me to prepare for the exam Anonymous upvote
siyaa commented on January 19, 2024 helped me understand the material better. Anonymous upvote
Bunny commented on June 19, 2023 Good Content Anonymous upvote
Demetrius commented on June 01, 2023 Important and useful Anonymous upvote
Kartoos commented on April 06, 2023 The practice exam was an important part of my preparation and helped me understand the material better. FRANCE upvote
Our website is free, but we have to fight against bots and content theft. We're sorry for the inconvenience caused by these security measures. You can access the rest of the DP-500 content, but please register or login to continue.