Databricks-DE-Assoc Data Ingestion and Loading Practice Test 2 — 15 Questions

Question 1

A data engineer is building a Databricks Structured Streaming pipeline that ingests new files from an S3 bucket into a Delta table. The pipeline must process each file exactly once, even if the stream is stopped and restarted, and must not reprocess files that were already ingested. The engineer is not allowed to modify the source files or use an external tracking database. Which approach should the engineer use?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

Use Auto Loader with a checkpoint location and a schema location, and rely on its built-in file tracking to avoid reprocessing.. Auto Loader provides exactly-once semantics by tracking ingested files in the checkpoint location. When combined with a schema location, it also handles schema inference and evolution. The checkpoint stores the list of files already processed, so on restart, it continues from where it left off without reprocessing. Other options either do not guarantee exactly-once or involve unnecessary configuration.

Answer

Use Auto Loader with a checkpoint location and a schema location, and set the option 'cloudFiles.useNotifications' to false.

Answer

Use the file source with a checkpoint location and specify the option 'maxFilesPerTrigger' to limit the number of files processed per micro-batch.

Answer

Configure the stream with a checkpoint location and use the file source with the option 'ignoreDeletes' set to true.

Question 2

A data engineering team needs to ingest millions of small JSON files from an S3 bucket into a Delta Lake table. The solution must provide incremental loading, support schema evolution, and automatically scale to handle increasing file volumes without manual tracking of processed files. Which tool is best suited for this requirement?

Accepted Answer

Auto Loader using the cloudFiles source in Structured Streaming.. Auto Loader is the recommended tool for incremental ingestion from cloud storage. It scales to millions of files using either directory listing or file notifications. Unlike standard Spark sources, it tracks processed files in a checkpoint, ensuring exactly-once semantics. This automation reduces operational overhead when managing unpredictable data volumes and evolving schema structures in production environments.

Answer

The standard Apache Spark DataFrame reader using the .load() method.

Answer

The COPY INTO SQL command with the mergeSchema option enabled.

Answer

A Python loop that iterates through filenames and uses INSERT INTO.

Question 3

A data engineer is evaluating whether to use Auto Loader or the COPY INTO command for a new ingestion pipeline. Which TWO features are unique to Auto Loader compared to COPY INTO?

Accepted Answer

Native support for schema evolution through schemaEvolutionMode.. Auto Loader and COPY INTO both offer idempotent loading, but Auto Loader is built on Structured Streaming. This allows it to provide more advanced features like automatic schema evolution and a notification mode that uses cloud services to detect new files. Understanding these differences is crucial for selecting the right tool based on volume and schema complexity.

Answer

The ability to perform incremental loads by tracking processed files.

Answer

Support for ingesting data from cloud object storage like S3 or ADLS.

Answer

Requirement for a Delta Lake table as the final destination.

Question 4

Refer to the exhibit. A data engineer is configuring an Auto Loader stream with the provided options. What will happen if a new JSON file arrives containing a field that is not currently in the target table schema?

Accepted Answer

The new field will be added to the target table automatically.. The schemaEvolutionMode configuration determines how Auto Loader reacts to changes in the source data structure. By setting this to addNewColumns, the engineer ensures that the pipeline remains operational when new fields appear. This is a key part of the Medallion architecture where bronze tables must capture all incoming raw data without strict validation rules.

Answer

The stream will fail and require a manual schema update.

Answer

The new field will be dropped and only existing columns are kept.

Answer

The data for the new field will be moved to a _rescued_data column.

Question 5

When using Auto Loader to ingest data, why is it recommended to provide a 'schemaLocation' rather than manually defining the entire schema in the code?

Accepted Answer

To allow Auto Loader to persist and evolve the schema over time.. Auto Loader uses the schemaLocation to store inferred schemas and track changes over time. This allows for schema inference and evolution, which are critical for handling unpredictable data sources. Without this location, the stream cannot persist the state of the schema, making it impossible to use the automatic evolution features provided by Databricks.

Answer

To improve the performance of the initial file listing process.

Answer

To bypass the need for a checkpoint location for the stream.

Answer

To encrypt the data schema for security and compliance reasons.

Question 6

A data engineer is designing a pipeline using Structured Streaming to ingest data into Delta Lake. Which THREE benefits are provided by using checkpoints in this scenario?

Accepted Answer

Enabling the stream to resume from the exact point of failure.. Checkpoints are a fundamental feature of Structured Streaming that enable fault tolerance and exactly-once processing. They store the current state and progress of a stream, allowing it to recover from failures without data loss or duplication. In the context of Delta Lake, checkpoints ensure that the ingestion process remains reliable and consistent across various execution cycles.

Answer

Providing the ability to recover the previous version of the table.

Answer

Automatically cleaning up old data files in the target directory.

Question 7

While ingesting CSV files using Auto Loader, a data engineer notices that some records have malformed data that does not match the inferred schema. How can the engineer capture these records without failing the entire ingestion stream?

Accepted Answer

By enabling the '_rescued_data' column in the Auto Loader configuration.. The rescued data column is a powerful feature in Auto Loader that prevents data loss during ingestion. By capturing malformed or unexpected data in a dedicated column, engineers can ensure that the main pipeline continues to run while providing a way to audit and correct data quality issues after the fact.

Answer

By setting the 'mode' option to 'FAILFAST' in the reader.

Answer

By using a TRY_CAST function in a transformation after the read.

Answer

By increasing the 'maxFilesPerTrigger' to handle more errors.

Question 8

A data engineer needs to ingest data from a legacy SQL Server database into a Delta Lake bronze table. The ingestion must be performant and support parallel reads from the source table. What is the best practice for configuring the JDBC connection in this scenario?

Accepted Answer

Configure 'partitionColumn', 'lowerBound', 'upperBound', and 'numPartitions'.. Parallelizing JDBC reads is essential for moving large datasets from relational databases to Databricks. By providing partitioning columns and boundaries, Spark can spawn multiple executors to read different segments of the data simultaneously. This significantly reduces the time required for the initial load and improves the overall throughput of the ingestion pipeline.

Answer

Use a single executor to ensure data consistency during the read.

Answer

Rely on the default JDBC settings for automatic parallelization.

Answer

Use the COPY INTO command to read directly from the JDBC source.

Question 9

Refer to the exhibit. A data engineer is using this command to reload data into a Delta table after a schema correction. What is the primary effect of setting the 'force' option to 'true' in this context?

Accepted Answer

The command will re-ingest all files, even if they were loaded before.. The COPY_OPTIONS parameter includes a force setting that determines whether the command should re-process files that have been loaded previously. By default, COPY INTO is idempotent and skips files that are already present in the transaction log. Setting force to true overrides this behavior, allowing the engineer to perform a full reload of the existing data.

Answer

The command will automatically vacuum the target table before loading.

Answer

The command will bypass schema validation for the incoming JSON files.

Answer

The command will delete the source files from S3 after a successful load.

Question 10

When designing an ingestion strategy for a Delta Lake architecture, which TWO advantages does Delta Lake provide over traditional Parquet tables for incoming data?

Accepted Answer

ACID transactions ensure partial writes do not occur during failures.. Delta Lake enhances the standard Parquet format by adding a transaction log that enables ACID compliance and efficient metadata handling. These features are vital for ingestion as they prevent data corruption and allow for advanced operations like schema evolution and time travel, which are not possible with standard Parquet files on cloud storage.

Answer

Native support for sub-second streaming latency from any source.

Answer

Automatic indexing of all string columns for faster search queries.

Answer

Support for data encryption at rest using proprietary algorithms.

Question 11

A data engineer is using Auto Loader to ingest data from a directory that receives thousands of files every hour. They are considering switching from the default directory listing mode to file notification mode. What is the primary reason for making this change?

Accepted Answer

To reduce the cost and latency of discovering new files as the bucket grows.. As the number of files in a cloud storage bucket grows, the time and cost required to list all files recursively increase significantly. File notification mode solves this by using cloud services to push events about new files directly to Auto Loader, making it far more efficient for high-volume, long-term ingestion projects.

Answer

To enable the use of schema evolution in the ingestion pipeline.

Answer

To ensure that files are processed in the exact order they arrived.

Answer

To allow the stream to process multiple file formats simultaneously.

Question 12

A data engineer wants to run an incremental ingestion job every six hours. They want to ensure that each run processes all available data and then shuts down the cluster to save costs. Which Trigger should be used in the Structured Streaming code?

Accepted Answer

Trigger.AvailableNow. Trigger.AvailableNow is the modern replacement for Trigger.Once. It provides better scalability by processing all available data in multiple micro-batches if necessary, while still allowing the job to terminate once all data is processed. This is ideal for cost-effective, periodic batch-style processing of streaming data sources.

Answer

Trigger.ProcessingTime('6 hours')

Answer

Trigger.Once

Answer

Trigger.Continuous('1 minute')

Question 13

A data engineer is using Auto Loader and wants to handle a situation where a column 'user_id' is sometimes an integer and sometimes a string in the source JSON files. Which TWO strategies can be used to manage this schema conflict?

Accepted Answer

Providing a schema hint to force 'user_id' to be treated as a string.. Handling type mismatches is a common challenge in data ingestion. Auto Loader provides schema hints to force a specific type and the rescued data column to capture records that fail to meet that type. These tools allow the engineer to maintain data integrity and prevent the ingestion pipeline from failing due to inconsistent source data.

Answer

Using the 'mergeSchema' option to create two separate columns for the types.

Answer

Setting 'cloudFiles.inferColumnTypes' to false to ignore all types.

Answer

Deleting the checkpoint and restarting the stream to re-infer the types.

Question 14

When ingesting data from cloud storage, what is the most important reason to use a Service Principal or IAM Role instead of individual user credentials?

Accepted Answer

To ensure the pipeline continues to run if the user leaves the company.. Using service-level authentication is a security best practice that ensures ingestion pipelines are not tied to specific individuals. This prevents production jobs from failing when a user leaves the organization or has their permissions revoked. It also provides a more secure and auditable way to manage access to sensitive data in cloud environments.

Answer

To increase the maximum file size that can be ingested by Spark.

Answer

To enable the use of the COPY INTO command in SQL warehouses.

Answer

To allow Auto Loader to use directory listing instead of notifications.

Question 15

A data engineer is configuring a Databricks Auto Loader stream to ingest CSV files from a cloud storage location into a Delta table. The CSV files have a header row, and the engineer wants to automatically infer the schema and store the inferred schema in a specified location for consistency across restarts. Which Auto Loader option should be used to persist the inferred schema?

Accepted Answer

Set cloudFiles.schemaLocation to a directory path.. Auto Loader requires a schemaLocation to persist the inferred schema and support schema evolution. Without it, the stream may re-infer the schema on each restart, causing inconsistencies. The other options control schema evolution behavior or listing optimizations but do not provide a storage location for the schema.

Answer

Set cloudFiles.inferColumnTypes to 'true'.

Answer

Set cloudFiles.schemaEvolutionMode to 'addNewColumns'.

Answer

Set cloudFiles.useIncrementalListing to 'true'.