Databricks Certified Data Engineer - Associate
Get started today
Ultimate access to all questions.
A data architect has determined that a table of the following format is necessary:
employeeId startDate avgRating
a1 2009-01-06 5.5
a2 2018-11-21 7.1
...
employeeId startDate avgRating
a1 2009-01-06 5.5
a2 2018-11-21 7.1
...
Which code block is used by SQL DDL command to create an empty Delta table in the above format regardless of whether a table already exists with this name?
Real Exam
Community
KKeng
A
CREATE OR REPLACE TABLE table_name ( employeeId STRING, startDate DATE, avgRating FLOAT )
B
CREATE OR REPLACE TABLE table_name WITH COLUMNS ( employeeId STRING, startDate DATE, avgRating FLOAT ) USING DELTA
C
CREATE TABLE IF NOT EXISTS table_name ( employeeId STRING, startDate DATE, avgRating FLOAT )
D
CREATE TABLE table_name AS SELECT employeeId STRING, startDate DATE, avgRating FLOAT
Explanation:
Explanation
Option A is correct because:
- It uses
CREATE OR REPLACE TABLEwhich ensures the table is created regardless of whether it already exists - It follows standard SQL DDL syntax with proper column definitions and data types
- It creates an empty Delta table (Delta is the default table format in Databricks)
- The column data types match the required format: STRING for employeeId, DATE for startDate, and FLOAT for avgRating
Why other options are incorrect:
Option B: Uses non-standard WITH COLUMNS syntax which is not valid SQL DDL. While USING DELTA explicitly specifies the Delta format, the WITH COLUMNS clause is incorrect.
Option C: Uses CREATE TABLE IF NOT EXISTS which only creates the table if it doesn't exist. This violates the requirement "regardless of whether a table already exists with this name" because it won't replace an existing table.
Option D: Uses CREATE TABLE ... AS SELECT which creates a table populated with data from a SELECT statement, not an empty table. Additionally, the SELECT syntax is incorrect for defining column types.
Key Points:
CREATE OR REPLACE TABLEis the correct approach when you want to ensure a fresh table regardless of existing state- Delta tables are the default in Databricks, so explicit
USING DELTAis optional but can be added for clarity - The column definitions in parentheses with data types is standard SQL syntax
Comments
Loading comments...