Databricks Certified Data Engineer - Associate

Explanation

In Databricks SQL, when creating a table from a CSV file using the CREATE TABLE statement with the LOCATION clause, you need to specify the data source format using the USING keyword.

The correct syntax is:

CREATE TABLE table_name
USING data_source_format
OPTIONS (options)
LOCATION "path/to/data"

CREATE TABLE table_name
USING data_source_format
OPTIONS (options)
LOCATION "path/to/data"

For CSV files, the correct format specification is USING CSV. This tells Databricks that the data at the specified location is in CSV format and should be parsed accordingly.

Let's examine why the other options are incorrect:

• A. None of these lines of code are needed: This is incorrect because the USING clause is required to specify the data source format.
• C. FROM CSV: This uses the wrong keyword. In Databricks SQL, FROM is used in SELECT statements, not in CREATE TABLE statements for specifying data source format.
• D. USING DELTA: This would create a Delta table, not read from a CSV file.
• E. FROM "path/to/csv": This is incorrect syntax for CREATE TABLE. The path is already specified in the LOCATION clause.

The complete correct statement would be:

CREATE TABLE new_table
USING CSV
OPTIONS (
    header = "true",
    delimiter = "|"
)
LOCATION "path/to/csv"

CREATE TABLE new_table
USING CSV
OPTIONS (
    header = "true",
    delimiter = "|"
)
LOCATION "path/to/csv"

This creates an external table that references the CSV file at the specified location, with the specified options for parsing (header row present, pipe delimiter).