Connect dbt projects on your Mac with Snowflake

There are several ways to connect a dbt project on your Mac to Snowflake. In this blog, we use RSA encryption to make a secure connection. That's not the easiest way at first, but then you don't really have to do anything. Your computer will connect to Snowflake by itself without you having to type in another password.

Dbt makes data transformations in Snowflake more manageable, robust and effective. Moreover, by combining it with git, collaboration in (large) teams becomes very possible. There is a cloud version of dbt (dbt Cloud), but dbt Core is open source and can also be installed directly on your computer. We will focus on local dbt Core in this blog.

Deze blog is gericht op dbt projecten die op een Mac staan. We gaan er verder vanuit dat je comfortabel bent met de Terminal app en openssl geïnstalleerd is. Let op: er worden een aantal technieken besproken, maar dit is geen beveiligingsadvies. Laat je altijd goed adviseren als het over de beveiliging van je systeem gaat.

Overview commands

To give an overview in advance, these are roughly the commands we will use in the Terminal:

• openssl rand -base64 24 | pbcopy
• openssl genpkey -des3 -algorithm RSA -out rsa.p8
• openssl pkey -in rsa.p8 -pubout | pbcopy
• vim ~/.dbt/profiles.yml (any editor is fine, of course)
• ALTER USER ⌞username⌝ SET RSA_PUBLIC_KEY = '⌞public key⌝';
• dbt debug

Creating a dbt Profile

To connect to Snowflake, there must be a dbt profile present on your computer. These profiles are in a profiles.yml-file. It can often be found in the home directory ~ under the (invisible) directory .dbt. If this file and directory do not already exist, create them (for example, with mkdir .dbt and touch .dbt/profiles.yml).

This profiles.yml-file we need to edit to create a connection between a dbt project on the Mac and Snowflake. Suggest the following:

• Your Snowflake organization hot VOOR and the account name of your account is BEELD, making the account identifier if so VOOR-BEELD is.
• Your Snowflake USER has the username ESEMPIO.
• The Snowflake ROLE you want to use is SYSADMIN And you can also use that ROLE.
• The Snowflake WAREHOUSE you want to use is called MAGAZIJN and SYSADMIN has the right PRIVILEGES to do so.
• The Snowflake DATABASE is called ONTWIKKEL_DB and SYSADMIN has all the necessary permissions to do so. Thereby, it is important in this example that SYSADMIN can also create a SCHEMA in that DATABASE. We then call this SCHEMA DBT_ESEMPIO in the profiles.yml. Then dbt will automatically create this SCHEMA for us.

In fact, we then need to fill in the following fields:

default:
  target: dev
  outputs:
    dev:
      type:      snowflake
      account:   voor-beeld
      user:      esempio
      role:      sysadmin
      warehouse: magazijn     
      database:  ontwikkel_db
      schema:    dbt_esempio
      
      private_key_path:       ⌞path to private key⌝ 
      private_key_passphrase: ⌞passphrase⌝

Above we see that only the YAML keys private_key_path and private_key_passphrase have not yet been completed. We are going to work on that now.

Create private key

First, we make a passphrase to the file, where our RSA private key in comes, to encrypt. Then we create the private key. From that we extract the public key (the private key file also contains the public key). We add this public key to our Snowflake USER. Finally, in the profiles.yml the path toward the private key. The private_key_passphrase is literally the passphrase we initially generated.

OpenSSL

For most of the steps, we use the open-source software library OpenSSL. Open the Terminal.

Generate a passphrase

Run the following command:

openssl rand -base64 24 | pbcopy

With this we create a pseudo-random passphrase of a certain length. With | pbcopy we then ensure that this passphrase enters our Clipboard and we can paste (paste with ⌘v). Then paste this passphrase into the profiles.yml after private_key_passphrase: . If the random passphrase t+gsjivVXODDzhujYLW/ZdjVuYrIz4n5 is, it looks like this:

private_key_passphrase: t+gsjivVXODDzhujYLW/ZdjVuYrIz4n5

Generate a key-pair

Now that we have a passphrase, we can easily create a RSA key-pair create. Go into the Terminal with cd to the desired directory and execute the following command:

openssl genpkey -des3 -algorithm RSA -out rsa.p8

Now we are asked twice to enter a passphrase:

Enter PEM pass phrase:
Verifying - Enter PEM pass phrase:

Enter the passphrase we created earlier (for example, by pasting it from the clipboard with ⌘v). After that, verify it.

Now we have a rsa.p8 file created that DES3 is encrypted (-des3) with our previously created passphrase. This encrypted private key may look like this (with the middle lines briefly replaced by ...):

-----BEGIN ENCRYPTED PRIVATE KEY-----
MIIFHDBOBgkqhkiG9w0BBQ0wQTApBgkqhkiG9w0BBQwwHAQI0D1voQZTDt8CAggA
MAwGCCqGSIb3DQIJBQAwFAYIKoZIhvcNAwcECCmq3xQ59T+GBIIEyF4NQLQcK7BB
...
2ZxlbJ5YtNJGQmTNeoXT5+x4uPAUgijT2S68beLIHuVnBb/r75c3VnBlDjt0kVdR
fvIaADH4uJsB5clHVJXl3rvFkYdF0Qln5iyFJa91ZX8/iSB1hothTC8pKXYdnedQ
Pz0oVMK18+0AeOVlwmVaNQ==
-----END ENCRYPTED PRIVATE KEY-----

We need the path of this file for in our profiles.yml. As our user name on this Mac esempio is and we also put the file in the .dbt brochure would keep, then that would /Users/esempio/.dbt/rsa.p8 are. (Note that ~ does not work in this case instead of /Users/esempio.) This path we place in the YAML:

private_key_path: /Users/esempio/.dbt/rsa.p8

Our dbt Profile is now ready:

default:
  target: dev
  outputs:
    dev:
      type:      snowflake
      account:   voor-beeld
      user:      esempio
      role:      sysadmin
      warehouse: magazijn     
      database:  ontwikkel_db
      schema:    dbt_esempio
      
      private_key_path:       /Users/esempio/.dbt/rsa.p8 
      private_key_passphrase: t+gsjivVXODDzhujYLW/ZdjVuYrIz4n5

Extracting the public key

For Snowflake we need a public key. The private key we just created also contains the public key. That might be a bit counterintuitive. To get this public key we need to extract it from the private key with the following command:

openssl pkey -in rsa.p8 -pubout | grep -ve '----$' | pbcopy

The above rule first extracts the public key from the private key that is in rsa.p8 state: openssl pkey -in rsa.p8 -pubout. For this we need to enter the passphrase when prompted. rsa.p8 is the path to the file, so we must be in the same directory in the example.

Snowflake does not accept the following rules:

-----BEGIN ENCRYPTED PRIVATE KEY-----
-----END ENCRYPTED PRIVATE KEY-----

So we have to filter these out with | grep -ve '----$'. Then we copy everything to the clipboard as we did before with | pbcopy.

Adding the public key to the Snowflake USER

Now that we have the public key, we can add it to our Snowflake user. This is basically quite simple. We go to Snowsight, Snowflake's web interface. There we create a new worksheet and enter the following command:

ALTER USER esempio 
SET RSA_PUBLIC_KEY =
'MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAxGaRi+FIm2Q2+DUG+aLc
/24Ia8/7oo5CznRT4y/QQxRVm8cFnXA+Lb7VwvI92uiJpMd/l0LMOgFhkbC1jOGt
VszogvRkn7/hwZfUhV7mT0EuMELQaUlAkY49XNvNWUSeSHwGAEaszr3hn1xRKUS8
ZNuAxob6uTTlKEy9k3F8wKDQqu6f3y+EUg8dv+xUWY4LW/966c6jQk0oJfX0CN9U
h8zzIYyEwL/gNKZ2ovOLabEj9FkLvKg9rUXDyo64IWfxr1xAUQ0uYvoC1oqh1pxo
NthhNpoeLll0HcLixereJUc6ypsszrTAXkNCxe/T41U5RYxIavv8vOmD5UtVo7n0
MQIDAQAB
';

Make sure you paste the public key from the clipboard between the 2 single-qutoes '' and does not add spaces, new lines or anything like that. However, there are already some line breaks in the public key itself. The last line of the public key is also shorter. It's supposed to be. And the new line after it is also supposed to be there, making the ' comes on the next line.

Testing

We're done: the connection is in place. We may want to test it by entering the directory of our dbt Project the command dbt debug run. If it runs successfully, we know that everything is working properly. As of now, we do not need to enter a password for this (nor is a password sent over the network!).

Discussion

Making a connection between Snowflake and a dbt project on your Mac is not very easy and you need knowledge of Terminal and certain commands. Once the connection is set up, though, it can save a lot of work by not having to log in every time. It is also a secure system.

Thanks for reading this blog. Check out our blog page for more blogs about Tableau, Alteryx, and Snowflake.

Work together with one of our consultants and get more out of your data. 

Contact us and we will help you.