A precise understanding of the evolution of the universe and its constituents strongly depends on observations across both large and small cosmic scales. Over the past few decades, large cosmological surveys have expanded the number of galaxy redshifts available to probe cosmic expansion and dark matter (DM) by an order of magnitude every ten years. To sustain this growth into the 2030s and beyond, the next generation of surveys must look farther back in time while maximizing the discovery potential of the faintest objects within the Milky Way. Achieving these goals requires advancements in observational technology to improve sensitivity. Ultra-low readout noise detectors are fundamental to this effort, enabling greater sensitivity for high-density and high-redshift spectroscopic surveys to place tighter constraints on dark energy and DM, the two dominant components in the concordance model of cosmology. In this thesis, I present the development and first on-sky demonstration of a novel detector technology, the Skipper CCD, integrated into a prototype focal plane array for the SOAR Telescope Integral Field Spectrograph (SIFS). These observations, carefully optimized to reduce readout times, achieved detector readout noise in the sub-electron (≲ 1 e- rms/pixel) and photon-counting (≲ 0.22 e- rms/pixel) regimes. The SIFS-Skipper instrument demonstrated significant signal-to-noise (S/N) improvements for signal-starved astronomical targets, such as high-redshift quasars and emission-line galaxies. Additionally, I present radial velocity measurements of a candidate member star in the ultra-faint dwarf (UFD) galaxy Boötes II, at its periphery. These measurements are of particular interest for mapping the DM distribution in UFDs and for unraveling their formation and evolutionary history. Furthermore, I explore future advancements in Skipper CCD technology, focusing on readout architectures designed to drastically reduce readout times while maintaining ultra-low noise performance. These projections highlight the potential efficiency gains in observation time and S/N that can be achieved with these novel detectors for future large cosmological surveys.