stg_postgres__subscriptions

staging view 1 PII columns

Subscriptions, with monthly-normalised amount.

Definition

One row per subscription. `monthly_amount` is already normalised on the source — annual plans store the monthly pro-rated value, so MRR aggregates that sum `monthly_amount` correctly count recurring revenue without re-amortising. Status transitions (`active` → `paused` / `cancelled` / `expired`) drive churn.

16 of 16

Column

Description

Tests · PII

subscription_id

Primary key — UInt64.

2 tests

customer_id

FK to `stg_postgres__customers.customer_id`. NOT NULL.

customer +17 PII2 tests

product_id

FK to `stg_postgres__products.product_id`. NOT NULL.

2 tests

plan_name

Plan cadence — `monthly`, `quarterly`, or `annual`.

1 test

status

`active`, `paused`, `cancelled`, or `expired`.

1 test

started_at

When the subscription started. DateTime64(6).

1 test

started_day

`toDate(started_at)`. Cohort key for retention.

current_period_start

Start of the current billing period. DateTime64(6).

current_period_end

End of the current billing period. DateTime64(6).

cancelled_at

When the subscription was cancelled. Null for live subscriptions.

monthly_amount

Monthly-normalised recurring revenue contribution. Decimal(10,2). Financial.

2 testsPII

currency

ISO-4217 currency code.

is_active

Derived — `status = 'active'`.

source_created_at

Row insert timestamp from the source DB. DateTime64(6).

source_updated_at

Row update timestamp from the source DB. DateTime64(6).

airbyte_extracted_at

When Airbyte extracted this row from postgres-source.