ETL/SSIS Developer (PostgreSQL to SQLServer)
Posted 1 day ago by 1765534942
Negotiable
Outside
Remote
USA
Summary: The SSIS Developer role focuses on designing, developing, and maintaining ETL pipelines for migrating data from Guidewire Aurora PostgreSQL to SQL Server databases. This position is crucial for a significant Guidewire upgrade/migration initiative, requiring expertise in ETL processes, SQL, and data migration within the insurance domain. The ideal candidate will ensure data accuracy and support nightly batch cycles while collaborating with functional teams. A strong background in ETL development and experience with Guidewire systems is preferred.
Key Responsibilities:
- Develop SSIS packages to extract data from Guidewire Aurora (PostgreSQL) and load it into SQL Server databases.
- Design scalable and optimized ETL frameworks supporting incremental loads, full loads, and delta logic.
- Configure secure connectivity between cloud Aurora DB and on-prem SQL Server.
- Implement transformations, business rules, surrogate key management, and referential integrity through ETL.
- Design and orchestrate nightly ETL batch processes using SQL Server Agent or enterprise schedulers.
- Implement error-handling, retry logic, logging, and alerting for batch jobs.
- Monitor performance and optimize slow-running jobs to meet ETL SLAs.
- Conduct field-level and record-level reconciliation between Aurora and SQL Server tables.
- Develop comparison scripts to validate counts, checksums/hashes, and business-critical attributes.
- Identify mismatches, perform root-cause analysis, and coordinate with database and Guidewire functional teams to resolve issues.
- Maintain audit trail, data lineage documentation, and reconciliation dashboards.
- Collaborate with Guidewire functional consultants to understand PC/CC/BC data models and changes introduced by the upgrade.
- Perform data profiling to identify data gaps or transformation needs stemming from version differences.
- Support parallel runs, cutover planning, and post-migration validation.
- Ensure ETL processes align with Guidewire data model standards and release cycles.
- Create detailed technical design documents (TDD), mapping sheets, and ETL flow diagrams.
- Maintain SSIS source code in version control (Git/Azure DevOps).
- Provide post-deployment support, including defect resolution, optimizations, and enhancements.
Key Skills:
- Strong hands-on experience with SSIS (SQL Server Integration Services).
- Expertise in SQL Server (T-SQL, stored procedures, triggers, indexing, query optimization).
- Experience extracting from PostgreSQL/Aurora using ODBC/OLEDB/.NET providers.
- Experience with data migration, large datasets, and performance tuning.
- Familiarity with Guidewire PolicyCenter, ClaimCenter, or BillingCenter data models.
- Understanding of Guidewire Aurora database structures for PC/CC/BC.
- Experience working on Guidewire version upgrades or cloud migrations is a plus.
- Experience with ETL job scheduling (SQL Server Agent, Control-M, Autosys, etc.).
- Experience using Git, Azure DevOps, Jenkins, or other CI/CD tools.
- Ability to write reconciliation scripts in SQL and/or Python (optional).
- Strong understanding of data quality, audit, and compliance practices.
- 5-8 years of ETL development experience, with at least 3+ years in SSIS.
- Prior experience in migration projects, preferably Guidewire or other insurance platforms.
- Strong analytical skills and ability to investigate data anomalies.
- Experience working in Agile/Scrum environments.
- Bachelor's degree in Computer Science, Information Systems, Engineering, or equivalent experience.
Salary (Rate): undetermined
City: undetermined
Country: USA
Working Arrangements: remote
IR35 Status: outside IR35
Seniority Level: undetermined
Industry: IT
Detailed Description From Employer:
Job Summary
Role: SSIS Developer Guidewire Aurora (PostgreSQL) to SQL Server Migration
Location: Canada/USA - Remote
Tentative Start Date: 3rd Week Dec
No of position: 1
Domain/Industry: Insurance
Year of Experience: 5-8 Years
Type: Contract/FTE
Role Overview
The SSIS Developer will be responsible for designing, developing, and maintaining ETL pipelines to extract data from the Guidewire Aurora PostgreSQL database and load it into on-premises SQL Server databases. This role plays a critical part in a major Guidewire upgrade/migration initiative and involves building a nightly batch cycle, ensuring data accuracy through reconciliation processes, and validating end-to-end data movement. The ideal candidate has strong ETL, SQL, and data migration experience within insurance domain systems.
The SSIS Developer will be responsible for designing, developing, and maintaining ETL pipelines to extract data from the Guidewire Aurora PostgreSQL database and load it into on-premises SQL Server databases. This role plays a critical part in a major Guidewire upgrade/migration initiative and involves building a nightly batch cycle, ensuring data accuracy through reconciliation processes, and validating end-to-end data movement. The ideal candidate has strong ETL, SQL, and data migration experience within insurance domain systems.
Required Technical Skills
ETL & Database
Strong hands-on experience with SSIS (SQL Server Integration Services).
Expertise in SQL Server (T-SQL, stored procedures, triggers, indexing, query optimization).
Experience extracting from PostgreSQL/Aurora using ODBC/OLEDB/.NET providers.
Experience with data migration, large datasets, and performance tuning.
Guidewire Migration Knowledge (Preferred)
Familiarity with Guidewire PolicyCenter, ClaimCenter, or BillingCenter data models.
Understanding of Guidewire Aurora database structures for PC/CC/BC.
Experience working on Guidewire version upgrades or cloud migrations is a plus.
Other Skills
Experience with ETL job scheduling (SQL Server Agent, Control-M, Autosys, etc.).
Experience using Git, Azure DevOps, Jenkins, or other CI/CD tools.
Ability to write reconciliation scripts in SQL and/or Python (optional).
Strong understanding of data quality, audit, and compliance practices.
Qualifications & Experience
5 8 years of ETL development experience, with at least 3+ years in SSIS.
Prior experience in migration projects, preferably Guidewire or other insurance platforms.
Strong analytical skills and ability to investigate data anomalies.
Experience working in Agile/Scrum environments.
Bachelor's degree in Computer Science, Information Systems, Engineering, or equivalent experience.
ETL & Database
Strong hands-on experience with SSIS (SQL Server Integration Services).
Expertise in SQL Server (T-SQL, stored procedures, triggers, indexing, query optimization).
Experience extracting from PostgreSQL/Aurora using ODBC/OLEDB/.NET providers.
Experience with data migration, large datasets, and performance tuning.
Guidewire Migration Knowledge (Preferred)
Familiarity with Guidewire PolicyCenter, ClaimCenter, or BillingCenter data models.
Understanding of Guidewire Aurora database structures for PC/CC/BC.
Experience working on Guidewire version upgrades or cloud migrations is a plus.
Other Skills
Experience with ETL job scheduling (SQL Server Agent, Control-M, Autosys, etc.).
Experience using Git, Azure DevOps, Jenkins, or other CI/CD tools.
Ability to write reconciliation scripts in SQL and/or Python (optional).
Strong understanding of data quality, audit, and compliance practices.
Qualifications & Experience
5 8 years of ETL development experience, with at least 3+ years in SSIS.
Prior experience in migration projects, preferably Guidewire or other insurance platforms.
Strong analytical skills and ability to investigate data anomalies.
Experience working in Agile/Scrum environments.
Bachelor's degree in Computer Science, Information Systems, Engineering, or equivalent experience.
Key Responsibilities
1. ETL Design & Development
Develop SSIS packages to extract data from Guidewire Aurora (PostgreSQL) and load it into SQL Server databases.
Design scalable and optimized ETL frameworks supporting incremental loads, full loads, and delta logic.
Configure secure connectivity between cloud Aurora DB and on-prem SQL Server.
Implement transformations, business rules, surrogate key management, and referential integrity through ETL.
2. Nightly Batch Cycle Setup
Design and orchestrate nightly ETL batch processes using SQL Server Agent or enterprise schedulers.
Implement error-handling, retry logic, logging, and alerting for batch jobs.
Monitor performance and optimize slow-running jobs to meet ETL SLAs.
3. Data Validation & Reconciliation
Conduct field-level and record-level reconciliation between Aurora and SQL Server tables.
Develop comparison scripts to validate counts, checksums/hashes, and business-critical attributes.
Identify mismatches, perform root-cause analysis, and coordinate with database and Guidewire functional teams to resolve issues.
Maintain audit trail, data lineage documentation, and reconciliation dashboards.
4. Support Guidewire Upgrade/Migration
Collaborate with Guidewire functional consultants to understand PC/CC/BC data models and changes introduced by the upgrade.
Perform data profiling to identify data gaps or transformation needs stemming from version differences.
Support parallel runs, cutover planning, and post-migration validation.
Ensure ETL processes align with Guidewire data model standards and release cycles.
5. Documentation & Delivery
Create detailed technical design documents (TDD), mapping sheets, and ETL flow diagrams.
Maintain SSIS source code in version control (Git/Azure DevOps).
Provide post-deployment support, including defect resolution, optimizations, and enhancements.
1. ETL Design & Development
Develop SSIS packages to extract data from Guidewire Aurora (PostgreSQL) and load it into SQL Server databases.
Design scalable and optimized ETL frameworks supporting incremental loads, full loads, and delta logic.
Configure secure connectivity between cloud Aurora DB and on-prem SQL Server.
Implement transformations, business rules, surrogate key management, and referential integrity through ETL.
2. Nightly Batch Cycle Setup
Design and orchestrate nightly ETL batch processes using SQL Server Agent or enterprise schedulers.
Implement error-handling, retry logic, logging, and alerting for batch jobs.
Monitor performance and optimize slow-running jobs to meet ETL SLAs.
3. Data Validation & Reconciliation
Conduct field-level and record-level reconciliation between Aurora and SQL Server tables.
Develop comparison scripts to validate counts, checksums/hashes, and business-critical attributes.
Identify mismatches, perform root-cause analysis, and coordinate with database and Guidewire functional teams to resolve issues.
Maintain audit trail, data lineage documentation, and reconciliation dashboards.
4. Support Guidewire Upgrade/Migration
Collaborate with Guidewire functional consultants to understand PC/CC/BC data models and changes introduced by the upgrade.
Perform data profiling to identify data gaps or transformation needs stemming from version differences.
Support parallel runs, cutover planning, and post-migration validation.
Ensure ETL processes align with Guidewire data model standards and release cycles.
5. Documentation & Delivery
Create detailed technical design documents (TDD), mapping sheets, and ETL flow diagrams.
Maintain SSIS source code in version control (Git/Azure DevOps).
Provide post-deployment support, including defect resolution, optimizations, and enhancements.