Databricks-DA-Assoc Importing Data — 25 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

Question 11

Refer to the exhibit. An analyst is attempting to read a CSV file using Spark. Why is the code failing?

Accepted Answer

The file path provided does not exist in the mounted location.. This error occurs because the specified path in DBFS does not resolve to an actual file. This often happens if the mount point was not created correctly or if the file name was mistyped. Debugging file paths is a fundamental skill for analysts, as it involves verifying storage connectivity and ensuring the file system abstraction layer correctly points to the underlying cloud storage container where the data resides.

Answer

The cluster is running on a single-node configuration.

Answer

Spark does not support reading CSV files directly from DBFS.

Answer

The user lacks sufficient memory to read the file.

Question 12

Which TWO of the following scenarios are valid use cases for using the COPY INTO command? (Choose two)

Accepted Answer

Ingesting data that arrives in files at unpredictable, irregular intervals.. COPY INTO is a powerful, idempotent command for loading data into Delta tables. It is best suited for scenarios where you need to ingest files from cloud storage periodically without setting up complex streaming infrastructure. Understanding when to use COPY INTO versus Auto Loader helps analysts choose the right balance between simplicity and advanced streaming capabilities for their specific data engineering requirements.

Answer

Implementing a continuous streaming pipeline with sub-second latency.

Answer

Performing complex, multi-stage ELT pipeline orchestration.

Answer

Ingesting historical data from a database using JDBC connectors.

Question 13

When importing data into Databricks using the 'Add Data' UI, what is the default file format for the created table if the source is a CSV file?

Accepted Answer

Delta. Databricks defaults to Delta Lake for all managed table creation because it provides ACID transactions, scalability, and time travel. Even when importing simple CSV files, Databricks automatically converts them into the Delta format to ensure that the data is queryable with high performance and reliability. Recognizing this default behavior is essential for understanding how Databricks standardizes data storage behind the scenes for all analytical workloads.

Answer

CSV

Answer

Parquet

Answer

JSON

Question 14

An analyst is using the Spark DataFrame API to read a large JSON dataset. The dataset contains nested fields that are causing schema inference to fail. Which approach best resolves this?

Accepted Answer

Define a schema explicitly using StructType.. When dealing with complex, nested JSON, relying on automated schema inference often fails or results in poor performance. Manually defining a schema using StructType allows the analyst to explicitly control data types, including handling nested structures. This approach is more robust for production-grade pipelines where data consistency is required, as it prevents schema mismatch errors during ingestion and improves overall query performance and type safety within the Spark environment.

Answer

Use the option 'mergeSchema' set to true.

Answer

Convert the JSON to CSV before reading it.

Answer

Increase the number of partitions during read.

Question 15

Which THREE actions occur when using Auto Loader with schema evolution enabled? (Choose three)

Accepted Answer

New columns found in the source files are automatically added to the target table.. Schema evolution is a powerful feature of Auto Loader that allows pipelines to adapt to changes in upstream data without requiring manual code updates. By understanding how the schema is managed, detected, and reconciled, analysts can build flexible ingestion systems that reduce maintenance overhead. This is critical in modern data environments where upstream sources frequently add or rename columns, requiring the platform to dynamically update downstream tables to accommodate the new structure.

Answer

Existing columns with data type conflicts cause the pipeline to stop immediately.

Answer

The schema is inferred from the metadata of the cloud storage provider.

Question 16

When importing data into Databricks, what is the primary benefit of using Delta Lake over standard Parquet files?

Accepted Answer

Delta Lake supports ACID transactions and schema enforcement.. Delta Lake adds a transaction log (the _delta_log folder) to Parquet files, enabling ACID transactions and time travel. This prevents data corruption during concurrent writes and allows users to query previous versions of data. This is a foundational concept for data reliability, as it ensures that analytical queries are always executed against a consistent and version-controlled state, which is impossible with standard, non-transactional Parquet files in a distributed system.

Answer

Delta Lake files require less storage space than Parquet.

Answer

Delta Lake is faster for reading large tables than Parquet.

Answer

Delta Lake is natively supported by all external BI tools.

Question 17

Which THREE of the following are valid sources for importing data into a Databricks Delta table? (Choose three)

Accepted Answer

S3, ADLS, and GCS cloud object storage.. Databricks provides flexible connectivity to ingest data from diverse sources into the Lakehouse. Whether the data is in cloud storage, a relational database, or a streaming message broker, the platform offers optimized connectors to load this information into Delta format. Mastering these integration points allows analysts to architect robust data pipelines that can pull information from virtually any enterprise system for unified analysis and reporting within a single environment.

Answer

Local files stored on the Databricks Driver node.

Answer

Direct memory buffers from the user's browser.

Question 18

A data analyst needs to ingest a daily batch of comma-separated value (CSV) files stored in cloud object storage into a Databricks Delta table. The files contain a header row, use a semicolon (;) as the delimiter, and occasionally include embedded newlines within quoted fields. Which approach ensures the parser correctly handles the multi-line fields and missing values?

Accepted Answer

Apply the spark.read.format("csv").option("delimiter", ";").option("header", "true").option("multiLine", "true").load(path) command.. Configuring the Spark CSV reader with explicit options like multiLine set to true ensures that records containing embedded newlines are not split prematurely across rows. Data analysts frequently encounter messy raw external datasets where standard single-line parsing fails, leading to corrupted row counts and schema mismatches. Explicitly defining formatting options guarantees reliable ingestion before downstream business intelligence reporting takes place.

Answer

Use spark.read.table() with default parameters because Spark automatically infers delimiters and multiline configurations for all incoming CSV files.

Answer

Convert the CSV files into Parquet format using a local Python script outside Databricks before uploading them to the workspace file system.

Answer

Execute a standard SQL COPY INTO command without specifying any options, as Delta tables automatically resolve non-standard delimiters.

Question 19

A junior data analyst attempts to load a large JSON file into a Databricks DataFrame using spark.read.json(path), but the resulting DataFrame contains numerous null values across critical columns. Upon inspection, the raw JSON records have inconsistent nesting structures and missing attributes. What is the most effective way for the analyst to inspect the inferred schema before transforming the data?

Accepted Answer

Run the schema property or printSchema() method on the DataFrame to review the hierarchical structure and data types inferred by Spark.. Using the printSchema() method on the loaded DataFrame prints out the hierarchical data types and inferred structure determined by Spark during the read operation. Data analysts rely heavily on this method to quickly identify schema evolution issues, unexpected nulls, and nested arrays before building downstream analytical queries and dashboards.

Answer

Execute the display() function on the raw file path string directly without creating a DataFrame object first.

Answer

Query the system information tables in the Hive metastore using a standard SQL DESCRIBE DATABASE statement.

Answer

Export the JSON file into Microsoft Excel locally to manually count the frequency of null values in each column.

Question 20

A data analyst needs to load a daily batch of 500 CSV files from an S3 bucket into a Delta table. The files are consistently formatted, and the analyst wants to ensure that files already processed are not re-imported in subsequent runs. Which approach is most efficient for this idempotency requirement?

Accepted Answer

Using the COPY INTO command to load the files from the S3 bucket.. COPY INTO is the ideal SQL command for idempotent batch loading from cloud object storage. It automatically tracks which files have been processed, preventing duplicate ingestion without requiring complex state management by the user. While Auto Loader is also idempotent, COPY INTO is often preferred for simpler batch SQL workflows where low-latency streaming is not a primary requirement for the analyst.

Answer

Using an INSERT INTO statement to append data from a temporary view.

Answer

Using the MERGE INTO command to check for existing records based on a key.

Answer

Creating a standard SELECT query on the cloud location and appending results.