What is the best way to include a large amount of configuration data in a SQL project?
Your intuition is right here - it's not common to have a 600 MB SQL script as part of your SSDT project in source control.

Especially in the world of distributed version control systems, like git, you're talking about potentially numerous copies of that file, on various machines, and tracking the history of it in detail.

However, there is no "built-in" way of dealing with static data other than post deploy scripts, as you've noticed:

SQL SSDT Database Projects: Source Control Lookup Data

One solution would be to create a one-time-use SSDT project, and all it has is your configuration data scripts.  Then you can produce a dacpac file from that (which is really just a compressed folder), and "reference" that dacpac in your main project.

The advantage of this approach is that you get a compressed deploy script, but it's in a format that easily fits with your existing deployment pipeline (rather than just throwing the script file into a .zip archive).

Here's what that looks like.

I have an SSDT project with the AdventureWorks sample database schema in it.  The resulting dacpac is 80 KB:



I'll create a second project, and all it contains is a Post Deploy script, which references my static config data script:




Post_Person.ContactType.sql is a 10 MB script file with a bunch of insert statements
it's configured as "Build Action" = "None" and "Copy to Output Directory" = "Do Not Copy"


This produces a dacpac file that is 34 KB.

Based on that compression ratio, which depends a lot on your data, your 600 MB could get as small as 2 MB (there is a lot of repetition in a script like this, which lends itself to good compression).

Then you can add the dacpac as a "Database Reference" in your main SSDT project:



At this point, you could discard the "InitScripts" project.  Or you could store it in a separate standalone repository, that less people will need, in case you ever need to update and regenerate the dacpac.

Note that, in the end, your build / deploy process will need to deploy both dacpac files - your "main" one, and the "InitScripts" one only on initial setup (or you could deploy it every time, and have the init scripts check for existing data before taking any action).