Job Specifications
Job Profile: SSIS Developer – Guidewire Aurora to SQL Server Migration
Duration: Initial contract: 6 months, with a high possibility of extension to 12 months.
Rate: $65-70 CAD
Location: US/ Canada
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.
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.
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 Policy Center, Claim Center, or Billing Center 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.
About the Company
KData accelerates enterprise data and AI initiatives by providing expert Databricks. Snowflake, and Informatica consulting, specialized talent, and innovative accelerators. Follow us on Twitter: https://x.com/KDataAI/
Know more