Databricks Certified Data Engineer - Associate

Get started today

Ultimate access to all questions.

Explanation:

The CREATE TABLE AS SELECT (CTAS) command is the ideal choice for the data engineer's needs. CTAS allows for the creation of a new table based on the results of a SELECT statement, enabling the engineer to combine data from multiple sources, apply transformations, and store the result in a new table all in one operation. This method is efficient as it both creates a new table and processes data simultaneously, without affecting the original data. For example:

CREATE TABLE new_table
AS
SELECT
    id,
    SUM(amount) as total_amount,
    AVG(score) as average_score
FROM
    old_table
GROUP BY
    id;

CREATE TABLE new_table
AS
SELECT
    id,
    SUM(amount) as total_amount,
    AVG(score) as average_score
FROM
    old_table
GROUP BY
    id;

This example demonstrates the use of CTAS to generate a new table named new_table by aggregating and averaging data from old_table. It's a practical approach for creating tables tailored for analysis.

Explanation:

CREATE TABLE new_table
AS
SELECT
    id,
    SUM(amount) as total_amount,
    AVG(score) as average_score
FROM
    old_table
GROUP BY
    id;

CREATE TABLE new_table
AS
SELECT
    id,
    SUM(amount) as total_amount,
    AVG(score) as average_score
FROM
    old_table
GROUP BY
    id;

Comments (0)

No comments yet.

A data engineer is looking to create a new table from various data sources and apply some transformations to the data in a single step, without altering the original data. Which SQL command is most suitable for this purpose?

Real Exam

MERGE INTO

11.7%

UPDATE

4.7%

INSERT INTO

6.3%

ALTER TABLE

5.4%

CREATE TABLE AS SELECT (CTAS)

71.9%