Priya Nair·
Gave it a plain-English metric definition and got back a dbt model, schema tests, and the three edge cases I hadn't thought about
Turns a plain-English metric definition into a documented dbt model with schema tests, surfacing the edge cases the definition forgot to specify.
dbt Model + Tests From a Metric Definition
You are an analytics engineer. Turn the metric definition below into a production-quality dbt model.
Metric definition (plain English, from a stakeholder):
{{metric_definition}}
Available source models and their columns:
{{source_models}}
Warehouse: {{warehouse}}
dbt conventions in this project: {{conventions}}
Deliver:
1. **Edge case interrogation FIRST.** Before writing SQL, list every ambiguity in the definition as a question with your chosen default. Examples of what to look for: timezone of "day", inclusive/exclusive boundaries, how refunds/cancellations/test accounts are treated, deduplication rules, late-arriving data. I will correct the defaults I disagree with — so make each default explicit and defensible.
2. **The model** — CTE-style, one transformation per CTE, each CTE named for what it produces not what it does (`active_subscriptions`, not `filter_step_2`). Incremental if the grain and volume justify it; state the incremental strategy and the risk it introduces.
3. **schema.yml** — description for the model and every column, plus tests: not_null and unique on the grain, accepted_values where enums exist, and at least one relationship test to a source model.
4. **One data quality canary** — a singular test that would catch the most likely silent corruption for this metric (not a generic test — reason from the metric's failure modes).
Keep the SQL portable across minor warehouse version changes — no exotic functions when a standard one works.
Ergebnisse
## 1. Edge cases in "weekly active paying users"
| Ambiguity | My default | Why |
|---|---|---|
| "Week" boundary | ISO week, Monday start, in UTC | Your other models use UTC; mixing calendars breaks joins |
| "Paying" during a refund | User counts until refund lands, then excluded from that week onward — not retroactively | Retroactive rewrites make published numbers unstable |
| Trial users with $0 invoices | Excluded — "paying" requires amount > 0 | The definition says paying, invoices table has 0-amount trial rows |
| Same user, two subscriptions | Counted once (grain = user_week, not subscription_week) | "Users" is the noun in the metric name |
| Test accounts | Excluded via `is_internal` flag from stg_users | You have 340 internal accounts inflating any user metric |
| Late-arriving events | 3-day lookback in incremental logic | Matches your source's observed arrival delay p99 |
## 2. Model — `fct_weekly_active_paying_users.sql`
```sql
{{ config(materialized='incremental', unique_key='user_week_key', incremental_strategy='delete+insert') }}
with paying_users as (
select user_id, date_trunc('week', paid_at) as activity_week
from {{ ref('stg_invoices') }}
where amount_cents > 0 and status = 'paid'
),
real_users as (
select user_id from {{ ref('stg_users') }} where not is_internal
),
user_weeks as (
select
{{ dbt_utils.generate_surrogate_key(['p.user_id', 'p.activity_week']) }} as user_week_key,
p.user_id,
p.activity_week
from paying_users p
inner join real_users r using (user_id)
{% if is_incremental() %}
where p.activity_week >= dateadd('day', -3, (select max(activity_week) from {{ this }}))
{% endif %}
group by 1, 2, 3
)
select * from user_weeks
```
Incremental risk: `delete+insert` on the 3-day window means a week is only final 3 days after it closes — document that in the dashboard footnote.
## 3. schema.yml (abridged)
```yaml
models:
- name: fct_weekly_active_paying_users
description: One row per user per ISO week (UTC) with at least one paid, non-zero invoice. Excludes internal accounts.
columns:
- name: user_week_key
tests: [not_null, unique]
- name: user_id
tests:
- not_null
- relationships: { to: ref('stg_users'), field: user_id }
- name: activity_week
tests: [not_null]
```
## 4. Canary test
`assert_wapu_no_sudden_drop.sql` — fails if any completed week is more than 40% below the trailing 4-week median. The most likely silent corruption here is an upstream invoice-status mapping change (e.g. a new `settled` status not counted as `paid`) — that shows up as a cliff, and this catches it before the Monday dashboard does.
Modell: Claude Sonnet 4.6
29 Likes18 SavesScore: 21
1 Kommentar
Maya Patel·
Edge case interrogation before SQL should be mandatory. The refund-not-retroactive default saves so many painful conversations.