Какой скрипт делает такой эффект?
Конкретный скрипт под эту задачу вы навряд ли найдете, но решение подсказать могу:
Footer находится на заднем плане страницы, то есть с меньшим z-index, чем у основного контента, также у него фиксированное позиционирование, которое привязывает его к низу страницы. Таким образом footer изначально есть на экране, но его не видно, а у контента задается отступ снизу, равный высоте footer (вычисляется через js), из всего этого получается, что при прокрутке страницы до конца основного контента, под ним остается пустое пространство, которое уже не может закрыть footer и он уже становится виден все больше по мере прокрутки.
Копайте в сторону parallax