**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
column_names = "(" + ", ".join(self.column_list) + ")" if self.column_list else ''
        return f"""
                    COPY {self.schema}.{self.table} {column_names}
                    FROM 's3://{self.s3_bucket}/{self.s3_key}'
                    with credentials
                    '{credentials_block}'
                    {copy_options};
        """
    def execute(self, context) -> None:
        postgres_hook = PostgresHook(postgres_conn_id=self.redshift_conn_id)
        s3_hook = S3Hook(aws_conn_id=self.aws_conn_id, verify=self.verify)
        credentials = s3_hook.get_credentials()
        credentials_block = build_credentials_block(credentials)
        copy_options = '\n\t\t\t'.join(self.copy_options)
        copy_statement = self._build_copy_query(credentials_block, copy_options)
        if self.truncate_table:
            truncate_statement = f'TRUNCATE TABLE {self.schema}.{self.table};'
            sql = f"""
            BEGIN;
            {truncate_statement}
            {copy_statement}
            COMMIT
            """
        else:
            sql = copy_statement
        self.log.info('Executing COPY command...')
        postgres_hook.run(sql, self.autocommit)
        self.log.info("COPY command complete...")