The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

ETL :: view or not to view

Can someone point me to best practices of when to use a view instead of going directly against a table when creating extract/transoform/load processes?
code junky
Friday, September 15, 2006
 
 
Avoid using a view.

If you're using a view you are tied to the structure it returns.

If you actually want an abstraction between your ETL process and the base tables, then the view might be good for ya. For example, if someone else is altering the base tables but keeps the view output the same, you're ETL won't break.

Personally I feel that in most cases it just seems to confuse matters. Any issue you have, you have to pull up the definition of the view and then use that to find out what tables you are actually hitting.
D in PHX Send private email
Friday, September 15, 2006
 
 
Thanks.

How is being tied to a view any different than being tied to a set of tables. 

It seems like it would be less overhead in the event that the underlying source tables change to only have to modify the view than to have to modify the source of the ETLs that would be tied to multiple tables.
code junky
Friday, September 15, 2006
 
 
Sorry, I read you post too quick.  I see where you answered that right off...  victim of multitasking...

What about from a security standpoint consideration?

Is there any best practice reference out there? or is this more of a preference thing?
code junky
Friday, September 15, 2006
 
 
Good and bad are in the eyes of the beholders...(:=)

I use views extensively to do the E and T. Preface it with an INSEERT statement, you've got ETL.

Oracle (don't know about SQL) provides great built-in functionality with their materialized views, you can create a whole change-data-capture app with just a DDL statement.
Steve Hirsch Send private email
Friday, September 15, 2006
 
 
I'm am a view proponent myself. It gives better flexibility IMHO, especially for sudden emergency jobs -- for example "For the love of god don't let the system load transaction 20060802XYZ000023432". Instead of hacking open the ETL code to make this temporary change you just redefine the view temporarily.

I have also used them so that I don't have to put up with crap from informatica thinking that 1.5*0.5 != 0.75 etc. due to floating point nonsense -- unlike Informatica, Oracle gets its arithmetic correct.
David Aldridge Send private email
Friday, September 15, 2006
 
 
My beef with views is too often I've seen them used as a crutch to make a terribly bad schema look good.

Like a pseudo "feel good" table.

Once it's been around long enough, most forget that they are using a view and the schema never gets fixed.

Views can be used for security purposes. The typical example is creating a view on an employee table that leaves out the wage column.

I'm not really a fan of that either, as I prefer to not have non-database people in the database.

You want access to the data, ya get a stored proc. :)
D in PHX Send private email
Friday, September 15, 2006
 
 
"Is there any best practice reference out there? or is this more of a preference thing? "

It's really more of a preference or situational type thing.

Alot of it comes down to how structured your environment is and who calls the shots on the db's.
D in PHX Send private email
Friday, September 15, 2006
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz