You will get a "Dark" Explosion
Non-explosive artillery can still have a significant explosive effect. That said, it will not make the sort of "fireball" explosion you get from a chemical warhead. So, you'll still get a significant explosive shockwave, a cloud of dust and debris, etc. But the heat and light from the impact will be significantly less than from a chemical explosive weapon of similar yield.
To understand the difference you have to realize that a kinetic weapon has to transfer energy into a target through physical interactions whereas an explosive or nuclear weapon does not. While a 5e10 Juele explosive releases all of its energy (and light) at the surface, most of the enegry released by a kinetic weapon will happen subsurface. What ever material a kinetic weapon hits only has a limited binding strength to stop the projectile before it becomes displaced. Most kinds of dirt, rock, sand, and soil have low enough of a binding energy that they become displaced before being heated up enough to visibly glow. While the metal rod will have a much higher binding energy and reach a very bright glow before loosing its integrity, nearly all of this ablation will happen underground such the the light from it will be concealed giving you only a minor flash at the moment of impact.
Also, you can not compare a rod from god to something like the Chelyabinsk meteor because a meteor can only airburst like that because it is a heterogeneous mixture of elements where internal element vaporize until they over pressure the yielding strength of the tougher elements. A pure element like tungsten will always ablate from the outside in during re-entry. Since tungsten has a very high melting point, and the rod has a very narrow cross section, it may in fact heat up enough to glow a good bit by the time it reaches the Earth, but it will still have plenty of integrity to spare when it hits which is the whole point of using tungsten to begin with.
The exception to this rule is when you hit somethin with more binding strength. When you strike steel for example, it will resist the kinetic impactor more before being displaced allowing it to reach temperatures needed to glow; so, you can still get a bright flash that is not concealed by non-glowing debris as shown by this tank being hit by a 30mm autocannon where strikes against the armor create bright flashes, but adjacent strikes against the ground kick up dark plumes of dirt. Striking a tank with a rod from god though will probably still kick up more dust than glowing metal because it will massively over-pernitrate the tank, but perhaps a large steel bunker or warship could still have a significant flash.