DP-750 Certification Practice Question #53

Question

A data engineering team schedules the following SQL to run **every hour** on a Databricks SQL warehouse to load newly arrived JSON booking files from a Unity Catalog volume into an existing Delta table:

```sql
COPY INTO main.sales.bookings_target
FROM '/Volumes/main/sales/landing/bookings'
FILEFORMAT = JSON
FORMAT_OPTIONS ('mergeSchema' = 'true')
COPY_OPTIONS ('mergeSchema' = 'true');
```

**Proposed solution:** The team relies on `COPY INTO` alone — with no external bookkeeping table, no manual file-tracking, and no MERGE deduplication logic — to guarantee that re-running the scheduled statement does not reload files that were already ingested, so each source file's rows are inserted exactly once even across repeated runs.

Does this solution meet the goal?

Accepted Answer

`COPY INTO` tracks which files in the source location have already been loaded and skips them on subsequent runs, making it idempotent and retriable with exactly-once file processing by default. Microsoft Learn explicitly states the statement can be scheduled to run repeatedly and will only load new data, with no need for an external bookkeeping table or MERGE deduplication. Because the source files are immutable booking files (not overwritten) and `force` is not enabled, repeated hourly runs will not reload previously ingested files — the goal is met.

More DP-750 practice questions