Integrating with DBT

dbt compile will compile the DBT project and generate the manifest file. This must be run first before SDF can work.

dbt compile

Using the next command, SDF will create a workspace.sdf.yml based on your DBT project’s configuration. This file will be placed adjacent to your dbt_project.yml file and should be committed to your repository.

sdf dbt init

Notice that your workspace.sdf.yml includes block points to files within the target directory. This is because SDF deals with raw SQL directly and not DBT models. To accomplish this, SDF copies the necessary compiled DBT models into the sdf directory with target/compiled (or your configured target-path).

This command will also compile DBT snapshots into a format SDF can understand, and it will configure SDF to work with your DBT seeds. It will add these DBT seed configurations to the workspace.sdf.yml file like so:

...
table:
	name: raw_orders
	location: seeds/raw_orders.csv
	file-format: csv
	with-header: true

---
table:
	name: raw_payments
	location: seeds/raw_payments.csv
	file-format: csv
	with-header: true

---
table:
	name: raw_customers
	location: seeds/raw_customers.csv
	file-format: csv
	with-header: true
...

All SDF needs to know is the name of the table, the location of the file, and the file format. From there, it can infer the schema and data types. As new seeds are added, simply running sdf dbt refresh will update the workspace.sdf.yml file with the new seeds. Seeds can also be configured manually according to the YAML specification for SDF Table Blocks

Now that we have our workspace.sdf.yml file configured, we can compile SDF. This will generate the column level lineage and place it in the .sdfcache directory local to your DBT project.

sdf compile

Great, now that we have our lineage, let’s try adding some metadata to our models.

In our jaffle shop example, we have a table created from a dbt seed called raw_customers. This table contains two columns (first_name, last_name) with personally identifiable information (PII). Let’s classify these columns as PII in SDF, and ensure any downstream usage of these columns also inherits this classification.

First, let’s define our PII classifier in the workspace.sdf.yml file.

---
classifier: 
	name: pii
	description: Personally Identifiable Information
	labels: 
		- name: name
		  description: An individual's first, middle, or last name

Next, let’s attach this to the right columns raw_customers table in the workspace.sdf.yml file.

---
table:
	name: raw_customers
	location: seeds/raw_customers.csv
	file-format: csv
	with-header: true
	columns:
		- name: first_name
			classifiers:
				- pii.name
		- name: last_name
			classifiers:
				- pii.name

Great, now let’s compile SDF again and see what happens.

sdf compile --show all

Working set 6 model files, 1 .sdf file
Finished 8 models [8 reused] in 0.089 secs

Schema jaffle_shop.dbt_alice.raw_customers
+-------------+-----------+------------+
| column_name | data_type | classifier |
+-------------+-----------+------------+
| id          | bigint    |            |
| first_name  | varchar   | pii.name   |
| last_name   | varchar   | pii.name   |
+-------------+-----------+------------+

...
...

Schema jaffle_shop.dbt_alice.stg_customers
+-------------+-----------+------------+
| column_name | data_type | classifier |
+-------------+-----------+------------+
| customer_id | bigint    |            |
| first_name  | varchar   | pii.name   |
| last_name   | varchar   | pii.name   |
+-------------+-----------+------------+

...
...

Schema jaffle_shop.dbt_alice.customers
+-------------------------+-----------+------------+
| column_name             | data_type | classifier |
+-------------------------+-----------+------------+
| customer_id             | bigint    |            |
| first_name              | varchar   | pii.name   |
| last_name               | varchar   | pii.name   |
| first_order             | date      |            |
| most_recent_order       | date      |            |
| number_of_orders        | bigint    |            |
| customer_lifetime_value | bigint    |            |
+-------------------------+-----------+------------+

You’ll notice the classification is not only attached to the raw_customers table, but also to the downstream customers table and others. This is because SDF is able to infer the lineage between these two tables and propagate the classification.

Now that we have our lineage and metadata generated, we can deploy it to the SDF cloud and explore it in the UI. Note this is only available to users who have been granted access to the SDF Cloud Console.

sdf auth login

sdf push