I think what you're referring to is the 'Custom Error Page' for URL Error conditions which is defined in a file called:
url_error.html within the Zip file that is uploaded to Business Manager and then stored on the POD's web adapter server.
There is a significant different between that 404 page and the one described by empiric in his answer. The condition when
url_error would be shown is a URL that cannot be routed to a Site in SFCC. The Web Adapter is unable to determine which site to route that URL to or the URL is malformed in some way resulting in a failure to determine which Site it is associated with.
ErrorNotFound controller method controls the rendered content when the Web Adapter was able to route the request to a Site, but the page didn't exist in that site. When you return a 404 response code from this controller, you will not be able to include any remote includes in that page's content. Furthermore you cannot use Slots either as they are also remote includes. To get around this we often return a 410 status code instead which allows us to perform remote includes.