Redshift Specturm: Create table with sortkey and distkey from a union all query

Hey there,

I have a create table query with sortkey and distkey defined, the source data comes from s3. The query looks like this

create  table "database_name"."schema_name"."new_table_name"
    diststyle key distkey (id)
    compound sortkey(event_at)
as (
     SELECT
        id
        , event_at
        , ...
    FROM "database_name"."schema_name"."source_table_a"
    WHERE ....
    
    UNION ALL
    
     SELECT
        id
        , event_at
        , ...
    FROM "database_name"."schema_name"."source_table_a"
    WHERE ....
  )

When I run the query, I always receive database error: Table database_name_schema_name_source_table_a_60bb138c76a18 not found. This 60bb138c76a18 suffix is always different when I run the query.

It works when:

• remove sort key and dist key from table creation
• remove union all

Does anyone experience the same issue? Thanks in advacne.