Practice — dbt & Analytics Engineering (4 questions)

Your team's fct_subscriptions incremental model is configured like this:

{{ config(
    materialized='incremental',
    unique_key='subscription_id',
    incremental_strategy='merge'
) }}

select
    subscription_id,
    customer_id,
    plan_tier,
    status,
    updated_at
from {{ ref('stg_subscriptions') }}

{% if is_incremental() %}
where updated_at > (select max(updated_at) from {{ this }})
{% endif %}

Two problems have shown up in production:

• Finance reports that some subscription_ids appear twice in fct_subscriptions with different status values (e.g., one row pending, another active), even though unique_key is set.
• Separately, an upstream retry in the billing system occasionally re-emits an updated_at timestamp for a subscription that is older than the current max updated_at already in the table (a delayed correction from a downstream system), and that correction never appears in fct_subscriptions at all.

1. Explain exactly why unique_key did not prevent the duplicate rows Finance is seeing — what has to be true about the incoming batch for merge to still produce duplicates.
2. Explain why the late correction is silently dropped, mechanically.
3. Rewrite the model to fix both problems, and explain what each change fixes.

A finance team flags that your warehouse bill jumped noticeably last month. Investigating, you find:

• int_orders_with_payments, an intermediate model joining orders to aggregated payments (a moderately expensive join across ~40M rows), is materialized as view. It's ref()'d by four different marts models (fct_orders, fct_revenue_by_region, fct_customer_ltv, and fct_refunds), each queried directly by a Looker dashboard that auto-refreshes every 5 minutes.
• stg_page_views, a thin staging model over a raw clickstream table with light column renaming and no joins, is materialized as table, rebuilt in full on every hourly run even though only one downstream model (int_sessions) ever reads it, and it's rebuilt completely every run regardless of how much new clickstream data actually arrived.

1. Explain the specific cost mechanism behind each of these two choices — why each one is more expensive than it needs to be.
2. Propose the correct materialization (or strategy) for each model, and justify the trade-off you're accepting by changing it.
3. stg_page_views sits at the very top of the DAG, directly on a source table with genuinely new rows arriving continuously. Would you consider incremental for it, or is a different fix more appropriate here — and why?

A teammate opens dbt docs serve to trace how fct_customer_ltv is built and notices it doesn't show any connection to int_orders_with_payments in the lineage graph, even though fct_customer_ltv.sql clearly reads from it:

-- models/marts/fct_customer_ltv.sql
{{ config(materialized='table') }}

select
    customer_id,
    sum(total_amount) as lifetime_value
from analytics_prod.intermediate.int_orders_with_payments
group by 1

Separately, a CI run on a PR that only changed int_orders_with_payments.sql did not rebuild or retest fct_customer_ltv at all, even though the PR's intent was to fix a bug in int_orders_with_payments that clearly affects LTV calculations downstream.

1. Diagnose the root cause connecting the missing lineage edge and the CI gap — they are the same bug. Explain precisely why.
2. Fix the model.
3. Beyond fixing this one model, what would you check across the rest of the project to make sure this isn't a systemic issue, and how would you check it without manually reading every model file?

A teammate opens a PR adding a new model, models/staging/stg_orders_with_ltv.sql:

-- models/staging/stg_orders_with_ltv.sql
{{ config(materialized='view') }}

select
    o.id as order_id,
    o.customer_id,
    o.status as order_status,
    c.signup_date,
    sum(p.amount) over (partition by o.customer_id) as customer_ltv
from {{ source('raw_app', 'orders') }} as o
left join {{ ref('stg_customers') }} as c
    on o.customer_id = c.customer_id
left join {{ ref('stg_payments') }} as p
    on o.id = p.order_id

The PR description says: "Added a staging model that gives us orders with customer signup date and lifetime value pre-joined, so marts don't have to redo this join every time."

1. Explain specifically what's wrong with placing this model in staging/, independent of whether the SQL itself is correct.
2. Where should this logic actually live, and how would you restructure it, potentially into more than one model?
3. The teammate pushes back: "it works fine and it's faster to just put everything in one folder." What's the concrete cost of that approach as the project grows past this one model?