Target Action Is Part of Query Design
Overwrite, Append and Add/Update have different operational costs.
Evidence tier describes my hands-on relationship with the content. Official sources describe platform contracts/documentation. Production observations should not automatically be treated as official limits.
The SQL text is only half the Query Activity. The target action changes how the platform writes data and can become the bottleneck as the target grows.
Use Overwrite for rebuildable staging outputs when appropriate.
Treat Add/Update as a potentially expensive existence-check workload at scale.
Use stable primary keys when updates are required.
Consider staging then Data Copy/Import for large Add/Update workloads.
Why Add/Update slows down
Salesforce documents that large Add/Update Query Activities can spend significant time checking whether each target row already exists before writing it. The effect becomes more visible as source and target volumes grow.
Scale pattern
Populate an intermediate DE with Overwrite, then use a Data Copy / Import activity to perform the Add/Update into the durable target when that pattern fits the use case.