ofcourse, try the below

create table test_new as select * from test_old distribute on random;

DIstribute on random is not a preferred way for Distribution. It will cause performance degradations unless the table is a small one

use the 'distribute on' clause as the last part of the CTAS - some reminders:

if you use a key other than the source table, you will initiate a broadcast from the data's current SPU to the new one. It's transparent but for larger results is more costly than maintaining the distribution (you sacrifice the co-located write)

If you are bridging from one distribution to another so that your next operation gets a co-located join/read, be sure to check the distribution of the result. If the result is highly skewed, you might want to consider doing the CTAS with some additional sorting logic to manufacture a zone map to mitigate this condition. The co-located join alone is not as efficient if one of the joining tables is highly skewed, but if using a zone map, skewing is mitigated.

As your CTAS table grows larger, it will become an issue. This is especially true for CTAS tables created in a BI reporting product. The temporary nature of the table is not the issue. If the table is poorly distributed then any join-operation consuming it will run less effciently. For smaller tables this may not seem like a big deal, but smaller tables invariably grow. It is best to apply a practice at the outset so there is no worry later, and it doesn't really cost anything to apply it. When performance starts to degrade because it was not applied earlier then for goodness sake apply it now. It is easy to apply later, just not so easy to diagnose later.

But again, the temporary nature of the CTAS result is not the issue. We want all ELT or BI reportng queries to run at top speed, so optimizing them with these simple tactics keeps them scalable with no mysteries.

Is there a way to do a CTAS keeping the distribution columns of the source table?

Use CTAS carefully. I personally prefer explicit definition.

CTAS drops the "Default Clause" in a table. This caused a lot of grief to me.

---

Ex:

scdwtst(ndwdm)=> create table tab_xyz (name varchar(30), id integer default 100);

CREATE TABLE

Elapsed time: 0m4.971s

scdwtst(ndwdm)=> \d tab_xyz

                       Table "tab_xyz"

Attribute |         Type          | Modifier | Default Value

-----------+-----------------------+----------+---------------

name      | character varying(30) |          |

id        | integer               |          | 100

Distributed on random: (round-robin)

scdwtst(ndwdm)=> create table ctas_tab_xyz as select * from tab_xyz;

INSERT 0 0

Elapsed time: 0m2.004s

scdwtst(ndwdm)=> \d ctas_tab_xyz

                     Table "ctas_tab_xyz"

Attribute |         Type          | Modifier | Default Value

-----------+-----------------------+----------+---------------

name      | character varying(30) |          |

id        | integer               |          |

Distributed on random: (round-robin)

-----