Repository Docs & Knowledge Base

Consolidated Spensara documentation repository (722 Markdown & architecture documents in ../repository)

Built Word Documents (.docx): Spensara Overview (.docx) Capability Assessment (.docx) Brink POS Ingestion Analysis (.docx) The Borg Ingestion Architecture (.docx)

Warehouse Model (93)

Analytical data warehouse schemas, curated tables, and Redshift/Athena data models.

warehouse-model/docs/features/ETL/warehouse-data/curated-model/loyalty_customers.md 3,286 bytes · Updated Oct 23, 2026 10:44 AM EDT

Raw File

curated.loyalty_customers

Purpose

This table stores customer records specific to a loyalty program rather than general POS identity. It is intended for balance checks, account linking, and customer engagement or reward workflows.

The table should be treated carefully because it mixes business identity fields with credential-oriented legacy columns from the current contract.

Schema contract

The deployed SQL under backend/Kds.Ai.Warehouse/Sql/Bootstrap/Tenant/Curated/ is the source of truth for the current column names, types, and nullability.

Column groups

Business properties describe the business entity, event, fact, or reference record represented by the table.
Technical and ETL properties describe warehouse lineage, surrogate keys, source reconciliation, tenant scoping, slowly changing dimension controls, and ingestion metadata.

Business properties

Column	Type	Nullable	Business Meaning
`customer_number`	`VARCHAR(50)`	No	Customer number used by the loyalty system.
`id`	`INTEGER`	No	Loyalty customer identifier.
`first_name`	`VARCHAR(100)`	No	Customer first name.
`last_name`	`VARCHAR(100)`	No	Customer last name.
`company`	`VARCHAR(150)`	Yes	Company associated with the loyalty customer.
`points_balance`	`INTEGER`	Yes	Current loyalty points balance.
`program_name`	`VARCHAR(255)`	Yes	Readable name of the loyalty program.
`member_status`	`VARCHAR(100)`	Yes	Business-friendly loyalty member status such as active, suspended, or inactive.
`tier_name`	`VARCHAR(100)`	Yes	Readable loyalty tier name.
`enrolled_at_utc`	`TIMESTAMP`	Yes	UTC timestamp when the customer enrolled in the loyalty program.
`last_activity_at_utc`	`TIMESTAMP`	Yes	UTC timestamp of the customer's last loyalty-related activity.

Technical and ETL properties

Column	Type	Nullable	Technical Meaning	Notes
`loyalty_customer_key`	`BIGINT`	Yes	Stable warehouse key for the loyalty customer entity.
`source_system`	`VARCHAR(100)`	Yes	Canonical name of the source platform that produced the loyalty record.
`source_loyalty_customer_id`	`VARCHAR(199)`	Yes	Original loyalty customer identifier from the source platform.
`tenant_key`	`BIGINT`	Yes	Stable warehouse key for the tenant, brand, or client that owns the loyalty program.
`tenant_name`	`VARCHAR(255)`	Yes	Readable tenant, brand, or client name associated with the loyalty member.
`is_current`	`BOOLEAN`	Yes	Canonical flag indicating whether the row is the current loyalty-member version.
`effective_from_utc`	`TIMESTAMP`	Yes	UTC timestamp from which the current loyalty-member version is valid.
`effective_to_utc`	`TIMESTAMP`	Yes	UTC timestamp until which the current loyalty-member version is valid.
`_etl_batch_id`	`VARCHAR(256)`	Yes	ETL batch lineage identifier.
`_etl_source_type`	`VARCHAR(100)`	Yes	Source adapter type used by ETL.
`_etl_ingested_at`	`TIMESTAMP`	No	Warehouse ingestion timestamp for the curated row.	Required ingestion audit timestamp.