Databricks-DA-Assoc Importing Data Practice Test 2 — 15 Questions

Question 1

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 2

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 3

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 4

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 5

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.

Question 6

A user wants to import a local 50MB CSV file into a Databricks workspace for a quick ad-hoc analysis. Which TWO methods are available within the Databricks UI to accomplish this task directly?

Accepted Answer

Using the 'Create or modify table from file upload' feature in the 'Add' menu.. Databricks provides several entry points for local file uploads to facilitate rapid analysis. The 'Create or modify table from file upload' UI is the primary method for non-programmatic ingestion, while Unity Catalog Volumes offer a modern way to manage non-tabular data. Both options allow analysts to bypass complex cloud storage configurations when dealing with small, local datasets for immediate exploratory data science tasks.

Answer

Using the Databricks File System (DBFS) CLI to push the file to the workspace.

Answer

Using the SQL Editor to copy and paste the raw text into a new table.

Answer

Using the Workspace Sidebar 'Import' button to select the local CSV file.

Question 7

A data analyst must load a Parquet file from an S3 bucket into a Databricks DataFrame, but the bucket is in a different AWS account and requires temporary credentials. The analyst has an AWS access key ID, secret access key, and session token. Which code snippet correctly configures Spark to read the file securely?

Accepted Answer

spark.conf.set("fs.s3a.access.key", access_key)
spark.conf.set("fs.s3a.secret.key", secret_key)
spark.conf.set("fs.s3a.session.token", session_token)
df = spark.read.parquet("s3a://bucket/path/file.parquet"). To read from S3 with temporary credentials, you must set the access key, secret key, and session token using the fs.s3a.* configuration keys and use the s3a:// URI scheme. This ensures Spark authenticates correctly to the cross-account bucket. Omitting the session token or using the wrong scheme will cause failures, so all three elements are essential for a successful read.

Answer

spark.conf.set("fs.s3.access.key", access_key)
spark.conf.set("fs.s3.secret.key", secret_key)
spark.conf.set("fs.s3.session.token", session_token)
df = spark.read.parquet("s3://bucket/path/file.parquet")

Answer

spark.conf.set("fs.s3a.access.key", access_key)
spark.conf.set("fs.s3a.secret.key", secret_key)
spark.conf.set("fs.s3a.session.token", session_token)
df = spark.read.parquet("s3://bucket/path/file.parquet")

Answer

spark.conf.set("fs.s3a.access.key", access_key)
spark.conf.set("fs.s3a.secret.key", secret_key)
df = spark.read.parquet("s3a://bucket/path/file.parquet")

Question 8

A data analyst has a 5 MB pipe-delimited text file with a header row on their local laptop and needs to load it into a Databricks Unity Catalog table for a one-time analysis. They want the fastest path with the least configuration. Which approach should they use?

Accepted Answer

Use the Add Data UI to upload the file and create the table through the guided wizard.. For a small local file that needs to become a table once, the Add Data UI is the intended workflow: it uploads the file, previews it, lets the analyst confirm delimiter and header handling, and creates the table. The other approaches assume cloud storage paths or streaming infrastructure that a laptop file simply does not have.

Answer

Run COPY INTO against the local filesystem path of the downloaded file.

Answer

Configure an Auto Loader stream pointing at a cloud storage location to ingest the file.

Answer

Create an external table over the local file so the data stays in place.

Question 9

A data analyst is using the Databricks Add Data UI to import a CSV file from cloud storage into a Delta table. The analyst wants to ensure the import process handles the data correctly. Which two actions can the analyst perform directly in the Add Data UI? (Choose two.)

Accepted Answer

Specify a custom delimiter for the CSV file.. The Add Data UI allows analysts to preview data, adjust column types, and specify a custom delimiter during import. It does not support scheduling, DDL schema definition, or schema evolution, which are features of other tools like Auto Loader or Jobs.

Answer

Set up a scheduled job to automatically ingest new files from the same location.

Answer

Enable schema evolution for future files.

Answer

Define a custom schema using DDL statements.

Question 10

A data analyst needs to quickly upload a small CSV file from their local machine into Databricks to explore its contents. They want to avoid writing code and prefer a graphical interface. Which Databricks feature should they use?

Accepted Answer

The Add Data UI. The Add Data UI is specifically designed for uploading local files through a graphical interface, automatically creating tables and inferring schema. It requires no coding and is perfect for quick exploration. Other options involve command-line or programmatic interfaces, which are not as user-friendly for ad-hoc uploads.

Answer

The Spark DataFrame API

Answer

The Databricks CLI

Answer

The DBFS CLI

Question 11

A data analyst runs COPY INTO to load new CSV files from an S3 path into a Delta table every night. One night the job completes successfully but the row count does not change, even though new files were placed in the source path. The files have the same names as files loaded previously. What is the most likely cause?

Accepted Answer

COPY INTO skips files whose paths were already loaded unless the load is forced or the files are modified.. COPY INTO is idempotent by design: it records the paths it has loaded and skips them on subsequent runs unless the files are new or the load is explicitly forced. Because the new files reused prior names, their paths matched the load history, so no rows were added despite a successful job status.

Answer

The CSV files were written with a different compression codec, so COPY INTO could not parse them.

Answer

The Delta table's transaction log has reached its retention limit and stopped accepting new commits.

Answer

Auto Loader had already claimed the files, so COPY INTO is blocked from reading them.

Question 12

An analyst is loading a CSV file where one column contains values like 007, 012, and 003. After creating a table through the Add Data UI, those values appear as 7, 12, and 3. The analyst needs the leading zeros preserved for downstream reporting. What should the analyst do?

Accepted Answer

Specify the column as a string type in the schema settings before creating the table.. Leading zeros carry meaning only in text form, so the column must be declared as a string before the table is created. Letting the reader infer an integer type silently drops the zeros, and no post-load fix reliably restores them. Overriding the type in the schema settings preserves the original values.

Answer

Apply a formatting function after loading that pads the numbers back to three characters.

Answer

Enable schema evolution so the column can switch types after the first load.

Answer

Set the file's delimiter to a character that prevents numeric interpretation of the column.

Question 13

A data analyst is setting up a recurring ingestion job that must load only new files from a cloud storage directory into a Delta table, must track which files have already been processed, and must fail clearly if the source schema changes unexpectedly. Which TWO capabilities should the analyst rely on? (Choose two.)

Accepted Answer

Auto Loader with a checkpoint location tracks processed files and supports schema enforcement or evolution policies.. Both COPY INTO and Auto Loader maintain ingestion state so only new files are processed, and both can be configured to surface unexpected schema changes rather than silently accepting them. COPY INTO uses table-level load history, while Auto Loader uses a checkpoint directory with an explicit schema policy. Either satisfies the incremental and schema-safety requirements.

Answer

Mounting the storage location and using spark.read on the directory with overwrite mode.

Answer

CREATE TABLE AS SELECT on the source directory rebuilds the table from all files on every run.

Answer

A scheduled notebook that lists the directory and compares filenames to a manually maintained table.

Question 14

An analyst is loading Parquet files from cloud storage into a Delta table using COPY INTO. The source directory contains files with several different schemas because upstream teams added columns over time. The analyst wants the table to accept the union of all columns without manual intervention. Which COPY INTO behavior should the analyst configure?

Accepted Answer

Set mergeSchema to true in the COPY INTO options so new columns are added to the target table.. The mergeSchema option in COPY INTO adds any columns found in the source files to the target table, producing the union of columns across heterogeneous files. This matches the requirement to accept new columns without manual ALTER TABLE statements, while retaining all previously loaded data.

Answer

Enable overwrite mode so each run replaces the table with the latest file's schema.

Answer

Create the target table with a manually defined superset schema containing every possible column.

Answer

Use a permissive mode that silently drops columns not present in the target table schema.

Question 15

A data analyst is using the Databricks SQL Connector for Python to query a large Delta table. The query returns millions of rows, and the analyst wants to process the results in batches to avoid memory issues. Which method should the analyst use to fetch rows in chunks?

Accepted Answer

cursor.fetchmany(size). The fetchmany(size) method allows fetching a specified number of rows at a time, enabling batch processing of large result sets. This is the standard way to avoid loading all rows into memory. Other methods either fetch all rows, one row, or require manual query modifications.

Answer

cursor.fetchone()

Answer

cursor.fetchall()

Answer

cursor.execute() with a LIMIT clause