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:
CREATE OR REPLACE TABLEcreates a new table or replaces an existing one with the same name- The syntax correctly specifies column names and data types in parentheses
- In Databricks, this creates a Delta table by default
- It matches the requirement to create the table "regardless of whether a table already exists with this name"
Why other options are incorrect:
- Option B: Uses incorrect syntax with
WITH COLUMNSandUSING DELTA- this is not valid SQL DDL syntax - Option C:
CREATE TABLE IF NOT EXISTSonly creates the table if it doesn't exist, which doesn't match the requirement to create it regardless of existing table - Option D: Uses
CREATE TABLE ... AS SELECTwhich requires data from a query, not just column definitions, and doesn't handle table replacement
Key Points:
- In Databricks SQL,
CREATE OR REPLACE TABLEis the standard way to ensure a table exists with specified schema - Delta tables are the default table format in Databricks
- The column data types match the expected format: STRING for employeeId, DATE for startDate, and FLOAT for avgRating
Comments
Loading comments...