The observed behavior has nothing to do with the fact a variable is used of not.
If you read carefully the documentation related to cmake_path(APPEND) you will see that the result depends on the semantics of arguments.
In your case, the variable CI_CD_STAGING_SUBDIR is an absolute path, so this implies the discard of previous arguments!
To get the expected results:
# Initialise variables
set(THIS_EXECUTABLE_NAME delta-deployment)
set(CI_CD_PROJECT_ROOT_DIR "/var/my-project/" CACHE STRING "Root project directory")
set(CI_CD_STAGING_SUBDIR "${THIS_EXECUTABLE_NAME}/staging/" CACHE STRING "Staging subdirectory")
# Do some path cleanup and convert to absolute path
cmake_path(SET CI_CD_PROJECT_ROOT_DIR NORMALIZE "${CI_CD_PROJECT_ROOT_DIR}")
cmake_path(ABSOLUTE_PATH CI_CD_PROJECT_ROOT_DIR NORMALIZE)
# Concatenate root dir and subdir (this is where things go awry)
cmake_path(APPEND CI_CD_STAGING_DIR "${CI_CD_PROJECT_ROOT_DIR}" "${CI_CD_STAGING_SUBDIR}")