CData Sync: DBT Transformations

CData Sync now integrates with dbt Core (a data building tool by dbt Labs™), which enables you to create powerful transformations. This integration enables you to develop, control, and run transformations to build Python models in a supported destination database. 

You have complete control over your project repository and the ability to write SQL SELECT statements to transform your data and build models. Sync simply creates a pipeline for you to run those transformations in your chosen destination. 

If you only need to execute simple transformations, then basic SQL transformations are sufficient. However, for advanced modeling requirements, transformations that use dbt Core models offer more robust capabilities. For example, if your models contain dependencies, a transformation that uses a dbt Core project recognizes and honors those dependencies, enabling the models to build in the correct order.

Supported Destinations 

Transformations of type DBT are supported for the following destinations: 

• Snowflake 
• Amazon Redshift 
• Databricks 
• PostgreSQL  
• Google BigQuery 

dbt Core Details

Setting Up Tools for DBT Transformations 

You need to set up dbt Core and other tools first to enable DBT transformations, as follows: 

1. Install Git for Windows and Python 3.7 or later for Microsoft Windows. 

Note: An application like Git is not required. However, such a repository is recommended because it provides versioning, enables you to share projects, and gives you more control of your source code. 

In Git, locate and copy the following information for later use in the Sync settings for a DBT transformation: 

• Git Repository URL - The repository URL is available in your Git repository window. Click Code to open the Clone dialog. The URL is on the HTTPS tab in the dialog. Copy the URL by clicking the copy icon on the right end of the URL field. Save the icon for use later in Sync. 
• Personal Access Token - To obtain your personal access token:   

1. Click your profile icon in the top right of the Git window and select Settings to open the Profile settings page.  
2. Select Developer Settings > Personal access tokens. This selection opens the New personal access token pane.  
3. Select the Repo check box in the Select scopes category. (You can set other criteria in this category as well, but it is not necessary.) For example, you might want to choose an expiration period (or no expiration) for your token under the Expiration category.   
4. Click Generate token at the bottom of the page. Copy and save the generated token for use later in Sync.  

For more details about obtaining a personal access token, see Creating a personal access token. 

1. Install dbt Core and the adapter plug-in for your destination on the same machine where Sync is installed. Plug-ins are available for each of the destinations that are listed earlier.   
2. Download your dbt projects locally by cloning your Git repository to your local machine. Sync clones the repository upon setup and before each run to ensure that any changes to the project are reflected in the next run from Sync. 
3. Create a transformation in Sync (using dbt projects that contain YML or SQL files), as described in the next section. 

Creating a DBT Transformation 

To create a dbt transformation: 

1. Open Sync and navigate to the Transformations page.  
2. Select the job on which you want to run a transformation. Then, click Add Transformations. 
3. Fill out the fields in the Add Transformation dialog box, as follows:   

• Name: Enter a name for your transformation. 
• Type: Select dbt Core as the transformation type. 
• Project Folder: Select one of the following options. The default option is Local. 
• Local: Enter the path for your existing local project folder. For this option, you also need to enter your dbt project folder page in the Folder Path field. 
• Github: Enter the URL for the project that you created earlier in Git. For this option, enter the following information: 
• Git Repository URL: Enter your Git repository URL in the form https://github.com/Owner/Repository.git. 
• Token: Enter the personal access token that you copied earlier in Git. 
• Destination Schema - Add a destination schema of your choice. 

4. Click Add Transformation. 

dbt Cloud Details

Connecting CData Sync to dbt Cloud 

dbt Cloud is an integrated development environment (IDE) in which you can write, run, and test code as well as manage version control in your dbt project from your browser (no command-line use is required). 

To connect to dbt Cloud: 

1. Open the Advanced tab on the Sync Settings page and define your account Id and API key.  

• Your account Id is available in the URL that you see in your dbt Cloud account settings. To retrieve the Id, sign in to your dbt Cloud account and select Settings (the gear icon) > Account Settings. The account Id follows the /accounts/ path component, as shown in the following example: 

Example: https://cloud.getdbt.com/settings/accounts/1234 

In this example, the account Id is 1234. 

The account Id is also available in the URL that is displayed when you click the Develop tab, as shown below: 

Example: https://cloud.getdbt.com/develop/1234/projects/ProjectId 

• The API key is available by clicking the Settings icon (at the top right of the dbt Cloud window) and then selecting Account Settings > API Access. The key is listed in the API section at the bottom of the API Access page. 

2. Click Test Connection to confirm that you are connected. 

Creating a DBT Cloud Transformation 

To create a dbt Cloud transformation: 

1. Open Sync and navigate to the Transformations page.  
2. Select the job on which you want to run a transformation. Then, click Add Transformations. 
3. Fill out the fields in the Add Transformation dialog box, as follows:  

• Name: Enter a name for your transformation. 
• Type: Select dbt Cloud as the transformation type. 
• dbt Job: Select a dbt job from the drop-down list. 

4. Click Add Transformation.