Bulk load files into Databricks

Bulk load files that already exist in cloud storage, server storage, or a Databricks Volume into a Databricks Delta table. The flow auto-generates COPY INTO and supports INSERT, MERGE, and CDC MERGE actions.

When to use this flow

Use this flow when the data is already in files (typically CSV, JSON, Parquet, or Avro) and no transformation is needed before loading into Databricks. Common scenarios:

• Files dropped daily by a vendor into S3 / ADLS / GCS.
• Files produced by another flow (Etlworks or external) into a Databricks Volume.
• A periodic catch-up after a streaming CDC pause.

If you need transformation, use Any to Databricks instead.

How does this flow work?

1. Etlworks iterates over the files in the stage matching the configured pattern.
2. For each file, Etlworks issues COPY INTO against the destination Delta table (auto-created with USING DELTA if missing).
3. For MERGE, the file is loaded into a temp Delta table first, then merged into the destination using the configured strategy.
4. On success, processed files can be deleted, archived, or skipped on the next run.

Features

• File formats: CSV, JSON, Parquet, Avro.
• Stage types: S3, ADLS Gen2, GCS, server storage, Databricks Volumes (managed and external).
• Auto-generated COPY INTO with FORMAT_OPTIONS for delimiter, quote, header, line separator, date format, timestamp format.
• MERGE strategies: DELETE / INSERT, Native MERGE, Native MERGE with separate DELETE.
• Wildcard / regex file selection. Recursive subfolder scanning.
• Parallel loads across multiple destination tables.
• Skip-already-processed-files cache.
• Schema drift resilience via TEXT-only temp tables (see Schema drift).

What do I need before I start?

• A Databricks connection. See Get started with Databricks for setup.
• A stage connection (S3, ADLS Gen2, GCS, server storage, or Volume) and read permission for the user / service principal on that location.
• Files already present in the stage, or arriving on a schedule.

Step-by-step setup

Step 1. Create the stage connection

Pick one based on where your files live:

• Amazon S3 — for AWS-hosted workspaces.
• Azure Storage (ADLS Gen2) — for Azure workspaces.
• Google Cloud Storage — for GCP workspaces.
• Server storage — pointed at a Databricks Volume path (/Volumes/<catalog>/<schema>/<volume>/…) or a directory on the Etlworks host.

Step 2. Create the Databricks connection

See Get started with Databricks for connection setup. PAT for development, OAuth Service Principal for production.

Step 3. Create the format

Match the file format on disk: CSV, JSON, Parquet, or Avro.

Step 4. Create the flow

In Flows, click + and pick Bulk load files into Databricks.

Step 5. Configure the load transformation

Add a transformation per destination table. Set FROM to the stage connection, format, and the file name or wildcard. Set TO to the Databricks connection and destination table (use catalog.schema.table for Unity Catalog).

Step 6. Set parameters

Databricks-specific load parameters:

Override the auto-generated COPY INTO SQL — optional

By default, Etlworks generates the COPY INTO statement from the destination table, the stage connection, the source file format, and the settings above. Set Override COPY INTO SQL when you need something the generator does not produce — a projection or a SELECT list on the source, COPY_OPTIONS (for example force or mergeSchema), a Databricks feature the generator does not know about, or a hand-tuned statement for a specific pipeline.

When this field is set, Etlworks uses your statement verbatim (after resolving the tokens below) instead of the generated one. The Format options setting in the table above and the auto-injected WITH (CREDENTIAL ...) clause are both bypassed — include everything the load needs in your statement.

Available tokens

Reference these tokens in your statement. The runtime resolves them per file, per batch, per destination table.

Structure of the auto-generated statement

Useful as a starting template for the override. The generator produces this shape:

COPY INTO <target-table>
  FROM '<stage-uri>/{FILE_TO_LOAD}[.{EXT} | *.*]'
  [WITH (CREDENTIAL <name-or-inline>)]
  FILEFORMAT = <CSV | JSON | PARQUET | AVRO>
  [FORMAT_OPTIONS (...)]

Details:

• Target: {TEMP_TABLE} for MERGE strategies, {TABLE} otherwise.
• Stage URI: composed from the stage connection's transport (S3, Azure Data Lake Storage Gen2, GCS, or server storage / Databricks Volume), bucket / container, and folder.
• File pattern: {FILE_TO_LOAD}.{EXT} for a single file, or {FILE_TO_LOAD}*.* when the flow is loading a wildcard match, split files, or archive-wrapped files (ZIP / GZIP).
• Credential: injected automatically when the Storage Credential setting is set or the stage connection has embedded AWS access / secret keys or an Azure SAS token. Skipped when the workspace has a Unity Catalog External Location or a managed Volume covering the stage path.
• FILEFORMAT: derived from the stage file extension.
• FORMAT_OPTIONS: emitted only for CSV, composed from header, delimiter, quote, line separator, date format, and timestamp format. The Format options setting above overrides just this clause without replacing the whole statement — use it in place of Override COPY INTO SQL when only parser options need to change.

Examples

MERGE flow — load into the temp Delta table with a custom escape and date/time formats:

COPY INTO {TEMP_TABLE}
FROM 's3://etlworks-stage/loads/{FILE_TO_LOAD}'
FILEFORMAT = CSV
FORMAT_OPTIONS ('header' = 'true', 'delimiter' = ',', 'quote' = '"',
                'dateFormat' = 'yyyy-MM-dd',
                'timestampFormat' = 'yyyy-MM-dd HH:mm:ss',
                'escape' = '"')

INSERT flow — load Parquet directly into the destination:

COPY INTO {TABLE}
FROM 's3://etlworks-stage/loads/{FILE_TO_LOAD}.{EXT}'
FILEFORMAT = PARQUET

Advanced — load selected columns with a SELECT list and force re-loads:

COPY INTO {TABLE} (id, name, updated_at)
FROM (SELECT id, name, current_timestamp() AS updated_at
      FROM 's3://etlworks-stage/loads/{FILE_TO_LOAD}.{EXT}')
FILEFORMAT = PARQUET
COPY_OPTIONS ('force' = 'true')

Interaction with other settings

• Format options is bypassed. Include your custom FORMAT_OPTIONS in the override statement itself.
• Storage Credential is bypassed. Include a WITH (CREDENTIAL ...) clause in your statement if the load needs one; the generator's automatic credential injection does not run when the SQL is overridden.
• Before COPY SQL and After COPY SQL still run — they are separate statements executed against the Databricks connection around your COPY INTO, not part of it.
• MERGE strategies (DELETE / INSERT, Native MERGE, Native MERGE with separate DELETE) still run on top of the loaded temp table. Your override handles only the load-into-temp step, not the MERGE step itself. Use {TEMP_TABLE} as the target when the flow uses MERGE, and let Etlworks generate the MERGE statement that follows.

When to use Override COPY INTO SQL vs Format options

• Only parser options need to change (escape, multiLine, quote handling, header, delimiter, date / timestamp format) — use Format options. Safer and narrower; keeps the generated target, stage URI, and credential handling intact.
• The load needs a projection or SELECT list on the source — use Override COPY INTO SQL. The generator emits no SELECT list.
• The load needs COPY_OPTIONS (for example force, mergeSchema, or a custom copy-options map) — use Override COPY INTO SQL. The generator does not emit COPY_OPTIONS.
• A hand-tuned statement is required for a specific pipeline, or a Databricks feature the generator does not know about needs to appear in the SQL — use Override COPY INTO SQL.

Step 7. Optionally configure mapping

Define per-field mapping if source columns don't match destination column names.

Step 8. Optionally add more transformations

One bulk-load flow can target many destination tables — add one transformation per table.

Schema drift

Two flags control how schema differences are handled:

• Create temp tables with only TEXT columns (default true) — the staging Delta table is created with every column as STRING, then the engine casts during COPY INTO. This absorbs minor schema changes between batches without failing the load.
• Alter target table if source has new columns — enable to ALTER TABLE ADD COLUMNS when the source widens.

Handling processed files

Delete processed files

Purge File if Success is on by default. To keep the files after load, turn it off.

Skip already-processed files

Enable Skip Previously Processed Files. Etlworks maintains a cache of processed filenames; on the next run, matching files are ignored. The cache TTL and storage location are configurable.

Move processed files

Set the Move processed files to named connection on the flow to archive loaded files to a different bucket / path.

Tuning bulk-load throughput (high-volume and CDC loads)

On high-volume and CDC loads, how staged files become table rows matters as much as how fast they are produced. Three destination-side settings control it. They are the Databricks end of the pipeline described in Optimizing CDC Snapshots for Large Databases — tune them together with the source-side snapshot settings so neither end starves the other.

• Load data into database (ms) — how often the destination load runs. Default 60000 (60 seconds).
• Combine staged files in one COPY INTO — disabled by default.
• COPY INSERT data directly into destination — disabled by default.

Load interval

Do not automatically minimize the load interval. A very short interval produces frequent COPY INTO operations over too few files, which is slower overall, not faster. Start with the 60-second default and tune based on the file-generation rate, COPY duration, and any backlog of staged files. Lengthen it when many tiny files are being loaded; shorten it only when the destination is sitting idle waiting for files.

Combine staged files in one COPY INTO

Enable this for compatible files that target the same table. Etlworks combines up to 1000 staged files into a single COPY INTO, cutting the number of COPY operations and the per-operation overhead. When files cannot be combined (incompatible schema or options), it safely falls back to per-file loading, so enabling it is low-risk on high-volume loads.

COPY INSERT data directly into destination

For Action = INSERT, this skips the temporary staging table and the final INSERT, loading directly into the destination table and reducing destination SQL and table-management overhead.

• Direct COPY does not apply to MERGE or all-text loading; those keep the existing staging-table path.
• INSERT-only loading is the fastest option, but it needs special care for retries, resets, and ad-hoc re-snapshots: regenerated snapshot files re-insert rows and can create duplicates. Pair it with truncate/replace, MERGE, or an explicit deduplication strategy — see Optimizing CDC Snapshots → reload / repair drift.

Troubleshooting

For common load issues (credential errors, missing external locations, type-cast failures), see Common issues when loading data into cloud data warehouses.