Deleted membership recurring statuses log fact table (Redshift)
Table name: Bi_FactRecurringStatusLogDeleted
Archive table for deleted recurring payment status log records, preserving the full history of recurring status changes that have been removed from the active fact table.
Field | Field type | Description | Primary Key | Mapped Dimension table | Mapped Dimension field |
|---|---|---|---|---|---|
FactRecurringStatusLogWID | BIGINT IDENTITY | Surrogate warehouse key for the deleted recurring status log record. | PK | ||
FactRecurringStatusLogId | BIGINT | Source system identifier for the recurring status log. | |||
RecurringStatusLogPK | BIGINT | Primary key of the recurring status log in the source system. | |||
UserMembershipPK | BIGINT | Primary key of the user membership in the source system. | |||
Status | INT | Current recurring payment status code. | |||
StartDate | TIMESTAMP | Start date/time of this status period. | |||
ChangedTo | INT | Status code that this record was changed to. | |||
Comments | VARCHAR(65535) | Free-text comments associated with the status change. | |||
EndDate | TIMESTAMP | End date/time of this status period. | |||
StartedManually | BOOLEAN | Indicates if the status was started manually by a user. | |||
EndedManually | BOOLEAN | Indicates if the status was ended manually by a user. | |||
CreatedDate | TIMESTAMP | Date/time the status log record was created in the source system. | |||
IsCurrentRecord | BOOLEAN | Indicates if this was the current active status record at time of deletion. | |||
Source | INT | Source system or channel from which the status change originated. | |||
MembershipStartDate | DATE | Start date of the overall membership. | |||
MembershipEndDate | DATE | End date of the overall membership. | |||
CancellationDate | TIMESTAMP | Date/time the recurring membership was cancelled. | |||
usermembershipwid | BIGINT | Warehouse key referencing the user membership dimension. | |||
CenterWId | BIGINT | Warehouse key referencing the center dimension. | Bi_DimCenter | centerwid | |
RenewedFromUMId | VARCHAR(36) | Source system user membership ID this was renewed from. | |||
RenewedToUMId | VARCHAR(36) | Source system user membership ID this was renewed to. | |||
CancellationReasonFK | BIGINT | Foreign key to the cancellation reason lookup. | |||
GroupId | BIGINT | Group identifier for multi-tenant partitioning. | |||
UserWId | BIGINT | Warehouse key referencing the user dimension. | Bi_DimUser | userwid | |
OrganizationId | VARCHAR(36) | Unique identifier of the organization. | |||
membershipversionwid | BIGINT | Warehouse key referencing the membership version dimension. | |||
UpgradeDate | TIMESTAMP | Date/time the recurring membership was upgraded. | |||
etlCreatedBy | BIGINT | Identifier of the ETL process or user that created this record. | |||
etlCreatedDate | TIMESTAMP | Timestamp when the ETL process created this record. | |||
podname | VARCHAR(100) | Name of the source pod from which the data was extracted. | |||
void | INT | Flag indicating whether the record is voided (soft delete). | |||
EtlLastUpdatedOn | TIMESTAMP | Timestamp of the last ETL update (defaults to current UTC time). | |||
EtlLastUpdatedBy | VARCHAR(50) | User or process that last updated this record via ETL (defaults to current user). |