Collected requirements from a variety of sources to define and design a custom coded ETL system. Once the design was approved; implemented the system in Transact-SQL using normalized table design, views, and stored procedures.
The developed ETL system was built around a data dictionary, which provided de-duping and common translation rules by adding field values into the dictionary. Custom rules could also be added by writing SQL User-defined functions. The system imported Unicode text files of varying layouts, performed necessary translations and loaded accepted data into a staging table.
The staging table was the source for feeding data to an OLTP system used to survey both phone and web customer, as well as into the data warehouse system for both client and internal reporting purposes. The system processed approximately 50,000 rows weekly of the nine months of the survey data collection.
Our responsibilities included designing the normalized table structure, writing the various stored procedures, and creating both technical documentation and end-user documentation for the entire system.