I am using a Nikon Z6 (short "Z6").
There are many reasons, why I am using the Z6, but I will spare you the details. The Z6 is a very good camera, even though for macro photography and especially for focus stacking there are better ones available.
First of all the full frame sensor (= 24mm x 36mm: Nikon calls it "FX") is not optimum for macro photography. The magnification does not change with the size of the sensor, but with a Micro-Four-Thirds sensor (MFT) I can use a standard lens with a magnification of 1:1 and take a photo of a subject half of the size compared to what I can take with my Z6.
Or with other words, using an MFT camera with 2:1, I can photograph subjects, which require already a 4x magnification with my Z6.
Secondly other manufacturers have a much better focus bracketing implementation. In the mean time even Canon (with the R6) is much faster. And the Z6 shows a black monitor while tacking the photos of the stack: other cameras show the picture, which is currently taken.
The number of stacks is limited to 300 at the Z6 (why that?), and the weirdest thing is that the focus bracketing cannot be started by pressing the trigger.
Pressing the trigger works like <Escape> and makes the camera jumping back to normal mode: why that? So many other things are configurable in the Z6, but focus bracketing obviously has never been in the focus of Nikon engineers.
However, some of the other brands are even much worse in terms of "usability": I tried to use an Olympus camera, and went crazy, since I had to request the user manual so many times...
Therefor I can accept the minor weaknesses of the Z6. I have programmed the buttons and menus of my Z6 in such a way, that
Starting a focus bracketing takes 2 clicks: one on the button, which selects the first item in "my menu", the other one to start the process ("OK" button). That is fast and convenient.
Do not underestimate such convenience in the daily life of a photographer!
The almost free programming of enough buttons is a very strong feature of the Z6!
At the end every photographer will be using the camera that he owns, especially when he starts the macro adventure. That sounds trivial, but means, we have to accept that there are several different ways to get to a result.
Something that works perfectly for person 1, might not work at all for person 2.
Still I want to take the chance and present my thoughts about which criteria is relevant for a "macro camera" from my point of view. I have played with other brands, but I am dealing with these topics out of my personal perspective, only using and knowing the Z6.
This sometimes heated up discussion about the sensor size is an overestimated topic. Every size, whether full frame (or even larger), APS-C or MFT has its own advantages and disadvantages.
A bigger sensor has the tendency to show less noise, which allows to use higher ISO settings (or better: which gives us a better sensitivity). That is physics - and a clear advantage.
On the other hand a full frame camera has the tendency to be bigger and heavier than e.g. its MFT pendant, especially when we are looking into lenses. That is physics, too, and a clear disadvantage.
Smaller sensors offer a better "enlargement" with the same magnification. A subject, which fills the MFT frame at 1:1, requires a 2:1 magnification with the full frame sensor. The resulting higher depth of field and the better effective aperture of the MFT I would see as an advantage here for macro photography, even though it might be seen as disadvantage in other areas of photography.
So I would most likely choose a MFT camera for macro photography, if no other reasons like usability, weather sealing, lens options, build quality, etc. play a role.
So to summarize this topic: Don't care about sensor size to much! More important criteria is, whether the camera concept suits me, i.e. whether or not I feel comfortable to use the camera.
I can only take great photos, if I love to handle my camera.
I have learned to appreciate the advantages of the mirrorless camera concept for macro photography, and I will not want to live without it in the future.
These are my major reasons:
1) First of all the size and weight of the camera. Every gram counts, when using the camera close to the ground, being mounted on rails, hanging under the tripod. The smaller the body, the closer I get down to the ground.
2) At second the advantage of the mirrorless camera is based on its disadvantage:
the electronic view finder.
There is no mental switch required between the view finder and monitor. On both I see the same, and even the switch between view finder and monitor normally is done automatically.
Even more important is the automatic brightness control of the electronic view finder. There are good macro lenses, which do not offer a camera controlled aperture.
The Laowa 25mm/2.8 Ultra Macro 2.5-5:1 is an example for that behavior. I can control the aperture at the lens, but the camera does neither know the magnification nor the aperture setting.
Why this is a problem?
Let us use this lens in nature, freehand. At a magnification of e.g. 3:1, the depth of field is less than 100µm with an open aperture. So it is required to close the aperture, which makes the optical view finder dark then: nothing can be seen anymore.
The electronic view finder compensates this: it produces a bright enough - even though noisy view - which allows me to control my composition.
A DSLR could show a similar view on the monitor, but honestly I have never got a reasonable macro photo, using my camera freehand with the monitor. Freehand I need the view finder!
All this does not mean that a DSLR cannot be used for macro photography. A modern DSLR allows to use the monitor for "live view", and it allows to use the electronic shutter.
The latter one is important for focus stacking (focus bracketing).