Data-Engineer-Associate domain - 21% of the exam

Data Ingestion and Loading

Data Ingestion and Loading is 21% of the Data-Engineer-Associate exam. These are the objectives it covers, each with practice questions, with every answer explained.

The domain in numbers

63
Practice questions
7
Objectives
45
Medium
18
Hard

Where people go wrong

  • Mixing up where each tool keeps its record of loaded files

    The wrong answers place the record of loaded files in the wrong tool. They give COPY INTO a checkpoint, expect a truncate to make files load again, or assume a new empty checkpoint changes nothing for Auto Loader. COPY INTO keeps its record against the target table, so a rerun skips files it has ingested and a truncate leaves them marked as loaded; the force option reloads them. Auto Loader keeps its record in the checkpoint, so an empty checkpoint reprocesses every file.

    See it in a sample question
  • Treating slow file discovery as a read-throughput problem

    The wrong answers add executors, limit files per trigger, stay in directory listing mode, or switch to COPY INTO, which still lists the source directory. Each leaves the listing sweep in place, so discovery cost keeps growing with the number of files already in the directory. Auto Loader in file notification mode learns of new files from cloud storage events and a queue, so discovery cost follows arrivals rather than the size of the directory.

    See it in a sample question
  • Pointing file ingestion tools at application and database sources

    The wrong answers use Auto Loader or COPY INTO to pull from Salesforce, PostgreSQL or SQL Server, or add an export job in front of them. Both tools read files in cloud storage, so neither can open a connection to an application or database, and an export job puts the extraction code back with the team. Lakeflow Connect managed connectors cover SaaS applications and operational databases, and database sources capture changes through an ingestion gateway.

    See it in a sample question
  • Using a schema setting that does an adjacent job

    The wrong answers offer a rescued data column, an evolution mode, schema inference or the force option where a column must be added or retyped. For COPY INTO, the mergeSchema copy option adds columns that the target table lacks, and force only reloads files. For Auto Loader, schemaHints retypes named columns while the rest keep their recorded types. inferColumnTypes replaces the default of reading JSON columns as strings, but it re-infers every column, so it does not suit a fix confined to one column.

    See it in a sample question
  • Landing non-tabular files outside a Unity Catalog volume

    The wrong answers put raw payload files and PDF documents in an object built for something else: an external table, a row in a table, a mount, the DBFS root, log delivery or a Git folder. Tables need a tabular format, and a mount, the DBFS root and workspace permissions sit outside Unity Catalog privileges and auditing. A Unity Catalog volume holds the original files at a path under /Volumes, with READ VOLUME and WRITE VOLUME privileges.

    See it in a sample question

Objectives in this domain

Sample questions from this domain

Free sampleData Ingestion and Loadingmedium

A team lands CSV files in a cloud storage folder each hour and loads them into a Delta table with a COPY INTO statement that is rerun on a schedule against the same folder. Files are retained in the folder for thirty days. What happens to a file that a previous run has already loaded successfully?

COPY INTO prod.raw.events
FROM 's3://landing/events/'
FILEFORMAT = CSV
FORMAT_OPTIONS ('header' = 'true')
  • AIt is read again on every run, so the table accumulates a duplicate copy of each retained file until the folder is emptied.
  • BIt is skipped, because COPY INTO records the files it has loaded for that target table and ingests each one at most once. Correct
  • CIt is read again unless the statement sets a rescued data column, which is the setting that suppresses files loaded by an earlier run.
  • DIt is read again unless a checkpoint location is supplied, since COPY INTO stores its list of loaded files in that checkpoint directory.
COPY INTO records which files it has loaded into a target table, so rerunning it against the same folder ingests each file at most once. COPY INTO keeps per-target metadata listing the source files it has already ingested. On each run it compares the folder contents against that record and loads the files it has not seen, which makes repeated scheduled runs idempotent without any streaming checkpoint or manual bookkeeping.

Why A is wrong: This is the behaviour of a plain read of the folder followed by an append, and candidates often assume COPY INTO works the same way. COPY INTO differs because it maintains its own record of loaded files against the target table.

Why B is correct: COPY INTO tracks the files it has already ingested for the target table, so rerunning the same statement loads the files that have appeared since the previous run and leaves the rest alone.

Why C is wrong: The rescued data column captures fields that do not match the expected schema, and it plays no part in deciding which files are ingested. Skipping loaded files is built into COPY INTO itself.

Why D is wrong: Checkpoint locations belong to Structured Streaming sources such as Auto Loader, and COPY INTO does not take one. Its idempotency comes from metadata held against the target table.

Free sampleData Ingestion and Loadingmedium

An Auto Loader stream ingests from a cloud storage folder that has grown to several million small files in one flat directory, and each micro-batch now spends most of its time discovering new files rather than reading them. The team wants to reduce that discovery cost without changing the folder layout. Which change addresses the cause?

  • ASwitch the stream to file notification mode, so that new files are learned from cloud storage event notifications rather than from repeated directory listings. Correct
  • BIncrease the size of the compute resource running the stream, so that more executor cores are available to read the storage folder in parallel.
  • CEnable schema evolution on the stream, so that Auto Loader maintains a schema log and can skip files whose structure has not changed since the last batch.
  • DSet a smaller maximum number of files per trigger, so that each micro-batch lists a smaller portion of the directory and finishes its discovery work sooner.
File notification mode learns of new files from storage events, so discovery cost does not grow with the number of files already in the directory. Directory listing mode finds new files by listing the source path, so its cost rises with the total number of objects present, whether or not they are new. File notification mode subscribes to storage event notifications through a queue, so the stream is told about each arriving file and the work per batch depends on arrivals rather than on directory size.

Why A is correct: File notification mode replaces the listing sweep with a notification service and queue, so discovery cost stops scaling with the number of files already in the directory.

Why B is wrong: Adding hardware helps when reading data is the bottleneck, which is why it is tempting. File discovery here is dominated by listing calls against the storage account, so extra executors do not shorten it.

Why C is wrong: Schema evolution is a real Auto Loader capability and sounds like a form of skipping work. It governs how new columns are handled, and it has no bearing on how files are discovered.

Why D is wrong: Limiting files per trigger does bound how much data a batch reads, which makes it look like a fix. The directory still has to be listed to find candidates, so the discovery cost per batch is unchanged.

Free sampleData Ingestion and Loadinghard

An operational SQL Server database holds eight tables that a reporting team needs in Unity Catalog, refreshed incrementally every hour. Row volumes are large enough that reloading each table in full every hour is not acceptable, and the team will not write or maintain extraction code, watermark tracking or change capture logic. Which ingestion method meets the requirement?

  • AConfigure a Lakeflow Connect managed ingestion pipeline for the database source, selecting the eight tables and scheduling the pipeline to refresh them incrementally into Unity Catalog. Correct
  • BSchedule a Lakeflow Jobs task that reads each table over JDBC into a DataFrame and overwrites the matching Unity Catalog managed table on every hourly run.
  • CExport the eight tables hourly to CSV files in cloud object storage with a database side job, then load the new files with an Auto Loader stream into the target tables.
  • DRun a COPY INTO statement per table on an hourly schedule, pointing each statement at the database connection so that only rows added since the previous load are appended.
Select Lakeflow Connect when a database or SaaS source needs governed incremental refresh without bespoke extraction code. Lakeflow Connect is the managed ingestion product for sources that are not files in object storage. Its database connectors handle the connection, the incremental read and the scheduled refresh, landing the result as Unity Catalog tables. Auto Loader and COPY INTO both start from files, so choosing either forces someone to build and own the export step that turns database rows into files, which is the maintenance burden the requirement excludes.

Why A is correct: Correct, because Lakeflow Connect provides managed connectors for database and SaaS sources, handling connection, incremental refresh and scheduling without the team writing extraction code.

Why B is wrong: Tempting because a JDBC read is a short piece of code that works, but a full overwrite every hour is precisely the reload pattern the requirement rules out on volume grounds.

Why C is wrong: Tempting because Auto Loader handles the file side neatly, but the hourly export job is bespoke extraction logic on the database side, which the team has said it cannot maintain.

Why D is wrong: Tempting because COPY INTO does track what it has already loaded, but that tracking applies to files in a storage location, and COPY INTO cannot read from a relational database at all.

Free sampleData Ingestion and Loadingmedium

A nightly COPY INTO statement loads JSON files into an existing Unity Catalog managed table whose schema has four columns. Tonight the upstream producer begins emitting a fifth field in every file, and the business wants that field stored in the table from tonight onward rather than discarded. Which single change to the load makes the new field land in the table?

COPY INTO prod.raw.clicks
FROM 's3://landing/clicks/'
FILEFORMAT = JSON
  • AChange FILEFORMAT to a value that carries its own schema with the data, because the new field is stored only when the source format is self describing rather than JSON.
  • BAdd COPY_OPTIONS ('mergeSchema' = 'true') to the statement, so the additional field in the incoming files is added as a new column on the target table. Correct
  • CAdd a PATTERN clause restricted to the files written tonight, because COPY INTO widens the target schema whenever the source listing is narrowed to files sharing one shape.
  • DAdd COPY_OPTIONS ('force' = 'true') to the statement, so the reprocessed history is written back under a schema derived afresh from the current source files.
Set the merge schema copy option so COPY INTO adds newly appearing source columns to the target table instead of dropping them. By default COPY INTO writes only the columns already defined on the target table, so a field appearing in the source files but missing from the table is ignored. The merge schema copy option changes that behaviour: the incoming schema is reconciled with the target and columns present only in the source are added to the table, letting the load continue while capturing the new field from that run onward.

Why A is wrong: Self describing formats do carry a schema, which makes this sound reasonable, but the file format does not decide whether the target table gains a column.

Why B is correct: Correct. The merge schema copy option allows COPY INTO to evolve the target schema by adding columns present in the source data but absent from the table.

Why C is wrong: PATTERN genuinely narrows the source listing, but it never alters the target schema, so the fifth field would still be dropped from every ingested row.

Why D is wrong: The force option is real and does reprocess every source file, but reprocessing under the existing four column schema still discards the fifth field.

Free sampleData Ingestion and Loadingmedium

A notebook calls a vendor REST endpoint and must land the raw JSON response payloads as files, not as rows. The team wants those files governed by Unity Catalog, with access granted through the same privilege model used for tables and with reads and writes captured in the audit log. Which object should the notebook write the payload files into?

  • AAn external table registered over the landing path, with the notebook writing the payload files into that path using ordinary Python file operations.
  • BA Unity Catalog volume in the target schema, which stores non-tabular files and is secured with the READ VOLUME and WRITE VOLUME privileges. Correct
  • CA managed Delta table in the target schema, holding one row per response with the whole payload kept in a single string column.
  • DA workspace file stored in a Databricks Git folder beside the notebook, which inherits the workspace permissions already set on that folder.
Unity Catalog volumes govern non-tabular files inside a schema, secured with READ VOLUME and WRITE VOLUME rather than table privileges. A volume is the Unity Catalog abstraction for file data. It is addressed by catalog, schema and volume name, it exposes a governed path that notebook code can write to, and access is granted with volume privileges, so raw REST payloads kept as files inherit the same permission model and audit coverage as tables in the same schema.

Why A is wrong: Tempting because an external table does sit over a governed storage path, but a table governs tabular reads of registered data rather than arbitrary file operations against the path.

Why B is correct: Correct: volumes are the Unity Catalog object for non-tabular data, they live inside a catalog and schema like tables, and access to them is granted and audited through the same governance model.

Why C is wrong: Tempting because it is governed and is a common bronze pattern, but the requirement is that the payloads remain as files, which a row in a table does not satisfy.

Why D is wrong: Tempting because Git folders do hold non-notebook files, but workspace permissions are not Unity Catalog privileges and a Git folder is for source code rather than ingested data.

Other domains in this exam

See also the Data-Engineer-Associate cert hub, the study guide, and the cheat sheet.

Examworthy is not affiliated with or endorsed by Databricks. Original, blueprint-aligned practice material only.