Databricks-DE-Assoc Data Ingestion and Loading — All Questions With Answers

Question 1

A company is ingesting large Parquet files into a Delta table. They notice that while the ingestion is fast, subsequent queries on the table are slow because each ingestion creates a few very large files. Which feature should be enabled to optimize the file size during ingestion?

Accepted Answer

The 'Optimized Writes' feature in the Spark configuration.. The stem states the ingestion creates 'very large files' which causes slow queries. Optimized Writes is a feature specifically designed to coalesce small files into larger, optimally sized files during write operations. If the files are already too large, Optimized Writes would not be the correct solution, and the explanation provided is inconsistent with the problem described in the stem.

Answer

Delta Lake Liquid Clustering to re-organize data during the write.

Answer

The 'VACUUM' command to remove old file versions.

Answer

Standard Spark partition dynamic allocation to balance the load.

Question 2

A data engineer is configuring an Auto Loader stream to ingest JSON files from an Azure Blob Storage container into a Delta table. The source files frequently have schema evolutions, such as new nested fields being added. Which setting should be enabled in the Auto Loader readStream configuration to automatically capture and incorporate these schema changes into the target Delta table without failing the stream?

Accepted Answer

spark.databricks.cloudFiles.schemaEvolution.mode = rescue. The option 'spark.databricks.cloudFiles.schemaEvolution.mode' should be set to 'addNewColumns' to automatically incorporate new nested fields into the target Delta table schema. 'rescue' mode is used to capture malformed or unexpected data into a column, but it does not evolve the table schema to include the new fields as top-level columns.

Answer

spark.databricks.cloudFiles.schemaEvolution.mode = overwrite

Answer

spark.databricks.cloudFiles.inferSchema = true

Answer

spark.databricks.delta.autoOptimize.enabled = true

Question 3

A data engineer is designing an ingestion pipeline to load data from a Kafka topic into a Delta Lake table using Databricks Structured Streaming. The pipeline must ensure exactly-once processing semantics and handle schema changes in the incoming JSON messages. Which TWO configurations or features should the engineer use? (Choose two.)

Accepted Answer

Set the checkpointLocation option to a reliable cloud storage path. To achieve exactly-once processing with Kafka and Delta Lake, a reliable checkpoint location is mandatory to track offsets and state. For schema changes, Delta Lake's mergeSchema option allows automatic addition of new columns during writes, accommodating evolving JSON messages. Together, these configurations ensure the stream can recover without duplicates and adapt to new fields. The other options either use the wrong ingestion tool, risk data loss, or do not contribute to the stated requirements.

Answer

Configure the Kafka source with failOnDataLoss set to true

Answer

Set the startingOffsets option to 'latest' on every restart

Answer

Enable Auto Loader with cloudFiles.schemaEvolutionMode set to 'addNewColumns'

Question 4

A data engineer needs to ingest a large CSV file from cloud storage into a Delta table using Databricks SQL. The file has a header row, and the engineer wants to create the table in one command while inferring the schema automatically. Which SQL command should be used?

Accepted Answer

COPY INTO target_table FROM '/path/to/file.csv' FILEFORMAT = CSV. COPY INTO is the recommended Databricks SQL command for ingesting files into Delta tables. It supports schema inference, automatically handles CSV headers, and creates the target table if it does not exist. It also provides idempotency by tracking which files have been ingested. The other options either require pre-existing tables, do not infer schema, or create external tables instead of Delta tables, failing to meet the one-command requirement.

Answer

INSERT INTO target_table SELECT * FROM '/path/to/file.csv'

Answer

CREATE TABLE ... USING DELTA AS SELECT * FROM csv.`/path/to/file.csv`

Answer

CREATE EXTERNAL TABLE target_table USING CSV OPTIONS (path '/path/to/file.csv', header 'true')

Question 5

A data engineer is using Databricks Auto Loader to stream CSV files from an Azure Data Lake Storage Gen2 container into a Delta table. The files are constantly appended, and the engineer needs to ensure that the ingested data includes the file path and ingestion timestamp for auditing. Which Auto Loader feature should be used to add these metadata columns?

Accepted Answer

Use the `_metadata` column with `file_path` and `file_modification_time`.. Auto Loader automatically includes a `_metadata` column that contains `file_path` and `file_modification_time`. By explicitly selecting these fields, the engineer can persist them into the Delta table for auditing. This is the intended mechanism for capturing file-level metadata during streaming ingestion with Auto Loader.

Answer

Use the `input_file_name()` SQL function in a SELECT statement.

Answer

Set the option `cloudFiles.includeExistingFiles` to `true`.

Answer

Enable schema evolution by setting `cloudFiles.schemaEvolutionMode` to `addNewColumns`.

Question 6

A data engineer is building a nightly ingestion pipeline that reads CSV files from a cloud storage location into a Delta table. The files are known to have a header row, and the engineer wants to ensure that the header is not treated as data. The engineer also wants to automatically infer the schema from the file contents. Which combination of options should be used with the COPY INTO command to achieve this?

Accepted Answer

Use COPY INTO with the options 'header' = 'true' and 'inferSchema' = 'true'.. The COPY INTO command in Databricks supports options to control CSV parsing. The 'header' option specifies that the first row contains column names, and 'inferSchema' enables automatic schema inference. These are the correct options to handle the header and infer the schema. Other options like 'mergeSchema' or 'schemaEvolution' serve different purposes and do not provide automatic schema inference.

Answer

Use COPY INTO with the options 'header' = 'true' and 'schemaEvolution' = 'true'.

Answer

Use COPY INTO with the options 'firstRowAsHeader' = 'true' and 'inferSchema' = 'true'.

Answer

Use COPY INTO with the options 'header' = 'true' and 'mergeSchema' = 'true'.

Question 7

A data engineer needs to load a large CSV file from cloud storage into a Delta table using Databricks SQL. The file has a header row and uses a pipe delimiter. The engineer wants to use the COPY INTO command. Which SQL statement will correctly load the data?

Accepted Answer

COPY INTO my_table FROM 's3://bucket/path/' FILEFORMAT = CSV OPTIONS ('header' = 'true', 'delimiter' = '|'). The COPY INTO command requires the FILEFORMAT clause and an OPTIONS clause with string values. The correct statement uses FILEFORMAT = CSV and OPTIONS ('header' = 'true', 'delimiter' = '|') to properly parse the pipe-delimited CSV with a header row. This ensures the data is loaded correctly into the Delta table.

Answer

COPY INTO my_table FROM 's3://bucket/path/' FILEFORMAT = CSV WITH (HEADER = TRUE, DELIMITER = '|')

Answer

COPY INTO my_table FROM 's3://bucket/path/' FORMAT_OPTIONS ('header' = 'true', 'delimiter' = '|')

Answer

COPY INTO my_table FROM 's3://bucket/path/' FILEFORMAT = CSV OPTIONS (header = true, delimiter = '|')

Question 8

A data engineer is designing an ingestion pipeline using Databricks Auto Loader to ingest Parquet files from cloud storage into a Delta table. The engineer wants to ensure that the pipeline can handle schema evolution and rescue malformed records. Which TWO options should be configured? (Choose two.)

Accepted Answer

Set cloudFiles.schemaEvolutionMode to 'addNewColumns'.. To handle schema evolution and rescue malformed records, Auto Loader requires a schemaLocation to persist the schema and a schemaEvolutionMode that allows adding new columns. These work together to track changes and rescue data that doesn't match the schema, ensuring pipeline resilience.

Answer

Set cloudFiles.format to 'parquet'.

Answer

Set cloudFiles.inferColumnTypes to 'true'.

Answer

Set cloudFiles.rescueDataColumn to '_rescued_data'.

Question 9

A data engineer is designing a pipeline to ingest data from a REST API that returns JSON responses. The API supports pagination and rate limiting. The engineer needs to load the data into a Delta table and wants to ensure that the ingestion is incremental and can handle API rate limits gracefully. Which two approaches should the engineer use? (Choose two.)

Accepted Answer

Use Auto Loader with the 'cloudFiles.format' set to 'json' to ingest the API responses saved to cloud storage.. For incremental ingestion from a REST API, two viable approaches are using Auto Loader on JSON files saved to cloud storage, or using a Databricks job with a Python script that handles API interactions and writes to Delta. Auto Loader provides incremental file processing, while a custom script offers control over pagination and rate limiting. The other options are either not designed for APIs or lack distributed processing.

Answer

Use the Spark DataFrame API with the 'requests' library to fetch data from the API and write to Delta.

Answer

Use Structured Streaming with the 'rate' source to simulate API calls.

Answer

Use the Databricks Lakehouse Federation to query the API directly without ingesting data.

Question 10

A data engineer is loading a batch of CSV files into a Delta table using the COPY INTO command. The source directory contains both new files and files that were loaded successfully in a previous run. The engineer notices that the second run reprocesses all files, causing duplicate records. Which COPY INTO option should the engineer use to avoid reprocessing already-loaded files?

Accepted Answer

Ensure the COPY INTO command does not include the FILES clause, so it uses the default file tracking.. COPY INTO maintains a record of files that have been successfully loaded into the target Delta table. By default, when no FILES clause is specified, it reads only new files in the source path and skips those already ingested. This prevents duplicate records in recurring loads. Using options like mergeSchema or FORCE does not address file tracking, and manually listing files is not scalable. Therefore, relying on the default file tracking by omitting the FILES clause is the correct approach.

Answer

Use the FORCE = TRUE option to skip already-loaded files.

Answer

Use the COPY_OPTIONS clause with mergeSchema set to true.

Answer

Use the FILES option to specify only the new files to load.

Question 11

A data engineer is using Databricks Auto Loader to ingest CSV files from an AWS S3 bucket into a Delta table. The engineer wants to ensure that the ingestion process automatically handles files that arrive with a different delimiter, such as semicolon instead of comma. Which Auto Loader option should be used to specify the delimiter?

Accepted Answer

Set cloudFiles.delimiter to ';'.. Auto Loader uses the cloudFiles.delimiter option to specify the field delimiter for CSV files. This allows ingestion of files with non-standard delimiters like semicolons. Other options either use invalid keys or incorrect prefixes, and thus will not work.

Answer

Set cloudFiles.format to 'csv' and cloudFiles.sep to ';'.

Answer

Set cloudFiles.option.delimiter to ';'.

Answer

Set cloudFiles.csv.delimiter to ';'.

Question 12

A data engineer is using the COPY INTO command to load Parquet files from a cloud storage location into a Delta table. The engineer notices that some files have been modified since the last load and wants to ensure that only new or updated files are ingested in the next run. Which COPY INTO option should be used to achieve this?

Accepted Answer

Default behavior (no additional option). COPY INTO by default tracks which files have been loaded and skips them on subsequent runs. If files are modified, their metadata changes, and COPY INTO may treat them as new. Therefore, using the default behavior without FORCE ensures only new or updated files are ingested, preventing duplicates.

Answer

FORCE = true

Answer

COPY_OPTIONS

Answer

LOADED_FILES

Question 13

A data engineer is using Auto Loader to ingest CSV files from a cloud storage location into a Delta table. The engineer wants to ensure that any malformed records that do not match the schema are captured for later analysis instead of causing the stream to fail. Which Auto Loader option should the engineer configure to achieve this?

Accepted Answer

Set 'cloudFiles.schemaEvolutionMode' to 'rescue'.. Auto Loader's 'rescue' schema evolution mode is designed to capture data that cannot be parsed according to the schema into a special rescued data column. This allows the stream to continue processing valid records while preserving malformed ones for later inspection. The other modes either evolve the schema, fail on new columns, or do nothing, none of which capture malformed records.

Answer

Set 'cloudFiles.schemaEvolutionMode' to 'addNewColumns'.

Answer

Set 'cloudFiles.schemaEvolutionMode' to 'none'.

Answer

Set 'cloudFiles.schemaEvolutionMode' to 'failOnNewColumns'.

Question 14

A data engineer is using Databricks Auto Loader to ingest data from a cloud storage directory that contains a mix of CSV and JSON files. The engineer wants to ingest only the CSV files and ignore the JSON files. Which approach should be used to filter the files?

Accepted Answer

Use cloudFiles.includeExistingFiles and cloudFiles.pathGlobFilter with '*.csv'.. To ingest only CSV files, use cloudFiles.pathGlobFilter with a pattern like '*.csv' to filter at the file discovery level. The format should be set to 'csv' to parse them correctly. Other options either do not filter or use invalid settings.

Answer

Use cloudFiles.format set to 'csv' and rely on Auto Loader to automatically ignore non-CSV files.

Answer

Use cloudFiles.pathGlobFilter with '*.csv' and set cloudFiles.format to 'json'.

Answer

Use cloudFiles.filter to specify a SQL expression 'file_name LIKE \'%.csv\''.

Question 15

A data engineer is using Auto Loader to ingest CSV files from a cloud storage location into a Delta table. The engineer wants to ensure that the ingestion process handles schema evolution gracefully when new columns are added to the CSV files. Which TWO actions should the engineer take to enable schema evolution and capture any data that does not match the evolving schema? (Choose two.)

Accepted Answer

Set the option cloudFiles.schemaEvolutionMode to 'addNewColumns' and also set cloudFiles.rescuedDataColumn to a column name.. To handle schema evolution gracefully, the engineer should enable automatic addition of new columns by setting cloudFiles.schemaEvolutionMode to 'addNewColumns'. Additionally, to capture data that does not conform to the schema, the engineer should enable the rescued data column by setting cloudFiles.rescuedDataColumn to a column name. This combination allows new columns to be added while preserving any malformed or unexpected data. Setting the mode to 'rescue' alone disables schema evolution, and 'failOnNewColumns' would cause failures. Schema hints cannot predict future columns.

Answer

Enable the rescued data column by setting cloudFiles.schemaEvolutionMode to 'rescue'.

Answer

Set the option cloudFiles.schemaHints to define all possible future columns.

Answer

Set the option cloudFiles.schemaEvolutionMode to 'failOnNewColumns'.

Question 16

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?

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 17

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 18

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 19

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 20

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.

Databricks-DE-Assoc Data Ingestion and Loading — All Questions With Answers

A company is ingesting large Parquet files into a Delta table. They notice that while the ingestion is fast, subsequent queries on the table are slow because each ingestion creates a few very large files. Which feature should be enabled to optimize the file size during ingestion?

A data engineer needs to ingest a large CSV file from cloud storage into a Delta table using Databricks SQL. The file has a header row, and the engineer wants to create the table in one command while inferring the schema automatically. Which SQL command should be used?

A data engineer needs to load a large CSV file from cloud storage into a Delta table using Databricks SQL. The file has a header row and uses a pipe delimiter. The engineer wants to use the COPY INTO command. Which SQL statement will correctly load the data?

A data engineer is using Databricks Auto Loader to ingest data from a cloud storage directory that contains a mix of CSV and JSON files. The engineer wants to ingest only the CSV files and ignore the JSON files. Which approach should be used to filter the files?

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?

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?

Exhibit

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?

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?

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?

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?

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?

Exhibit

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

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?

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?

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?

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?

A data engineer needs to ingest a large CSV file from cloud storage into a Delta table using Databricks SQL. The engineer wants to perform a one-time load and ensure that the operation is atomic. Which command should be used?

A data engineer is designing an ingestion pipeline using Databricks Auto Loader to process JSON files from an S3 bucket. The pipeline must handle schema evolution and ensure that data is ingested exactly once. Which two features of Auto Loader support these requirements? (Choose two.)

A data engineer is using Auto Loader to ingest data from a Kafka topic into a Delta table. The engineer wants to ensure that the ingestion handles late-arriving data and provides exactly-once semantics. Which combination of features should the engineer use?

Databricks-DE-Assoc Data Ingestion and Loading — All Questions With Answers

A company is ingesting large Parquet files into a Delta table. They notice that while the ingestion is fast, subsequent queries on the table are slow because each ingestion creates a few very large files. Which feature should be enabled to optimize the file size during ingestion?

A data engineer needs to ingest a large CSV file from cloud storage into a Delta table using Databricks SQL. The file has a header row, and the engineer wants to create the table in one command while inferring the schema automatically. Which SQL command should be used?

A data engineer needs to load a large CSV file from cloud storage into a Delta table using Databricks SQL. The file has a header row and uses a pipe delimiter. The engineer wants to use the COPY INTO command. Which SQL statement will correctly load the data?

A data engineer is using Databricks Auto Loader to ingest data from a cloud storage directory that contains a mix of CSV and JSON files. The engineer wants to ingest only the CSV files and ignore the JSON files. Which approach should be used to filter the files?

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?

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?

Exhibit

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?

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?

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?

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?

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?

Exhibit

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

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?

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?

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?

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?

A data engineer needs to ingest a large CSV file from cloud storage into a Delta table using Databricks SQL. The engineer wants to perform a one-time load and ensure that the operation is atomic. Which command should be used?

A data engineer is designing an ingestion pipeline using Databricks Auto Loader to process JSON files from an S3 bucket. The pipeline must handle schema evolution and ensure that data is ingested exactly once. Which two features of Auto Loader support these requirements? (Choose two.)

A data engineer is using Auto Loader to ingest data from a Kafka topic into a Delta table. The engineer wants to ensure that the ingestion handles late-arriving data and provides exactly-once semantics. Which combination of features should the engineer use?