TL;DR
Any function with negligible values at a distance greater than some "pre-scribed", but arbitrary (dictated by the problem and its formulation) distance can act as a source function for a monopole source.
The "prerequisites" are for the function to be negligible outside some confined domain and its volume integral be equal to one.
Introduction
The equation that describes the acoustic pressure field of a monopole (point) source is
$$ p \left( r , t \right) = \frac{j \omega \rho Q}{4 \pi r} e^{j \left(\omega t - k r \right)} \tag{1} \label{point source pressure} $$
with $r$ denoting the distance from the source, $t$ the time variable, $j$ the imaginary unit for which $j^{2} = -1$ is true, $\omega$ the radial frequency for which $\omega = 2 \pi f$ is true and $f$ signifies the temporal frequency, $\rho$ the (unperturbed) medium density ($\approx 1.21 ~ kg/m^{3}$ for air), $k$ the wavenumber for which $k = \frac{\omega}{c} = \frac{2 \pi}{\lambda}$ where $c$ is the speed of wave propagation ($\approx 343 ~ m/s$ for air in "room" atmospheric conditions) and $\lambda$ the wavelength. $Q$ is the source "strength", which actually is the volume of fluid displaced by the source and for a pulsating sphere it equals the product of surface area $4 \pi \alpha^{2}$, with $\alpha$ the radius of the sphere and surface velocity $U_{0}$, giving
$$ Q = 4 \pi \alpha^{2} U_{0} \tag{2} \label{source strength} $$
Please note that the solution is expressed in spherical coordinates due to the spherical symmetry of the problem (I assume you already know that).
(Possible) explanation
1. Preliminaries
The following explanation follows that presented in "Acoustics - An Introduction to its Physical Principles and Applications (3rd Edition) by Allan D. Pierce, Section 4.3.1 - Concept of a Point Source. Note that the explanation in the textbook is rather involved and one would require knowledge of vector calculus to completely comprehend the derivations. I will do my best (not sure how successful I'll be) to simplify the concepts here. For that I'd like to apologise in advance for any abuse of the terms and/or inconsistencies of mathematical exactness.
For simplicity we'll express the solution of equation \eqref{point source pressure} like
$$ p = A \frac{e^{j k r}}{r} \tag{3} \label{pierce point source pressure} $$
where $A$ denotes the "source amplitude" and the explicit dependence on distance $r$ and time $t$ have been dropped for convenience.
The point source is the limiting case for which $\alpha \rightarrow 0$ and at the same time $U_{0}$ grows larger so that $Q = \frac{- 4 \pi A}{j \omega r} \sim constant$. Replacing equation \eqref{source strength} to the previous equation for $Q$ implies that $A \approx -j \omega \rho \alpha^{2} U_{0}$ and this quantity must be also constant.
2. Mathematical formulation
For linear acoustics, equation \eqref{pierce point source pressure} must satisfy the Helmholtz equation shown below for completeness
$$ \left( \nabla^{2} + k^{2} \right) p \left(x, y, z, \omega \right) = f \left( x, y, z, \omega \right) \tag{4} \label{Helmholtz} $$
Equation \eqref{Helmholtz} must be satisfied everywhere except for $x = y = z = 0$, which is the position of the source. This case is a limiting one of some particular solution to equation \eqref{Helmholtz} for some (dummy) variable $\epsilon \rightarrow 0$ and the solution $p_{\epsilon}$ satisfies the (monopole) Helmholtz equation of the form
$$ \left( \nabla^{2} + k^{2} \right) p_{\epsilon} = - 4 \pi A \Delta_{\epsilon} \left( r \right) \tag{5} \label{limiting Helmholtz} $$
Please note that $\Delta_{\epsilon}$ has an explicit dependence on distance $r$. Now, the problem boils down to finding an appropriate $\Delta_{\epsilon} \left( r \right)$ that satisfies both equation \eqref{pierce point source pressure} and equation \eqref{limiting Helmholtz} such that
$$ p = \frac{A}{r} e^{j k r} \tag{6} \label{possible solution} $$
for finite distances $r$.
Skipping some "technicalities" (actually mathematical details) one reaches the conclusion that an appropriate function for $\Delta_{\epsilon}$ is any function confined to a region for which $r < \epsilon$ and is negligible for some value $r$ away from $\epsilon$ (in the aforementioned textbook the value is denoted as $\approx 10 \epsilon$ but this is an arbitrary choice since $\epsilon \rightarrow 0$ anyway) and its volume integral (integral over the region it occupies) is unity, so that
$$ \iiint \Delta_{\epsilon} dV = 1 $$
In the strict mathematical limiting case a very good candidate (quite often used in analytical formulations) is the Dirac delta function
$$ \delta \left( x, y, z \right) = \begin{cases} \infty &, x = y = z = 0 \\ 0 &, else \end{cases} \tag{7} \label{delta function} $$
3. Practical solution
As stated in the above discussion (and in the cited textbook) any function with negligible values far from the source position whose volume integral is unity is a good candidate for $\Delta_{\epsilon}$. One example of such a function is shown in the following image taken from the mentioned textbook.
where $R = r$ in our discussion. As you can see in the graph presented above, the value of the function at the origin (position of the source) is finite. This is due to the fact that the extent of the function is also finite and non-zero, making its volume integral equal to unity and satisfying the "prerequisites" set for $\Delta_{\epsilon}$ in order to satisfy equation \eqref{limiting Helmholtz}.
This depicts a more "practical" source function than a delta function. Such functions have greater value than just "getting rid of" the pressure singularity at the source position. They allow for numerical formulations of the wave equation in finite precision (pretty much all computers) with a great example being the Finite-Difference Time-Domain (FDTD) method.
Additional remarks
Please note that close to real sources with finite dimensions and complex vibrational patterns on their surface the phenomena that take place are way more complex than this "simplified" (kinda macroscopic) formulation of source.
If one wants to look further into radiation may have to delve into the evanescent waves which constitute solutions to the wave equation where the wavenumber is imaginary (and are oscillations rapidly decaying with distance from the source).
Furthermore, as stated above, in the limiting case of a point source where $\alpha \rightarrow 0$, $U_{0}$ has to be significantly large and at this point you are way outside the regime of linear acoustics and many non-linear effects take place.
Extra info on maximum pressure amplitude
I would also like to note that the maximum "allowed" amplitude of about $1 ~ atm$ (or whatever the unperturbed atmospheric pressure is) is indeed the maximum attainable value but only for bilateral functions such as (co)sinewaves. It is easy and quite common to witness asymmetric (with respect to the x-axis) functions whose pressure amplitude can well exceed that of the current unperturbed atmospheric pressure value. Just to mention some common ones, explosions and stun grenade peak pressure amplitude values can reach and even exceed this maximum value.
This can happen on the positive half of the pressure. In the negative side this is unattainable since you cannot really have a pressure value lower than $0 ~ Pa$.
