Canyon Ranch Data Infrastructure Project

Business Problem

Canyon Ranch—a luxury wellness resort—struggled with fragmented legacy systems across reservations, staff scheduling, and service management. Guest bookings were often made on paper or through outdated tools like CLS and GuestWare. As a result:

• Staff repeatedly asked guests for the same information
• Program coordinators couldn't view full itineraries in one place
• Service coordination was disjointed and slow
• Guests experienced long wait-times and inconsistent personalization

The core issue: data was scattered across disconnected systems, creating friction for both staff and guests.

Project Scope and Approach

This project delivered a centralized data architecture that supports both daily operations and strategic reporting. The engagement included:

• Business requirements gathering
• Entity-Relationship (ER) modeling
• Third Normal Form (3NF) relational schema design
• Dimensional modeling with a snowflake schema
• Implementation of ETL and ELT pipelines in SQL Server

Data Architecture and Design

Logical Modeling

The first step was creating an Entity-Relationship Diagram (ERD) to capture the flow of data between guests, services, reservations, staff, feedback, and referral sources.

Operational Database (OLTP)

A relational schema was built in SQL Server to manage transactional data. The design was fully normalized to ensure integrity and eliminate redundancy. Core tables included:

• Customer, Reservation, Room, Feedback, ReferralSource
• Staff, Service, ReservationService, ProgramCoordinator

This structure allowed Program Coordinators to retrieve complete guest itineraries in real-time.

Analytical Warehouse (OLAP)

To support strategic reporting and trend analysis, a snowflake schema was implemented in SQL Server. The central Fact_ReservationService table captures service-level transactions and is supported by four dimension tables, each designed to enable a specific layer of insight:

• Dim_Customer: Tracks demographic attributes (e.g., gender, age group, loyalty tier) to identify high-value segments, understand service preferences, and evaluate loyalty program effectiveness.
• Dim_Service: Contains detailed service-level data including price, duration, and average guest rating—enabling performance monitoring of individual offerings like massages, coaching sessions, or fitness classes.
• Dim_ServiceType: Normalizes the service categories into broader types (e.g., Spa, Fitness, Nutrition). This allows department heads to evaluate category-level performance, resource allocation, and satisfaction trends.
• Dim_Date: Encodes calendar information (day of week, month, quarter, year) to facilitate seasonal demand forecasting, peak/off-peak scheduling, and longitudinal guest behavior analysis.

This dimensional model enables Canyon Ranch to perform deep-dive analyses by guest profile, service type, and time period—supporting data-informed marketing, staffing, and service optimization strategies.

Pipeline and Infrastructure

The data pipeline used a hybrid ETL/ELT strategy:

• ETL: Moved and shaped operational data into dimension tables
• ELT: Performed on-demand metrics like average ratings, usage counts, and department-level satisfaction using SQL queries

Business Impact

• Unified Access to Guest Profiles: The centralized system enabled staff to instantly retrieve guest preferences, service history, and itinerary—eliminating the need to ask guests for the same information twice.
• Reduced Wait Times and Better Service: Program coordinators could now schedule and confirm services more efficiently, improving guest satisfaction and operational flow.
• Referral and Feedback Integration: Each referral—whether from affiliate, guest, or website—was tracked. Staff-level feedback could be tied back to specific services, helping management target coaching and quality improvements.
• Department-Level Visibility: The warehouse allowed slicing by service type, time period, guest segment, and satisfaction rating, helping department heads make data-informed decisions.

Technical Summary

• 13-table OLTP schema in 3NF, with referential integrity enforced
• Snowflake DW schema, one fact and four dimensions
• ETL pipeline for loading base data; ELT transformations for analytics
• Synthetic but realistic test data to reflect domain logic
• Security and compliance modeled to support HIPAA-like environments

← Back to Home