Recommended Shapes or Notations to use for drawing ETL-style flow charts

• webrunner

  SSC-Dedicated

  Points: 31711

  More actions

  March 3, 2015 at 2:38 pm

  #315160

  Hello experts,

  I'm having difficulty finding a standardized way to draw flowcharts (as in Visio) to represent the steps involved in an ETL-type data update process I'm working on. For example, I have:

  1. a view based on a query that pulls source data from several different databases;

  2. that view is used in a stored procedure that uses the MERGE statement to find the differences;

  3. that MERGE is used in a stored procedure to log the changes and write the final updates to the ETL destination database;

  4. finally, that destination data is used in a stored procedure that a developer is using to feed an API process to synch data elsewhere.

  In Visio 2010, I could find a database shape easily enough, but for queries or stored procedures, I wasn't sure. Would a script shape do the trick? Or maybe a flowchart process or "rounded process" shape? The choices seem so inelegant, probably because I'm trying to shoehorn them into something they are not for.

  Does anyone have any recommendations? Or am I mixing metaphors, so to speak, and should use some other tool or notation, such as data flow diagrams or business process notation? I guess I could also use SQL Server Data Tools which has shapes along the lines of what I need. But I figure there must be something else out there for just documenting these things.

  Or should I just resort to pencil and paper?

  Thanks for any help!

  - webrunner

  -------------------
  A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
  Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html