3.2.5. Pipeline Ingestion and Continuous Integration

💡 First Principle: Pipelines orchestrate data movement from sources to destinations. The Copy Data activity handles basic movement; more complex scenarios require additional activities. For bulk loading into warehouses, the T-SQL COPY statement dramatically outperforms row-by-row INSERTs.

Copy Data Activity

• Purpose: Move data from source to destination
• Capabilities:
  • Supports 100+ connectors
  • Wildcard file paths in the source (e.g., sales_*.csv) to read many files in one Copy activity
  • Source = where data is read; sink = the destination it's written to. A file with the same name in a file sink is overwritten—there is no skip-if-exists setting, so filter out existing files first (e.g., Get Metadata on the destination)
  • Basic column mapping; use explicit mapping when source and destination columns differ
  • Schema drift handling
  • File metadata (binary copy to Lakehouse Files): Copy behavior only shapes folders (Flatten hierarchy, Merge files, Preserve hierarchy); to keep each source file's last-modified time, add a destination Metadata entry with the reserved value $LASTMODIFIED
• Limitations: No complex transformations (use Dataflow Gen2 or Notebook)

COPY Statement (T-SQL)

• Purpose: Bulk load data into warehouse tables
• Advantages:
  • High performance for large files
  • Supports wildcards for multiple files
  • Minimal transaction overhead

-- Load multiple Parquet files using wildcard
COPY INTO dbo.Sales
FROM 'https://storage.blob.core.windows.net/data/sales/*.parquet'
WITH (
    FILE_TYPE = 'PARQUET'
);

⚠️ Exam Trap: Using individual INSERT statements for bulk loading creates excessive transaction overhead. The COPY statement is optimized for bulk operations—questions about "loading millions of rows" or "loading multiple files" typically require COPY, not INSERT.

COPY INTO options worth knowing: the target table must already exist. For CSV use FILE_TYPE = 'CSV', FIELDTERMINATOR (delimiter), FIRSTROW = 2 (skip header) and FIELDQUOTE. For CSV and JSONL (not Parquet), MAXERRORS plus ERRORFILE write rejected rows to an error location instead of failing the whole load. BULK INSERT is also supported for compatibility with SQL Server code (in a warehouse it maps to COPY INTO behavior), but COPY INTO is the documented highest-throughput statement; LOAD DATA and Spark-style OPTIONS(header=true) are not T-SQL.