The effect is BG masking via sprite priority logic. You put a low priority "layering" sprite behind the BG layer, but this sprite is a higher priority than the 'object' it is masking/clipping. Usually, the 'masking' sprite is of two colors; one normal color and one 'transparency color" (color #0 of every sprite). The effect is that anywhere there is a solid pixel in the masking sprite, and another sprite appears in the same area, the BG pixels will show in place of either of these two sprites. The masking sprite is almost always invisible. Ys I & II uses all kinds of masking sprites to allow BG areas to overlap parts of sprites, because the PCE doesn't have priority levels on a per tile. You just rarely see it as a 'mesh', but there are other games that do this for a 'transparency' effect (more so that you can see the object than anything else).
That SGX demo is doing something different