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?

closed as primarily opinion-based by Mark Sinkinson, Paul White, RLF, RolandoMySQLDBA, Vérace Jul 23 '15 at 19:41

Many good questions generate some degree of opinion based on expert experience, but answers to this question will tend to be almost entirely based on opinions, rather than facts, references, or specific expertise. If this question can be reworded to fit the rules in the help center, please edit the question.

  • This will probably get closed as 'Primarily Opinion Based'. – Mark Sinkinson Jul 23 '15 at 10:17

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.