Building an ETL Pipeline Using Spring Boot and Spring Cloud

ETL (Extract, Transform, Load) is a crucial process for moving data from various sources to a central repository while applying necessary transformations. In this article, we will build an ETL pipeline using Spring Boot with Spring Cloud Data Flow for distributed and scalable ETL orchestration.

1. Project Setup

Step 1: Create a Spring Boot Project

Use Spring Initializr and include the following dependencies:

• Spring Cloud Data Flow (for ETL orchestration)
• Spring Cloud Stream (for messaging and streaming integration)
• Spring Data JPA (for database operations)
• MS SQL Server Driver (for database connectivity)
• Lombok (optional for reducing boilerplate code)

<dependencies>
    <dependency>
        <groupId>org.springframework.cloud</groupId>
        <artifactId>spring-cloud-starter-dataflow-server</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.cloud</groupId>
        <artifactId>spring-cloud-starter-stream-kafka</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>
    <dependency>
        <groupId>com.microsoft.sqlserver</groupId>
        <artifactId>mssql-jdbc</artifactId>
    </dependency>
</dependencies>        

2. Configure the Database

Update the application.properties file to connect to MS SQL Server:

spring.datasource.url=jdbc:sqlserver://localhost:1433;databaseName=etl_db
spring.datasource.username=your_username
spring.datasource.password=your_password
spring.datasource.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver

spring.jpa.database-platform=org.hibernate.dialect.SQLServerDialect
spring.cloud.dataflow.server.uri=http://localhost:9393        

3. Define the ETL Entities

Extract (E) - Define the Source Entity

@Entity
@Data
@NoArgsConstructor
@AllArgsConstructor
public class SourceData {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String name;
    private Double value;
    private LocalDateTime timestamp;
}        

Transform & Load (T & L) - Define the Target Entity

@Entity
@Data
@NoArgsConstructor
@AllArgsConstructor
public class SourceData {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String name;
    private Double value;
    private LocalDateTime timestamp;
}        

4. Create the ETL Stream with Spring Cloud Data Flow

Step 1: Define Source Data Stream

@EnableBinding(Source.class)
public class DataProducer {
    @Autowired
    private Source source;

    public void sendData(SourceData data) {
        Message<SourceData> message = MessageBuilder.withPayload(data).build();
        source.output().send(message);
    }
}        

Step 2: Transform Data Stream

@EnableBinding(Processor.class)
public class DataProcessor {
    @StreamListener(Processor.INPUT)
    @SendTo(Processor.OUTPUT)
    public ProcessedData process(SourceData sourceData) {
        ProcessedData processedData = new ProcessedData();
        processedData.setName(sourceData.getName());
        processedData.setTransformedValue(sourceData.getValue() * 1.1);
        processedData.setProcessedAt(LocalDateTime.now());
        return processedData;
    }
}        

Step 3: Load Data to Database

@EnableBinding(Sink.class)
public class DataConsumer {
    @Autowired
    private ProcessedDataRepository repository;

    @StreamListener(Sink.INPUT)
    public void saveData(ProcessedData processedData) {
        repository.save(processedData);
    }
}        

5. Deploying the ETL Pipeline

Run the Spring Cloud Data Flow server locally:

docker run --name dataflow-server --rm -p 9393:9393 springcloud/spring-cloud-dataflow-server        

Then, deploy the ETL pipeline using:

stream create etlStream --definition "source | processor | sink" --deploy        

6. Conclusion

This article demonstrated how to build an ETL pipeline in Spring Boot using Spring Cloud Data Flow. You learned how to:

• Extract data from a source using Spring Cloud Stream.
• Transform data using a Processor service.
• Load data into a target database using a Sink service.
• Deploy and manage ETL jobs with Spring Cloud Data Flow.

For production use, consider adding:

• Error handling & logging (e.g., using @Slf4j and try-catch).
• Kafka Integration for real-time streaming.
• Monitoring using Spring Boot Actuator.