DBT ELT Tricks using Google Cloud Projects & DBT Macros — External Tables

DBT is the new and rising data tool of the Data Engineer (Analytics Engineer?) and I wanted to try using Macros for a common ELT task.

Now, any of you familiar with DBT will say ‘isn’t there a package service that includes tools to do all types of tasks?’ well, yes, you are correct and you should totally check out the real package from DBT for external tables which includes cross platform support and various other features.

But where is the fun in that?

External Tables

For those of you not familiar with external tables, they are a feature of most databases whereby database developers can create a link out from the database, to some sort of filesystem, and read data directly from that filesystem and present it as a ‘real’ table in the database. Most databases support the common file formats (csv/json/etc) but the more modern databases such as BigQuery, Azure Synapse Analytics, Snowflake all support different numbers of modern formats ranging from Parquet to Delta Lake, making the range of external sources fairly large. (and if they don’t you can use other techniques to import data via native techniques)

Whilst external tables come with a variety of limitations and restrictions they are often a cornerstone of ELT processes in databases and are a well understood technique for those of us crusty old database veterans 😉.