Context
CBRE manages maintenance operations across thousands of facilities, with work order, asset, technician, and preventive maintenance data originating in a CMMS such as CBRE's enterprise maintenance platforms and related building operations systems. Today, regional teams export CSVs manually and run ad hoc SQL in separate reporting environments, which creates inconsistent KPIs and delays operational decisions.
Design a production-grade pipeline that centralizes CMMS data into an analytics layer used by operations managers to improve technician utilization, SLA compliance, preventive maintenance completion, and asset downtime reporting.
Scale Requirements
- Sites: 8,000+ properties globally
- Assets: 12M managed assets
- Work orders: 4M new records/day, 25M updates/day
- Batch cadence: core operational tables refreshed every 15 minutes; full historical backfill of 3 years
- Latency target: source change to dashboard availability in < 20 minutes
- Storage: ~35 TB raw history, ~8 TB curated warehouse tables
Requirements
- Ingest CMMS entities including
work_orders, work_order_status_history, assets, locations, technicians, vendors, and preventive_maintenance_plans.
- Support both incremental CDC-style loads for mutable operational tables and nightly batch loads for slower-changing reference data.
- Build curated models for operational reporting, including MTTR, first-time fix rate, overdue PM %, repeat failures, and technician productivity.
- Ensure idempotent loads and support replay/backfill by site, date range, or entity.
- Implement data quality checks for duplicate work orders, invalid status transitions, missing asset IDs, and timestamp anomalies.
- Orchestrate dependencies so downstream CBRE reporting tables only publish after quality gates pass.
- Expose analytics-ready tables for Power BI/Tableau-style dashboards and downstream finance/operations consumers.
Constraints
- Existing stack is primarily Azure + Snowflake + Airflow/dbt.
- Source APIs have rate limits and some regions only provide SFTP extracts.
- Budget favors managed services over large custom infrastructure.
- Must meet client data segregation requirements and maintain auditability for operational KPI changes.
