Idempotent Data Ingestion with COPY INTO

1. Introduction to COPY INTO and Idempotency

Before we dive into the code, let's establish what COPY INTO is and why its most important feature, idempotency, is so critical for data engineering.

An operation is idempotent if running it multiple times has the same effect as running it just once. In the context of data ingestion, this means that if you accidentally re-run an ingestion job, you won't create duplicate records in your target table. COPY INTO guarantees this "exactly-once" processing for a given set of files, which is a cornerstone of building reliable and fault-tolerant data pipelines.

To get a quick overview of COPY INTO, let's start with a short video from Databricks.

As the video mentioned, COPY INTO is best used when you have a manageable number of files to load and prefer to work within a SQL environment. It keeps track of the files it has already processed, preventing duplicates.

2. A Step-by-Step Guide to Using COPY INTO

Now, let's walk through a complete, practical example of using COPY INTO. The following video provides a clear, step-by-step demonstration that covers creating a source location, setting up a target table, and running the command.

Let's break down the key components of the COPY INTO command you just saw:

COPY INTO dev.bronze.invoices_cp -- 1. Target Table
FROM 'dbfs:/path/to/source'      -- 2. Source Location
FILEFORMAT = CSV                 -- 3. File Format
PATTERN = 'invoice_*.csv'        -- 4. File Pattern (Optional)
FORMAT_OPTIONS (                 -- 5. Options for Reading Source Files
  'header' = 'true',
  'mergeSchema' = 'true'
)
COPY_OPTIONS (                   -- 6. Options for Writing to Target Table
  'mergeSchema' = 'true'
);

1. COPY INTO: Specifies the target Delta table. This table must exist before you run the command, but it can be empty and schemaless.
2. FROM: The path to the cloud storage location containing the source files.
3. FILEFORMAT: The format of the source files (e.g., CSV, JSON, PARQUET, AVRO).
4. PATTERN: An optional glob pattern to select specific files from the source directory.
5. FORMAT_OPTIONS: A dictionary of settings that control how the source files are read. For example, 'header' = 'true' tells the reader that the CSV files have a header row.
6. COPY_OPTIONS: A dictionary of settings that control the write operation. 'mergeSchema' = 'true' allows the target table's schema to evolve if the source files contain new columns.

The most crucial takeaway is what happened on the second run: zero rows were inserted. COPY INTO consulted its internal log, saw that the source files had already been processed, and simply did nothing. This is idempotency in action.

3. Transforming Data During Ingestion

A common requirement is to perform light transformations on data as it's being loaded. COPY INTO supports this elegantly by allowing you to embed a SELECT query. This is useful for:

• Selecting a subset of columns.
• Casting data types.
• Renaming columns.
• Adding new columns with fixed values or functions (e.g., an ingestion timestamp).

Let's see how this works.

The syntax for this pattern looks like this:

COPY INTO target_table
FROM (
  SELECT
    invoice_number,
    stock_code,
    CAST(quantity AS DOUBLE),
    current_timestamp() AS insert_date
  FROM 'dbfs:/path/to/source'
)
FILEFORMAT = CSV
FORMAT_OPTIONS ('header' = 'true');

This capability is powerful because it allows you to shape your data into a clean, well-defined schema right at the point of ingestion, without needing a separate transformation step.

4. Let's Practice

Now it's your turn to put this into practice. The following Databricks tutorial provides a self-contained notebook that you can run in your own workspace. It walks you through setting up a data source, creating a target table, and using COPY INTO to idempotently load JSON data.

This exercise reinforces the core concepts we've covered: creating a target table, loading data from a source, and verifying the idempotent nature of the command. For a deeper dive into more options, you can always refer to the documentation page on Common data loading patterns using COPY INTO.

Conclusion

In this lesson, you've learned how to use the COPY INTO command, a vital tool for batch data ingestion in Databricks. You now understand its syntax, its options for handling different file formats and schemas, and how to use it for basic transformations.

Key Takeaways

• COPY INTO is a SQL command used for batch ingestion of files into a Delta table.
• Its key feature is idempotency, which prevents duplicate data by tracking which files have already been processed.
• This state is maintained in a copy_into_log within the target table's Delta transaction log.
• You can perform transformations like column selection, casting, and adding new columns by embedding a SELECT query.
• It's a simple yet powerful tool for building reliable ELT pipelines, especially when your primary interface is SQL.

Next Steps

We have now covered two primary methods for file ingestion: Auto Loader (for streaming) and COPY INTO (for batch). A critical skill for a data engineer is knowing which tool to use for a given job. In our next lesson, we will do a head-to-head comparison of Auto Loader vs. COPY INTO to help you make the right choice for different ingestion scenarios.