Question 1

How the SQL will be compiled based on the following model:

{% set payment_methods = ["bank_transfer", "credit_card", "gift_card"] %}

select order_id,

{% for payment_method in payment_methods %}

sum(case when payment_method = '{{payment_method}}' then amount end) as {{payment_method}}_amount,

{% endfor %}

from {{ ref('raw_payments') }}

group by 1

Options :

A : select

order_id,

sum(case when payment_method = 'bank_transfer' then amount end) as bank_transfer_amount

,

sum(case when payment_method = 'credit_card' then amount end) as credit_card_amount

,

sum(case when payment_method = 'gift_card' then amount end) as gift_card_amount,

from raw_jaffle_shop.payments

group by 1

B : select

order_id,

sum(case when payment_method = 'bank_transfer' then amount end) as bank_transfer_amount

,

sum(case when payment_method = 'credit_card' then amount end) as credit_card_amount

,

sum(case when payment_method = 'gift_card' then amount end) as gift_card_amount

from raw_jaffle_shop.payments

group by 1

C : select

order_id,

sum(case when payment_method = 'bank_transfer' then amount end) as bank_transfer_amount

,

sum(case when payment_method = 'credit_card' then amount end) as credit_card_amount

from raw_jaffle_shop.payments

group by 1

D : Compile Error

Answer: A

Question 2

Given the snapshot config, how check_cols will be used

{{

config(

target_schema='history',

target_database='analytics',

strategy='timestamp',

unique_key='id',

updated_at='updated_at',

check_cols=[“col_1”, “col_2”]

)

}}

Options :

A : check_cols will be ignored

B : updated_at column will be used to determine changes first and check_cols will be used in case of any duplicate

C : Snapshot will check all columns in check_cols for changes

D : updated_at and col_1 will be used for snapshot

Answer: A

Question 3

Given the below properties, how marts/model_a.sql will be materialized

models:

my_project:

dwh:

# materialize all models in models/events as tables

+materialized: incremental

marts:

# this is redundant, and does not need to be set

+materialized: table

Options :

A : View

B : Table

C : Incremental

D : Ephemeral

Answer: B

Question 4

Which statement best describes snapshot feature in dbt

Options :

A : Snapshots implement type-1 Slowly Changing Dimensions

B : Snapshots implement type-2 Slowly Changing Dimensions

C : Snapshot implements fact less facts

D : Snapshot implements incremental data loading strategy

Answer: B

Question 5

Which 2 platforms are supported by dbt

Options :

A : Databricks

B : MongoDB

C : Dremio

D : Casandra

Answer: A,C

Free dbt-Analytics-Engineering Mock Exam – Practice Online Confidently

Buying Options: