Importing Data practice questions

The 'inferSchema' option set to 'true'.

Why it fails: InferSchema controls data type detection (e.g., distinguishing between strings and integers), not column name identification. Even with it set to false, Spark should respect the header row. The issue of _c0/c1 suggests the header row itself is being interpreted as the first row of data, not a schema issue.

The 'mode' option set to 'PERMISSIVE'.

Why it fails: The 'mode' option controls how Spark handles corrupt records during ingestion (e.g., dropping them or throwing an exception). It has no effect on how Spark identifies headers or assigns column names. The default behavior is usually sufficient for standard CSV ingestion tasks involving well-formatted files.

The 'encoding' option for UTF-8.

Why it fails: While encoding can sometimes cause file-reading issues, the specific symptom of _c0/c1 implies the structure of the CSV header is not being identified. UTF-8 is the default encoding for Spark, and specifying it rarely fixes issues where headers are incorrectly treated as rows; delimiter issues are the primary culprit.

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

Why it fails: While this CTAS statement would create a table by reading the Parquet file, it copies the data into a new Delta table, not an external table. The scenario asks for a table that reads directly from the file, and using CTAS duplicates storage and does not maintain a link to the original file. It also requires the file to be accessible during creation.

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

Why it fails: Creating a view does not persist the table definition or metadata in the same way as a table. While a view can query the Parquet file, it is not a table and may not be suitable for dashboard performance or for other users to discover. The scenario requires a table, not a view, to be used as a data source for a dashboard.

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

Why it fails: This is Hive syntax for external tables and is not the recommended approach in Databricks SQL. Databricks SQL uses the USING clause to specify the format, and the schema is inferred from the files if not provided. Specifying columns manually is unnecessary and error-prone, and this syntax may not be supported in all Databricks SQL versions.

Databricks CLI

Why it fails: The Databricks CLI is a command-line tool that requires installation and configuration. It is not a quick, code-free method for uploading a single file. While it can be used for automation, the scenario asks for a quick, one-time upload without writing code, making the CLI unnecessarily complex.

DBFS File Browser in the Databricks UI

Why it fails: The DBFS File Browser allows browsing and managing files in DBFS, but it does not provide a direct upload button for local files in all versions. While you can sometimes upload via the UI, the primary method for quick, code-free upload is the Add Data UI, which guides you through uploading and creating a table.

dbutils.fs.put() in a notebook

Why it fails: dbutils.fs.put() is a programmatic method to write files to DBFS, but it requires writing code in a notebook. It is not a code-free, quick upload method. The scenario explicitly asks for a quick upload without writing code, so this option is not appropriate.

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

Why it fails: cloudFiles.maxFileAge controls how far back Auto Loader looks when bootstrapping a stream; it is not a size limit and does not silently drop files that are older than the threshold once the stream is running. The described behavior is inconsistent with this option because the setting governs discovery scope, not per-file retention.

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.

Why it fails: The _metadata column is produced by Auto Loader itself, not by the writer. Source files do not need to carry a file_path field for ingestion to succeed. Missing writer-side metadata would not cause selective skipping of files, so this explanation does not match the observed behavior where some new files are processed and others are not.

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

Why it fails: Auto Loader does not perform primary-key deduplication; it ingests files and can optionally apply schema evolution, but record-level dedup is the analyst's responsibility (for example, MERGE INTO). This option misattributes a data-quality behavior to the ingestion layer and would not explain why entire files are skipped rather than individual records.

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

Why it fails: When creating a table with LOCATION, it is an external table that points to existing data. No separate insert is needed if the data is already in Delta format. The problem is that the data is in Parquet format, not Delta, so the table cannot see any data. The external table property is not the cause.

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

Why it fails: When creating a Delta table with LOCATION, the path must contain Delta files, not raw Parquet files. If the directory contains Parquet files, the table will be empty because Delta expects a transaction log. Specifying the data source format is not the issue; the format is already set to DELTA. The error is that the files are not in Delta format.

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

Why it fails: The path '/mnt/data/parquet_files/' is a valid DBFS path, as DBFS paths can be specified with or without the 'dbfs:/' prefix in some contexts. The issue is not the path format but the file format. The table creation would succeed, but the table would be empty because the files are not Delta.

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

Why it fails: A single-connection JDBC read pulls all rows through one task, which is the opposite of the parallel-read goal and can exhaust executor memory on large tables. Delta's OPTIMIZE compacts existing small files after the data is written; it does not parallelize the initial extraction. This option misplaces the performance lever on the write phase instead of the read phase.

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

Why it fails: CREATE TABLE ... USING DELTA LOCATION creates an external table that references an existing storage path; it does not ingest data and is not a managed table. In Unity Catalog, managed versus external affects lifecycle and permissions, but this statement does not perform the one-time snapshot load the scenario requires and would leave the table pointing at a path with no data.

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

Why it fails: Disabling adaptive query execution forces static shuffle partitions and typically increases spill and skew risk, working against the goal of reducing memory pressure. Delta optimized writes are helpful for file sizing but do not parallelize the JDBC read. This combination does not address the scenario's read-side bottleneck and would likely degrade the write step.

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

Why it fails: The mode option governs how malformed rows are handled (PERMISSIVE, DROPMALFORMED, FAILFAST); it does not influence type inference. Setting mode to PERMISSIVE would still produce a bigint ID column because inference is independent of parse mode. This option misattributes row-parsing behavior to schema typing and would not fix the leading-zero loss.

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

Why it fails: Spark's CSV reader accepts either a full user-supplied schema or inference; it does not merge a partial schema with inferred types for the remaining columns. Supplying a schema that only covers the ID column would cause the remaining columns to be missing or misaligned. The correct pattern is to define an explicit schema for all columns when overriding inference.

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

Why it fails: columnNameOfCorruptRecord specifies where to place the raw text of rows that fail parsing under PERMISSIVE mode; it is not a mechanism for retyping a specific column. Repurposing it as the ID column name would collide with the real ID field and produce confusing results. This option confuses error-capture configuration with schema control.

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

Why it fails: multiLine is intended for records that span multiple physical lines, such as embedded newlines inside quoted fields. It does not improve delimiter handling for embedded commas and does not guarantee more accurate schema inference for large files. Enabling multiLine can also reduce parallelism and change parsing behavior unexpectedly. It does not address the misaligned rows caused by quoting and will not reliably convert string columns to numeric types.

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

Why it fails: The escape character controls how quote characters inside a quoted field are escaped, not how embedded commas are handled. Commas inside a properly quoted field are already protected by the quote option. Adding an escape option does not fix type inference for large files and does not realign rows that were misparsed due to quoting issues. This change alone will not resolve the observed column shifting or the string-typed numeric columns.

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

Why it fails: samplingRatio only affects how much of the file is sampled when inferSchema is enabled; setting it while disabling inferSchema has no effect on type inference. Even with a full scan, inferSchema can still choose string types for ambiguous numeric columns, and it does not change how quoted fields with embedded commas are parsed. This approach does not guarantee correct types or fix the misaligned rows.

Use the COPY INTO command with a static file path.

Why it fails: COPY INTO is excellent for ad-hoc or simple batch ingestion, but it lacks the advanced file-tracking and schema evolution capabilities of Auto Loader. It requires manual management of file lists or path filtering to handle incremental ingestion, making it less scalable than Auto Loader for high-velocity data streams.

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

Why it fails: Manual DataFrame processing lacks native state management. If the job fails halfway through, you risk re-processing the same files, leading to duplicate records in the destination table. Furthermore, it requires a full scan of the storage container, which becomes prohibitively expensive and slow as the number of files grows.

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

Why it fails: The Import wizard in the Databricks UI is intended for small, one-time data uploads. It does not support automated, incremental pipelines, schema evolution, or error handling. Using it for large-scale data ingestion would require constant manual intervention, violating best practices for building robust and scalable data integration workflows.

Auto Loader

Why it fails: Auto Loader is a streaming-based ingestion engine designed for massive, continuous data flows from cloud object storage. Using it for a small, one-time local file upload is massive overkill, requiring unnecessary configuration of cloud buckets and IAM roles that contradicts the simplicity needed for this specific use case.

COPY INTO command

Why it fails: COPY INTO requires the source files to be accessible in a cloud storage location like S3, ADLS, or GCS. It cannot directly ingest a file sitting on a local laptop hard drive, making it unsuitable for an analyst trying to upload a local file without first manually moving it.

Databricks Connect

Why it fails: Databricks Connect is a client library that allows local IDEs to execute code on remote clusters. It is a development tool, not a data ingestion utility. It would not facilitate the physical movement of a local file into the Databricks workspace storage in a way that is accessible for SQL queries.

The cluster is running on a single-node configuration.

Why it fails: A single-node cluster is fully capable of accessing DBFS and external mounts. The error message explicitly points to a path issue, not a cluster architecture limitation. If the path were correct, the single node would be able to read the file successfully regardless of the compute cluster configuration.

Spark does not support reading CSV files directly from DBFS.

Why it fails: Spark has robust support for reading CSV files from various sources including DBFS. The 'format("csv")' method is a core part of the Spark DataFrame API. The error is strictly related to path resolution, not a lack of functionality or capability regarding the file format being read by Spark.

The user lacks sufficient memory to read the file.

Why it fails: A memory issue would result in an OutOfMemoryError or a driver failure during execution, not a path-based AnalysisException. The path error is a metadata or file system level issue, meaning Spark has not even attempted to load the content of the file into the cluster memory yet.

Implementing a continuous streaming pipeline with sub-second latency.

Why it fails: COPY INTO is not designed for continuous streaming at sub-second latency. It operates as a batch process, even if triggered frequently. Auto Loader is the correct choice for low-latency streaming because it maintains persistent state and uses efficient event notifications to process data as it lands in storage.

Performing complex, multi-stage ELT pipeline orchestration.

Why it fails: COPY INTO is a single-step loading command. It is not an orchestration tool and does not manage multi-stage data pipelines, dependency tracking, or workflow scheduling. For complex orchestration, tools like Databricks Workflows or Delta Live Tables should be used to manage dependencies and task sequencing across the environment.

Ingesting historical data from a database using JDBC connectors.

Why it fails: COPY INTO is specifically designed for file-based ingestion from cloud storage (S3, ADLS, GCS). It does not interact with JDBC connectors. To ingest data from external databases, you must use the Spark read API with a JDBC connector to load data into a DataFrame before writing to Delta.

CSV

Why it fails: While the source file is CSV, Databricks automatically converts this into the Delta Lake format upon ingestion. Keeping the data in raw CSV format would prevent the use of advanced features like ACID transactions, schema enforcement, and time travel, which are standard for all tables within the Databricks ecosystem.

Parquet

Why it fails: Parquet is the underlying storage format for Delta Lake, but the table itself is defined as a Delta table. Simply storing as Parquet would miss the transaction log features provided by the Delta format. Delta provides a management layer on top of Parquet files to ensure data integrity during operations.

JSON

Why it fails: JSON is a semi-structured format that is not the default for table storage in Databricks. While Databricks can process JSON, it is not optimized for large-scale table storage compared to the Delta format, which provides indexing and metadata management that JSON files lack, leading to significantly worse performance for analytics.

Use the option 'mergeSchema' set to true.

Why it fails: MergeSchema is intended for Parquet files to handle cases where schemas evolve over time. It is not designed to fix issues with nested JSON schema inference. For JSON, the structure must be explicitly defined if the inference fails, as Parquet-specific options do not apply to semi-structured JSON text files.

Convert the JSON to CSV before reading it.

Why it fails: Converting nested JSON to CSV often results in data loss or extreme complexity, as nested structures are difficult to flatten into a flat-file format. This is inefficient and unnecessary given Spark's native support for complex nested structures via the StructType and ArrayType APIs, which preserve the hierarchical nature of data.

Increase the number of partitions during read.

Why it fails: Increasing partitions affects parallelism during execution but does not influence how the schema is inferred from the data. If the schema cannot be inferred due to structural complexity, changing the number of partitions will not address the root cause of the error. Schema definition is the required solution here.

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

Why it fails: While data type conflicts can cause issues, Auto Loader with schema evolution enabled is designed to handle discrepancies gracefully. It does not automatically stop unless specifically configured to do so, preferring to either capture the data in a hidden column or attempt to resolve the type based on settings.

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

Why it fails: Schema inference is performed by sampling the actual data within the files, not by querying cloud metadata. Cloud storage providers do not hold internal schema definitions for text-based files like CSV or JSON, so Auto Loader must inspect the file content itself to determine the data structure during the process.

Delta Lake files require less storage space than Parquet.

Why it fails: Delta Lake uses Parquet as the underlying storage format. Therefore, the actual data on disk is essentially the same size as raw Parquet. The transaction log adds a small amount of extra metadata overhead, but the actual data compression ratio remains identical to standard Parquet files.

Delta Lake is faster for reading large tables than Parquet.

Why it fails: The performance of reading data is comparable because Delta uses the same underlying Parquet format. While Delta does provide metadata optimizations like file skipping, the raw read speed for a single file is determined by the Parquet format itself. The real advantage of Delta is reliability, not raw read speed.

Delta Lake is natively supported by all external BI tools.

Why it fails: Delta Lake is not natively supported by every single BI tool, although support is growing through Databricks SQL. Many BI tools still rely on standard connectors that work better with Parquet or other formats. The value of Delta lies in its internal integrity and management, not in universal tool compatibility.

Local files stored on the Databricks Driver node.

Why it fails: Files stored on the driver node are ephemeral and not accessible across the cluster. While they can be used for small, local scripts, they are not a reliable source for enterprise-level data ingestion into Delta tables, which require persistent storage that all worker nodes can access consistently during processing.

Direct memory buffers from the user's browser.

Why it fails: Databricks does not support direct streaming of memory buffers from a user's browser into the Delta table. Data must be persisted to a supported file system or stream source before it can be ingested. The browser interface is for interaction, not for direct, low-level data ingestion via memory pointers.

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

Why it fails: Spark's default CSV reader assumes comma delimiters and multiLine disabled; it does not infer semicolons or quoted embedded newlines, so rows are split incorrectly. It is tempting because schema inference handles headers and types, and would be correct for standard comma-separated files without embedded newlines.

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

Why it fails: Pre-converting outside Databricks adds an external pipeline step and still requires a parser to read the semicolon-delimited, multi-line CSV correctly, so the parsing problem simply moves. It is tempting because Parquet ingestion is fast and schema-stable, and would be correct if the source were already columnar and no CSV parsing were needed.

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

Why it fails: COPY INTO without options assumes comma delimiters and does not enable multiLine parsing, so semicolon-separated rows and quoted embedded newlines are mis-split. It is tempting because COPY INTO is the standard idempotent ingestion path, and would be correct once delimiter, header and multiLine options are supplied.

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

Why it fails: The display function expects a DataFrame or tabular object as its argument when rendering data in notebooks. Passing a raw file path string directly results in a type error rather than displaying the file contents or schema.

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

Why it fails: The Hive metastore holds metadata only for registered tables and databases, not for an unregistered JSON file read directly from a path, so DESCRIBE DATABASE returns nothing about its inferred schema. It is tempting because DESCRIBE statements do reveal column structures, but only for catalogued objects.

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

Why it fails: Excel cannot parse nested, inconsistent JSON into a tabular grid, and manual counting gives no access to Spark's inferred schema, which is what the analyst must inspect. It is tempting as a familiar tool for eyeballing small files, yet it fails on large semi-structured data and cannot show struct or array types.

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

Why it fails: INSERT INTO requires manual logic to filter out previously loaded files, making it prone to errors and duplicates. It does not natively track file metadata or ingestion state, which leads to significant overhead for the data analyst when dealing with daily recurring batch transfers from external cloud storage locations.

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

Why it fails: The MERGE INTO command is typically used for upserting data based on a specific key rather than initial ingestion from raw files. While it can prevent duplicates based on data values, it is significantly more computationally expensive and complex to configure for simple idempotent file loading compared to purpose-built ingestion commands.

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

Why it fails: Creating a temporary view on the raw files and using a standard SELECT statement provides no mechanism for tracking state. Every time the query is executed, all files in the directory will be re-processed and appended, leading to massive data redundancy and increased storage costs within the Databricks Lakehouse environment.