There are several ways the achieve your goal. One would be to use the filter option to accept only the first two lines. filter uses the ifthen package syntax:
filter={\value{csvrow}<2}
The complete code is:
\documentclass{article}
\usepackage{csvsimple,filecontents}
\begin{filecontents*}{scientists.csv}
name,surname,age
Albert,Einstein,133
Marie,Curie,145
Thomas,Edison,165
\end{filecontents*}
\begin{document}
\csvreader[tabular=|l|l|c|,
table head=\hline & Name & Age\\\hline,
late after line=\\\hline,
filter={\value{csvrow}<2},
]%
{scientists.csv}{name=\name,surname=\surname,age=\age}%
{\thecsvrow & \surname~\name & \age }%
\end{document}

Alternatively, the before filter could be used to implement an own filter option. Here, \ifnum can be used:
\documentclass{article}
\usepackage{csvsimple,filecontents}
\begin{filecontents*}{scientists.csv}
name,surname,age
Albert,Einstein,133
Marie,Curie,145
Thomas,Edison,165
\end{filecontents*}
\begin{document}
\csvreader[tabular=|l|l|c|,
table head=\hline & Name & Age\\\hline,
late after line=\\\hline,
before filter=\ifnum\thecsvrow<2\relax\csvfilteraccept\else\csvfilterreject\fi,
]%
{scientists.csv}{name=\name,surname=\surname,age=\age}%
{\thecsvrow & \surname~\name & \age }%
\end{document}
Your attempt had not the expected result, because between \ifnum ... \else ... \fi you cannot have column separators &. This could be fixed to have the selection at this place, but you would get empty tabular lines instead of no lines.