Azure Synapse Analytics | Data Ingestion Patterns, Polybase, and Copy Command

Having right data ingestion framework and patterns are important considerations to have consistent data ready for analytics in timely manner, especially when you are dealing with large data volume and expect your data to grow every day.

In this video, you will learn about different data ingestion patterns and options available with Azure Synapse Analytics - dedicated SQL pool.

00:00:00 Introduction
00:00:28 ELT (Extract Transform and Load) vs ELT (Extract Load and Transform)
00:01:57 Single Gated Bulk Data Load
00:05:06 Parallel Data Load
00:07:01 Parallel Data Load - Polybase
00:09:42 Data Import - with CTAS
00:11:52 Data Export - with CETAS
00:14:58 Parallel Data Load - Copy Command
00:19:25 Demo - Bulk Data Load with bcp
00:26:55 Demo - Bulk Data Load with Polybase
00:53:07 Demo - Data Import (CTAS) vs. Data Export (CETAS)
00:57:32 Demo - Bulk Data Load with Copy Command
01:11:49 Demo - Incremental Data Refresh
01:17:29 Demo - Monitor Data Load Operations

Thank you for watching, in my next video, I am going to talk in detail about multi-layer security architecture available with Azure Synapse and how you can build modern data warehouse with confidence in Azure Synapse. Stay tuned.

GitHub Repo to download deck and script used in the video:
https://github.com/AasTrailblazers/AzureSynapse/tree/main/SQL%20pool

Data loading strategies for dedicated SQL pool in Azure Synapse Analytics
https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/design-elt-data-loading

Design a PolyBase data loading strategy for dedicated SQL pool in Azure Synapse Analytics
https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/load-data-overview

CREATE EXTERNAL TABLE
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-external-table-transact-sql?view=azure-sqldw-latest&preserve-view=true&tabs=dedicated

CETAS with Synapse SQL
https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/develop-tables-cetas

Bulk load data using the COPY statement
https://docs.microsoft.com/en-us/sql/t-sql/statements/copy-into-transact-sql?view=azure-sqldw-latest

What is the file splitting guidance for the COPY command loading CSV files?
https://docs.microsoft.com/en-us/sql/t-sql/statements/copy-into-transact-sql?view=azure-sqldw-lates #what-is-the-file-splitting-guidance-for-the-copy-command-loading-csv-files

How to maximize COPY load throughput with file splits
https://techcommunity.microsoft.com/t5/azure-synapse-analytics/how-to-maximize-copy-load-throughput-with-file-splits/ba-p/1314474

bcp Utility
https://docs.microsoft.com/en-us/sql/tools/bcp-utility?view=sql-server-ver15