# dbt Core

## Supported Capabilities

{% tabs %}
{% tab title="Supported Capabilities" %}
**General**

* **Metadata** — metadata extraction and display of asset information (tables, columns, schemas). Types collected: Schema, Virtual Table, Virtual Column, Data Job, Data Run, Data Task

**Data Quality Monitors**

* Job Failure
  {% endtab %}

{% tab title="Not Supported" %}
**General**

* Profiling
* Preview
* Data Quality
* Configurable Collection
* External Table
* View Table
* Stored Procedure

**Data Quality Monitors**

* Freshness
* Volume
* Field Health
* Custom SQL
* Schema Drift
  {% endtab %}
  {% endtabs %}

dbt Core can map lineage relationships to upstream and downstream objects from the following connectors:

* Upstream Connectors: postgresql, redshift, snowflake, bigquery, mysql
* Downstream Connectors: postgresql, redshift, snowflake, bigquery, mysql

## Connection Requirements

This documentation is on how to add a data source connection to dbt Core, which is the open source framework for dbt. If you are interested to connect to your dbt Cloud instance instead, please check out [this documentation](https://docs.decube.io/transformation-tools/dbt) for dbt Cloud version.

{% hint style="info" %}
Important: Our system does not parse or collect metadata from past/old DBT runs.

In order for metadata to be collected and properly ingested into our system, the DBT data job must be re-run to get the same day data.
{% endhint %}

Integrating DBT Core with Decube involves reading files from an AWS S3 bucket, which shares similarities with how AWS S3 itself connects to the platform.

1. Set up an S3 bucket following the same procedure outlined in our documentation for [AWS S3](https://docs.decube.io/datalake/s3).
2. Define folder partitions (details will be provided in the following section).
3. Upload the necessary files to those partitions.

A summary of steps to set up dbt core:

1. Set up an S3 bucket following the same procedure outlined in our documentation for [AWS S3](https://docs.decube.io/datalake/s3).
2. Define folder partitions (details will be provided in the following section).
3. Upload the necessary files to those partitions.

Following these steps, the metadata collector will connect to the S3 bucket and retrieve the data.

## Minimum Requirement

{% hint style="info" %}
Currently, only S3 storage is supported for DBT Core under the "Storage Type" dropdown.
{% endhint %}

To connect your AWS Glue to decube, we will need the following information:

Choose authentication method:

a. [**AWS Identity**](#a.-aws-roles):

* Select AWS Identity
* Customer AWS Role ARN
* Path
* Region
* Storage Type
* Data source name

<figure><img src="https://1779874722-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FTw0qpCVzfrIXqS4FEg4T%2Fuploads%2Fgit-blob-53d3e40a141e188d422d63883d64d2c27e36864b%2Fimage.png?alt=media" alt=""><figcaption><p>Connecting DBT Core using AWS Identity</p></figcaption></figure>

b. **AWS Access Key**:

* Access Key ID
* Secret Access Key
* Path
* Region
* Storage Type
* Data source name

{% hint style="info" %}
where 'Path' follows these format:\
s3://some-bucket\
s3://some-bucket/path-to-dbt-core

The path spec above will be created during the [Upload Project Files](#upload-project-files) step.
{% endhint %}

<figure><img src="https://1779874722-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FTw0qpCVzfrIXqS4FEg4T%2Fuploads%2Fgit-blob-fd0a4b1829e996fed3370cb439b3d9e97a6b1e69%2Fimage.png?alt=media" alt=""><figcaption><p>Connecting DBT Core using AWS Access Key</p></figcaption></figure>

## Connection Options:

### a. AWS Roles

{% hint style="info" %}
This section will create a Customer AWS Role within your AWS account that has the right set of permission to access your data sources.
{% endhint %}

* Step 1: Go to your AWS Account → IAM Module → Roles
* Step 2: Click on **Create** **role**.

<figure><img src="https://1779874722-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FTw0qpCVzfrIXqS4FEg4T%2Fuploads%2Fgit-blob-bc6d8296019ea69d4e3edd1cd421cdb472d2a77b%2Fimage%20(444).png?alt=media" alt=""><figcaption></figcaption></figure>

* Step 3: Choose **Custom** **trust** **policy**.

<figure><img src="https://1779874722-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FTw0qpCVzfrIXqS4FEg4T%2Fuploads%2Fgit-blob-c6ecfb6e4b9ba1639d62502abe968593c95482da%2Fimage%20(446).png?alt=media" alt=""><figcaption></figcaption></figure>

* Step 4: Specify the following as the trust policy, replacing `DECUBE-AWS-IDENTITY-ARN` and `EXTERNAL-ID` with values from [#generating-a-decube-aws-identity](https://docs.decube.io/security-and-connectivity/aws-identities#generating-a-decube-aws-identity "mention").

```
{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Principal": {
                "AWS": "<DECUBE-AWS-IDENTITY-ARN>"
            },
            "Action": "sts:AssumeRole",
            "Condition": {
                "StringEquals": {
                    "sts:ExternalId": "<EXTERNAL-ID>"
                }
            }
        }
    ]
}
```

* Step 5: Click next to proceed to attach policy.
* Step 6: Click on **Attach Policies** and Create Policy and choose **JSON Editor**. Input the following policy and press next, input the **policy name** of your choice and press **Create Policy**.

```
{
	"Version": "2012-10-17",
	"Statement": [
		{
			"Sid": "VisualEditor0",
			"Effect": "Allow",
			"Action": [
				"s3:GetObject",
				"s3:ListBucket",
				"s3:ListAllMyBuckets"
			],
			"Resource": [
				"arn:aws:s3:::{bucket-name}",
				"arn:aws:s3:::{bucket-name}/*"
			]
		}
	]
}
```

### b. Retrieving Access Keys from AWS

* Step 1: Login to AWS Console and proceed to IAM > User > Create User

<figure><img src="https://1779874722-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FTw0qpCVzfrIXqS4FEg4T%2Fuploads%2Fgit-blob-ad3bb999be0bb8033a333895879ef2c56af79039%2Fimage.png?alt=media" alt=""><figcaption></figcaption></figure>

* **Extra Step:** Click on Attach Policies and Create Policy and choose JSON Editor input the following policy and press next, input the policy name of your choice and press Create Policy

```json
{
	"Version": "2012-10-17",
	"Statement": [
		{
			"Sid": "VisualEditor0",
			"Effect": "Allow",
			"Action": [
				"s3:GetObject",
				"s3:ListBucket",
				"s3:ListAllMyBuckets"
			],
			"Resource": [
				"arn:aws:s3:::{bucket-name}",
				"arn:aws:s3:::{bucket-name}/*"
			]
		}
	]
}
```

* Step 2: Search for the policy you created just now, select it and press **Next**.

<figure><img src="https://1779874722-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FTw0qpCVzfrIXqS4FEg4T%2Fuploads%2Fgit-blob-f10eec471490c051c5473603216d29e13e1e32c2%2Fimage.png?alt=media" alt=""><figcaption></figcaption></figure>

* Step 3: Review and **Create user**.

<figure><img src="https://1779874722-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FTw0qpCVzfrIXqS4FEg4T%2Fuploads%2Fgit-blob-40fdfefa927fe6220a054ce5ea862b77df52bb34%2Fimage.png?alt=media" alt=""><figcaption></figcaption></figure>

* Step 4: Navigate to the newly created user and click on `Create access key`

<figure><img src="https://1779874722-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FTw0qpCVzfrIXqS4FEg4T%2Fuploads%2Fgit-blob-2cfa289309f84474b44bbcd67383a35174ce5b55%2Fimage.png?alt=media" alt=""><figcaption></figcaption></figure>

* Step 5: Choose `Application running outside AWS`

<figure><img src="https://1779874722-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FTw0qpCVzfrIXqS4FEg4T%2Fuploads%2Fgit-blob-f70ef3c1fd294d071afbf17a5cc36f5197f23897%2Fimage.png?alt=media" alt=""><figcaption></figcaption></figure>

* Step 6: Save the provided access key and secret access key. You will not be able to retrieve these keys again.

<figure><img src="https://1779874722-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FTw0qpCVzfrIXqS4FEg4T%2Fuploads%2Fgit-blob-3d976ae441747334757453ecde5ab2581bf91ca7%2Fimage.png?alt=media" alt=""><figcaption></figcaption></figure>

### AWS KMS

If the bucket intended to be connected to Decube is encrypted using a customer managed KMS key, you will need to add the AWS IAM user created above to the key policy statement.

1. Login to AWS Console and proceed to AWS KMS > Customer-managed keys.
2. Find the key that was used to encrypt the AWS S3 bucket.
3. On the Key policy tab, click on `Edit`

<figure><img src="https://1779874722-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FTw0qpCVzfrIXqS4FEg4T%2Fuploads%2Fgit-blob-1673b13426cd4a536e5e70442e1b0c3bfe2d264d%2Fimage.png?alt=media" alt=""><figcaption></figcaption></figure>

4. Assuming the user created is `decube-s3-datalake`

a. If there is not an existing policy attached to the key

```
{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "Allow decube to use key",
            "Effect": "Allow",
            "Principal": {
                "AWS": [
                    "arn:aws:iam::<AWSAccountID>:user/{decube-s3-datalake}"
                ]
            },
            "Action": "kms:Decrypt",
            "Resource": "*"
        }
    ]
}
```

b. If there is an existing policy, append this section to the `Statement` array:

```
{
    "Statement": [
        {
            "Sid": "Allow decube to use key",
            "Effect": "Allow",
            "Principal": {
                "AWS": [
                    "arn:aws:iam::<AWSAccountID>:user/{decube-s3-datalake}"
                ]
            },
            "Action": "kms:Decrypt",
            "Resource": "*"
        }
    ]
}
```

5. `Save Changes`

### Folder partition

* Decube supports ingesting information from multiple dbt projects. You would need to structure the bucket using a format that we define based on the current date.

Given that `base_path` for a single project uses the following format:

* `base_path = ”${year}/${month}/${day}”` where:
  * `year = $(date +%Y)`
  * `month = $(date +%B)`
  * `day = $(date +%d)`
* Example of a folder partition on your S3 - `s3://your-bucket/${base_path}`
  * Where the full path of the folder could be `s3://your-bucket/2024/May/01/`

After setting up the format based on the current date partition, you can proceed to define your own structure.

decube currently supports reading two-level deep bucket structure. You could define how you would want to upload project files into separate directories.

Basically, all of the following are valid bucket path and you can refer to the examples below:

* Assuming the run takes place on the 1st of May 2024:
  * **project\_a**/2024/May/01 - [Example 1](#example-1-multiple-projects)
  * **level1**/2024/May/01 - [Example 1](#example-1-multiple-projects)
  * **dev**/**project\_a**/2024/May/01 - [Example 2](#example-2-multiple-projects-with-environments)
  * **level1**/**level2**/2024/May/01 - [Example 2](#example-2-multiple-projects-with-environments)
  * **the\_project**/2024/May/01 - [Example 3](#example-3-single-project)
  * 2024/May/01 - [Example 4](#example-4-no-project)

### Example 1 - Multiple Projects

* project\_a
  * year=2024
    * month=May
      * day=01
        * \[location of project files]
* project\_b
  * Same as project\_a
* project\_c
  * Same as project\_a

### Example 2 - Multiple Projects with Environments

* dev
  * project\_a
    * year=2024
      * month=May
        * day=01
          * \[location of project files]
  * project\_b
    * Same as project\_a
  * project\_c
    * Same as project\_a
* prod
  * project\_a\_prod
  * project\_b\_prod
  * …

### Example 3 - Single Project

* project\_a
  * year=2024
    * month=May
      * day=01
        * \[location of project files]

### Example 4 - No Project

* year=2024
  * month=May
    * day=01
      * \[location of project files]

### Upload project files

You would need to upload specific files from the `target/` directory into the bucket after your dbt command has concluded.

* `manifest.json`, which is generated by [any command that parses your project](https://docs.getdbt.com/reference/artifacts/manifest-json). Here is an example of a command that generates the file:
  * `dbt run —full-refresh`
    * This [single file](https://docs.getdbt.com/reference/artifacts/manifest-json) contains a full representation of your dbt project's resources (models, tests, macros, etc), including all node configurations and resource properties.
* `run_results.json`, which is generated by a few commands such as `build`, `compile`, and `run` just to name a few (you can refer to the [documentation](https://docs.getdbt.com/reference/artifacts/run-results-json)). Here is an example of a command that generates the file:
  * `dbt build`
    * This [file](https://docs.getdbt.com/reference/artifacts/run-results-json) contains information about a completed invocation of dbt, including timing and status info for each node (model, test, etc) that was executed.
* `catalog.json`, which is only produced by `docs generates` and is **optional**. This is required if you want to acquire column metadata. The command can be run like so:
  * `dbt docs generate`
    * This [file](https://docs.getdbt.com/reference/artifacts/catalog-json) contains information from your [data warehouse](https://docs.getdbt.com/terms/data-warehouse) about the tables and [views](https://docs.getdbt.com/terms/view) produced and defined by the resources in your project.

{% hint style="warning" %}
To ensure the collector runs successfully, you will need to upload in the following manner:

* (in pair) `manifest.json` and `run_results.json` or
* (in triplets) `manifest.json` and `run_results.json` and `catalog.json.`
  {% endhint %}

{% hint style="info" %}
Please be aware in order for the lineage to connect successfully with accuracy, you would need to [configure the source tables](https://docs.getdbt.com/docs/build/sources) on your dbt project.
{% endhint %}

#### Additional Notes

For uploading the project files, you may choose to do the following:

* Only upload the latest project files to the specified bucket where there is only one set of `manifest.json` and `run_results.json` in that bucket for that folder partition at any time.
  * **Caution:** If you were to do it this way, you may lose out information of the runs before the latest project files are processed.
* Retain a series of project files based on the timestamp of when it was run. For example, for each run append a timestamp after the filename:
  * **Do:** manifest\_20240503142827.json
  * **Do not:** 20240503142827\_manifest.json
  * Timestamped project file in this example was generated using the following commands:
    * Using `timestamp=$(date +%Y%m%d%H%M%S)` to create `manifest_${timestamp}.json`

**Note:** To ensure that each project is successfully collected by our metadata collector, we recommend uploading the `manifest.json` and `run_results.json` in the same folder. If you want to include column metadata, make sure you include `catalog.json` as well.

#### Sample Script

Here is a sample script for uploading the project files:

```
#!/bin/bash

# Project name
project_name=some_project

# Generate timestamp
export TZ=UTC
timestamp=$(date +%Y%m%d%H%M%S)

# Generate date-based directory structure
year=$(date +%Y)
month=$(date +%B)
day=$(date +%d)

# Define the base path for S3
base_path="${project_name}/${year}/${month}/${day}"

# Copy project files to S3 with the new structured path
aws s3 cp /path/to/target/manifest.json s3://some-bucket/${base_path}/manifest_${timestamp}.json
aws s3 cp /path/to/target/run_results.json s3://some-bucket/${base_path}/run_results_${timestamp}.json
aws s3 cp /path/to/target/catalog.json s3://some-bucket/${base_path}/catalog_${timestamp}.json
```

You may modify and integrate this into your existing workflows.

## Connecting DBT Core with Decube

After following the above steps, you may start ingesting the metadata from your DBT Core bucket into decube by navigating to `My Account > Data Sources Tab > Connect A New Data Source > DBT Core.`

where 'Path' follows these format:\
s3://some-bucket\
s3://some-bucket/path-to-dbt-core

<figure><img src="https://1779874722-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FTw0qpCVzfrIXqS4FEg4T%2Fuploads%2Fgit-blob-26b3ea25500db46ae42460f79e4a4051266ee2e3%2Fimage.png?alt=media" alt=""><figcaption></figcaption></figure>

Please provide the required credentials and click "`Test this connection`" to verify their validity. Afterward, assign a name to your data source, and by selecting the "`Connect This Data Source`" option, your connection between DBT Core and Decube will be successfully established.

### Additional configuration for lineage

Once you have connected your dbt core, you will then need to map the connection sources to the data sources on the decube platform. Refer how to do that in [this documentation](https://docs.decube.io/transformation-tools/additional-configurations).