Job Description :
Senior SQL Server ETL Developer
Boston, MA
1+ year contract

The following job description is for the 6 12 months contract position of MS SQL Server ETL Developer - Senior for the Department of Public Health - Office of Data Management and Outcomes Assessment.
The project involves rebuilding a large, complex clinical and administrative database. This database holds the Center for Health Information and Analysis (CHIA) Case Mix data, which includes all hospitalizations (HDD), outpatient observations (OP), and emergency department (ED) visits across Massachusetts from the late 1980s to present. This database needs to be restructured to align with business needs.
Database Architecture Re-Design and Development: This project will involve restructuring the database into a relational structure, with views and lookups as needed. Particular focus will be on improving efficiency and streamlining annual upload of case mix files.
Database Security and Minimal Access: Ensuring the database limits access and complies with state, federal, and MDPH guidelines, regulations, and laws are essential. This project will involve working closely with Mass IT and EOHHS IT to ensure the database is secure and access is limited to authorized parties only. In addition, this will involve the creation of user-based access levels within MDPH.

Documentation and Variable Mapping: This project will involve mapping variables and extensively documenting variable options and descriptions in a data dictionary.

Functional Title: MS SQL Server ETL Developer Senior

The Senior ETL Developer shall work closely with the DPH IT and Business teams and will be responsible for the proper re-design, development, documentation, support, maintenance and enhancements to the current database and application environments. The Senior ETL developer is also responsible for ensuring high availability of all related business applications through the strict adherence to all MassIT and EOHHS PMO processes, ensuring proper confidentiality of data, security, quality assurance, change management, organizational management and release management practices. This position reports to the bureaus designated IT Program Manager and DPH IT Director of Applications.

Key Responsibilities:

Review the current database architecture, re-design & develop the new database to improve the efficiency, usability and streamline the data import and export processes.
Develop advanced T-SQL code for implementing application business logic using stored procedures, views, and functions.
Document specific requirements for data integration, including file format, fields, etc.
Develop ETL processes for the import and export of data between the DPH internal systems using SQL Server Integration Services and advanced T-SQL to include stored procedures, user-defined functions, views, etc.
Assist in the Creation and Analysis of optimized stored procedures for data pulls for reports/exports.
Develop data warehouse objects and processes for the purpose of supporting business intelligence, including ETL, fact/dimension tables, and cubes.
Research, analyze, document, and maintain internal Applications and Database architectures.
Assist with RICE (Reports, Integrations, Conversions, and Enhancements) through the use of current tools.
Provide technical assistance and information to technical and non-technical users.
Continually look for opportunities to improve and automate current processes.
Ensure adherence and compliance with department policies, guidelines, and objectives.
Analyze, design, implement and maintain the businesses critical systems, maintaining at all times the proper data security standards set forth by the organization.

Experience and Skills:

Experience with SQL Server databases and their native utilities (Query Analyzer, Analytical Workbench, Etc
Advanced SQL query-writing skills, Linux/Unix scripting, and automated job scheduling.
Experience in ETL Development using standard tools like SSIS, SSAS, or other open source ETL tools.
Knowledge of file and file transmission protocols (SFTP, Web Services, etc.
Strong knowledge of networking protocols and ODBC, JDBC connectivity as it relates to user based connectivity.
Knowledge of applications security including authentication and role based access control within the DB.
Robust data modeling and database design experience.
Strong adherence to source control, version management, or configuration management practices.
Ability to communicate effectively both verbally and in writing (clearly and succinctly
Experienced in providing ongoing maintenance and support of IT systems and User communities.
Capable of working independently or as part of a large team.
Experience in maintaining the confidentiality of sensitive information.
SQL Development ability to write, review and troubleshoot SQL Code and design (stored procs, functions, tables, views, triggers, indexes, constraints
T-SQL, sqlcmd, bcp, sqldiag, tablediff and other programing and diagnostics - Expert level knowledge/experience.
Understanding of Data encryption, TDE, and key management - Intermediate level knowledge/experience.
Experience in troubleshooting and resolving database integrity issues, performance issues, blocking and deadlocking issues, replication issues, log shipping issues, connectivity issues, security issues etc.
Experience in Performance Tuning, Query Optimization, using Performance Monitor, SQL Profiler and other related monitoring and troubleshooting tools.
Strong knowledge of Database server administration and in virtual environments.
Strong understanding of how indexes, index management, integrity checks, configuration, patching. How statistics work, how indexes are stored, how they can be created and managed effectively.
Knowledge of Database native tools (Profiler, DTA, SSMS, SAC, SSCM, PerfMon, DMVs, and system sprocs
Working experience of other advanced ETL tools is preferred (Informatics, Data Stage, CloverETL, Talend, etc.
Working knowledge Database Error and Exception Handling.
Knowledge of Base SAS programming and migration of SAS procs to SQL is a Plus.


Minimum 8 years of experience in developing TSQL code, and install/implement on production environments.
5+ years of experience in database architecture and administration, SQL tuning, expdp/impdp, SQL loader, Erwin/Data modeler, OEM and ADDM/AWR generation.
5+ Years of experience in implementing complex ETL processes.
3+ years of experience in database parameter tuning, data guard and golden gate.
Bachelors degree or equivalent work experience is required.

Experience in developing MySQL, Oracle, NoSQL (MongoDB, Cassandra etc database is a plus.
Strong adherence to source control, version management, or configuration management practices.
Experience in maintaining the confidentiality of sensitive information, including adhering to state, federal, and department privacy laws and regulations.
Exceptional analytical and problem-solving skills.
Ability to communicate effectively both verbally and in writing.