WS4 Data Import Update

Overview

This is the one of several challenges of Alamasato project build services for Sugar Cane growers app. In this challenge we'd like to update existing Heroku scheduler process to export data from external SQLServer database and import data into Heroku Postgres DB. This is only a one way integration, the SQLServer data is read only and this process never updates or delete any data from SQLServer database. Current Import process inserts duplicate records due to database key, so our objective here is to fix the import NOT to insert duplicated records for Sample - labtransactions data. 

You need to fix/ recreate existing test data generator test-data.js process for import so there are no duplicates. Note that our data generator exists as separate node module to create some test data for QA purpose. All data is then linked with grower ID and for QA testing, we use gorwer A900-A920 that is accessible to topcoder test users in the system. The Test data module when executed needs to read set of existing data records and copy this data for new test growers with current date.

This is needed so that our QA test and develpment can continue in a simulation mode even without live data feed (as if the data feed was live).

This process will run on separate Heroku app dedicated only for this data import. It will use Heroku Scheduler addon to run the Node.js program on schedule base, the frequency be as often as every 1hr to 30 min.

Detailed Requirements

- Update existing import Node.js app to avoid inserting duplicated data. The workflow is as follows

* The LabTransaction table data has a process that creates sample records in SQL Server initially with 0 data as placeholder but valid sample ID.

* The sample are taken to the lab for analysis and results are available after 3-4 hour period.

* As results arrive from the lab they are inserted as new rows in the SQL Server db with existing Sample ID row is filled with values. Our import prcess runs at a 30 min interval, that pick up same Samples and duplicates them because SQL Serve extid is used as unique key.

We need to change import to use Sample ID as the external key as it is unique data value for each sample.

The following requirements must be met

- Deploy on Heroku to run as Heroku Scheduler (free addon) process to import data one-way SQLServer -> Postrges DB.This app will run Heroku app on sheduler every 30min to read from SQLServer and insert records in Postgres DB. Note that our data Volume is low, estimate 800 records per day across 3 tables.

Tables: Quota, LabTransaction, CropToDateGrowers, CropToDateMill

NOTE: We disabled import for LoadDetails this table is not used for this app for now and removed from configuration. This may be enabled later on.

You need to make sure that the database connection information must be configurable through Heroku environment variables, ensure to close all open connections at the end of the process.

Error Handling

If error occured on single record insert/update write a system log entry and continue import for other records.

Assume that data error are possible on some records but shiould be rare cases.

Sample Postgres DB

*This is shared database with limited number of available connections (total 20) it can have outages due to overload. It is recomended to create a seperate free copy of this database for development. SQL for table creatiuon and sample data is provided.

Note SQLServer is single copy shared DB for this challenge.

Postgres DB connection: postgres://mmczoddjtzsyfa:BRFZ95MJig_wai2wDzomGVgQbR@ec2-54-83-53-120.compute-1.amazonaws.com:5432/d2ejsilvqs141e

External Test SQLServer environment connection information:
Database Name:  DB_76059_alma  
Version:  MS SQL 2014    
Database Server:  s11.winhost.com
Database User:  DB_76059_alma_user     
Database Password:  @lm@DB1!       
Connection String:  "Data Source=tcp:s11.winhost.com;Initial Catalog=DB_76059_alma;User ID=DB_76059_alma_user;Password=******;Integrated Security=False;"