The short answer
In early versions of Azure SQL Data Warehouse, loading external files from Azure Data Lake required configuring PolyBase. PolyBase required creating external data sources, database-scoped credentials, and external file formats before running CREATE TABLE AS SELECT (CTAS) scripts.
Microsoft introduced the COPY INTO statement to provide a simpler, higher-throughput ingestion mechanism.
COPY INTO dbo.raw_orders
FROM 'https://mystorageaccount.dfs.core.windows.net/lake/curated/orders/*.parquet'
WITH (
FILE_TYPE = 'PARQUET',
CREDENTIAL = (IDENTITY = 'Managed Identity'),
AUTO_CREATE_TABLE = 'OFF'
);Advantages of COPY INTO over legacy methods
- Native parallel streaming: Compute nodes connect directly to Azure Data Lake Storage Gen2, reading files concurrently across all 60 distributions.
- Flexible authentication: Supports Managed Identities, Microsoft Entra ID service principals, and Shared Access Signatures (SAS) without creating external database credential objects.
- Native file formats: Directly parses Parquet, ORC, and delimiter-separated text files without requiring manual schema serial-deserialization mappings.
- Fault tolerance with error files: You can specify
MAXERRORSandERRORFILElocations. If 10 rows out of 50 million contain corrupted strings, Synapse writes the bad rows to an error directory in your storage account and finishes loading the remaining 49.99 million rows successfully.
Never load production data using single-row INSERT statements in Synapse. Standard INSERTs route sequentially through the control node and generate heavy transaction log contention.