**Apache Airflow version**: 2.0.0


**Environment**:

- **Cloud provider or hardware configuration**: AWS
- **OS** (e.g. from /etc/os-release): Amazon Linux 2


**What happened**:

The TRUNCATE operation has a fine print in Redshift that it is committing the transaction.

See https://docs.aws.amazon.com/redshift/latest/dg/r_TRUNCATE.html

> However, be aware that TRUNCATE commits the transaction in which it is run.

and

> The TRUNCATE command commits the transaction in which it is run; therefore, you can't roll back a TRUNCATE operation, and a TRUNCATE command may commit other operations when it commits itself.



Currently with truncate=True, the operator would generate a statement like:

```sql
BEGIN;
TRUNCATE TABLE schema.table; -- this commits the transaction
--- the table is now empty for any readers until the end of the copy
COPY ....
COMMIT;
```

**What you expected to happen**:

Replacing with a DELETE operation would solve the problem, in a normal database it is not considered a fast operation but with Redshift, a 1B+ rows table is deleted in less than 5 seconds on a 2-node ra3.xlplus. (not counting vacuum or analyze) and a vacuum of the empty table taking less than 3 minutes.

```sql
BEGIN;
DELETE FROM schema.table;
COPY ....
COMMIT;
```

It should be mentioned in the documentation that a delete is done for the reason above and that vacuum and analyze operations are left to manage.


**How often does this problem occur? Once? Every time etc?**
Always.

tests/providers/amazon/aws/transfers/test_s3_to_redshift.py
#
#
#
#
import unittest
from unittest import mock
from boto3.session import Session
from airflow.providers.amazon.aws.transfers.s3_to_redshift import S3ToRedshiftOperator
from tests.test_utils.asserts import assert_equal_ignore_multiple_spaces
class TestS3ToRedshiftTransfer(unittest.TestCase):
    @mock.patch("boto3.session.Session")
    @mock.patch("airflow.providers.postgres.hooks.postgres.PostgresHook.run")
    def test_execute(self, mock_run, mock_session):
        access_key = "aws_access_key_id"
        secret_key = "aws_secret_access_key"
        mock_session.return_value = Session(access_key, secret_key)
        mock_session.return_value.access_key = access_key
        mock_session.return_value.secret_key = secret_key
        mock_session.return_value.token = None
        schema = "schema"
        table = "table"
        s3_bucket = "bucket"
        s3_key = "key"
        copy_options = ""
        op = S3ToRedshiftOperator(
            schema=schema,
            table=table,
            s3_bucket=s3_bucket,
            s3_key=s3_key,