Databricks-DE-Pro Data Ingestion and Acquisition — 10 Questions

Question 1

You are migrating a legacy system that generates files with inconsistent headers. Which feature should you use to ensure successful ingestion into a structured Delta table?

Accepted Answer

Enable the 'cloudFiles.includeMissingColumns' option.. The 'rescuedDataColumn' feature in Auto Loader is designed to capture data that does not match the inferred or provided schema (e.g., type mismatches). It does not resolve issues where headers are inconsistent (e.g., different column names or missing columns). For inconsistent headers in CSV files, the correct approach is to use 'header' options or schema evolution features, but 'rescuedDataColumn' is not the primary mechanism for header-level inconsistencies.

Answer

Define a custom schema with all possible columns as strings.

Answer

Configure the 'rescuedDataColumn' option.

Answer

Reject the files using an exclusion filter.

Question 2

Refer to the exhibit. You encounter this error in your production pipeline. What is the most likely cause, and how do you resolve it?

Accepted Answer

The pipeline requires 'cloudFiles.schemaEvolutionMode' set to 'addNewColumns'.. When Auto Loader detects new columns in the source data, it can automatically evolve the schema if 'cloudFiles.schemaEvolutionMode' is set to 'addNewColumns'. This allows the pipeline to adapt to structural changes without manual intervention, provided a schema location is defined to track these changes.

Answer

The source files are corrupted; delete them.

Answer

The cloud provider permission was denied; grant access.

Answer

The cluster is out of memory; restart it.

Question 3

Your organization requires that all data files ingested into the landing zone be deleted from the source storage immediately after they are successfully processed by the pipeline. What is the recommended way to achieve this in Databricks?

Accepted Answer

Use the 'cloudFiles.deleteAfterProcess' configuration parameter.. For requirements where files must be deleted immediately after processing (often for compliance), Databricks Auto Loader provides the 'cloudFiles.cleanSource' configuration. Setting this to 'delete' ensures that files are removed only after they have been successfully committed to the checkpoint. While cloud lifecycle policies are a good general practice for data retention, they do not provide the immediate deletion requested and are configured at the storage level, not 'in Databricks'.

Answer

Add a post-processing block in the Spark code to delete files using 'dbutils.fs.rm'.

Answer

Configure the cloud storage provider's lifecycle management policy.

Answer

Move files to a 'processed' directory instead of deleting them.

Question 4

Refer to the exhibit. You are using Auto Loader to ingest JSON files. The schema inference failed because of a type mismatch. What is the most efficient way to resolve this while maintaining the ability to process new files?

Accepted Answer

Provide an explicit schema using the 'cloudFiles.schema' option.. When schema inference fails due to complex types or format variations, manual schema specification is the most robust solution. By using the .schema() method with a predefined Schema string or StructType, you bypass the inference engine's limitations. This gives you full control over data types, ensuring that the ingestion pipeline is predictable, performant, and resistant to inference errors that often occur with evolving or messy semi-structured data sources.

Answer

Set 'cloudFiles.inferColumnTypes' to 'false'.

Answer

Cast the column using a 'withColumn' transformation.

Answer

Ignore the error by setting 'cloudFiles.ignoreSchemaErrors' to true.

Question 5

A data engineer needs to ingest data from a PostgreSQL database into a Delta Lake table using Databricks. The source table is large and has a numeric primary key that increases monotonically. The engineer wants to perform incremental ingestion to avoid reprocessing all data on each run. Which approach is most appropriate?

Accepted Answer

Use the JDBC connector with the 'query' option to filter rows based on the maximum primary key value from the previous run.. Incremental ingestion from a JDBC source with a monotonic primary key is best achieved by filtering rows based on the last ingested key. Using the JDBC connector's query option allows a dynamic subquery to fetch only new rows, minimizing data transfer and processing. This approach is efficient, scalable, and maintains data integrity without modifying the source.

Answer

Use the JDBC connector with the 'partitionColumn' and 'numPartitions' options to parallelize reads, and append the entire table each time.

Answer

Use the JDBC connector with the 'dbtable' option to read the entire table each time and overwrite the Delta table.

Answer

Use the JDBC connector with the 'truncate' option to truncate the source table after reading.

Question 6

A data engineer is using Databricks Auto Loader to ingest Parquet files from a cloud storage location. The schema of the incoming files changes frequently, with new columns added and some columns changing data types. The engineer wants to automatically handle these schema changes without failing the stream. Which configuration should be used?

Accepted Answer

Set cloudFiles.schemaEvolutionMode to 'addNewColumns' and enable cloudFiles.schemaLocation.. The correct configuration is to set cloudFiles.schemaEvolutionMode to 'addNewColumns' and provide a cloudFiles.schemaLocation. This allows Auto Loader to automatically add new columns as they appear in the data and store the evolving schema. Type changes are not automatically handled and may require additional steps, but for new columns, this is the appropriate setting.

Answer

Set cloudFiles.schemaEvolutionMode to 'addNewColumns' and set mergeSchema to true in the write options.

Answer

Set cloudFiles.schemaEvolutionMode to 'rescue' and provide a schema hint.

Answer

Set cloudFiles.schemaEvolutionMode to 'failOnNewColumns' and use a merge schema option.

Question 7

You are building a streaming ingestion pipeline using Databricks Auto Loader to ingest JSON files from Amazon S3 into a Delta Lake table. The pipeline must handle schema evolution and ensure that data is not lost if the stream is restarted. You also need to minimize the number of small files in the target table. Which two actions should you take? (Choose two.)

Accepted Answer

Enable Auto Compaction by setting delta.autoOptimize.autoCompact to true on the target Delta table.. To handle schema evolution and ensure restartability, you must enable schema evolution with addNewColumns and set a schema location. To minimize small files, enabling Auto Compaction on the Delta table automatically combines small files during writes. These two actions together meet the requirements without compromising streaming semantics.

Answer

Use trigger(availableNow=True) to process all available data in a single micro-batch.

Answer

Configure the stream to use a checkpoint location and set cloudFiles.partitionColumns to partition the data.

Answer

Set cloudFiles.useNotifications to false to force directory listing for file discovery.

Question 8

A data engineer is using Databricks Auto Loader to ingest Parquet files from a cloud storage location into a Delta table. The engineer notices that some files have a column named 'timestamp' with a data type of string in some files and timestamp in others. The engineer wants to ensure that all data is ingested correctly and that the schema evolves to accommodate the different types. Which approach should the engineer take?

Accepted Answer

Set cloudFiles.schemaEvolutionMode to 'addNewColumns' and rely on Auto Loader's automatic type widening to promote the 'timestamp' column to a compatible type.. Auto Loader's schema evolution with 'addNewColumns' mode can automatically handle type widening for columns when the new data has a wider type. In this case, a string column can be promoted to timestamp if the string values are valid timestamps. This feature is available in newer Databricks Runtime versions and simplifies ingestion of evolving data. The other options either do not address type changes or require manual steps that are unnecessary.

Answer

Set cloudFiles.schemaEvolutionMode to 'rescue' and define a rescuedDataColumn to capture mismatched records.

Answer

Set cloudFiles.schemaEvolutionMode to 'failOnNewColumns' and manually alter the Delta table schema to use string for the 'timestamp' column.

Answer

Set cloudFiles.schemaEvolutionMode to 'addNewColumns' and enable type widening by setting spark.databricks.delta.schema.autoMerge.enabled to true.

Question 9

A data engineering team ingests CSV files from an external SFTP server into a Bronze Delta table using Auto Loader. The files are delivered every 30 minutes, and the pipeline runs continuously in Structured Streaming. The team notices that the same files are being reprocessed on every trigger, causing duplicate records. The files are never modified after delivery, and the cloud storage is immutable. Which Auto Loader configuration should be used to ensure each file is processed exactly once?

Accepted Answer

Specify a checkpoint location and ensure the stream is configured with a unique checkpoint directory.. Auto Loader uses the Structured Streaming checkpoint to maintain a record of which files have been ingested. Without a checkpoint location, the stream cannot persist this state, leading to reprocessing of files on each trigger or restart. Configuring a unique checkpoint directory ensures exactly-once processing for immutable files. This is the correct solution for eliminating duplicate records in the Bronze table.

Answer

Set cloudFiles.useNotifications to true and configure a cloud notification service.

Answer

Set cloudFiles.includeExistingFiles to false and restart the stream.

Answer

Set cloudFiles.schemaEvolutionMode to 'addNewColumns' and enable schema inference.

Question 10

A data engineer is designing an ingestion pipeline using Databricks Auto Loader to ingest JSON files from cloud storage into a Bronze Delta table. The engineer needs to ensure that the pipeline handles schema evolution, rescues malformed records, and provides efficient file discovery. Which two configurations should be set? (Choose two.)

Accepted Answer

Set cloudFiles.schemaLocation to a valid path.. The two essential configurations are setting cloudFiles.schemaEvolutionMode to 'addNewColumns' and providing a cloudFiles.schemaLocation. These enable Auto Loader to automatically add new columns and store the evolving schema, which handles schema evolution. Rescuing malformed records is handled by default via the _rescued_data column, so no additional configuration is needed. File notification mode is an optimization but not required for the stated goals.

Answer

Set cloudFiles.useNotifications to true.

Answer

Set cloudFiles.rescueDataColumn to '_rescued_data'.

Answer

Set cloudFiles.format to 'json'.