Repository Docs & Knowledge Base

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

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

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

Raw File

curated.pos_destinations

curated.pos_destinations is the shared destination reference dimension in the warehouse.

It should answer the question: what order routing destinations exist, how are they configured, and are they currently active?

gives one row per destination snapshot
provides order routing context: name, indicator, is_delivery, delivery_minutes
keeps destination lifecycle context such as is_active and snapshot_date
used as a lookup join in curated.pos_orders for order_type, destination, service_channel, and fulfillment_channel
creates a stable contract for future cross-source order-channel analyses

One row per destination snapshot.

Business properties describe the destination: identity, routing configuration, delivery settings, and active status.
Technical and ETL properties describe warehouse lineage, surrogate keys, source reconciliation, tenant scoping, and ingestion metadata.

Column	Type	Nullable	Business Meaning	Notes
`destination_id`	`BIGINT`	No	Source-native destination identifier.	Preferred identifier for downstream joins.
`name`	`VARCHAR(1024)`	Yes	Human-readable destination name.	Used as `order_type` and `destination` in `curated.pos_orders`.
`indicator`	`VARCHAR(256)`	Yes	Short indicator code for the destination.
`description`	`VARCHAR(65535)`	Yes	Full description of the destination.
`is_active`	`BOOLEAN`	Yes	Whether the destination is currently active.
`is_delivery`	`BOOLEAN`	Yes	Whether the destination represents a delivery channel.	Used to derive `service_channel` and `fulfillment_channel` in `curated.pos_orders`.
`delivery_minutes`	`INTEGER`	Yes	Expected delivery time in minutes.
`snapshot_date`	`DATE`	Yes	Date of the destination snapshot used for ETL partitioning.
`currency_code`	`VARCHAR(10)`	Yes	Currency of monetary values.	Required when curated data spans multiple currency contexts.

Column	Type	Nullable	Technical Meaning	Notes
`source_system`	`VARCHAR(100)`	Yes	Canonical integration name used for lineage across source platforms.	Identifies which operational platform produced the record.
`source_destination_id`	`VARCHAR(199)`	Yes	Original source identifier kept for reconciliation and drill-through.	Preserves the native source key.
`destination_key`	`BIGINT`	Yes	Warehouse-generated stable surrogate key for the destination entity.	Surrogate warehouse key for dimensional joins.
`tenant_key`	`BIGINT`	Yes	Warehouse surrogate key for tenant or brand scope.
`tenant_name`	`VARCHAR(255)`	Yes	Tenant or brand identifier injected by the ETL context.	Human-readable tenant or brand lineage value.
`effective_from_utc`	`TIMESTAMP`	Yes	SCD2 effective-from timestamp.	Currently deferred to NULL.
`effective_to_utc`	`TIMESTAMP`	Yes	SCD2 effective-to timestamp.	Currently deferred to NULL.
`is_current`	`BOOLEAN`	Yes	SCD2 current-row flag.	Currently deferred to NULL.
`_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.