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 questionsDomain Modeling
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.
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. ---
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).
Keep practicing
Take the free DBT-ANALYTICS-ENGINEERING practice test
Ten exam-style questions with answers and explanations, plus the exam facts and study guides.
More questions
Other DBT-ANALYTICS-ENGINEERING practice questions
- Question 1What is the purpose of the --resource-type argument in the dbt ls command?
- Question 2Select the valid methods for configuring incremental models in dbt. (Select all that appl…
- Question 3When fixing a dbt model issue, it is acceptable to skip the testing process in a separate…
- Question 4You want to run tests on only the models that have been modified since your last dbt run.…
- Question 5You discover that a dbt model is directly referencing a raw object in the database withou…
- Question 6What is the purpose of the dbt deps command in the dbt workflow?
