Exam DP-203 topic 2 question 42 discussion

how to use PolyBase when copy data from Synapse to file ? I don't have idea. Moreover PolyBase option is available only when the target is Synapse it should be P1: Set the partition option to "Dynamic range " P2: PolyBase regarding to P1 https://docs.microsoft.com/en-us/azure/data-factory/connector-azure-sql-data-warehouse?tabs=data-factory#parallel-copy-from-synapse-analytics Scenario: "Full load from large table, without physical partitions.." -> Suggested settings: Partition options: Dynamic range partition.

P1: Copy data from SQL to Data Lake. • Bulk insert and PolyBase are not a choice in Sink tab if target is Data Lake. So they are not correct. • Isolation level can be setup if SQL database is the source. Repeatable Read means that locks are placed on all data that is used in a query. Don't think it maximize parallelism and performance. • Set the Partition option to Dynamic range Can be setup if source is SQL in copy activity. And it maximizes parallelism and performance. So I select this option. P2: Copy data from Data Lake to SQL. It is for sure to select PolyBase.

P1: Set the Partition option to Dynamic range: 171 votes Set the Copy method to PolyBase: 26 votes Set the Copy method to Bulk insert: 3 votes Set the Isolation level to Repeatable read: 0 votes P2: Set the Copy method to PolyBase: 95 votes Set the Copy method to Bulk insert: 8 votes Set the Isolation level to Repeatable read: 0 votes Set the Partition option to Dynamic range: 1 vote

ChatGPT: To maximize parallelism and performance in your Azure Data Factory pipelines, configure the dataset settings as follows: For Pipeline P1 (copying from WS1 to Azure Data Lake Storage Gen2): Copy Method: Set to PolyBase. This method is optimized for large data loads from SQL pools to Azure Data Lake Storage. For Pipeline P2 (copying from Azure Data Lake Storage Gen2 to WS2): Copy Method: Set to Bulk Insert. This is efficient for loading data into SQL pools from Azure Data Lake Storage, especially with text-delimited files. These settings will help enhance performance by leveraging the most efficient data transfer methods for each scenario.

For the Azure Data Factory pipelines P1 and P2, the dataset settings for the copy activity should be configured as follows: P1: b. Set the Copy method to PolyBase PolyBase is a technology that accesses data outside of the database via the T-SQL language. It’s designed to leverage parallelism, which can lead to significant performance improvements when copying large amounts of data12. P2: a. Set the Copy method to Bulk insert Bulk insert is a process that can be used to import large amounts of data into a SQL Server table. It’s a highly efficient way to push data into a table, especially when dealing with text-delimited files12. Please note that the actual performance may vary depending on the specific requirements and the structure of your data12. Learn more 1 learn.microsoft.com 2 learn.microsoft.com 3 social.msdn.microsoft.com

Got this question today on the exam

P1: Dynamic range according to https://techcommunity.microsoft.com/t5/fasttrack-for-azure/leverage-copy-data-parallelism-with-dynamic-partitions-in-adf/ba-p/3692133

Correct, chatgpt For P1, where data is copied from a non-partitioned table in a SQL pool to Azure Data Lake Storage Gen2: - **Set the Copy method to PolyBase**: This is because PolyBase is designed to efficiently transfer large amounts of data to and from SQL-based data stores into Azure Data Lake Storage. For P2, which copies data from text-delimited files in Azure Data Lake Storage Gen2 to a non-partitioned table in a SQL pool: - **Set the Copy method to Bulk insert**: Bulk insert is an efficient way to load data from files into SQL tables, especially when dealing with non-partitioned tables where PolyBase might not be applicable or the most optimal choice.

P1 : set the partition option to dynamic range (see here : https://techcommunity.microsoft.com/t5/fasttrack-for-azure/leverage-copy-data-parallelism-with-dynamic-partitions-in-adf/ba-p/3692133) P2: Polybase give the best performance PolyBase loads data from UTF-8 and UTF-16 encoded delimited text files. PolyBase also loads from the Hadoop file formats RC File, ORC, and Parquet. PolyBase can also load data from Gzip and Snappy compressed files. PolyBase currently does not support extended ASCII, fixed-width format, and nested formats such as WinZip, JSON, and XML.

P1) Set the partition option to dynamic range p2) set the copy method to PolyBase

There's a really interesting video regarding PolyBase/COPY INTO here: https://microsoft.github.io/PartnerResources/skilling/modern-analytics-academy/vignettes/polybase-vs-copy This video indicates that PolyBase can actually be used to pull/push data from/to Azure Data Lake Storage to/from Azure Synapse Analytics Dedicated SQL Pool tables (via CTAS & CETAS statements).

P1 dynamic range, link is pretty clear: https://learn.microsoft.com/en-us/azure/data-factory/connector-azure-sql-data-warehouse?tabs=data-factory#parallel-copy-from-azure-synapse-analytics

for P1, you should set the copy method to Polybase, and for P2, you should set the copy method to Bulk. The reason is that Polybase is better suited for copying data between Azure Synapse Analytics and Azure Data Lake Storage Gen2, and can achieve better performance than Bulk copy in this scenario. On the other hand, Bulk copy is the fastest method for copying data from text-delimited files in Azure Data Lake Storage Gen2 to Azure Synapse Analytics. Setting the partition option to Dynamic range for both pipelines can help to maximize parallelism and performance by allowing the copy activity to split the data into multiple partitions based on the data range.

I tried to create a copy activity in adf and these were results: P1) Synapse to ADLS --> Source Partition option: None/Dynamic range Sink Copy behavior: Add dynamic content/None/Flatten hierarchy/Merge files/Preserve hierarchy P2) ADLS to Synapse --> Source Copy method: NA Sink Copy method: Copy command/PolyBase/Bulk insert/Upsert So I think correct answers should be: P1) Set the partition option to dynamic range p2) set the copy method to PolyBase

Both answer are polybase

I think you can put both as PolyBase. PolyBase is much faster and supports text delimited files as well now. https://docs.microsoft.com/en-us/azure/data-factory/connector-azure-sql-data-warehouse?tabs=data-factory#use-polybase-to-load-data-into-azure-synapse-analytics

Agree with marcin1212 it should be P1: Set the partition option to "Dynamic range " P2: PolyBase