/
Loading...
Loading...
Complete, production-ready code samples you can use as starting points for your own projects.
Complete staging to marts pipeline
Define your source tables with freshness tests and descriptions.
version: 2
sources:
- name: shopify
database: raw
schema: shopify
description: "Shopify e-commerce platform data synced via Fivetran"
freshness:
warn_after: { count: 12, period: hour }
error_after: { count: 24, period: hour }
loaded_at_field: _fivetran_synced
tables:
- name: orders
description: "All orders placed on the Shopify store"
columns:
- name: id
description: "Primary key for the order"
- name: customer_id
description: "Foreign key to the customer"
- name: total_price
description: "Total order amount"
- name: created_at
description: "Order creation timestamp"
- name: customers
description: "Customer records from Shopify"
columns:
- name: id
description: "Primary key for the customer"
- name: email
description: "Customer email address"
- name: first_name
description: "Customer first name"
- name: last_name
description: "Customer last name"MRR, churn, and subscription analytics
-- models/marts/finance/fct_mrr.sql
-- Monthly Recurring Revenue calculation
with subscriptions as (
select * from {{ ref('stg_stripe__subscriptions') }}
),
months as (
{{ dbt_utils.date_spine(
datepart="month",
start_date="'2023-01-01'",
end_date="current_date"
) }}
),
subscription_months as (
select
m.date_month,
s.subscription_id,
s.customer_id,
s.plan_name,
s.monthly_amount,
case
when m.date_month >= date_trunc('month', s.started_at)
and (s.ended_at is null or m.date_month < s.ended_at)
then true
else false
end as is_active
from months m
cross join subscriptions s
)
select
date_month,
count(distinct customer_id) as active_subscribers,
sum(monthly_amount) as mrr,
sum(monthly_amount) * 12 as arr
from subscription_months
where is_active
group by 1
order by 1