Is it better to have a CASE expression or a JOIN with a dimensional table?

Question

I have a fact table which has a timestamp and I want to express the day of the week. I could do something like this:

SELECT CASE WHEN date_part(timestamp,'week') = 0 then 'Monday'
WHEN date_part(timestamp,'week') = 1 then 'Tuestday'...
FROM fact_table

Or I could create a dimensional table like the following:

day_number day_name
0          Monday
1          Tuesday

and then make a JOIN with the fact table:

SELECT day_name
FROM fact_table
LEFT JOIN dm_days
on day_number = date_part(timestamp, 'week')

Which one is better?

What if instead of a date is some indicator (for instance where the traffic comes from: newsletter, SEO, affiliates...) that does not require any function (like date_part) to be obtained? For example, would this be better:

SELECT CASE id_channel WHEN 0 then 'SEO'
WHEN 1 then 'Newsletter'...
FROM fact_table

Than this? SELECT channel_name FROM fact_table LEFT JOIN dm_channels on channel_id = id_channel

Answer 1

For this problem I would use the date formatting functions (after setting lc_time to the appropriate locale)

SELECT to_char(timestamp, 'Day') 
FROM fact_table

In general the table form is, to me, cleaner (when there is no suitable function)

Perhaps with an in-line table if there is a good reason to not have a permanent table.

SELECT day_name
FROM fact_table
LEFT JOIN (VALUES (1,'Monday'),(2,'Tuesday'),(3,'Wednesday')) AS days(day_number ,day_name ) 
on day_number = date_part('dow',timestamp)::int

Answer 2

Neither, for MySQL:

SELECT ts, DATE_FORMAT(ts, '%W') AS W,
           DATE_FORMAT(ts, '%a') AS a   FROM ...
+---------------------+--------+------+
| ts                  | W      | a    |
+---------------------+--------+------+
| 2020-01-26 10:53:05 | Sunday | Sun  |
+---------------------+--------+------+

That is, just use an expression.