Databricks-DA-Assoc Importing Data — Timed 10 Questions

Question 1

Refer to the exhibit. The analyst finds that the resulting DataFrame has column names like '_c0', '_c1'. What is missing in the configuration?

Accepted Answer

The 'sep' option to define the delimiter.. When Spark reads a CSV file and produces column names like '_c0', '_c1', it indicates that Spark did not find a header row. This happens because either the 'header' option was not set to 'true', or the delimiter (sep) in the file does not match the default (comma), causing the entire first line (including the header) to be parsed as a single column or incorrectly split, thus failing to identify the header row.

Answer

The 'inferSchema' option set to 'true'.

Answer

The 'mode' option set to 'PERMISSIVE'.

Answer

The 'encoding' option for UTF-8.

Question 2

A data analyst is creating a Databricks SQL dashboard that must ingest a Parquet file stored in cloud object storage. The analyst wants to define the table using pure SQL in the Databricks SQL editor, without using Python or notebooks. Which SQL command should be used to create a table that reads directly from the Parquet file?

Accepted Answer

CREATE TABLE sales USING parquet LOCATION 's3://bucket/sales.parquet'. The correct SQL command to create a table that reads directly from a Parquet file in cloud storage is CREATE TABLE ... USING parquet LOCATION 'path'. This defines an external table in Databricks SQL, allowing the analyst to query the data without moving it. Other options either copy the data, use outdated syntax, or create a view instead of a table.

Answer

CREATE TABLE sales AS SELECT * FROM parquet.`s3://bucket/sales.parquet`

Answer

CREATE VIEW sales AS SELECT * FROM parquet.`s3://bucket/sales.parquet`

Answer

CREATE EXTERNAL TABLE sales (id INT, amount DOUBLE) STORED AS PARQUET LOCATION 's3://bucket/sales.parquet'

Question 3

A data analyst wants to upload a small CSV file from their local machine to the Databricks workspace to analyze it in a notebook. They need a quick, one-time upload without writing code. Which Databricks feature should they use?

Accepted Answer

Add Data UI. The Add Data UI is designed for quick, code-free data uploads. It allows analysts to upload local files, preview data, and create tables with a few clicks. Other options require coding, command-line tools, or are less direct for one-time uploads.

Answer

Databricks CLI

Answer

DBFS File Browser in the Databricks UI

Answer

dbutils.fs.put() in a notebook

Question 4

A data analyst is ingesting a stream of JSON event files from cloud object storage into a Bronze Delta table using Auto Loader in a Databricks notebook. The source directory receives new, uniquely named files continuously, and the analyst sets the schema location to a Unity Catalog volume path. After several days, the analyst notices that some newly arriving files are being ignored entirely, while others are processed. Which Auto Loader behavior most likely explains why a subset of files is never ingested?

Accepted Answer

Auto Loader uses a RocksDB-backed file notification or directory listing state store keyed by file path; if older files are renamed into the directory, they may be treated as already seen and skipped.. Auto Loader maintains a persistent state store (typically backed by RocksDB) under the schema location to track which file paths have already been discovered and processed. When a file with a previously recorded path reappears—common with rename-based ETL that writes to a temp name and renames into the target directory—the stream treats it as already seen and skips it. This produces the pattern of silently ignored files while other, genuinely new paths continue to be ingested.

Answer

Auto Loader only ingests files smaller than the value set by cloudFiles.maxFileAge, so files older than the threshold are dropped before schema inference.

Answer

Auto Loader requires each file to have a unique _metadata.file_path column generated by the source system; files without that column are discarded by the ingestion engine.

Answer

Auto Loader deduplicates records by primary key across files, so any event whose identifier was previously seen is filtered out at read time.

Question 5

A data analyst is creating a Delta table from a Parquet file stored in DBFS. They use the following SQL command: CREATE TABLE my_table USING DELTA LOCATION '/mnt/data/parquet_files/'. After running the command, they notice the table contains no data. What is the most likely cause?

Accepted Answer

The Parquet files are not in Delta format, so the Delta table cannot read them.. Delta tables rely on a transaction log to identify data files. A directory of Parquet files without the _delta_log will result in an empty table when created with USING DELTA. The analyst must either convert the Parquet files to Delta or create a Parquet table instead.

Answer

The table was created as an external table, but no data was inserted.

Answer

The LOCATION path points to a directory containing Parquet files, but the table was created without specifying the data source format.

Answer

The LOCATION path must be an absolute path starting with 'dbfs:/' or 's3://'.

Question 6

A data analyst must load a one-time snapshot of a JDBC-accessible PostgreSQL database into a Delta table in Unity Catalog using a Databricks notebook. The analyst wants to minimize cluster memory pressure and enable downstream predicate pushdown on the resulting Delta table. Which two configuration choices should the analyst apply? (Choose two.)

Accepted Answer

Write the DataFrame using df.write.format('delta').mode('overwrite').saveAsTable('catalog.schema.tbl') so the snapshot is materialized as a managed Delta table.. Parallel JDBC reads via partitionColumn, lowerBound, upperBound, and numPartitions split the extraction across executors, which reduces per-task memory pressure and shortens load time. Writing the resulting DataFrame with the Delta writer using saveAsTable into a Unity Catalog three-part name materializes the snapshot as a managed Delta table whose statistics support downstream predicate pushdown. Together these choices address both the read bottleneck and the target table's query performance.

Answer

Read the source using spark.read.jdbc with a single connection and rely on Delta Lake's automatic file compaction to parallelize the load.

Answer

Use CREATE TABLE ... USING DELTA LOCATION to register the Delta table in Unity Catalog, relying on the managed flag to preserve the data.

Answer

Enable spark.databricks.delta.optimizeWrite.enabled and set spark.sql.adaptive.enabled to false to force a single shuffle partition.

Question 7

A data analyst is loading a CSV dataset into a Delta table using spark.read.csv with header=true and inferSchema=true. The CSV contains an ID column whose values are numeric but must be preserved as strings to avoid losing leading zeros. The analyst notices that the resulting Delta table has the ID column typed as bigint, and downstream joins against a string-typed source are failing. Which change should the analyst make to the load to produce the correct schema?

Accepted Answer

Set inferSchema to false and rely on the default all-string schema, then cast only the columns that need numeric types.. Type inference in the CSV reader examines sample values and will classify a numeric-looking ID column as bigint, dropping leading zeros. Disabling inference makes every column a string, after which the analyst can cast only the columns that truly need numeric types. This yields a Delta table whose ID column is string-typed, preserving the values and allowing joins against the string-typed source to succeed.

Answer

Add the option mode='PERMISSIVE' to the read so the ID values are kept as strings while other columns are inferred.

Answer

Pass a schema via the schema option that declares the ID column as StringType, and let the remaining columns be inferred automatically.

Answer

Set the option columnNameOfCorruptRecord to 'ID' so the CSV reader treats the ID field as an opaque string column.

Question 8

A data analyst is importing a 30 GB CSV dataset from an ADLS Gen2 location into a Bronze Delta table in Unity Catalog. The CSV has a header row, uses commas as delimiters, and contains quoted fields with embedded commas. The analyst runs spark.read.option("header", "true").option("inferSchema", "true").csv("abfss://raw@storage.dfs.core.windows.net/events/") and then writes the DataFrame to a Delta table. The resulting table has several columns typed as strings that should be numeric, and a few rows are misaligned with values shifted into the wrong columns. Which change should the analyst make to correct BOTH the type inference and the misaligned rows?

Accepted Answer

Specify an explicit schema with the correct numeric types and add .option("quote", "\"") and .option("escape", "\"") to correctly parse quoted fields containing commas.. The misaligned rows are caused by quoted fields containing commas being split as if the commas were delimiters. The correct fix is to ensure the quote and escape characters are configured so quoted fields are treated as single values. The string-typed numeric columns are caused by inferSchema sampling a subset of the 30 GB file and choosing string types for ambiguous values. Supplying an explicit schema removes that uncertainty and enforces the intended types.

Answer

Add .option("multiLine", "true") and keep inferSchema enabled so Spark samples the full file for both parsing and typing.

Answer

Add .option("escape", "\") to the read to handle embedded commas, and keep inferSchema enabled.

Answer

Disable inferSchema and set samplingRatio to 1.0 to force Spark to scan the entire file and infer correct types and delimiters.

Question 9

A data analyst needs to ingest a large volume of CSV files from an external S3 bucket into a Delta table. Which method provides the most efficient, fault-tolerant, and incremental loading approach in Databricks?

Accepted Answer

Implement Auto Loader using cloudFiles source.. Auto Loader is specifically designed to process files as they arrive in cloud storage. It maintains state information in a checkpoint location, ensuring that only new or modified files are ingested during subsequent runs. This pattern is critical for production pipelines where data volume scales over time, as it avoids full scans of the source directory, significantly reducing latency and compute costs while ensuring idempotent processing for reliability.

Answer

Use the COPY INTO command with a static file path.

Answer

Manually read files into a DataFrame and use write.mode('append').

Answer

Use the Databricks SQL 'Import' wizard via the UI.

Question 10

An analyst is preparing to upload a small local CSV file to a Databricks workspace. Which tool is most appropriate for a quick, one-time upload without requiring infrastructure setup?

Accepted Answer

Databricks Add Data UI. The 'Add Data' UI in Databricks provides a user-friendly interface to upload small files directly from a local machine to DBFS. This approach is ideal for exploratory analysis or small datasets where setting up external storage or complex ingestion pipelines is unnecessary. Understanding this tool allows analysts to quickly pivot from local analysis to platform-based workflows without needing deep knowledge of cloud storage configurations.

Answer

Auto Loader

Answer

COPY INTO command

Answer

Databricks Connect