'Order By' statement isn't working in dbt model - snowflake

Question

I am quite new working with dbt, and I am working in a really simple project.

I have this presentation model and in the end when I do the ORDER BY "Date" the table doesn't get ordered by date. Bellow follows my code:

WITH agg_tbl_union AS(

SELECT *
FROM {{ ref('dim_agg_mp_w_filter_1') }}
UNION
SELECT *
FROM {{ ref('dim_agg_mp_w_filter_2') }}
)  

select 
    "Manufacturer",
    "TTV",
    "ATV",
    "Total",
    "No. of Buyers",
    "Date" 
from agg_tbl_union
ORDER BY "Date" 

The results are OK nothing wrong on there, and if I just do

select * from table order by "Date" in Snowflake it does what I want it to do.

I basically just wanted for the table to be straight away ordered in Snowflake. Am I missing something here?

Answer 1

Tables by definition are unordered sets and ORDER BY clause used during table creation does not guarantee sorting of future queries.

CREATE TABLE

If you want the table to be created with rows in a specific order, then use an ORDER BY sub-clause in the SELECT clause of the CTAS. Specifying CLUSTER BY does not cluster the data at the time that the table is created; instead, CLUSTER BY relies on automatic clustering to recluster the data over time.

The ORDER BY sub-clause in a CREATE TABLE statement does not affect the order of the rows returned by future SELECT statements on that table. To specify the order of rows in future SELECT statements, use an ORDER BY sub-clause in those statements.