Stored procedure deployment - push or pull into database? 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?
Although it's all opinion I strongly favour using pull techniques for the following reasons:

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 &quot;pull&quot;, not &quot;push&quot;.

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.

I think &quot;pull&quot; 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 &quot;push&quot; is a better fit. I don't like to mix the flows.

Talk to your admins / ops team. If I was the DBA, I would prefer to admin systems that &quot;pull&quot; 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.