What is the significance of a church being "consecrated"?

Consecration establishes a valid place of Catholic divine worship as confirmed by the local ordinary (Bishop)

Can. 1214 By the term church is understood a sacred building designated for divine worship to which the faithful have the right of entry for the exercise, especially the public exercise, of divine worship.

Can. 1215 §1. No church is to be built without the express written consent of the diocesan bishop.

From the Catholic Encyclopedia

Consecration, in general, is an act by which a thing is separated from a common and profane to a sacred use, or by which a person or thing is dedicated to the service and worship of God by prayers, rites, and ceremonies.

Definitions

CONSECRATED GROUND Any place or space that has been liturgically blessed, or where some sacred object such as a church is built or has stood. {snipped burial grounds explanation}.

Significance (truth in advertising)

The laws, rites, and processes that go into consecration¹ of a place of divine worship ensure that when you arrive, for example, at a place called St Paul's Catholic Church, you are in a place that conforms to the standards for valid divine worship, to include the sacraments.

The Code of Canon Law 1214 through 1222 addresses the requirements.

Can. 1217 §1. After construction has been completed properly, a new church is to be dedicated or at least blessed as soon as possible; the laws of the sacred liturgy are to be observed.

§2. Churches, especially cathedrals and parish churches, are to be dedicated by the solemn rite.

The Roman Ritual (Sancta Missa, 1964) describes in detail the rite. (See section 2 at the link, there is a detailed liturgical rite in establishing a church). See also the dedication of a church and altar.

Addressing further elements of your question.

• If a church is consecrated, does that mean that it has been sanctified? (i.e. has it become a holy site?)

  If you were using a generic definition for sanctification, yes.

  The generic meaning of sanctification is "the state of proper functioning." To sanctify someone or something is to set that person or thing apart for the use intended by its designer. Key Concepts. God's usual modus operandi is to sanctify common things for his redemptive purposes, rather than to employ perfect heavenly things

  Using a Catholic definition, sanctification normally applies to people, so it applies to the Church in the sense that the faithful are the mystical body of Christ.

  From the Catholic Encyclopedia entry on sanctity The object of the Passion was the redemption and sanctification of the Church:

  • "Christ also loved the church, and delivered Himself up for it: that he might sanctify it, cleansing it by the laver of water in the word of life" (Ephesians 5:25, 26).

  Again the Church is the body of Christ. He is the head of the mystical body: and supernatural life — the life of Christ Himself — is communicated through the sacraments to all His members.

  For another Catholic definition ...

  SANCTIFICATION Being made holy. The first sanctification takes place at baptism, by which the love of God is infused by the Holy Spirit (Romans 5:5). Newly baptized persons are holy because the Holy Trinity begins to dwell in their souls and they are pleasing to God. The second sanctification is a lifelong process in which a person alredy in the state of grace grows in the possession of grace and in likeness to God by faithfully corresponding with divine inspirations. The third sanctification takes place when a person enters heaven and becomes totally and irrevocably united with God in the beatific vision. (Etym. Latin sanctificare, to make holy.)

  I'd recommend against using the term "sanctification" as a synonym for consecration in this case. The setting aside for the special purpose of divine worship and administering the sacraments is the purpose of the blessings, etc. It is worth noting that of the 38 times that the term sanctification comes up in the Catechism, in all 38 it applies to people not things.

• Does it mean that God dwells inside the church in some special sense (Aside from the Eucharistic presence in the tabernacle)?

  Catholic churches are not a "put God in a box" places. They are places where people (as the Catholic Church sees it, God's people, DE POPULO DEI) come for divine worship of God and to receive the sacraments. The tabernacle in a Catholic church is a special place to store the consecrated host.

• Perhaps it is invalid or illicit to celebrate mass in a church that has not been consecrated, and therefore the consecration is essential?

  Per the Code of Canon Law cited above, it would be illicit to assert that one is at "St Paul's Catholic Church" unless the process described above had been properly completed.

  Perhaps the consecration is optional and is more like a "good luck blessing" which is just a nice-to-have.

  No. See all of the above. It is not "just nice to have," it's Canon Law, and a liturgical blessing is not "a good luck blessing."

• Does anything about the building actually change (on a spiritual level or otherwise)?

  It becomes an officially sanctioned place where Catholic divine worship and liturgy are done, which includes administration of the sacraments.

¹ The current Code of Canon law uses the term dedication, but the process takes on the character of consecration -- equivalent terms.