3.1 Workspace Uploads & Cloud File Ingestion

3.1 Workspace Uploads & Cloud File Ingestion

Data ingestion is the gateway to analytics on the Databricks Data Intelligence Platform. Data analysts routinely encounter scenarios where raw datasets must be loaded into governed Delta Lake tables. Databricks offers multiple ingestion pathways tailored to file size, source location, and automation requirements. Understanding when and how to utilize Workspace UI uploads, Unity Catalog Volumes, and the SQL COPY INTO command is essential for building efficient, secure analytics pipelines.

---

Workspace UI Uploads & Unity Catalog Volumes

For ad-hoc analysis, business teams frequently receive standalone data files such as CSV spreadsheets, JSON extracts, or Parquet files. Databricks enables users to upload files directly into the lakehouse using the Databricks Workspace UI.

Unity Catalog Volumes vs. Legacy DBFS

Historically, file uploads landed in the Databricks File System (DBFS) root or DBFS mounts. Modern Databricks architectures replace legacy DBFS paths with Unity Catalog Volumes. A volume is a Unity Catalog object representing a logical volume of storage in a cloud object storage location. Volumes govern non-tabular datasets, including raw data files, media files, and model artifacts.

Volumes exist in two distinct types:

1. Managed Volumes: Storage locations managed entirely by Unity Catalog within the default storage location of the containing schema. When a managed volume is deleted, the underlying files are also deleted from cloud storage.
2. External Volumes: Storage locations mapped to an existing external cloud storage path (such as AWS S3, Azure ADLS Gen2, or Google Cloud Storage). Deleting an external volume removes the Unity Catalog object registration but preserves the underlying physical files in cloud storage.

Workspace UI Upload Limits

The Workspace UI provides an intuitive drag-and-drop interface for uploading files directly into Unity Catalog Volumes under paths following the structure /Volumes/catalog_name/schema_name/volume_name/filename. The Workspace UI supports uploading individual files up to 5 GB in size. For datasets exceeding 5 GB, or for automated ingestion workflows, file transfers must be executed using cloud CLI utilities, Delta Live Tables, or SQL ingestion commands.

---

Unity Catalog Governance for Cloud File Access

In enterprise environments, data analysts cannot use hardcoded cloud storage credentials—such as AWS Access Keys, Azure SAS tokens, or GCP Service Account keys—in SQL queries or notebooks. Unity Catalog enforces secure file access through a two-tiered security abstraction model:

1. Storage Credentials: An object that encapsulates a long-term cloud identity (such as an AWS IAM Role, Azure Managed Identity, or GCP Service Account key). Storage Credentials are created by metastore administrators and managed exclusively within Unity Catalog.
2. External Locations: A Unity Catalog object that combines a Storage Credential with a specific cloud storage path URI (e.g., s3://company-landing-bucket/raw_data/ or abfss://container@account.dfs.core.windows.net/landing/).

By granting analysts the READ ACCESSIBLE FILES (or READ FILES) privilege on an External Location or Volume, administrators permit users to access cloud storage files without exposing underlying storage keys.

---

API-Driven Data Intake

Beyond Workspace UI uploads and cloud-file paths, the official exam page lists API-driven intake as an ingestion method analysts should recognize.

Common API-driven patterns on Databricks:

External system --(REST/Files/Jobs API)--> Volume or cloud landing zone
                                      --> COPY INTO / Auto Loader --> Delta table (Unity Catalog)

How this differs from UI upload

• UI upload is interactive and capped (for example, practical browser upload size limits for ad-hoc analyst files).
• API-driven intake is automatable, repeatable, and suitable for partner systems or CI-style data drops.
• After landing files, analysts still apply the same governance: External Locations/Volumes, certified curated tables, and SQL cleaning before dashboard or Genie consumption.

Exam tip: if a prompt says a source system must push files without a human using the Workspace UI, the answer family is API-driven intake (often followed by Auto Loader or COPY INTO), not manual Upload.

Batch File Ingestion using SQL COPY INTO

When files continuously arrive in cloud storage or volumes, analysts use the COPY INTO SQL command for batch loading. COPY INTO is a native, SQL-only command designed to load data from cloud file locations directly into existing Delta Lake tables.

SQL Syntax and Structure

COPY INTO catalog_name.sales_schema.monthly_orders
FROM 's3://company-landing-bucket/sales/2026/05/'
FILEFORMAT = CSV
FORMAT_OPTIONS (
  'header' = 'true',
  'inferSchema' = 'true',
  'emptyValue' = ''
)
COPY_OPTIONS (
  'mergeSchema' = 'true',
  'force' = 'false'
);

Execution Options and Options Tuning

• FILEFORMAT: Specifies the format of source files. Supported formats include CSV, JSON, PARQUET, ORC, AVRO, BINARYFILE, and TEXT.
• FORMAT_OPTIONS: Configures format-specific parsing behaviors. For CSV files, common options include 'header' = 'true', 'delimiter' = ',', 'quote' = '"', and 'dateFormat' = 'yyyy-MM-dd'.
• COPY_OPTIONS: Controls loading behavior. Setting 'mergeSchema' = 'true' enables schema evolution, allowing the target Delta table to add new columns present in the source files. Setting 'force' = 'true' forces COPY INTO to re-load all files in the source path regardless of whether they were previously ingested.

---

Idempotency and Delta Lake Transaction Logs

A fundamental requirement for reliable data pipelines is idempotency—the property where executing an operation multiple times produces the exact same result as executing it once.

COPY INTO provides built-in idempotency. When COPY INTO executes, it records the filenames and modification timestamps of ingested files inside the target Delta Lake table's transaction log (_delta_log/).

Cloud Landing Folder:              Delta Table Transaction Log (_delta_log):
├── sales_2026_05_01.csv --------> Ingested (Commit 001)
├── sales_2026_05_02.csv --------> Ingested (Commit 002)
└── sales_2026_05_03.csv --------> [NEW] Ingested during re-run (Commit 003)

If an analyst re-executes COPY INTO against the same directory, the engine compares the folder contents against the table's transaction history and skips already ingested files. This prevents duplicate rows from polluting downstream reporting.

---

Comparing File Ingestion Approaches

Databricks provides multiple mechanisms for ingesting and interacting with cloud files. Analysts must select the appropriate pattern based on volume, frequency, and governance requirements:

---

Real-World Analyst Ingestion Scenario

Consider a financial analyst responsible for generating monthly revenue reports. Raw transaction exports arrive as CSV files in an S3 bucket managed by an external vendor.

1. Step 1: Access Verification: The administrator creates an External Location s3://vendor-finance-bucket/monthly_exports/ and grants the analyst READ ACCESSIBLE FILES.
2. Step 2: Initial Table Creation: The analyst defines an empty target Delta table in Unity Catalog:

   CREATE TABLE finance_catalog.revenue_db.raw_vendor_sales (
     transaction_id STRING,
     customer_id STRING,
     amount DECIMAL(10,2),
     transaction_timestamp TIMESTAMP
   ) USING DELTA;
   

3. Step 3: Batch Ingestion: The analyst schedules a weekly job executing COPY INTO:

   COPY INTO finance_catalog.revenue_db.raw_vendor_sales
   FROM 's3://vendor-finance-bucket/monthly_exports/'
   FILEFORMAT = CSV
   FORMAT_OPTIONS ('header' = 'true')
   COPY_OPTIONS ('mergeSchema' = 'true');
   

Because COPY INTO is idempotent, scheduling this query ensures newly arriving vendor CSV files are ingested weekly without duplicating historical sales data.