DBT-ANALYTICS-ENGINEERING practice question 10 of 35

Which of the following SQL statements can be used to remove all rows from a table without deleting the table structure?

Full set: 409 questions

Domain Modeling

Answer options
A: DELETE FROM table_name Correct answer
B: DROP TABLE table_name
C: TRUNCATE TABLE table_name Correct answer
D: EMPTY TABLE table_name
Correct Answer:
A. DELETE FROM table_name; C. TRUNCATE TABLE table_name
Explanation

Both DELETE and TRUNCATE can be used to remove all rows from a table without altering its underlying structure (i.e., columns and constraints). TRUNCATE TABLE is a Data Definition Language (DDL) command designed specifically for this purpose. It is highly efficient as it deallocates the data pages, bypassing individual row deletions, and typically cannot be rolled back.
DELETE FROM is a Data Manipulation Language (DML) command. When used without a WHERE clause, it also removes all rows. However, it does so row-by-row, which is slower and generates significant transaction log data, but allows the operation to be rolled back. Both commands satisfy the question's requirements.

Why Incorrect

B. DROP TABLE tablename is incorrect because it is a DDL command that permanently removes the entire table object, including its structure and all associated data.
D. EMPTY TABLE tablename is incorrect because it is not a valid or standard SQL command in any major database system used with dbt. ---

References

1. Snowflake Documentation

TRUNCATE TABLE: "Removes all rows from a table

but leaves the table structure

including any column definitions

constraints

and sequence generators intact." (Snowflake Inc.

SQL Command Reference

DML Commands

TRUNCATE TABLE section).

2. Snowflake Documentation

DELETE: "Deletes rows from a table. If the WHERE clause is omitted

all rows in the table are deleted..." (Snowflake Inc.

SQL Command Reference

DML Commands

DELETE section).

3. PostgreSQL 16 Documentation

TRUNCATE: "TRUNCATE quickly removes all rows from a set of tables. It has the same effect as an unqualified DELETE on each table

but since it does not actually scan the tables it is faster." (The PostgreSQL Global Development Group

PostgreSQL 16 Documentation

Part III. SQL Commands

TRUNCATE section).

4. dbt Labs Documentation

pre-hook and post-hook: The dbt documentation illustrates the use of DML and DDL commands like TRUNCATE and DELETE within hooks to manage table data before or after a model run

confirming their relevance in dbt workflows for data manipulation without altering model-defined structures. (dbt Labs

dbt Docs

Reference

pre-hook and post-hook section

Examples).

Scroll to Top