**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.

airflow/providers/amazon/aws/transfers/s3_to_redshift.py
self,
        *,
        schema: str,
        table: str,
        s3_bucket: str,
        s3_key: str,
        redshift_conn_id: str = 'redshift_default',
        aws_conn_id: str = 'aws_default',
        verify: Optional[Union[bool, str]] = None,
        column_list: Optional[List[str]] = None,
        copy_options: Optional[List] = None,
        autocommit: bool = False,
        truncate_table: bool = False,
        **kwargs,
    ) -> None:
        super().__init__(**kwargs)
        self.schema = schema
        self.table = table
        self.s3_bucket = s3_bucket
        self.s3_key = s3_key
        self.redshift_conn_id = redshift_conn_id
        self.aws_conn_id = aws_conn_id
        self.verify = verify
        self.column_list = column_list
        self.copy_options = copy_options or []
        self.autocommit = autocommit
        self.truncate_table = truncate_table
    def _build_copy_query(self, credentials_block: str, copy_options: str) -> str: