Databricks-DE-Pro Data Ingestion and Acquisition — 25 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'.

Question 11

A data engineer is using Databricks Auto Loader to ingest CSV files from a cloud storage location. The files sometimes contain malformed rows that cause the entire ingestion to fail. The engineer wants to capture these malformed rows in a separate location for later analysis without failing the pipeline. Which Auto Loader option should be used?

Accepted Answer

Enable the rescued data column by setting the option 'rescuedDataColumn' to a column name.. To capture malformed rows without failing the pipeline, the rescued data column feature should be used. It stores unparseable data in a designated column, allowing the pipeline to continue. This data can be later analyzed or reprocessed. The option is set via 'rescuedDataColumn', which is a valid Auto Loader configuration. This approach maintains data integrity and pipeline reliability.

Answer

Set cloudFiles.permissiveMode to true to capture malformed rows in a rescued data column.

Answer

Set cloudFiles.schemaEvolutionMode to 'rescue' to capture malformed rows.

Answer

Set cloudFiles.badRecordsPath to a directory to store malformed records.

Question 12

You need to ingest a large CSV file from cloud storage into a Delta Lake table using Databricks. The CSV file has a header row and uses a pipe delimiter. You want to ensure that the ingestion is performant and the data types are correctly inferred. Which approach should you use?

Accepted Answer

Use the COPY INTO command with the appropriate options for header and delimiter.. COPY INTO is the recommended command for ingesting files from cloud storage into Delta Lake. It is idempotent, performant, and supports CSV options such as header and delimiter. It also handles schema inference and can be used for both batch and incremental ingestion, making it the best choice for this scenario.

Answer

Use spark.read.csv with inferSchema=true and write to Delta.

Answer

Use the Databricks JDBC connector to read the CSV file directly.

Answer

Use Auto Loader with cloudFiles.format set to csv and specify the delimiter.

Question 13

A data engineer is using the Databricks JDBC ingestion connector to ingest data from a PostgreSQL database into a Delta table. The engineer wants to perform incremental ingestion based on a monotonically increasing ID column. Which parameter should be used to specify the column for incremental reads?

Accepted Answer

incrementalColumn. The Databricks JDBC ingestion connector provides an incrementalColumn option to specify a column for incremental reads. This column, typically a monotonically increasing ID or timestamp, is used to track the last ingested value and fetch only new or updated rows in subsequent runs. This enables efficient incremental ingestion without manual high-water mark management, making it the correct choice.

Answer

query

Answer

partitionColumn

Answer

fetchSize

Question 14

A data engineer is ingesting data from an Azure Event Hubs instance into a Delta Lake table using Structured Streaming. The engineer needs to ensure exactly-once processing semantics and wants to minimize data duplication. Which combination of configurations and practices should be used?

Accepted Answer

Use the Event Hubs connector with checkpointing enabled and write to Delta Lake using the foreachBatch sink with idempotent writes.. Exactly-once processing with Event Hubs and Delta Lake requires checkpointing to track offsets and a sink that supports idempotent writes. Using foreachBatch with Delta Lake allows implementing deduplication by batch ID, ensuring that each batch is applied exactly once. This combination prevents duplicates even after failures. Other sink modes like append, complete, or update do not provide the necessary guarantees for exactly-once ingestion.

Answer

Use the Event Hubs connector with checkpointing disabled and write to Delta Lake using the append mode.

Answer

Use the Event Hubs connector with checkpointing enabled and write to Delta Lake using the complete mode.

Answer

Use the Event Hubs connector with checkpointing enabled and write to Delta Lake using the update mode.

Question 15

A data engineer is building a streaming ingestion pipeline using Auto Loader to ingest Parquet files from an S3 bucket into a Delta table. The engineer needs to ensure that the pipeline can handle schema evolution and that new columns added to the Parquet files are automatically added to the target Delta table. The engineer also wants to minimize manual intervention and ensure that the schema is stored persistently. Which two actions should the engineer take? (Choose two.)

Accepted Answer

Set cloudFiles.schemaEvolutionMode to 'addNewColumns' in the Auto Loader options.. To handle schema evolution and persist the schema, you must set cloudFiles.schemaEvolutionMode to 'addNewColumns' and provide a cloudFiles.schemaLocation. The former allows automatic addition of new columns, while the latter stores the inferred schema persistently so it can be reused and evolved. These two actions together ensure the pipeline adapts to schema changes without manual intervention.

Answer

Set the Delta table property 'delta.autoMerge.enabled' to true.

Answer

Configure the stream to use a checkpoint location and set the write mode to 'append'.

Answer

Enable cloudFiles.useNotifications to true to detect new files faster.

Question 16

A data engineer is designing an ingestion pipeline using Databricks Auto Loader to ingest files from a cloud storage location into a Delta table. The engineer needs to ensure that the pipeline is resilient to schema changes and can handle files that contain corrupt records. Which two features should the engineer configure? (Choose two.)

Accepted Answer

Configure a rescuedDataColumn to capture records that do not match the schema.. To handle schema changes and corrupt records in Auto Loader, the engineer should configure schema evolution to 'addNewColumns' (or 'rescue') and enable a rescuedDataColumn. The 'rescue' mode automatically captures malformed records into a rescued column, preventing stream failure. The rescuedDataColumn option provides a place to store those records. Together, they ensure resilience to both schema evolution and corrupt data, making these two features the correct choices.

Answer

Set cloudFiles.schemaLocation to a reliable storage path to store the inferred schema.

Answer

Set cloudFiles.schemaEvolutionMode to 'addNewColumns' to automatically add new columns to the schema.

Answer

Enable cloudFiles.allowOverwrites to allow files to be overwritten in the source location.

Question 17

Refer to the exhibit. You are using Auto Loader to ingest data with evolving schemas. After running the job for a week, you realize that new columns added to the source JSON are not being captured in the destination table. What must you add to the configuration?

Accepted Answer

Add 'cloudFiles.schemaEvolutionMode': 'addCol'.. By default, Auto Loader schema inference only detects the schema during the initial load. To capture schema evolution, you must explicitly enable 'cloudFiles.schemaEvolutionMode'. Without this parameter, Auto Loader ignores new fields to protect downstream consumers from breaking changes. Enabling 'addCol' allows the schema to expand dynamically, ensuring the target Delta table reflects the structure of the incoming data files as they arrive.

Answer

Add 'cloudFiles.schemaEvolutionMode': 'rescue'.

Answer

Add 'cloudFiles.maxFiles': '1000'.

Answer

Add 'cloudFiles.allowOverwrites': 'true'.

Question 18

Which approach is most appropriate for ingesting data from a JDBC source into Delta Lake where the source table has no 'updated_at' or 'version' column for incremental loading?

Accepted Answer

Perform a full overwrite of the Delta table for every load.. When a source table lacks a watermark column, you cannot use standard incremental loading techniques. The most robust approach is a full overwrite of the destination table, ensuring that the target remains a faithful copy of the source. While this can be resource-intensive for large tables, it is the only way to ensure data integrity without primary keys or timestamps to track changes.

Answer

Use the 'partitionColumn' parameter with a random UUID.

Answer

Enable streaming ingestion using the JDBC source readStream API.

Answer

Use the 'fetchSize' parameter to optimize the load.

Question 19

Your organization is ingesting sensitive PII data. You need to ensure that personal identifiers are masked during the ingestion process before they are stored in the Bronze layer of your Medallion architecture. What is the best practice for this?

Accepted Answer

Apply masking logic within the initial streaming ingestion transformation.. Implementing masking at the ingestion layer using Delta Live Tables (DLT) expectations or standard Spark transformations ensures that sensitive data is never written in plaintext to the Bronze layer. Applying transformations during the 'Acquisition' phase is a critical security practice, ensuring that governance requirements are met before data becomes available to analysts, thereby reducing the risk of accidental exposure and maintaining data privacy compliance across the pipeline.

Answer

Mask the data using Delta Lake column masking after the data reaches the Silver layer.

Answer

Configure Unity Catalog to mask columns only for specific users.

Answer

Use a post-ingestion job to delete PII columns.

Question 20

Which TWO of the following are primary benefits of using Delta Live Tables (DLT) for data ingestion over standard Structured Streaming pipelines?

Accepted Answer

Declarative pipeline management and automated dependency handling.. DLT simplifies the operational complexity of data pipelines by providing automatic infrastructure management and built-in quality controls. It abstracts the configuration required for managing checkpoints, scaling, and handling schema drift, allowing engineers to focus on defining transformations. The 'Expectations' framework and declarative pipeline management provide superior observability and data quality enforcement compared to manual, imperative coding in standard Spark Structured Streaming.

Answer

DLT supports significantly higher throughput than Structured Streaming.

Answer

DLT is the only way to read from cloud object storage.

Answer

DLT supports non-Delta storage formats for all outputs.