Data Engineering and my|CalPERS Support

Roles/Responsibilities:
Deliverable 1:

Fulfill high-volume one-time ad hoc requests for business data requested by CalPERS business divisions.

Monthly fulfillment of business data requests is expected to consist of fulfilling 70-80 requests per month. This estimate is based on counts from 2025 and is expected to be representative of the workload in the 2026/2027 fiscal year. Most of the business data requests use SQL queries to query records from the myCalPERS database and/or the CalPERS data warehouse. The myCalPERS data model includes 2000 tables that contain business transactions and related information. The CalPERS data warehouse model consists of 30 fact tables and 100 dimension tables. Business data requests have a 10-day service level agreement that the vendor will be bound to.

Deliverable 2:

Design and deliver a set of data marts that will support myCalPERS analytics.

Data marts will be handled in phases. Phase I is the design phase. CalPERS and the vendor will agree to the design and scope of the data marts. Phase II is the development phase. The vendor will develop data models for the data marts and the code needed to refresh the data in the data marts based on Phase I. Phase III is the review and acceptance phase where the data in the data marts is validated and the refresh code is integrated into the existing data platforms ETL batch window.

Deliverable 3: Knowledge Transfer

Knowledge transfer between the vendor and CalPERS will be facilitated through an existing process called Data Request Centralization where (1) requests for business data are submitted via ServiceNow (2) requests are routed to the fulfiller (3) request fulfillment artifacts such as technical documents and SQL scripts are added to the Data Request Library and (4) data is transferred securely to the requestor via a Data Locker. Any data issues that are identified during fulfillment of business data requests are reported to CalPERS and recorded in the Data Request Library

Mandatory Skills: N/A

1. Advanced Oracle SQL and PL/SQL Development - Expertise in writing complex SQL queries stored procedures functions packages and performance-tuned PL/SQL programs for high-volume data extraction transformation and validation from large databases (2000 tables in myCalPERS and 30 fact / 100 dimension tables in the data warehouse).
2. Data Mart Design and Development - Strong experience in designing developing and implementing dimensional data models and data marts (especially Pension-related) including ETL/refresh logic integration into existing data platforms and supporting recurring analytical requests.
3. Ad-hoc Data Request Fulfillment in Enterprise Environment - Proven ability to interpret complex business requirements quickly develop accurate data extracts under tight SLAs (10-day turnaround) handle high-volume requests (70-80 per month) and work with sensitive domains such as Pension Health Insurance Finance or Customer Service data while ensuring data accuracy and compliance.

Desirable Skills: N/A