3

Using stored procedures as part of an ETL process to populate tables in another database, which method would be considered a best practice (if any) in terms of deployment, maintenance, visibility and any other reasons for adopting a push or pull approach.

For example:
Push approach, the stored procedure would be in the Staging database and would push data into a table in the destination database.

Pull approach, the stored procedure would be in the destination database and would pull data from the Staging database.

Both approaches would involve cross-database queries and neither option appears to be perfect, though I prefer to keep all my ETL stored procedures and supporting objects in the Staging database away from others.

What approaches are people generally implementing?

1
  • This will probably get closed as 'Primarily Opinion Based'. Jul 23, 2015 at 10:17

1 Answer 1

1

Although it's all opinion I strongly favour using pull techniques for the following reasons:

  1. I think it's easier to keep track of the dependencies by using a purely pull-approach. In a way it's similar to the whole concept of queries on tables or queries on views on tables. It's all "pull", not "push".

  2. Most DBMSes have better control of transactions etc in the current database vs the remote database when running code in stored procedures.

    For example, in Oracle, you can't truncate a table on the other side of database-link. So you'd require a helper-package on the other side to perform these actions. Much better to have the stored procedure execute the truncate locally and pull the data to populate the table.

  3. I think "pull" is a better fit to the requirements of a reporting data-warehouse / reporting system. These tend to run at scheduled times.

    If you have an architecture that does incremental updates of the warehouse or reports, then "push" is a better fit. I don't like to mix the flows.

  4. Talk to your admins / ops team. If I was the DBA, I would prefer to admin systems that "pull" there data from others. I think it's easier to manage the cascading effects of eg down time in this way.


Just some of the reasons and the ones I can think of right now.

Not the answer you're looking for? Browse other questions tagged or ask your own question.