DB bulk validation and upload

I am designing an application which will involve bulk upload of records to a Postgres DB (Lets call the schema DB-1). The uploads will be done almost every week. Size could range from a few million to a billion records. The data that is going to be uploaded needs to be validated/cleansed first as it will need to conform to the constaints and format of DB-1. I am thinking of adopting a following approach:

  1. Everytime a new upload needs to be done, a new schema is created (Lets call it DB-2 - A staging place) same as DB-1 but with lenient constraints. This will make sure that the data gets loaded in the DB-2 to start with.
  2. Run a validation process on the data. Initially I was thinking a middleware process but when i realized the amount of data that will be processed, I kind of started thinking about coding a validation+cleansing layer in the DB itself - A set of stored Procs which will run on DB-2, check the data and generate a report with the records which do not conform to the rules (ie constaints present in DB-1, data format etc).
  3. After this, the data which needs to be changed again at the source, Step 1 repeated and if all looks ok, then a SELECT INTO DB-1 from DB-2 would shift the valid data to the final desitnation.

What is your opinion on the above process? Any obvious/hidden issues you see here? Suggestions to make it better most welcome.

Thanks

J

Category:database Views:0 Time:2018-09-12

Related post

  • problems with bulk insertion and "bulk validation" in Rails 2010-06-21

    I'm using ar-extensions' import feature to do bulk import and it's quick, but not as quick as I'd like. Two problems I am seeing from the logs: I still see individual SQL insert statements - why isn't it doing multirow insertion? I have a :validates_

  • What Event to Trigger Javascript Form Field Validation and Formatting? 2008-09-22

    Let me first say, we validate every field on the server side, so this a question about client-side usability. What is the conventional wisdom on exactly when to validate and format html form input fields using javascript? As an example, we have a pho

  • asp.net mvc 1 to many saving post and upload files 2009-05-12

    I'm new in asp.net mvc. I'm using Linq to Sql and trying to do everything loosely coupled. I've two tables: News NewsFiles What I'm trying to do is save a news and upload its files at the same time. How can I create a news in conjunction with his fil

  • using php's libcurl to register user and upload file to server 2010-06-12

    here is a site http://www.lyrkjsw.gov.cn that can let the registered user to upload file (e.g. images or office files) to the site. i want to register user and upload image to this site using libcurl binding with php. only registered user can upload

  • Allowing user to download, edit and upload a database table 2010-07-08

    I'm looking to create an easy way for a user to create a table, and upload it to the server using ftp. On the server side, I'd like to query this table like an SQL-like query. As I'd like the user to edit this in something like OO Calc, or MS Excel,

  • Is it easier to scrape data for a gae app in dev and upload it to prod or should you scrape in prod? 2010-10-26

    I have to run a scraping task to collect data for my App Engine (Java) app. I'm not sure which is best - scrape data in development mode and upload it to prod or scrape it while the app is running in production. Does it make a difference? Are there a

  • Comma's causing a problem using BULK INSERT and a Format File 2011-04-04

    I'm trying to import .CSV files using BULK INSERT on SQL Server and a Format File. I have created the format file and set the correct field terminators for each value. The format file has been correctly validated and I have successfully imported some

  • packaging and uploading visual webparts in SharePoint 2010 2011-04-09

    I m creating a visual webpart in SharePoint 2010 and I want to package it and upload in site other than the one it was validated for. How to do it? Thanks Anoop George Thomas --------------Solutions------------- Assuming your project directory is C:\

  • Why doesn't this regex work Even though it is valid and runs fine on test code 2011-05-30

    I already put several posts out here on SO, related to this but yet this is another one that works on test code and not with regular expression validator control with clientScript Enabled i want the fileUpload to be validated on the clientside at tim

  • Spring MultipartFile validation and conversion 2011-08-23

    I currently have a Spring MVC controller that takes a MultipartFile @RequestMapping(method = RequestMethod.POST) public String doUpload(@RequestParam("file") final MultipartFile file) { /* ... */ } The file contains csv data which will be used, one p

  • Integrating Ext.grid.panel validation and Ext.data.Model.validations 2012-01-05

    I've been learning ExtJS4 after having done quite a bit of dev in ExtJS3. I'm quite intrigued by the new class Ext.data.Models, but I would love to integrate these validations with the validation function in Ext.grid.Panel. Can anyone point me in the

  • sahi script for choosing and uploading a file 2012-02-08

    I'm using Sahi for Test Automation of web application. I have to write a script for sahi for uploading a file. But unfortunately I don't know the way. Can anybody please help me? --------------Solutions------------- File upload can be a complex thing

  • Does ANYONE know a way I can obliterate ALL WIN XP fonts and then download a validated and CLEAN set??? 2012-02-12

    Does ANYONE know a way I can obliterate ALL WIN XP fonts and then download a validated and CLEAN set??? Microsoft Font Validater can only handle a small subset at a time without crashing, and I'd rather nuke the lot and start again. Win XP Pro SP3, O

  • Business Objects, Validation And Exceptions 2008-09-17

    I’ve been reading a few questions and answers regarding exceptions and their use. Seems to be a strong opinion that exceptions should be raised only for exception, unhandled cases. So that lead me to wondering how validation works with business objec

  • Forms based security and uploading asp.net web site 2008-11-04

    I've written a little web site in my effort to learn vb.net and asp.net, fairly happy with it so rented some space and uploaded it, it was written using asp.net express edition 2008 and sql server express .... I've uploaded it and I've found that it

  • i need a C# library about strict HTML validation and filtering 2008-11-25

    i need a C# library about strict HTML validation and filtering --------------Solutions------------- Jeff Atwood posted some code for HTML tag filtering and sanitizing a while back. From your brief description it sounds like something you might want t

  • Drupal wizard form: Validation and previous button 2009-02-21

    In my drupal6 site I have wizard form. I implemented it with FormAPI using form storage and the rebuild property. My form validation is being done with the #required property and with functions in the #element_validate property. It's working fine but

  • Crystal Reports and uploaded PDF Documents 2009-03-27

    I have a system that builds up reports on incedents. This allows the users to fill in multiple web forms and upload PDF documents, jpeg images and tiff images. I have a cystal report that prints of all the form data related to the incedent but cannot

  • Combining form validation and closing the popup window? 2009-04-05

    How can I make sure that the window doesn't close before the form is valid and properly submited? Because now it closes the popup and nobady knows if the form was valid. Because even iff there are errors the form is immediately closed. $(document).re

  • Bulk Copying and Deleting in OneTransaction 2009-06-01

    In C# application I like to copy the table data from one server(SQLServer2000) to another server (SQLServer2005). I like to copy the data in single instance and delete the existing data in the SQL Server 2000 table. I need to do all this(Bulk copying

  • Validation and in Service Layer or Business Objects? 2009-06-10

    Martin Fowler suggests using a service layer as a boundary between the domain model and and "Data Loaders". However, Rockford Lhotka suggests building validation into the business object itself and this is exactly what CSLA.NET does. The benefits of

  • Scan folder on local (user's) PC and upload all files(images) to web server 2009-06-13

    I wish my users could select a directory from their PC and upload all files from this directory, so they could upload whole album(directory) instead of uploading every single file separately. I would like to ask you if this is somehow possible using

  • How can i save and upload the image in C# desktop application 2009-08-01

    How can i save and upload the image in C# desktop application --------------Solutions------------- For a C# desktop application you can use the OpenFileDialog to allow the user to select the image like this: OpenFileDialog dlg = new OpenFileDialog();

  • Record Audio and Upload as Wav or MP3 to server 2009-08-11

    Im not sure if Im asking the right place, but basically Im looking for advice on the best way to: Record Audio through a microphone on a website and Upload the audio as a Wav or MP3 file to the server Has anyone got extensive experience with flash, w

Copyright (C) dskims.com, All Rights Reserved.

processed in 0.217 (s). 11 q(s)