Add columns from a Fivetran-synced source to an existing intermediate model without breaking downstream consumers. Six prompts. Three interchangeable paths.
Path A walkthrough: extend int_customer_order_summary with retail.RET_TICKETS at the customer grain.
Path B walkthrough: extend int_orders_enriched with retail.RET_TICKETS at the order grain. Same source as Path A, different model and grain.
Path C walkthrough: extend int_product_sales_summary with retail.RET_PRODUCT_REVIEWS at the product grain.
An existing intermediate model is missing data needed for a follow-up question. The source already exists in Snowflake (Fivetran-synced) but isn't referenced by the model.
Steps: locate the target model and downstream consumers, find an unused source, validate grain and coverage, modify the target model with a LEFT JOIN, compile the downstream lineage, materialize.
Pick one path before Step 1. The prompt flow is identical across paths; only the placeholders change. All three can be run simultaneously in the room.
int_customer_order_summaryretail.RET_TICKETSopen_tickets_count, last_ticket_status, last_ticket_opened_atint_orders_enrichedretail.RET_TICKETS (at order grain)ticket_count, has_open_ticket_flag, last_ticket_statusint_product_sales_summaryretail.RET_PRODUCT_REVIEWSavg_rating, review_count, low_rating_count (1-2 stars)RET_TICKETS at different grains. The source is new to the target model, not new to the project.
The prompts use placeholders. Substitute values for the path you picked. The placeholder table is below.
Identify the target model, its grain, and its downstream consumers before editing.
Find [TARGET_MODEL] in this project. Show me what it currently produces, its grain, and which models depend on it downstream.
Then ask what unused sources are available.
Find every source in this project related to [ENTITY] that [TARGET_MODEL] does NOT currently reference. I want to know what data is sitting in our warehouse that we're not using yet.
dbt Wizard surfaces a configured source that isn't referenced by the target model.
Describe the schema of [NEW_SOURCE]. Show me the columns, their types, the grain, and which column joins back to [ENTITY].
Verify grain, coverage, and join-key match before writing SQL. Catches name-matching keys that don't actually align by data.
Run a quick check: count rows in [NEW_SOURCE], count distinct join keys, and count how many of those keys match an [ENTITY] already in [TARGET_MODEL]. Tell me whether the grain is one-to-one or one-to-many.
[ENTITY], decide whether to aggregate (open tickets count, review count) or pick the latest row by timestamp (last ticket status) before Step 4.
Edit the existing model file. Do not create a new file.
Update [TARGET_MODEL] to add [NEW_COLUMNS] from [NEW_SOURCE]. Use a LEFT JOIN so [ENTITY] rows without a match still appear, and aggregate [NEW_SOURCE] to one-row-per-[ENTITY] before joining if its grain is many-to-one. Preserve every column the model currently emits - only add new columns at the end.
[ENTITY] rows without a match). Preserve existing columns - downstream consumers select specific columns by name, so renaming or removing them is a contract break.
Compiling the downstream models confirms no consumer breaks on a renamed or removed column.
Compile [TARGET_MODEL] and every downstream model that depends on it. Then preview 20 rows of [TARGET_MODEL] ordered deterministically. Do not materialize anything.
[TARGET_MODEL] is unchanged. Every downstream model compiles.
Build into your dev schema. Step 5 already validated output and downstream compile; verification is scoped out for this timed lab.
Materialize [TARGET_MODEL] into my dev schema. Skip the verification pass - the preview and downstream compile already confirmed the output.
int_customer_order_summaryretail.RET_TICKETSopen_tickets_count, last_ticket_status, last_ticket_opened_atint_orders_enrichedretail.RET_TICKETSticket_count, has_open_ticket_flag, last_ticket_statusint_product_sales_summaryretail.RET_PRODUCT_REVIEWSavg_rating, review_count, low_rating_count[TARGET_MODEL] emits [NEW_COLUMNS] from [NEW_SOURCE] in your dev schema. Existing column contract preserved, downstream models compile, row count matches the pre-edit baseline.
Skill definition with all three paths and instructor notes.
Every prompt in this scenario, formatted for print.
Activity layer plus segment model for VIPs, big spenders, and category-loyal customers.