ETL Data Analyst

Scope:

Contractor's Key Person under this WOC will work with ORRAI's data warehouse team and CAM/CALMS project contractors to lead data modeling efforts that bridge business requirements with data architecture implementation. This includes the development of a conceptual data model, translation into dimensional star schemas, creation of mapping documents, and delivery of a data dictionary consistent with ODHS standards. The contractor will ensure modeling work is accurate, well-documented, and aligned with data governance and warehouse integration needs.

Tasks:

Contractor's Key Person shall:

• Collaborate with CAM/CALMS contractors and ORRAI DBEs and DBAs to gather and clarify data requirements.
• Develop a comprehensive conceptual data model that defines key business entities, relationships, and data domains for the CAM system.
• Translate the conceptual model into a dimensional model (star schema) suitable for data warehouse integration.
• Design and document fact and dimension tables in alignment with data warehousing best practices.
• Develop mapping documentation between CAM system data and the designed star schema to support ETL planning and implementation.
• Create and maintain a detailed data dictionary for all modeled entities, attributes, and business rules, following ODHS metadata standards.
• Facilitate workshops and meetings to validate models with stakeholders and ensure shared understanding.
• Use tools such as Microsoft Visio or Erwin to produce clear and maintainable diagrams and documentation.
• Ensure all deliverables align with ORRAI architecture, data governance practices, and metadata frameworks.
• Provide support and consultation as the data structures move through the logical and physical implementation phases in the ODHS data warehouse.

Qualifications:

• Extensive experience in conceptual and dimensional data modeling, including star schema design for data warehouses.
• Strong proficiency in data modeling tools such as Microsoft Visio and/or Erwin.
• Demonstrated ability to create clear and accurate data documentation, including entity definitions, mappings, and data dictionaries.
• Strong verbal and written communication skills, with the ability to facilitate discussions and document requirements across technical and non-technical audiences.
• Experience working with data warehouse teams, including DBEs and DBAs, in enterprise environments.
• Familiarity with human services programs or public sector data systems is preferred.
• Knowledge of data governance, metadata, and ETL processes is a plus.

Required skills and experience:

• Data Analysis (7-9 years of experience)
• Data Modeling in conceptual data modeling for large, complex system (7-9 years of experience)
• Data Warehousing (4-6 years of experience)
• SQL (7-9 years of experience)
• Microsoft Visio and/or Erwin data modeler (4-6 years of experience)
• Strong understanding, experience in design dimensional models and data modeling methodologies
• Excellent verbal and written communication
• Demonstrated ability to work closely with:
• Business analysts and program subject matter experts
• Database Engineers
• Database Administrators
• External contractors and project team
• In addition, the ideal candidate will have skills and experience in:
• Experience working with public sector, human services, or healthcare data systems (e.g., APD, CAM/CALMS, MMIS)
• Familiarity with data governance, data cataloging, and metadata management initiatives
• Experience supporting data warehouse development using SQL Server, Snowflake, or similar platforms