I am working on some analytical work and we need to transform data from one source to another and we are using DBT for transformation purpose. one of the data available to use via only REST API. so my question is can we call external API inside dbt file and extract the fields from its response. Do we have something?
1 Answers
Quoting from the founder's blog post: "What, exactly, is dbt?",
"dbt is the T in ELT. It doesn’t extract or load data, but it’s extremely good at transforming data that’s already loaded into your warehouse. This “transform after load” architecture is becoming known as ELT (extract, load, transform)."
Consequently, unless you already have the api response IN your warehouse, dbt won't be able to help you. You'll probably need an ELT engine (Stitch, Fivetran, Airflow etc. to name a few) to retrieve and store the API response. However, if you have the API response stored as say, a JSON object or a nested string - dbt can work with that.
It may seem a little underwhelming but the magic of a great product sometimes is it's focus on being really, really great at just one thing.
Edit 2022: If you are using dbt on a database that supports http or curl function calls and insist on taking this route, I recommend the following question as a starting point. Good luck.
Related