I can't answer the question directly - I've been to Vancouver twice, but almost all of my Canadian train watching has been further east in the mountains - so this is general information:
There are basically three freight railway/railroad companies that serve Vancouver, BC - Canadian Pacific Kansas City (CPKC), Canadian National (CN), both coming in from the east along the Fraser River from Hope, BC (CPKC on the north side, CN on the south side), and BNSF from the south over the border with the USA.
This is the general area on OpenRailwayMaps (the blue lines are the SkyTrain transit system) -
https://www.openrailwaymap.org/?sty...4337962531287&lon=-122.94851303100586&zoom=12
CPKC's main freight yard, loco servicing facilities etc. are at Port Coquitlam about 16 miles east of downtown Vancouver. It also has a downtown yard (filled mostly with container traffic, when I was there 11 years ago) just east of the downtown Waterfront commuter rail, metro station & ferry terminal (the west end of yard is alongside it). There are other rail freight facilities stretching eastwards along the harbourside, and the mainline hugs the shoreline most of the way to Coquitlam.
As far as I know, CN's main yard etc. (Thornton Yard) is at Port Mann, a few miles south of Coquitlam on the other side of the Fraser River.
Main traffics are grain, coal, potash, other minerals, general/mixed freight and containers. AFAIK the coal traffic (and maybe some of the other bulk traffic) heads for Delta Port/Roberts Bank southwest of Vancouver for export -
https://www.openrailwaymap.org/?style=standard&lat=49.24337962531287&lon=-122.94851303100586&zoom=12
I suspect that if you want to find somewhere with a decent freight train density and the possibility of seeing both CPKC and CN traffic, then hiring a car and heading towards Hope (where the two mainlines are close to each other, albeit on opposite sides of the river) might be your best bet. Note that CPKC and CN share their routes through the Fraser River canyon, with westbound trains using one route and eastbounds the other (mostly on opposite sides of the river), and as far as I know regain their 'home' lines via the river bridge that links the two at Mission, west of Hope -
https://www.openrailwaymap.org/?style=standard&lat=49.12579196759559&lon=-122.30456829071045&zoom=14
(Note that about 250 miles south is Vancouver, WA in the USA - just north of Portland, OR - so don't get confused between the two if searching for railroad information)
A few pics of Waterfront station and yard 11 years ago (with freight, SkyTrain and WestCoast Express commuter rail):