{"id":104979,"date":"2025-01-20T03:43:00","date_gmt":"2025-01-20T03:43:00","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=104979"},"modified":"2025-04-07T20:12:42","modified_gmt":"2025-04-07T20:12:42","slug":"loading-models-from-source-data-with-dbt","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/business-intelligence\/general-analytics\/loading-models-from-source-data-with-dbt\/","title":{"rendered":"Loading Models from Source Data with dbt"},"content":{"rendered":"

The data transformation tool dbt<\/a> (data build tool) has become more and more popular over the past years. It focuses heavily on SQL, and it adds a bunch of interesting features into the mix, such as data lineage, automatic orchestration, reusable macros etc. In the first article in this series, A gentle introduction to dbt<\/a>, it\u2019s explained how you can get dbt for free in the cloud version, how you can set up an account and create a connection to Microsoft Fabric. We also created our first models, where a model can eventually be persisted as a view or a table in your database. It\u2019s recommended to go through that article first if you haven\u2019t already, because we will build upon it.<\/p>\n

In this article, we will dive a bit deeper into how you can specify the source tables for your dbt project, and we will create a couple of models that will load some fact and dimension tables.<\/p>\n

How to read your Source Data<\/h2>\n

In an ELT pipeline, dbt is responsible for the \u201cT\u201d, meaning it can transform data that is already in the data store of choice using SQL. This means that some other tool \u2013 this can be pipelines in Azure Data Factory or in Fabric \u2013 needs to be responsible for extracting source data and storing it in your database. Suppose this part of the process has already been completed and you have data available for you in staging tables. How can you process that data with dbt?<\/p>\n

Get sample data in Fabric SQL DB<\/h3>\n

As a source, we\u2019ll use sample data loaded into a SQL DB in Microsoft Fabric (this feature is in preview<\/a> since the Microsoft Ignite 2024 conference). In a Fabric-enabled workspace, you can find the SQL database item under the \u201cstore data\u201d category when you want to add a new item.<\/p>\n

\"A<\/p>\n

Give the new database a name. This name will be displayed when you connect to the database with the SQL Analytics Endpoint (which reads the mirrored Parquet files in the delta lake storage).<\/p>\n

\"A<\/p>\n

Once the database is provisioned, you\u2019re presented with multiple options to load data into it. In our case, we want to load the sample AdventureWorksLT<\/code> database.<\/p>\n

\"A<\/p>\n

It might take a little while to load the data.<\/p>\n

\"A<\/p>\n

When the database is ready, you can see several tables are loaded into the SalesLT<\/code> schema:<\/p>\n

\"A<\/p>\n

We will now try to read and transform this data in dbt.<\/p>\n

Specifying the Sources in dbt<\/h3>\n

dbt needs to know where it can find the source tables. The declaration of the source tables is done in a YAML file. Let us first add a new folder to the dbt project and<\/p>\n

\"A<\/p>\n

Name it \u201cAdventureWorksDW\u201d:<\/p>\n

\"A<\/p>\n

Using the same context menu, we can add a new YAML file to this folder:<\/p>\n

\"A<\/p>\n

The file doesn\u2019t necessarily need to be called sources.yml<\/code>, but inside the YAML file there needs to be a sources:<\/code> key in the file. In the file, we place the following contents:<\/p>\n

version: 2\nsources:\n\u00a0 - name: adventureworkslt\n\u00a0 \u00a0 database: AdventureWorks\n\u00a0 \u00a0 schema: SalesLT\n\u00a0 \u00a0 tables:\n\u00a0 \u00a0 \u00a0 - name: Product\n\u00a0 \u00a0 \u00a0 - name: ProductCategory\n\u00a0 \u00a0 \u00a0 - name: ProductDescription\n\u00a0 \u00a0 \u00a0 - name: ProductModel\n\u00a0 \u00a0 \u00a0 - name: ProductModelProductDescription<\/code><\/pre>\n
We gave our source a name \u2013 adventureworkslt<\/code> \u2013 and we specify the name of the database, the schema and the names of the different tables. Because the warehouse can use three-part naming, we can access the tables in the SQL DB from the warehouse itself. This uses the SQL Analytics Endpoint of the SQL DB. For example:<\/p>\n
\"A<\/p>\n
In the folder we created earlier, we can now add a new file called DimProduct.sql<\/code>. It\u2019s important to include the .sql<\/code> extension, so dbt can recognize it as a model. Inside the file, we can add the following SQL, which includes a jinja<\/a> reference to one of the source tables we specified in the sources.yml<\/code> file:<\/p>\n
SELECT *\nFROM {{ source('adventureworkslt', 'Product') }}<\/code><\/pre>\n
The reference consists of the source name and the name of the table. At compile time, dbt will translate this to the correct three-part name:<\/p>\n
\"A<\/p>\n
It\u2019s also possible to do a preview of the data, if the SQL statement is valid:<\/p>\n
\"A<\/p>\n
Let\u2019s change our query with something more complex; we now read from several source tables in the same query:<\/p>\n
WITH cte_categories AS\n(\n\u00a0 \u00a0 SELECT\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0ProductCategoryID\n\u00a0 \u00a0 \u00a0 \u00a0 ,ProductCategoryName \u00a0 \u00a0= [Name]\n\u00a0 \u00a0 FROM {{ source('adventureworkslt', 'ProductCategory') }}\n\u00a0 \u00a0 WHERE ParentProductCategoryID IS NULL\n)\n, \u00a0 cte_subcategories AS\n(\n\u00a0 \u00a0 SELECT\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0ProductSubCategoryID \u00a0 = sc.ProductCategoryID\n\u00a0 \u00a0 \u00a0 \u00a0 ,ProductSubCategoryName = sc.[Name]\n\u00a0 \u00a0 \u00a0 \u00a0 ,c.ProductCategoryName\n\u00a0 \u00a0 FROM {{ source('adventureworkslt', 'ProductCategory') }} sc\n\u00a0 \u00a0 LEFT JOIN cte_categories \u00a0 \u00a0 c ON sc.ParentProductCategoryID = c.ProductCategoryID\n\u00a0 \u00a0 WHERE ParentProductCategoryID IS NOT NULL\n)\n, \u00a0 cte_models AS\n(\n\u00a0 \u00a0 SELECT\n\u00a0 \u00a0 \u00a0 \u00a0 m.ProductModelID\n\u00a0 \u00a0 \u00a0 \u00a0,ProductModelName \u00a0 \u00a0= m.[Name]\n\u00a0 \u00a0 \u00a0 \u00a0,ProductModelDesc \u00a0 \u00a0= ISNULL(d.[Description],'Description missing...')\n\u00a0 \u00a0 FROM {{ source('adventureworkslt', 'ProductModel') }} \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0m\n\u00a0 \u00a0 LEFT JOIN {{ source('adventureworkslt', 'ProductModelProductDescription') }} md \n       ON m.ProductModelID = md.ProductModelID AND md.Culture = 'en'\n\u00a0 \u00a0 LEFT JOIN {{ source('adventureworkslt', 'ProductDescription') }} \u00a0 \u00a0 \u00a0d \u00a0\n       ON md.ProductDescriptionID = d.ProductDescriptionID\n)\nSELECT\n\u00a0 \u00a0 \u00a0p.ProductID\n\u00a0 \u00a0 ,ProductName \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0= p.[Name]\n\u00a0 \u00a0 ,p.ProductNumber\n\u00a0 \u00a0 ,ProductColor \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 = ISNULL(p.Color,'N\/A')\n\u00a0 \u00a0 ,p.StandardCost\n\u00a0 \u00a0 ,p.ListPrice\n\u00a0 \u00a0 ,ProductSize \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0= ISNULL(p.Size,'N\/A')\n\u00a0 \u00a0 ,ProductWeight \u00a0 \u00a0 \u00a0 \u00a0 \u00a0= p.[Weight]\n\u00a0 \u00a0 ,p.SellStartDate\n\u00a0 \u00a0 ,p.SellEndDate\n\u00a0 \u00a0 ,p.DiscontinuedDate\n\u00a0 \u00a0 ,ProductSubCategoryName = ISNULL(pc.ProductSubCategoryName,'N\/A')\n\u00a0 \u00a0 ,ProductCategoryName \u00a0 \u00a0= ISNULL(pc.ProductCategoryName,'N\/A')\n\u00a0 \u00a0 ,ProductModelName \u00a0 \u00a0 \u00a0 = ISNULL(m.ProductModelName,'N\/A')\n\u00a0 \u00a0 ,ProductModelDesc \u00a0 \u00a0 \u00a0 = ISNULL(m.ProductModelDesc,'N\/A')\nFROM {{ source('adventureworkslt', 'Product') }} p\nLEFT JOIN cte_subcategories pc ON p.ProductCategoryID = pc.ProductSubCategoryID\nLEFT JOIN cte_models \u00a0 \u00a0 \u00a0 \u00a0m \u00a0ON p.ProductModelID = m.ProductModelID;<\/code><\/pre>\n
When we save and compile the model, we get an updated lineage view, which shows the dependencies of the model on the source tables:<\/p>\n
\"A<\/p>\n
This only works if you use the source macro to reference the source tables, if you would hardcode the table names the lineage view will not show the dependencies. We can now create a similar model for the customers. First, we will need to append the names of the source tables to the sources.yml<\/code> file:<\/p>\n
\"A<\/p>\n
Add a new file called DimCustomer.sql<\/code> and add the following SQL statement:<\/p>\n
WITH cte_mainoffices AS\n(\n    SELECT\n         ca.CustomerID\n        ,a.City\n        ,a.StateProvince\n        ,a.CountryRegion\n        ,a.PostalCode\n    FROM {{ source('adventureworkslt', 'CustomerAddress') }} ca\n    JOIN {{ source('adventureworkslt', 'Address') }}       a \n          ON ca.AddressID = a.AddressID\n    WHERE ca.AddressType = 'Main Office'\n)\nSELECT\n     c.CustomerID\n    ,CustomerName           = CONCAT_WS(' ',c.Title, c.FirstName, c.MiddleName, \n                                                              c.LastName, c.Suffix)\n    ,CustomerCompanyName    = ISNULL(c.CompanyName,'N\/A')\n    ,CustomerSalesPerson    = ISNULL(c.SalesPerson,'N\/A')\n    ,CustomerEmail          = ISNULL(c.EmailAddress,'N\/A')\n    ,CustomerPhone          = ISNULL(c.Phone,'N\/A')\n    ,CustomerPostalCode     = ISNULL(m.PostalCode,'N\/A')\n    ,CustomerCity           = ISNULL(m.City,'N\/A')\n    ,CustomerStateProvince  = ISNULL(m.StateProvince,'N\/A')\n    ,CustomerCountryRegion  = ISNULL(m.CountryRegion,'N\/A')\nFROM {{ source('adventureworkslt', 'Customer') }}       c\nLEFT JOIN cte_mainoffices   m ON c.CustomerID = m.CustomerID;<\/code><\/pre>\n
Resulting in this lineage view:<\/p>\n
\"A<\/p>\n
For our final model, FactSales.sql<\/code>, we can use the following SQL statement (don\u2019t forget to add the two extra source tables to the sources.yml<\/code> file!).<\/p>\n
WITH cte_details AS\n(\n\u00a0 \u00a0 SELECT\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0SalesOrderID\n\u00a0 \u00a0 \u00a0 \u00a0 ,SalesOrderDetailID\n\u00a0 \u00a0 \u00a0 \u00a0 ,OrderQty\n\u00a0 \u00a0 \u00a0 \u00a0 ,ProductID\n\u00a0 \u00a0 \u00a0 \u00a0 ,UnitPrice\n\u00a0 \u00a0 \u00a0 \u00a0 ,UnitPriceDiscount\n\u00a0 \u00a0 \u00a0 \u00a0 ,LineTotal = \n            ISNULL(OrderQty * (1.0 - ISNULL(UnitPriceDiscount,0.0)) * UnitPrice,0.0)\n\u00a0 \u00a0 FROM {{ source('adventureworkslt', 'SalesOrderDetail') }}\n), cte_sales AS\n(\n\u00a0 \u00a0 SELECT\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0soh.SalesOrderID\n\u00a0 \u00a0 \u00a0 \u00a0 ,sod.SalesOrderDetailID\n\u00a0 \u00a0 \u00a0 \u00a0 ,SK_OrderDate \u00a0 = CONVERT(DATE,soh.OrderDate)\n\u00a0 \u00a0 \u00a0 \u00a0 ,SK_DueDate \u00a0 \u00a0 = CONVERT(DATE,soh.DueDate)\n\u00a0 \u00a0 \u00a0 \u00a0 ,SK_ShipDate \u00a0 \u00a0= CONVERT(DATE,soh.ShipDate)\n\u00a0 \u00a0 \u00a0 \u00a0 ,soh.PurchaseOrderNumber\n\u00a0 \u00a0 \u00a0 \u00a0 ,soh.AccountNumber\n\u00a0 \u00a0 \u00a0 \u00a0 ,soh.CustomerID\n\u00a0 \u00a0 \u00a0 \u00a0 ,sod.ProductID\n\u00a0 \u00a0 \u00a0 \u00a0 ,sod.OrderQty\n\u00a0 \u00a0 \u00a0 \u00a0 ,sod.UnitPrice\n\u00a0 \u00a0 \u00a0 \u00a0 ,sod.UnitPriceDiscount\n\u00a0 \u00a0 \u00a0 \u00a0 ,sod.LineTotal\n\u00a0 \u00a0 \u00a0 \u00a0 \/* the next two costs are evenly divided over the detail lines *\/\n\u00a0 \u00a0 \u00a0 \u00a0 ,TaxAmt \u00a0 \u00a0 \u00a0 \u00a0 = soh.TaxAmt \/ (COUNT(1) OVER (PARTITION BY soh.SalesOrderID))\n\u00a0 \u00a0 \u00a0 \u00a0 ,Freight \u00a0 \u00a0 \u00a0 \u00a0= soh.Freight \/ (COUNT(1) OVER (PARTITION BY soh.SalesOrderID))\n\u00a0 \u00a0 FROM {{ source('adventureworkslt', 'SalesOrderHeader') }} \u00a0 soh\n\u00a0 \u00a0 JOIN cte_details \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0sod ON soh.SalesOrderID = sod.SalesOrderID\n)\nSELECT\n\u00a0 \u00a0 \u00a0SalesOrderID\n\u00a0 \u00a0 ,SalesOrderDetailID\n\u00a0 \u00a0 ,SK_OrderDate\n\u00a0 \u00a0 ,SK_DueDate\n\u00a0 \u00a0 ,SK_ShipDate\n\u00a0 \u00a0 ,PurchaseOrderNumber\n\u00a0 \u00a0 ,AccountNumber\n\u00a0 \u00a0 ,CustomerID\n\u00a0 \u00a0 ,ProductID\n\u00a0 \u00a0 ,OrderQty\n\u00a0 \u00a0 ,UnitPrice\n\u00a0 \u00a0 ,UnitPriceDiscount\n\u00a0 \u00a0 ,LineTotal\n\u00a0 \u00a0 ,TaxAmt\n\u00a0 \u00a0 ,Freight\n\u00a0 \u00a0 ,TotalAmount \u00a0= LineTotal + ISNULL(TaxAmt,0.0) + ISNULL(Freight,0.0)\nFROM cte_sales;<\/pre>\n
Which will result in this lineage view:<\/p>\n
\"A<\/p>\n
Our (simple) data warehouse is finished, for now. We can build all the models and deploy them to the Fabric warehouse with the following command:<\/p>\n
dbt\u00a0run --models AdventureWorksDW.*<\/code><\/pre>\n
This command<\/a> will take all compiled models within the path specified and execute them against the database.<\/p>\n
\"A<\/p>\n
However, the models are created as views, not as tables.<\/p>\n
\"A<\/p>\n
This is the default in dbt<\/a>, but we can override this by setting it in the model itself. If you want to do this for an entire folder, you can specify the behaviour in the dbt_project.yml <\/code>file.<\/p>\n
\"A<\/p>\n
Add the folder and the +materialized<\/code> keyword and set it to Table<\/strong>:<\/p>\n
\"A<\/p>\n
When we run the command again, we can see load times have increased as data is loaded into tables:<\/p>\n
\"A<\/p>\n
The views have been removed and tables have been created instead:<\/p>\n
\"A<\/p>\n
Conclusion<\/h2>\n
In this article, we showed how you can load sample data into a Fabric SQL Database. Then we used this data to load a couple of models in dbt. We needed to specify those source tables in a YAML file, which allows metadata references inside the SQL statement, allowing dbt to derive dependencies and create a lineage view.<\/p>\n
Those models aren\u2019t \u201cbest practice\u201d yet when it comes to data warehouse modelling. In a following article, we will discover how we can use macros to add surrogate keys, reuse code and create a date dimension.<\/p>\n","protected":false},"excerpt":{"rendered":"
The data transformation tool dbt (data build tool) has become more and more popular over the past years. It focuses heavily on SQL, and it adds a bunch of interesting features into the mix, such as data lineage, automatic orchestration, reusable macros etc. In the first article in this series, A gentle introduction to dbt,……<\/p>\n","protected":false},"author":110905,"featured_media":105002,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[53,159165],"tags":[159230],"coauthors":[159081],"class_list":["post-104979","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-featured","category-general-analytics","tag-dbt"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/104979","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/110905"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=104979"}],"version-history":[{"count":7,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/104979\/revisions"}],"predecessor-version":[{"id":106135,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/104979\/revisions\/106135"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media\/105002"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=104979"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=104979"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=104979"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=104979"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}