Course contents

The Analytics Engineer’s Field Guidedbt 1: build the modelsChallenge: dbt 1

Challenge: dbt 1

Challenge: dbt 1

Three situations from a real project. Each one draws on more than one lesson of this part.

Challenge questions

  1. fct_orders is incremental with incremental_strategy='delete+insert' and unique_key='order_date'. Each run selects orders updated in the last two days. Today the till corrects one order placed ten days ago. What happens to that old date in fct_orders?

    1. The correction is skipped, since its date is outside two days
    2. Only the corrected order is replaced, as merge would do
    3. Its other orders vanish: the whole day is replaced by one row
    4. The corrected order lands twice, once per strategy step
  2. fct_order_lines is incremental, merging on order_line_id, with the default on_schema_change. Alice adds discount_eur to its SELECT and her change is merged. What does the next nightly run do?

    1. Fails, since the model and the table no longer match
    2. Builds without error, and discount_eur doesn't appear
    3. Adds discount_eur, filled for new rows and null for old ones
    4. Rebuilds the whole table, since its columns changed
  3. dim_menu_item sits on a snapshot, one row per version. fct_order_lines joins it ON item_id only. After the margherita's June price change, May's margherita revenue has doubled. What's the fix?

    1. Keep only rows where dbt_valid_to is null in dim_menu_item
    2. Also join on ordered_at within the version's valid range
    3. Rebuild the snapshot with --full-refresh to merge versions
    4. Put a unique test on item_id so the join can't fan out

Finish this set to count it as done.