Data Build Tool DBT-Analytics-Engineering Dumps

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

Question # 11

Consider this DAG:model_a model_c model_emodel_b model_d model_f(With model_c and model_d both feeding into the final layer.)You execute:dbt run --fail-fastin production with 2 threads. During the run, model_b and model_c are running in parallelwhen model_b returns an error.Assume there are no other errors in the model files, and model_c was still running whenmodel_b failed.Which model or models will successfully build as part of this dbt run? Choose 1option.

A. model_a, model_c, model_d, model_e, model_f  
B. model_a, model_c  
C. model_a  
D. model_a, model_c, model_e  

Show Answer

Question # 12

Given this dbt_project.yml:name: "jaffle_shop"version: "1.0.0"config-version: 2profile: "snowflake"model-paths: ["models"]macro-paths: ["macros"]snapshot-paths: ["snapshots"]target-path: "target"clean-targets:- "logs"- "target"- "dbt_modules"- "dbt_packages"models:jaffle_shop:orders:materialized: tableWhen executing a dbt run your models build as views instead of tables:19:36:14 Found 1 model, 0 tests, 0 snapshots, 0 analyses, 179 macros, 0 operations, 0seed files, 0 sources, 0 exposures, 0 metrics19:36:16 Concurrency: 1 threads (target='default')19:36:17 Finished running 1 view model in 3.35s.19:36:17 Completed successfully19:36:17 Done. PASS=1 WARN=0 ERROR=0 SKIP=0 TOTAL=1Which could be a root cause of why the model was not materialized as a table?The target-path is incorrectly configured.

A. Yes  
B. No  

Show Answer

Question # 13

What must happen before you can build models in dbt?Choose 1 option.

A. Sources must have been defined in your dbt project.  
B. You must have created a service account in your data platform.  
C. Underlying data must be accessible on your data platform.  
D. Raw data must be cleaned.  

Show Answer

Question # 14

Which two mechanisms allow dbt to write DRY code by reusing logic, preventingwriting the same code multiple times?Choose 2 options.

A. Copy/pasting folders containing multiple models  
B. Writing and using dbt macros  
C. Creating singular tests  
D. Using dbt packages  
E. Changing a model materialization from view to ephemeral  

Show Answer

Question # 15

A developer imports a package from a private repository called timeformat for usewithin their project.Which statement is correct? Choose 1 option.Options:

A.“The package can be added with this configuration in the packages.yml file:”packages:- local: /opt/dbt/timeformat 
B.“The package can be installed by running the command dbt build.”
C.“The package default schema can be overridden in the dbt_project.yml file as:”models:timeformat:+schema: timeseries
D.“Including the package version/revision in the packages.yml file, for private git packageswill result in an error.”

Show Answer

Question # 16

Which two are true for a dbt retry command?Choose 2 options.

A. It reruns all nodes in your previous invocation statement.  
B. It retries the previous command if it is not a syntax error in a model.  
C. It picks up from the error without running all of the upstream dependencies.  
D. It reuses selectors from the previous command.  
E. It reads a manifest.json file to identify the models and tests that failed in the last run.  

Show Answer

Question # 17

Is this materialization supported by Python models in dbt?Ephemeral

A. Yes  
B. No  

Show Answer

Question # 18

You run the command:dbt test --select 'test_type:singular'What will the command run?Options shown: 

A.furniture_customers_test
B.furniture_customers_testmacro_stg_tpch_orders_assert_pos_pricemacro_stg_tpch_suppliers_assert_pos_acct_balstg_tpch_orders_assert_positive_price
C.macro_stg_tpch_orders_assert_pos_pricemacro_stg_tpch_suppliers_assert_pos_acct_balstg_tpch_orders_assert_positive_price 
D.furniture_customers_teststg_tpch_orders_assert_positive_priceChoose 1 option.

Show Answer

Question # 19

Which command materializes my_model and only its first-degree parent model(s) in thedata platform?Choose 1 option.

A. dbt run --select +my_model  
B. dbt compile --select +my_model  
C. dbt run --select 1+my_model  
D. dbt run --select +1 my_model  
E. dbt compile --select +1 my_model  

Show Answer

Question # 20

Which two statements about Exposures are true?Choose 2 options

A. Models, sources, and metrics are downstream from Exposures.  
B. Exposures are materialized in the database.  
C. Exposures describe a downstream use of your dbt project.  
D. Exposures are defined in .sql files.  
E. You can run, test, and list resources that feed into your Exposure.  

Show Answer