DB Design - importing data

[xenou]

Hi, just thinking out loud here.

Over the past year as I work with "outside" data sources that I import/download (from banks, vendors, even other departments) - I've gotten into the habit of making it a two step process:

1) Download to a temporary table. Use the field headers as found in the source data - these can be long and awkward. Also, use a text data type for everything so no original data is lost.

2) From the "Imported" data, append to my "real" table the information I want or need. My real table has more consistent naming, data typing, and may or may not include all the fields that are in the imported data.

3) The payoff is - if something in the import changes, I only need to change my import table for the new field or changed field name or missing field. Maybe, I'll also need change a field name in the append query that appends to the "real table" too. But after that - everything that depends on the "real" table sees the same data just as always. No surprises.

I'm curious, does anyone else find themselves doing this kind of thing? as you might guess, I've found that data sources change too much - and rather than fight with my sensitive tables to get them changed to accept new fields or new field names, I can use the import table to act as the interface.

Or are there other solutions to such problems. I think I started this once when I couldn't get a date to load properly. Spent 3 hours fighting it...then loaded it to a temp table as text and from there to the main table...and kept on doing it...I feel like its a comfort knowing that when I've set up my "main" tables I can count on them being more consistent even when outside sources are being changed (recently had a vendor just pull a space out of field name for no apparent reason - "Site ID" to "SiteID" - hard to understand the fiddling).

Alex.

 

[Ron Coderre]

IMHO...Importing the source data to a staging table is the way to go.
You can build a whole model and not have to change anything but the
transformation from the staging table when the source data or structure
changes.

 

[SydneyGeek]

I always use a staging table (and, if I pull from Excel, txt or csv, I make everything except date fields into Text). That gives me a place to check that the data is landing OK, and more control over the mapping / transformation.

Denis

 

[xenou]

Ah, thanks...this is reassuring then, to know I'm not alone in taking precautionary measures here (I suppose experience can be a wise teacher after all...). Cheers!