From Informatica to Apache SeaTunnel: A Financial-Grade ETL Migration in Practice

1. Project Background and Challenges

As the head of data architecture at a financial technology company, I led the migration of our core ETL system from Informatica PowerCenter to a domestic ETL platform last year. The migration covered more than 200 workflows and a core system processing terabytes of data every day. It took five months from start to finish, and we ultimately completed the transition smoothly with zero data incidents. Today, I’d like to share the key decisions, technical details, and lessons learned from the project.

As a long-standing leader in enterprise data integration, Informatica has dominated the traditional ETL market for more than 20 years. Its visual development environment, reliable scheduling engine, and comprehensive metadata management have made it a de facto standard in industries such as financial services and telecommunications. However, with the changing international landscape and the growing demand for domestic technology adoption, we had to confront three practical challenges:

1. High licensing costs: Annual maintenance fees running into millions of RMB were a significant burden for a mid-sized enterprise.
2. A closed technology stack: It was difficult to integrate deeply with emerging real-time computing and AI platforms.
3. Slow response to customization needs: Custom requirements often required cross-border collaboration, resulting in delivery cycles that could stretch to several months.

After years of development, domestic ETL platforms such as Kettle, DataX, and SeaTunnel have become viable alternatives for core ETL capabilities. Our technical evaluation showed that, in batch-processing scenarios, domestic platforms could cover approximately 85% of Informatica’s functionality while costing only one-third as much and offering the flexibility for secondary development.

2. Migration Strategy and Design

2.1 Technology Selection

We conducted an in-depth evaluation of three mainstream domestic ETL tools:

We ultimately selected Apache SeaTunnel as the primary migration platform for three main reasons:

• Support for both Spark and Flink engines, making it a good fit for our future real-time data warehouse strategy.
• A plugin-based architecture that makes it easier to extend support for custom data sources.
• An active Chinese-language community that enables us to resolve technical issues quickly.

2.2 Migration Strategy

We adopted a hybrid approach combining phased migration with parallel-run validation:

1. Decouple the components: Break each Informatica workflow into three independent modules for extraction, transformation, and loading.
2. Map the functionality:

• Source data extraction → SeaTunnel Source plugins
• Complex transformation logic → Rebuild with Spark SQL
• Scheduling dependencies → Orchestrate with Apache DolphinScheduler
  1. Validate the data:

# Use a combination of CRC32 and sample-based comparison for validation
def verify_data(source_df, target_df):
    if source_df.count() != target_df.count():
        return False
    sample_ratio = 0.01
    source_sample = source_df.sample(sample_ratio)
    target_sample = target_df.sample(sample_ratio)
    return source_sample.exceptAll(target_sample).isEmpty()

Key lesson: Don’t try to replicate Informatica workflows 1:1. Use the migration as an opportunity to optimize the data flows. We redesigned 30% of the transformation logic that had performance bottlenecks.

3. Core Migration Implementation

3.1 Metadata Migration

The Informatica Repository contained thousands of metadata objects. We developed a metadata parsing tool to automate the migration:

1. Export XML metadata through the PowerCenter CLI.
2. Use XSLT to transform key attributes:

<!-- Example of mapping transformation -->
<xsl:template match="SOURCE">
  <connector type="jdbc">
    <property name="url" value="{@DBSERVER}"/>
    <property name="table" value="{@OBJECTNAME}"/>
  </connector>
</xsl:template>

1. Generate SeaTunnel configuration file templates.

3.2 Refactoring Complex Transformations

Informatica components such as Expression and Aggregator required special handling during the migration.

1. Conditional routing: The original workflows used the Router component.

-- Reimplemented with Spark SQL
df.createTempView("source");
spark.sql("""
  SELECT *, 
    CASE 
      WHEN amount > 10000 THEN 'VIP' 
      ELSE 'NORMAL' 
    END AS customer_level
  FROM source
""");

1. Slowly Changing Dimensions (SCD): The original implementation relied on the Slowly Changing Dimension wizard.

-- Implement Type 2 SCD using MERGE INTO
MERGE INTO dim_customer t
USING stage_customer s
ON t.customer_id = s.customer_id
WHEN MATCHED AND t.current_flag='Y' AND t.email <> s.email THEN
  UPDATE SET t.current_flag='N', t.end_date=CURRENT_DATE
  INSERT VALUES (s.customer_id, s.email, ..., 'Y', CURRENT_DATE, NULL)

3.3 Performance Tuning in Practice

The most challenging issue we encountered was an ETL job containing 20 joins that ran into an OOM error on SeaTunnel. We resolved it through the following optimizations:

1. Analyze the execution plan:

# Get the Spark physical execution plan
EXPLAIN EXTENDED 
SELECT * FROM fact f JOIN dim1 d1 ON f.id=d1.id ...

1. Optimization measures:

• Enable dynamic partition pruning: spark.sql.optimizer.dynamicPartitionPruning=true
• Adjust the broadcast threshold: spark.sql.autoBroadcastJoinThreshold=20MB
• Force broadcast joins for dimension tables: /*+ BROADCAST(dim1) */

1. Parameter comparison:

4. Validation and Cutover

4.1 Ensuring Data Consistency

We established a three-level validation framework:

1. Record-level validation: Generate a CRC32 fingerprint for the entire table.

SELECT 
  SUM(CAST(CRC32(CONCAT_WS('|',col1,col2,...)) AS BIGINT)) AS checksum 
FROM table

1. Business metric comparison: Keep month-over-month fluctuations in key KPIs below 1%.

2. User acceptance testing: Have business teams validate the data in their reports.

4.2 Gradual Rollout

We migrated workloads incrementally by business line:

1. First, migrate non-core marketing analytics workloads.
2. Next, migrate the risk management system.
3. Finally, migrate the financial settlement system.

We monitored each phase for one week, with a particular focus on:

• Data latency
• Resource utilization
• Error logs

5. Lessons Learned

5.1 Key Success Factors

1. Team training: Two months before the migration, we organized training sessions for Informatica developers to learn Spark and SeaTunnel.
2. A complete toolchain:

• Developed an auxiliary workflow conversion tool.
• Built an automated data comparison platform.
  1. Vendor support: Established a direct communication channel with the SeaTunnel core team.

5.2 Lessons Learned the Hard Way

1. Time zone issues: Informatica uses the server's time zone by default, while Spark uses UTC. We therefore needed to explicitly handle time zone conversion for all relevant time fields:

FROM_UTC_TIMESTAMP(CAST(col AS TIMESTAMP), 'Asia/Shanghai')

1. Character encoding pitfalls: The ZHS16GBK encoding used by the Oracle source database had to be explicitly configured:

source:
  jdbc:
    connection_options: "oracle.jdbc.convertNlsStrings=true"

1. Transaction semantics: Informatica uses auto-commit by default, while Spark requires transaction behavior to be controlled explicitly:

df.write
  .option("isolationLevel", "READ_COMMITTED")
  .mode("overwrite")
  .saveAsTable("target")

Quantified Results After the Migration

• 60% reduction in infrastructure costs, from eight physical servers to a Kubernetes cluster
• 40% reduction in average job execution time
• New capabilities for real-time data processing

The biggest takeaway from this migration is that domestic infrastructure software has reached a level where it can serve as a viable alternative to traditional enterprise platforms. But successful migration requires more than simply replacing one tool with another. It requires a shift in technical mindset.

Instead of treating migration as a straightforward tool replacement exercise, we used it as an opportunity to rethink and modernize our data architecture, laying the groundwork for the next stage of real-time processing and intelligent data operations.