"When handling large CASE WHEN statements, it is better to create a dimension table or view, ideally sourced from the landed table where the original status column is populated."

Is this code for 'use a lookup table' or am I falling behind on the terminology? The modern term should be 'sum table' or something similar surely.

▲

LikesPwsh 15 hours ago | parent | next [-]

"Dimension table" is the name for lookup tables in a star or snowflake schema.

▲

jacknews 15 hours ago | parent [-]

TIL, Thanks.

'Landed table'? Is that the 'fact table', the one that contains the codes that need to be looked-up?

	▲	tremon 15 hours ago \| parent [-]
		I'm pretty sure the landed table refers to the local copy of the original source. In an ETL* pipeline, the place where source data is stored for further processing is usually called the landing zone. Fact and Dimension tables are outputs of the process, whereas the landing tables are the inputs. * in whatever order they're used

▲

parpfish 15 hours ago | parent | prev [-]

but sometimes large case statements cant be turned into a simple dimension table/lookup table because it's not a simple key-value transformation.

if your case statement is just a series of straighahead "WHEN x=this THEN that", you're very lucky.

the nasty case statements are the ones were the when expression sometimes uses different pieces of data and/or the ordering of the statements is important.