{"id":11626,"date":"2022-09-21T13:36:27","date_gmt":"2022-09-21T13:36:27","guid":{"rendered":"https:\/\/predictly.se\/using-circleci-to-fix-ci-cd-in-dataform-part-2-2\/"},"modified":"2024-05-08T11:46:39","modified_gmt":"2024-05-08T11:46:39","slug":"using-circleci-to-fix-ci-cd-in-dataform-part-2-2","status":"publish","type":"post","link":"https:\/\/predictly.se\/en\/using-circleci-to-fix-ci-cd-in-dataform-part-2-2\/","title":{"rendered":"Using CircleCI to fix CI\/CD in Dataform – Part 2\/2"},"content":{"rendered":"

In my previous article<\/a> I tried to sketch a CI\/CD solution for Dataform using CircleCI in a compact way; I had to leave out some details and obstacles that usually surface along the way. Today, I’d like to explore that a little further by looking at a real deal breaker: how to manage changes in the datamodel.<\/p>\n

This has always been a delicate question. Most of the cases, there are more or less advanced SQL scripts that are supposed to do the job, some teams have discovered the power of Liquibase<\/a> – which unfortunately doesn’t support BigQuery at this point – and some are still scratching their heads.<\/p>\n

One of the core objectives of CI\/CD is to remove the human factor from releasing and deploying. There shouldn’t be any obscure script that needs to be run and can be forgotten – or is run but against the wrong database or schema. We all have seen it happen. Instead, this should be done in a controlled fashion, reproducible, automated.<\/p>\n

Looking at a typical DWH implemented in Dataform: which objects and potential changes do we have? Which are handled automatically and which need some attention? To get a clearer picture I put it all in a table:<\/p>\n

\"Table\"<\/p>\n

As we can see, problems exist with both incremental and partitioned tables. There is a simple solution, though: the “Run with full refresh” option. This means that Dataform will force a complete recreation of all tables and views in the run, building them from scratch – and it actually solves the problems seen above completely:<\/p>\n

\"Settings\"<\/p>\n

In order to use the full refresh option in CircleCI, we need to get the Dataform command line interface (CLI) running within our Workspace which is the container in which all commands and jobs of the Workflow are run. For that we need to install it locally, get some configuration in place and finally execute the run. Here’s how this might look like:<\/p>\n

\nexecutor: node_df\nsteps:\n  - checkout\n  - attach_workspace:\n  at: .\n  - run:\n  name: run whole project with full refresh\n  command: |\n  LAST_COMMIT_MSG=$(git --no-pager log -n1 --no-merges $CIRCLE_SHA1 --pretty=format:%s)\n  # perform a full refresh if requested in commit-message:\n  if [[ $LAST_COMMIT_MSG == *\"[full refresh]\"* ]]]; then\n  echo \"Found request to trigger a full refresh, executing... Message: $LAST_COMMIT_MSG\"\n  sed -i 's\/[your_project_id]\/'${GOOGLE_PROJECT_ID}'\/g' dataform.json\n  sudo npm i -g @dataform\/cli\n  echo $DF_CREDENTIALS >.df-credentials.json\n  mkdir -p ~\/.dataform && echo '{\n  \"allowAnonymousAnalytics\": false,\n  \"anonymousUserId\": \"'${DF_ANONYMOUS_USERID}'\"\n  }' > ~\/.dataform\/settings.json\n  data form install\n  dataform run --full-refresh\n  fi\n<\/pre>\n
Let’s go through some of the most interesting – or least obvious – places in that job.<\/p>\n
On line 13, [your_project_id]<\/em>  in the configuration file dataform.json<\/em> is being replaced with a variable called GOOGLE_PROJECT_ID<\/em>. This will be relevant if your environments are in different projects. For example, your test environment might be on elegant-elephant-12345<\/em> while production is on precious-panda-98765<\/em>. Depending on where you deploy, you’d have the current project ID in the variable making the job more flexible. After that we actually install the Dataform CLI using npm<\/a>.<\/p>\n
Now we’ve come to the fiddly part, the one I detest: getting the configuration done. I can point you in the direction here, introduce you to some basics, but I’m afraid you’ll have to get the details right yourself for your own setup. Trial and error…<\/p>\n
First off is the credentials file required by Dataform. Normally, you’d run the init-creds<\/em> command but as that requires user input the easiest way is to do this once manually and just copy the contents of the resulting file. Store that in a secret which you then write back into a file just as seen on line 15.<\/p>\n
Then there’s the question of some settings to make life with the CLI easier. You’ll want to switch off Anonymous Analytics for these runs in order to avoid the CLI to prompt a question about it and also use an anonymous user ID. This can be hardcoded and written to a settings file as seen on lines 16-19.<\/p>\n
After that you’re set! Two more simple commands will install and run the whole project with the full refresh option. So, time for a short recap on what we’ve done so far:<\/p>\n