Incremental Data Loading with COPY INTO

1. What is COPY INTO?

COPY INTO is a SQL command that efficiently loads data from a file location in cloud storage into a Delta table. It is best used when you need to perform batch ingestion, perhaps as part of a scheduled job, and prefer to work within a SQL-native environment.

Let's start with a brief overview from Databricks.

The three most important characteristics of COPY INTO are:

• Idempotent: This is a crucial feature. COPY INTO tracks which files have already been processed and will not load them again, even if you re-run the command. This ensures exactly-once processing at the file level, preventing data duplication.
• Incremental: When you execute the command, it automatically discovers and processes only the new files that have arrived in the source directory since the last run.
• SQL-native: It's a straightforward SQL command, making it easy to integrate into SQL-heavy workflows and Databricks SQL queries.

2. A Practical Walkthrough of COPY INTO

The best way to understand COPY INTO is to see it in action. We'll follow a detailed video that walks through a complete example, from setting up the environment to demonstrating its core features.

Step 1: Setting up the Source Data and Target Table

Before you can copy data, you need a source and a destination. A common pattern is:

1. Land raw files (e.g., CSV, JSON) in a specific cloud storage location.
2. Create an empty "placeholder" Delta table that will receive the data.

Unlike some other tools, COPY INTO requires the target Delta table to exist before you run the command. You can create it with a full schema, or, more flexibly, create it without any columns and let COPY INTO infer and evolve the schema for you.

Watch the next part of the video to see this setup process.

Step 2: The COPY INTO Command Syntax

Now for the main event. The COPY INTO command has a clear structure. Let's look at the syntax and break down the key options.

COPY INTO my_bronze_table
FROM '/path/to/source/data'
FILEFORMAT = CSV
FORMAT_OPTIONS (
  'header' = 'true',
  'mergeSchema' = 'true'
)
COPY_OPTIONS (
  'mergeSchema' = 'true'
);

Let's dissect this with help from a practical guide.

Now, watch the demonstration of this command in the video.

Step 3: Verifying Idempotency and Incremental Loading

This is the magic of COPY INTO. What happens if you run the exact same command again?

How does it work?
COPY INTO maintains metadata about the files it has already ingested within the Delta table's transaction log. Specifically, it creates a _copy_into_log directory. When the command runs, it first lists the files in the source directory and then filters out any files that are already recorded in this log.

The video gives a great look at this internal mechanism.

Now, let's confirm the incremental behavior. We'll add a new file to the source and re-run the command.

3. Advanced Usage: Transformations During Ingestion

A powerful feature of COPY INTO is the ability to perform transformations as you load the data. You can embed a SELECT statement to choose specific columns, cast data types, or even add new columns, such as a load timestamp. This is highly useful for shaping data as it enters your bronze layer.

The syntax looks like this:

COPY INTO my_table
FROM (
  SELECT
    col_a,
    CAST(col_b AS INT),
    current_timestamp() AS load_ts
  FROM '/path/to/source/data'
)
FILEFORMAT = ...
...

Let's see a demonstration of this technique.

4. Hands-On Practice

Now it's your turn. The best way to solidify your understanding is to run the commands yourself. The following Databricks tutorial provides a self-contained notebook that you can run in your workspace. It will guide you through setting up a data source, creating a target table, and using COPY INTO to load data idempotently.

5. Key Insights and Best Practices

As you move towards production implementations, keep these professional-level insights in mind. They are critical for building robust and predictable pipelines with COPY INTO.

Conclusion

In this lesson, you have gained a comprehensive understanding of the COPY INTO command. It is a vital tool for any data engineer working in Databricks, especially for batch-oriented ingestion tasks.

Key Takeaways:

• COPY INTO is a SQL command for incremental and idempotent batch loading of data into Delta tables.
• It works by tracking processed file names in the Delta transaction log, which prevents data duplication.
• It requires the target table to exist beforehand but can handle schema inference and evolution using FORMAT_OPTIONS and COPY_OPTIONS.
• You can perform transformations on the fly by embedding a SELECT query within the command.
• For robust production pipelines, always use unique file names for each new batch of data.

Preview of the Next Lesson:
You are now familiar with two powerful incremental ingestion tools: Auto Loader and COPY INTO. Each has its strengths. In our next lesson, we will compare Auto Loader vs. COPY INTO directly. You will learn how to analyze a scenario and select the most appropriate tool based on factors like data volume, velocity, latency requirements, and operational complexity.