My short answer, is that in the great majority of situation where this $\mathcal{Y}/f^*$ plays a role, it is a mistake to see it as a topos. There are exceptions, but most of the time it is not meant to be a topos, but rather a $\mathcal{X}$-indexed topos.
To clarify the discussion, I will follow Joyal's terminology:
I'm calling "Logos" what we usually call a Grothendieck topos. Morphisms of logos are the continuous left exact functor, i.e. the $f^*$.
Toposes are the object of the opposite category of the category of logos. If $\mathcal{X}$ is a topos I denote by $Sh(\mathcal{X})$ the corresponding logos, which I think of as the category of sheaves of sets over $\mathcal{X}$. If I say "$x \in \mathcal{X}$" I mean that $x$ is a point (or maybe a generalized point) of $\mathcal{X}$.
This is mean't to mimic the picture of the conection between frames and locales (where the frame corresponding to a locales is denoted by $\mathcal{O}(\mathcal{X})$.
Now, The topos corresponding to the logos $Sh(\mathcal{Y})/f^*$ is the following object:
$$ \left(\mathbb{S} \times \mathcal{Y} \right) \coprod_{\mathcal{Y}} \mathcal{X}$$
where $\mathbb{S}$ is the Sierpinski topos, i.e. corresponding to the topological space with one closed point and one open point. (this is easily seen using that colimits of toposes corresponds to limits of logos, which are justs limits of categories)
So you can think of it as a kind of "cone construction" where the Sierpinski space is used as an interval. This object can indeed be interesting, and you can somehow guess that it will be especially interesting for the theory of local morphisms as you mentioned in your post.
But, in my opinion, this topos as simply nothing to do with the idea of working with $\mathcal{Y}$ as "an object over $\mathcal{X}$", i.e. to try to think of the map $\mathcal{Y} \rightarrow \mathcal{X}$ by somehow looking the fiber $\mathcal{Y}_x$ for $x \in \mathcal{X}$ and how it varies when $x$ varies in $\mathcal{X}$. Which is what we want to do in all the case you mentioned:
A proper map is a map whose fiber are compacts, in a "nicely locally uniforme way""
A locally connected map is map whose fiber are locally connected in a nicely locally uniform way.
A separated map... well you can actually also see it as a map whose fiber are separated in a locally uniforme way, but that is not quite what the definition you gave really says. The way I think about this definition is that when you have a class of map stable under composition and pullback then it is nice to consider the class of maps whose diagonals have this property, because you then get for free the lemma that "if $f \circ g$ is proper and $f$ is separated then $g$ is proper".
Let's now look at what happen when you want to work with $\mathcal{Y}$ as an object over $\mathcal{X}$ :
Essentially, instead of looking at $Sh(\mathcal{Y})$ you want to look at something like $Sh(\mathcal{Y}_x)$ for all $x \in \mathcal{X}$, which should give you some kind of family of logoses parametrized by $x \in \mathcal{X}$.
In the same way that the correct notion of "familly of set parametrized by $x \in \mathcal{X}$ is a sheaves over $\mathcal{X}$ the correct notion of such familly of logos is something like "a sheaves of logos". It is not quite just a "sheaves in the category of logos" because the definition of logos involved some infinitary operations (the infinite coproduct/colimits) which you want to replace by $Sh(\mathcal{X})$-indexed colimits/coproducts. So the correct notion is what I would call an "internal logos" and is a special kind of sheaf of logos (It is exactly a sheaf of logos which admits $Sh(\mathcal{X})$-indexed disjoint and universal coproducts).
Also note that these sheaf of logos in particular gives you the type of structure that you asked about in your last question. (they satisfies stronger property though, like Beck Cheavely conditions related to the fact that they have indexed colimits)
Then for technical reason, we tend to look at sheaves of categories rather as indexed categories or fibered categories, that is why you end up with a fibration over $Sh(\mathcal{X})$.
But if you somehow forget that it was mean't to be a "sheaf of logos" over $Sh(\mathcal{X})$ and see the total category of the fibration as a new logos, then you just get a completely different and new object that have very little to do with what I was describing.
I'm finishing with an informal discussion of why the $\mathcal{X}$-indexed logos corresponding to $\mathcal{Y} \rightarrow \mathcal{X}$ should be $Sh(\mathcal{Y})/f^*$. This is not a proof, just some kind of heuristic arguement. The proof is justs that there is a relatively deep theorem saying that the category of $Sh(\mathcal{X})$-indexed logoses is equivalent to the category of toposes over $\mathcal{X}$ and that the equivalence is given by this construction, and is compatible with pullbacks along maps $\mathcal{X'} \rightarrow \mathcal{X}$.
So What should be the "sheaf of logos" (or internal logos) corresponding to $\mathcal{Y} \rightarrow \mathcal{X}$. I need to think about "what should be its section over some étale cover $p: \mathcal{E} \rightarrow \mathcal{X}$".
I want something that, in a continuous way associate to each $e \in \mathcal{E}$ a sheaves over $\mathcal{Y}_p(e)$, i.e. something that continuously in $e \in \mathcal{E}$ and $y \in \mathcal{Y}_{p(e)}$ associate a set. So basically it is a sheaf of set over $\mathcal{Y} \times_{\mathcal{X}} \mathcal{E}$.
Now if $\mathcal{E}$ is the etale space of a sheaves $ E \in Sh(\mathcal{X})$, one has that $Sh(\mathcal{E}) = Sh(\mathcal{X})/E$, and $Sh(\mathcal{Y} \times_{\mathcal{X}} \mathcal{E}) = Sh(\mathcal{Y})/f^* E$
So in the end you do get the fibered category we are talking about.
Best Answer
The category $\mathbf{Cond}$ of condensed sets is equivalent to the category of small sheaves over any of the following three large sites. (For small sheaves, see Mike Shulman's paper Exact completions and small sheaves in TAC.)
It follows that for any Grothendieck topos (and more generally any infinitary-pretopos) $\mathscr{E}$, the category of cocontinuous left exact functors from $\mathbf{Cond}$ to $\mathscr{E}$ is equivalent to the following three categories:
These are all instances of the universal property of the category of small sheaves on a (possibly large) site (for which, see Shulman's paper cited above). Namely, the category of condensed sets is the infinitary-pretopos completion of (i) the pretopos $\mathbf{Comp}$, (ii) the coherent category $\mathbf{ProFin}$, (iii) the weakly lextensive category $\mathbf{Proj}$.
Now, essentially because the category $\mathbf{Cond}$ of condensed sets is "too big" (e.g. it is not locally presentable), cocontinuous functors $\mathbf{Cond} \to \mathscr{E}$ need not have right adjoints. For that reason, in place of geometric morphisms (defined as adjoint pairs with left exact left adjoint) from a Grothendieck topos $\mathscr{E}$ to $\mathbf{Cond}$, one should instead consider cocontinuous left exact functors from $\mathbf{Cond}$ to $\mathscr{E}$. In particular, the "correct" notion of point of $\mathbf{Cond}$ is a cocontinuous left exact functor $\mathbf{Cond} \to \mathbf{Set}$.
Thus, by the above, points of $\mathbf{Cond}$ are equivalent to (i) coherent functors $\mathbf{Comp} \to \mathbf{Set}$, (ii) coherent functors $\mathbf{ProFin} \to \mathbf{Set}$, and (iii) functors $\mathbf{Proj} \to \mathbf{Set}$ that preserve finite coproducts and weak finite limits.
Finally, for $\kappa$ a strong limit cardinal, the category of $\kappa$-condensed sets is equivalent to be the category of sheaves over the sites $\mathbf{Comp}_{\kappa}$, $\mathbf{ProFin}_{\kappa}$, and $\mathbf{Proj}_{\kappa}$, defined to be the full subcategories of the above sites spanned by the spaces of cardinality $< \kappa$, with the induced topologies. As above, it follows that points of the topos of $\kappa$-condensed sets are equivalent to certain kinds of functors (the same kinds as above) from these full subcategories to $\mathbf{Set}$.