7.3 Batch Ingestion with COPY INTO, CTAS, & Multipart Files

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.

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);