Databricks-DE-Assoc Data Ingestion and Loading — 15 Questions

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?

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

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'.