Data Build Tool DBT-Analytics-Engineering Practice Exam Questions

Data Build Tool DBT-Analytics-Engineering Sample Question Answers

Question # 1

You have just executed dbt run on this model:select * from {{ source("{{ env_var('input') }}", 'table_name') }}and received this error:Compilation Error in model my_modelexpected token ':', got '}'line 14{{ source({{ env_var('input') }}, 'table_name') }}How can you debug this?

A. Incorporate a log function into your macro.  
B. Check your SQL to see if you quoted something incorrectly.  
C. Check your Jinja and see if you nested your curly brackets.  
D. Take a look at the compiled code.  

Show Answer

Question # 2

Consider these SQL and YAML files for the model model_a:models/staging/model_a.sql{{ config(materialized = "view") }}with customers as (...)dbt_project.ymlmodels:my_new_project:+materialized: tablestaging:+materialized: ephemeralWhich is true about model_a? Choose 1 option.Options:

A.Select statements made from the database on top of model_a and transformationprocessing within model_a will be quicker, but the data will only be as up to date as the lastdbt run.
B.Select statements made from the database on top of model_a will result in an error
C.Select statements made from the database on top of model_a will be slower, but the datawill always be up to date.
D.Select statements made from the database on top of model_a will be quicker, but the datawill only be as up to date as the last dbt run.(Note: A and D are duplicates — typical exam formatting.) 

Show Answer

Question # 3

You want to run and test the models, tests, and snapshots you have added ormodified in development.Which will you invoke? Choose 1 option.Options:

A.dbt build --select state:modified --defer <path/to/artifacts> 
B.dbt run --select state:modified --defer --state <path/to/artifacts>dbt test --select state:modified --defer --state <path/to/artifacts>
C.dbt build --select state:modified --defer --state <path/to/artifacts> 
D. dbt run --select state:modified --state <path/to/artifacts>dbt test --select state:modified --state <path/to/artifacts>
E.dbt build --select state:modified --state <path/to/artifacts>

Show Answer

Question # 4

Your model has a contract on it.When renaming a field, you get this error:This model has an enforced contract that failed.Please ensure the name, data_type, and number of columns in your contract matchthe columns in your model's definition.| column_name | definition_type | contract_type | mismatch_reason ||-------------|------------------|----------------|-----------------------|| ORDER_ID | TEXT | TEXT | missing in definition || ORDER_KEY | TEXT | | missing in contract |Which two will fix the error? Choose 2 options. 

A. Remove order_id from the contract.  
B. Remove order_key from the contract.  
C. Remove order_id from the model SQL.  
D. Add order_key to the contract.  
E. Add order_key to the model SQL.  

Show Answer

Question # 5

Which two dbt commands work with dbt retry?Choose 2 options.

A. run-operation  
B. parse  
C. debug  
D. deps  
E. snapshot  

Show Answer

Question # 6

You work at an e-commerce company and a vendor provides their inventory data viaCSV file uploads to an S3 bucket.How do you prep the data for dbt transformations?Choose 1 option.

A. Create a dbt model with a view querying the external table directly.  
B. Run a pre-hook to create a temporary table and query from it in a staging model.  
C. Use dbt seed to stage the data in your data platform.  
D. Declare the external table as a source using the external configuration.  

Show Answer

Question # 7

You have written this new agg_completed_tasks dbt model:with tasks as (select * from {{ ref('stg_tasks') }})selectuser_id,{% for task in tasks %}sum(casewhen task_name = '{{ task }}' and state = 'completed'then 1else 0end) as {{ task }}_completed{% endfor %}from tasksgroup by 1The dbt model compiles to:with tasks as (select * from analytics.dbt_user.stg_tasks)selectuser_id,from tasksgroup by 1The case when statement did not populate in the compiled SQL. Why?

A. Because there is not a {% if not loop.last %}{% endif %} to compile a valid case when statement.  
B. Because the Jinja for-loop should be written with {{ }} instead of {% %}.  
C. Because there is no {% set tasks %} statement in the model defining the tasks variable.  
D. Because there is not a task_name column in stg_tasks.  

Show Answer

Question # 8

An analyst on your team has informed you that the business logic creating theis_active column of your stg_users model is incorrect.You update the column logic to:casewhen state = 'Active'then trueelse falseend as is_activeWhich test can you add on the state column to support your expectations of thesource data? Choose 1 option.

A.- name: statetests:- accepted_values:values: ['active', 'churned', 'trial']- not_null 
B.- name: is_activetests:- accepted_values:values: ['active', 'churned', 'trial']- not_null
C.- name: statetests:- not_null- unique 
D.- name: is_activetests:- not_null- unique 

Show Answer

Question # 9

You are working on a complex dbt model with many Common Table Expressions (CTEs)and decide to move some of those CTEs into their own model to make your code moremodular.Is this a benefit of this approach?The new model can be documented to explain its purpose and the logic it contains

A. Yes  
B. No  

Show Answer

Question # 10

You are creating a fct_tasks model with this CTE:with tasks as (select * from {{ ref('stg_tasks') }})You receive this compilation error in dbt:Compilation Error in model fct_tasks (models/marts/fct_tasks.sql)Model 'model.dbt_project.fct_tasks' (models/marts/fct_tasks.sql) depends on a nodenamed 'stg_tasks' which was not foundWhich is correct? Choose 1 option.Options:

A. stg_tasks is configured as ephemeral.  
B. There is no dbt model called stg_tasks.  
C. There is no stg_tasks in the data warehouse.  
D. A stg_tasks has not been defined in schema.yml.  

Show Answer