etl cli test project

C# console application for ETL operations.

---

General

MS SQL Server running in Docker (configured via docker-compose) and processes CSV input files. The database schema is initialized using sql/init.sql. The application is invoked via the root command with the following options:

• --source (-s) [required] - path to the source files
• --quiet (-q) [optional] - suppresses console output

Tech stack & Dependencies

• C# .NET (console application)
• Command-line parsing: System.CommandLine
• Database access: Microsoft.Data.SqlClient
• Logging: Microsoft.Extensions.Logging
• Data processing: CsvHelper, FastMember
• Dependency Injection: used to orchestrate services and provide dependencies on each execution

Implementation details

• Complete data transformation implemented, including time zone adjustments
• Multiple source files supported; duplicates are detected across all processed files as a single dataset
• File I/O handlers are type-agnostic, allowing flexible data processing
• The architecture is extensible - modifying data types only requires updating DataModel records and DataTransformerService
• The application is optimized for low memory usage by chaining IEnumerable sequences without eager loading
• File I/O is performed using streams and batch processing, so it can handle large files efficiently
• Database loading is using streamed bulk copy, avoiding unnecessary memory allocation
• The database schema includes one computed column and several indexes optimized for hot queries

Results

Processing the provided sample dataset produced the following results:

• Unsafe rows: 49
• Duplicated rows: 111
• Rows inserted into db: 29840

Possible improvements:

• Implementing automappers to simplify data convertion between transformations (in current implementation it is field names-based)
• Adopting more declarative delegates-based style to allow dynamic modifications of data transformation rules
• Implement more explicit and supported rules on DataModel fiels