Study guide
7.3 Batch Ingestion with COPY INTO, CTAS, & Multipart Files
Microsoft Certified: Azure Databricks Data Engineer Associate DP-750
Track your progress as you read
Finished sections get a check mark.
1 Chapter 1: Azure Databricks Architecture & Compute Selection
1.1 DP-750 Exam Overview & Azure Databricks Lakehouse Architecture 1.2 Compute Types & Workload Matching 1.3 Cluster Access Modes, Standard/Dedicated, & Serverless Compute
2 Chapter 2: Compute Configuration, Performance & Cost Management
2.1 Autoscaling, Databricks Pools, & Node Type Selection 2.2 Photon Vectorized Engine & Cost Governance with Cluster Policies 2.3 Library Management: Workspace, Compute-Scoped, Notebook, & Init Scripts 2.4 Auto-Termination, Budget Alerts, & Tagging for Cost Allocation 2.5 Databricks Runtime & ML Runtime Selection, and Compute Access Permissions
3 Chapter 3: Unity Catalog Storage Infrastructure & Workspace Setup
3.1 Unity Catalog 3-Level Namespace & Metastore Architecture 3.2 Azure Managed Identity, Storage Credentials, & External Locations 3.3 Managed Tables, External Tables, & Unity Catalog Volumes 3.4 Unity Catalog Naming Conventions, Environment Isolation, & Data Granularity 3.5 Lakehouse Federation: Connections, Foreign Catalogs, & Query Pushdown
4 Chapter 4: Unity Catalog Privilege Model & Access Control
4.1 Hierarchical Privilege Model & Securable Objects 4.2 Account-Level Identities, SCIM Sync, & Group Management 4.3 Object Ownership, Ownership Transfer, & Administrative Delegation
5 Chapter 5: Advanced Data Security, Masking & ABAC
5.1 Dynamic Row Filters & Column Masking Implementation 5.2 Attribute-Based Access Control (ABAC) with Unity Catalog Tags 5.3 Data Isolation across Workspaces & Multiple Metastore Patterns 5.4 Secure Data Sharing within Azure Databricks
6 Chapter 6: Governance, Auditing, Lineage & Delta Sharing
6.1 Automated Data Lineage Tracking in Catalog Explorer 6.2 Unity Catalog System Tables: Audit, Billing, and Access Logs 6.3 Data Retention Policies, Time Travel Security, & Tag Governance 6.4 Delta Sharing Protocols: Open Sharing vs. Databricks-to-Databricks 6.5 Data Discovery: Table and Column Descriptions, and AI/BI Genie Instructions
7 Chapter 7: Ingestion with Auto Loader, Batch & Structured Streaming
7.1 Auto Loader (cloudFiles) Architecture, File Notification vs. Directory Listing 7.2 Auto Loader Schema Inference, Evolution, & Rescued Data Column 7.3 Batch Ingestion with COPY INTO, CTAS, & Multipart Files 7.4 Structured Streaming Fundamentals & Azure Event Hubs / Kafka Ingestion 7.5 Streaming Watermarking, Triggers, & Output Modes 7.6 Choosing an Ingestion Tool: Lakeflow Connect, Notebooks, & Azure Data Factory
8 Chapter 8: Delta Lake Core Mechanics, ACID & Table Maintenance
8.1 Delta Lake Architecture, Parquet Files, & the _delta_log Transaction Log 8.2 ACID Transactions, Concurrency Control, & Conflict Resolution 8.3 Compaction (OPTIMIZE), Z-Ordering, & Liquid Clustering (CLUSTER BY) 8.4 Data Retention, Snapshot Isolation, & VACUUM Operations 8.5 Delta Change Data Feed (CDF) & MERGE INTO Upsert Patterns 8.6 Choosing a Table Format: Delta, Parquet, CSV, JSON, & Apache Iceberg
9 Chapter 9: Data Cleansing, Transformation & Medallion Architecture
9.1 The Medallion Lakehouse Pattern: Bronze, Silver, and Gold Layer Design 9.2 Data Cleansing: Deduplication, Null Handling, & Type Casting in PySpark/SQL 9.3 Complex Transformations: Higher-Order Functions, Arrays, Structs, & JSON Parsing 9.4 Relational Joins, Window Functions, Set Operators, & Pivoting 9.5 Enforcing Data Quality: Check Constraints & Table Invariants 9.6 Data Profiling: Summary Statistics & Distribution Assessment
10 Chapter 10: Declarative Pipelines, Lakeflow Spark & Delta Live Tables
10.1 Declarative Pipeline Fundamentals: Streaming Tables vs. Materialized Views 10.2 Data Quality Expectations: expect, expect or drop, & expect or fail 10.3 Change Data Capture Ingestion with AUTO CDC / APPLY CHANGES INTO 10.4 Pipeline Execution Modes, Serverless DLT, & Event Logs
11 Chapter 11: Workflow Orchestration with Lakeflow Jobs
11.1 Multi-Task DAG Design in Lakeflow Jobs & Task Type Selection 11.2 Task Dependencies, Parameters, Task Values, & Dynamic Variables 11.3 Triggers (Scheduled, File Arrival, Continuous), Retries, & Timeouts 11.4 Notifications, Alerting (Email, Slack, Webhooks), & Failure Handling
12 Chapter 12: CI/CD, Git Integration & Databricks Asset Bundles
12.1 Databricks Git Folders & Source Control Integration 12.2 Databricks Asset Bundles (DAB) Architecture & databricks.yml Structure 12.3 Multi-Environment Deployment (Dev / Staging / Prod) & Target Overrides 12.4 Automated Testing, Unit Testing PySpark, & CI/CD Pipelines
13 Chapter 13: Workload Monitoring, Troubleshooting, & Performance Tuning
13.1 Spark UI Mastery: Query Execution DAG, Stages, Tasks, & Skew Analysis 13.2 Troubleshooting Common Failures: OOM, Disk Spill, Data Skew, & Network Timeouts 13.3 Performance Tuning: Adaptive Query Execution (AQE), Caching, & Broadcast Joins 13.4 Workspace Monitoring with Azure Monitor, Diagnostic Settings, & Log Analytics
7.3 Batch Ingestion with COPY INTO, CTAS, & Multipart Files
Key Takeaways Explain This Topic AI
COPY INTO is an idempotent, SQL-native batch ingestion command that loads new files from cloud storage paths or Unity Catalog volumes into an existing Delta Lake table.
Unlike Auto Loader which tracks state in external RocksDB checkpoint directories, COPY INTO maintains ingested file metadata directly within the Delta Lake table transaction log (_delta_log).
COPY INTO is optimized for predictable, low-to-moderate batch workloads (e.g., thousands of files per batch) orchestrated via Databricks SQL Warehouses or scheduled batch jobs.
CREATE TABLE AS SELECT (CTAS) performs full batch table creation or snapshot overwrites from external data, but does not provide incremental file tracking.
Format options and validation rules in COPY INTO allow fine-grained parsing of CSV, JSON, Parquet, Avro, ORC, and text files, including regex pattern matching and bad record capture.
Last updated: August 2026
7.3 Batch Ingestion with COPY INTO, CTAS, & Multipart Files
DP-750 Exam Focus: Master the SQL COPY INTO command for idempotent batch ingestion. Understand how COPY INTO tracks file metadata in the Delta transaction log, contrast its operational trade-offs against Auto Loader, understand CREATE TABLE AS SELECT (CTAS) and external tables, and configure format options for multipart, compressed files.
1. The COPY INTO Command: Architecture & Idempotence
While Auto Loader is the premier ingestion tool for high-frequency or large-scale file drops, data engineering pipelines often require pure SQL-based batch ingestion executing directly on Serverless Databricks SQL Warehouses without provisioning Spark streaming clusters.
COPY INTO is a declarative, idempotent SQL statement that loads data from an ADLS Gen2 folder or Unity Catalog Volume into an existing Delta Lake table. -- Core COPY INTO Syntax in Databricks SQL
- COPY INTO bronze.crm.customer_raw
- FROM 'abfss://landing@myadls.dfs.core.windows.net/crm_updates/'
- FILEFORMAT = CSV
- FORMAT_OPTIONS (
- 'header' = 'true',
- 'inferSchema' = 'true',
- 'delimiter' = '|'
- )
- COPY_OPTIONS (
- 'mergeSchema' = 'true'
- ); +-----------------------------------------------------------------------------------+
- | COPY INTO IDEMPOTENT PROCESSING |
- +-----------------------------------------------------------------------------------+
- | |
- | [ ADLS Gen2 Landing Path / Unity Catalog Volume ] |
- | /Volumes/bronze/crm/raw_files/ |
- | |-- file_2026_01.csv (Already ingested) |
- | |-- file_2026_02.csv (Already ingested) |
- | |-- file_2026_03.csv [NEW FILE] |
- | |
- | | |
- | v |
- | [ COPY INTO Execution Engine ] |
- | | |
- | +--------------------------+--------------------------+ |
- | | (Inspects Ingested File List) | |
- | v v |
- | [ Delta Table Transaction Log ] [ Ingest New Data ] |
- | _delta_log/00000000000000000042.json file_2026_03.csv |
- | (Contains Commit Metadata of processed files) | |
- | | | |
- | +-------------------------> + <-----------------------+ |
- | | |
- | v |
- | [ Appended to Target Delta Table ] |
- | (Skips file_01 and file_02 automatically) |
- +-----------------------------------------------------------------------------------+
- How Idempotence Operates
Transaction Log Bookkeeping: Every time COPY INTO executes, it records the file names, paths, and modification timestamps of ingested files inside the Delta Lake table's transaction log ( _delta_log/ ).
Skipping Previously Loaded Files: On subsequent runs targeting the same source directory, COPY INTO cross-references the storage container contents with the transaction log metadata. Any file that has already been loaded is automatically skipped.
Zero External State: Unlike Auto Loader or custom Spark scripts, COPY INTO does not maintain an external checkpoint directory. State is 100% self-contained within the target Delta table.
Forced Reprocessing ( 'force' = 'true' ): If historical files need to be reloaded (e.g., following data corruption or upstream correction), the engineer specifies COPY_OPTIONS ('force' = 'true') , which re-ingests all matching files regardless of prior load history.
2. Auto Loader vs. COPY INTO : The DP-750 Decision Matrix
A critical competency on the DP-750 exam is choosing between Auto Loader ( cloudFiles ) and COPY INTO based on workload volume, latency requirements, compute architecture, and operational complexity.
- Architectural Dimension COPY INTO Auto Loader ( cloudFiles )
- Primary Interface Pure ANSI SQL Python / PySpark / Scala / SQL (via DLT)
Compute Engine Databricks SQL Warehouses & All-Purpose/Job Clusters All-Purpose & Job Clusters (Photon / Spark)
- Underlying Runtime Batch SQL Engine Apache Spark Structured Streaming
- File Volume Scalability Up to thousands of files per batch Millions to billions of files
- State Management Internal Delta transaction log ( _delta_log ) External checkpoint directory + RocksDB state
- File Notification Support No (Directory listing only) Yes (Azure Event Grid + Azure Queue Storage)
- Schema Evolution Supports mergeSchema = true addNewColumns , failOnNewColumns , rescue , none
- Data Rescue Column Not supported (uses badRecordsPath ) Native _rescued_data column support
- Cost Profile Pay-per-query on Serverless SQL Warehouses Continuous streaming or scheduled micro-batch
Best For Predictable, scheduled daily/hourly batch SQL loads High-frequency, massive-scale, continuous or backlogged ingestion
Exam Rule of Thumb:
Choose COPY INTO when you need a simple, idempotent SQL command running on a Databricks SQL Warehouse for regular batch loads with thousands of files.
Choose Auto Loader when ingesting millions of files , requiring Azure Event Grid notifications , dynamic rescued data , or sub-minute streaming latency.
3. Format Options, Pattern Matching, & Bad Record Handling
COPY INTO supports rich configuration parameters to parse varied file formats and filter landing directories.
Regex Pattern Filtering
When a landing directory contains heterogeneous files (e.g., temporary lock files, metadata manifests, and data files), use PATTERN to filter matching files: -- Ingest only CSV files matching the naming pattern 'sales_2026_*.csv'
- COPY INTO bronze.sales.raw_invoices
- FROM '/Volumes/raw_catalog/sales/landing_volume/'
- FILEFORMAT = CSV
- PATTERN = 'sales_2026_[0-9]{2}\\.csv'
- FORMAT_OPTIONS (
- 'header' = 'true',
- 'inferSchema' = 'true'
- );
- Handling Bad Records in Batch Ingestion
When ingesting corrupt CSV or JSON files where schema violations prevent parsing, COPY INTO can isolate malformed rows into an external error directory using badRecordsPath : -- Redirect corrupt records to an isolated storage path for audit
- COPY INTO bronze.telemetry.events
- FROM 'abfss://raw@myadls.dfs.core.windows.net/iot_events/'
- FILEFORMAT = JSON
- COPY_OPTIONS (
- 'badRecordsPath' = 'abfss://quarantine@myadls.dfs.core.windows.net/bad_records/events/'
- );
- 4. CREATE TABLE AS SELECT (CTAS) & External Tables
In addition to incremental commands, batch pipelines utilize CREATE TABLE AS SELECT (CTAS) and INSERT INTO for batch data initialization.
CTAS Characteristics
One-Time Load / Full Overwrite: CTAS evaluates a query against a source (e.g., Parquet files or existing tables) and instantiates a new Delta table populated with the result set.
No Incremental Tracking: CTAS has no awareness of which files have previously been read. Executing CREATE OR REPLACE TABLE AS SELECT re-reads all files matching the source path from scratch.
Schema Generation: CTAS automatically derives the Delta table schema from the query projection. -- Example: One-Time Snapshot Ingestion via CTAS
- CREATE OR REPLACE TABLE bronze.legacy.archive_2025 AS
- SELECT
- id,
- event_type,
- cast(event_timestamp AS TIMESTAMP) AS event_timestamp,
- payload
- FROM parquet.`abfss://historical@myadls.dfs.core.windows.net/2025_archive/`;
- Unity Catalog Managed vs. External Tables in Batch Ingestion
Managed Tables ( CREATE TABLE without LOCATION ): Unity Catalog manages both the metadata in the metastore and the underlying Delta files inside the catalog/schema's managed storage root. When you DROP a managed table, the underlying Parquet files in ADLS Gen2 are permanently deleted after the retention window.
External Tables ( CREATE TABLE ... LOCATION 'abfss://...' ): Created over pre-existing ADLS Gen2 directories via Unity Catalog External Locations. When you DROP an external table, only the metadata registration is removed from Unity Catalog; the physical data files remain untouched in cloud storage.
5. Multipart Files & Compression Considerations
In enterprise batch pipelines, source systems often emit compressed multipart archives.
File Sizing & Compression Guidelines
Splitability: Uncompressed Parquet, ORC, and Avro files are splittable across Spark worker threads. However, compressed text files (e.g., gzip -compressed CSVs) are non-splittable ; a single worker core must decompress the entire file sequentially.
Target File Size: For optimal batch ingestion throughput, configure upstream producers to emit files between 100 MB and 500 MB .
Small File Ingestion: Ingesting millions of tiny KB-sized files via batch COPY INTO causes severe metadata overhead. When small file drops are unavoidable, data engineers should follow batch ingestion with Delta table file compaction: -- Compact small files into optimal 1 GB Parquet files post-ingest
- OPTIMIZE bronze.sales.raw_invoices
- ZORDER BY (order_date, customer_id);