H16 Look up customers

Use your current course session. Preparation and course runner · R script ↓

H16a Add a customer table

Goal: Prepare a shared key.

Prerequisites: H07.

Add IDs in a new delivery variant. Each of three customers holds two policies.

training_step(
  "H16a",
  {
    customers <- data.frame(
      customer_id = 1:3,
      region = c("North", "South", "West")
    )
    linked_policies <- policies
    linked_policies$customer_id <- c(1L, 1L, 2L, 2L, 3L, 3L)
    linked_policies$broker_id <- c(1L, 2L, 1L, 2L, 1L, 2L)
    print(customers)
  },
  needs = c("H07")
)

Expected result: Three unique customer IDs.

Task: Why keep regions in a separate customer table?

H16 Look up customers

Goal: Enrich data through a checked relationship.

Prerequisites: H16a.

The output contract includes the added region. The dm-based lookup checks the key relationship.

training_step(
  "H16",
  {
    linked_contract <- dr_contract(
      "policies.linked",
      c(
        policy_id = "integer",
        premium = "numeric",
        cancelled = "logical",
        customer_id = "integer",
        broker_id = "integer",
        region = "character"
      ),
      key = "policy_id"
    )
    customer_recipe <- dr_recipe() |>
      dr_step_lookup(customers, by = "customer_id")
    customer_product <- dr_product(
      "policies.linked",
      linked_policies,
      contract = linked_contract
    ) |>
      dr_add_recipe(customer_recipe)
    customer_rows <- dr_collect(dr_run(customer_product, write = FALSE))
    print(customer_rows)
    stopifnot(nrow(customer_rows) == 6L)
  },
  needs = c("H16a")
)

Expected result: Six policies with regions.

Task: What should happen for an unknown customer_id?