Databricks Data Engineer Associate Data Ingestion and Loading: 172 practice questions

12 of the 172 Data Ingestion and Loading questions in the Certsqill Databricks Data Engineer Associate bank, shown in full below. Each one carries an explanation for every option, not just the correct one — the wrong answers are where the marks go.

Preparing for Databricks Data Engineer Associate? Take the free 5-min readiness check →

1. Use Auto Loader in a streaming query with an appropriate: Which implementation satisfies the requirement?

1. Use a Lakeflow Connect database connector without verifying source support

   A database connector is unsuitable for cloud files and requires supported source connectivity and connector prerequisites.
2. Use Auto Loader in a streaming query with an appropriate checkpoint ✓

   File-based streaming incrementally discovers and processes arriving files while recording progress for continued execution.
3. Run a batch query over the directory every night

   A nightly batch processes bounded snapshots and does not provide incremental daytime consumption of arriving files.
4. Use `COPY INTO` as a one-time table load

   A one-time load does not establish the requested continuously running incremental processing behavior.

Auto Loader in a streaming query is designed for incremental file arrival processing.

2. The retry skipped previously loaded files: What explains the observed behavior?

1. COPY INTO deduplicates every shipment_id across all loaded files.

   COPY INTO tracks loaded files, not business keys, so duplicate shipment rows in new files can still be inserted.
2. COPY INTO reloads all files but Delta silently removes duplicate rows.

   COPY INTO normally skips previously loaded files; Delta does not automatically remove duplicate business records during append.
3. The retry skipped previously loaded files, while the new file was loaded normally. ✓

   COPY INTO provides file-level idempotency; a newly discovered file is processed even when its rows repeat business keys.
4. The retry succeeded only because COPY INTO automatically enabled a primary key.

   COPY INTO has no automatic business-key constraint that makes repeated shipment identifiers unique during ingestion.

Previously loaded files were skipped, but duplicate keys in a new file were not deduplicated.

3. The checkpoint location: Which answer is correct?

1. The source directory itself.

   The source directory contains input files but does not record the stream’s offsets, state, or completed processing progress.
2. The schema location.

   The schema location stores inferred schema and evolution metadata, not the stream’s committed processing progress.
3. The checkpoint location. ✓

   The checkpoint stores Structured Streaming progress and state used to resume the stream after a restart.
4. The target table’s catalog metadata.

   Target catalog metadata describes the table and does not replace the source stream checkpoint for restart progress.

The checkpoint records stream progress and state; schemaLocation records schema metadata.

4. Define `customer` as `STRUCT<id: What prerequisite is missing?

1. A checkpoint shared with another support stream

   Checkpoint state does not convert a string into a struct, and separate streams should not share checkpoints.
2. A file-notification discovery mode

   File-discovery mode changes how files are found, not how the `customer` value is typed.
3. Define `customer` as `STRUCT<id: STRING>` ✓

   The input schema must represent `customer` as a struct before `customer.id` can be resolved.
4. A target Delta table with a matching string column

   A matching string target preserves the incompatible input representation and cannot create a nested `id` field.

Define `customer` as a struct before selecting its nested `id` field.

5. Use Auto Loader with the cloudFiles Structured Streaming: Which approach best fits these requirements?

1. Use Auto Loader with the cloudFiles Structured Streaming source. ✓

   Auto Loader is designed for scalable incremental file discovery and ingestion as new cloud files arrive.
2. Use a notebook that calls Python file-listing APIs before each batch.

   Repeated custom listings add operational work and do not provide Auto Loader’s scalable incremental discovery semantics.
3. Run COPY INTO once per hour against the directory.

   COPY INTO supports incremental batch file loading, but hourly runs do not provide the requested continuously arriving-file processing model.
4. Use a scheduled SQL query over the directory without a streaming source.

   A scheduled query can be periodic, but it does not itself provide the requested scalable incremental file-discovery mechanism.

Auto Loader provides scalable incremental discovery for high-volume arriving files.

6. Verify the connector networking and source connectivity: What is the safest remediation?

1. Replace the connector with query federation

   Query federation is a different remote-access pattern and does not remediate the selected connector's connectivity prerequisites for managed ingestion.
2. Grant the destination catalog additional permissions to the service account

   Destination authorization cannot establish network reachability from the managed connector to the source endpoint.
3. Verify the connector networking and source connectivity prerequisites ✓

   After source authorization is confirmed, validating the connector's network path and connectivity prerequisites directly addresses the failure.
4. Grant the source account additional read privileges

   The source read permission is already confirmed, and additional authorization does not resolve a network-reachability failure.

Verify the managed connector's networking and source connectivity prerequisites.

7. Validate the hostname: Which diagnosis and remediation are most appropriate?

1. Rotate the database password.

   The credentials are confirmed valid, and a pre-authentication timeout indicates a connectivity problem.
2. Increase JDBC partitioning so connection attempts are retried through the database.

   JDBC partitioning controls read parallelism and does not repair a blocked route or unreachable endpoint.
3. Validate the hostname, port, routing, and firewall from the compute environment. ✓

   A timeout before authentication requires checking endpoint and network reachability from the environment running the JDBC read.
4. Install another copy of the JDBC driver.

   The driver already loads successfully, so installing another copy will not repair endpoint reachability.

A pre-authentication timeout indicates network reachability, not invalid credentials.

8. Process the available files in micro-batches: What should the run do?

1. Process the available files in micro-batches, then stop. ✓

   AvailableNow processes the data available at execution time in one or more micro-batches and then terminates.
2. Reload every previously processed file in each micro-batch.

   Auto Loader tracks ingestion progress and does not inherently reload previously processed files.
3. Wait for future files.

   AvailableNow is bounded and terminates after processing the currently available input.
4. Process only the first file.

   AvailableNow drains the available backlog rather than limiting execution to one file.

AvailableNow drains currently available input in micro-batches and then terminates.

9. Use COPY INTO: Which design satisfies both requirements?

1. Use COPY INTO, then deterministically deduplicate by shipment_id downstream. ✓

   COPY INTO provides file-level retry handling, while downstream key-based logic removes duplicate business records.
2. Force COPY INTO to reload every retry.

   force=true intentionally reloads files and can create duplicate rows, contrary to the retry requirement.
3. Use default COPY INTO settings.

   Default COPY INTO skips previously loaded files but does not deduplicate repeated shipment_id values in new files.
4. Rely on the shipment_id column name to enforce uniqueness.

   A column name does not create a uniqueness constraint or remove repeated records during ingestion.

Combine file-aware loading with downstream deterministic business-key deduplication.

10. Restart with the existing checkpoint: What should the operator do next?

1. Change the trigger without restarting the stopped stream.

   Changing trigger behavior does not resume a stopped query or handle the schema-evolution exception.
2. Delete the checkpoint and reload every file from the beginning.

   Deleting the checkpoint can replay input and is unnecessary when schema metadata is durable.
3. Restart with the existing checkpoint. ✓

   In `addNewColumns` mode, Auto Loader updates the stored schema and stops; restarting preserves progress and uses the expanded schema.
4. Move the checkpoint into the schema location.

   The checkpoint tracks stream progress, while the schema location stores schema metadata; they should remain separate.

Restart with the existing checkpoint.

11. Unmatched or type-mismatched data is collected: What should analysts expect from the rescued column?

1. The record is duplicated into a separate quarantine table automatically.

   Rescue mode writes rescued information in the rescued column; it does not automatically create a quarantine table.
2. Only the unexpected locale field is retained; incompatible values are discarded.

   Rescue mode can capture both unexpected fields and type-mismatched data, not only newly named fields.
3. Unmatched or type-mismatched data is collected in the rescued-data column. ✓

   Rescue mode preserves the input record while placing unmatched or incompatible data in the rescued column for inspection.
4. All columns from the affected record become null and the record is discarded.

   Rescue mode preserves the record and captures unmatched or incompatible data rather than discarding every field.

Rescue mode preserves the record and captures unmatched or incompatible data for inspection.

12. Configure source networking and grant source permissions: Which missing prerequisite must be addressed first?

1. Configure a source-side incremental cursor for the connector.

   An incremental cursor can support change tracking, but it cannot substitute for network reachability to the private endpoint.
2. Grant the connector additional permissions on the destination tables.

   Destination permissions are already verified, and additional target privileges do not make the private source reachable.
3. Configure source networking and grant source permissions. ✓

   The connector runtime must be able to reach the SaaS endpoint and must have the required source permissions before ingestion can begin.
4. Enable Auto Loader schema inference on the destination.

   Auto Loader discovers cloud files; it does not make an inaccessible SaaS endpoint reachable.

Managed connectors need source reachability and permissions before destination configuration can help.

Other Databricks Data Engineer Associate domains

• Data Transformation and Modeling — 180 questions →
• Working with Lakeflow Jobs — 129 questions →
• Governance and Security — 123 questions →
• Implementing CI/CD — 82 questions →
• Troubleshooting, Monitoring, and Optimization — 82 questions →
• All 817 Databricks Data Engineer Associate questions →
• Databricks Data Engineer Associate certification: requirements, cost and exam format →