Load Data with dbt

dbt is a transformation workflow that helps you get more work done while producing higher quality results. You can use dbt to modularize and centralize your analytics code, while also providing your data team with guardrails typically found in software engineering workflows. Collaborate on data models, version them, and test and document your queries before safely deploying them to production, with monitoring and visibility.

tidbcloudlake-dbt is a plugin developed by TiDB Cloud Lake with the primary goal of enabling smooth integration between dbt and TiDB Cloud Lake. By utilizing this plugin, you can seamlessly perform data modeling, transformation, and cleansing tasks using dbt and conveniently load the output into TiDB Cloud Lake. The table below illustrates the level of support that the tidbcloudlake-dbt plugin offers for commonly used features in dbt:

Install tidbcloudlake-dbt

Installing the tidbcloudlake-dbt plugin has been streamlined for your convenience, as it now includes dbt as a required dependency. To effortlessly set up both dbt and the tidbcloudlake-dbt plugin, run the following command:

pip3 install tidbcloudlake-dbt

However, if you prefer to install dbt separately, you can refer to the official dbt installation guide for detailed instructions.

Tutorial: Run dbt Project jaffle_shop

If you're new to dbt, TiDB Cloud Lake recommends completing the official dbt tutorial available at https://github.com/dbt-labs/jaffle_shop. Before you start, follow Install tidbcloudlake-dbt to install dbt and tidbcloudlake-dbt.

This tutorial provides a sample dbt project called "jaffle_shop," offering hands-on experience with the dbt tool. By configuring the default global profile (~/.dbt/profiles.yml) with the necessary information to connect to your TiDB Cloud Lake instance, the project will generate tables and views defined in the dbt models directly in your TiDB Cloud Lake database. Here's an example of the file profiles.yml that connects to a TiDB Cloud Lake instance:

jaffle_shop_lake:
  target: dev
  outputs:
    dev:
      type: tidbcloudlake
      host: tnxxxx.gw.aws-us-east-2.default.tidbcloud.com
      port: 443
      schema: sjh_dbt
      user: <username>
      pass: ********
      warehouse: default
      secure: true

For more information about configuring and using the adapter, see the lake-dbt repository.