Is dbt an ETL tool?


#1

I am new to dbt and would like to know if dbt is an ETL tool?

  1. I am trying to create models where my source table is in postgres but I would like my target model table to be created in Redshift can I do that using dbt?
  2. Another use case both source and target tables are in redshift same warehouse but different host name and different schema and database can I create models where source and target are in dfferent database in redshift?

#2

I don’t believe dbt can handle either of those use cases. It’s the “T” in ETL. For “EL,” AWS has DMS to replicate from Postgres to Redshift (doesn’t work if you have jsonb columns), or Stitch Data can do the same.


#3

Thanks for your reply so its basically you can just build models on the same warehouse and same database.
what if I need to move my old model table to new database do I have to rebuild all again?


#4

I think your options are to rebuild or if it’s an incremental model you could unload and copy. I don’t believe redshift supports creating a table from a different database but I’m not 100% sure about that.


#5

Thankyou! I guess it doesn’t will have to use unload and copy into s3.


#6

Hey Pallavi! dbt doesn’t do any sort of data extraction or loading – there are many other tools that excel at this task like Singer/Stitch, Fivetran, AWS DMS, etc. For loading data from Postgres into Redshift, I think a tool like Singer or AWS DMS might be your best bet.

So, rather than extraction and loading, dbt exists to transform data once it’s already loaded into your warehouse. Using dbt, you can select data from your source schemas, then transform that data in SQL, writing the resulting data out into a different “analytics” schema. You can find a little bit more about this workflow in the dbt documentation.

You mentioned:

can I create models where source and target are in dfferent database in redshift?

The answer is unfortunately “no” – Redshift doesn’t allow you to transfer data between different “logical” databases in a single warehouse. I’d recommend using schemas inside of a single “logical” database to separate your source data and your dbt models.

Hope this helps!


#7

Thanks alot for detailed explanation.
This makes so much sense!!