FABRICate SQL database with dbt | Data Exposed: MVP Edition

Key Takeaways

learn how you can use DBT to accelerate your development with SQL database and fabric as well as fabric data warehouses as well as the SQL analytics endpoint you'll learn all about it some gotas and tips and tricks for getting started this week on data expose MVP [Music] Edition hi I'm Anna Hoffman and welcome to this episode of data expose in VP Edition today I'm joined by Nan thanks so much for joining us on the show today can you tell us a little bit about what you do thank you Anna for this opportunity I am Nandan hegi based in India I work as a data architect in my current organization I a two times Microsoft data platform MVP and a Microsoft fabric super user focusing on majorly Azure data platform and Microsoft fabric awesome cool well it's great to have you here and I'm excited for this episode because it's on a topic that I don't know much about uh and the title is is very cleverly named fabricate SQL database with DBT uh and I'd love to just learn a little bit more from your perspective before we get into the details like what is SQL database in fabric what's it good for in your perspective as an MVP and then kind of how does DBT play into all of this okay so uh before we move into the fabric SQL database let's cover a basic introductory about what is Microsoft fabric so Microsoft fabric is basically a software as a service introduced by Microsoft which is an allin1 analytics solution for Enterprises that covers everything from the data movement to the data science realtime analytics and business intelligence now SQL database in Microsoft fabric was recently uh during last November released into public preview it is basically a developer friendly transactional database equivalent in the Microsoft fabric world based on the Azure SQL database itself that allows one to easily create an operational database in Microsoft fabric now the SQL database in Fab break uses the same SQL database engine as the pass equivalent that is azure SQL database so one can consider that it's a transactional database equivalent for operational purposes in the Microsoft fabric world now the counterart that is DBT now the DBT stands for data build tool it's an open-source python software tool that enables data analysts and data Engineers to transform and model the data in the respective data warehouse now the dataw of terminology here is a generic one it can be any destination or sync across any platform that supports the concept of structured data warehousing it can include our Microsoft SQL Server across on premises or infrastructure as a service Aro SQL databases signups fabric SQL warehouse and even fabric SQL database so DBT enables analytical Engineers to transform the data in their warehouses by writing simple select statements and turns the select statement into the respective tables and Views so one can consider that it simplifies the ETL or the El process by focusing the T part the transformation and integrate seamlessly with modern cloudbased data platform that sounds great I I'd love to see it kind of in action if you have something to show us yes now since DBT is an open- Source python software and it's a command so DBT has two versions one is a command line version and another is a cloud version now in this demo we would be seeing the command line version of it since it's a command line version and a python open source software one of the it acts as an infrastructure as a service hence one of the prerequisite is for me to have python installed within this server on which via which I'm executing the DBT comp so I already have python installed within my server now in order to install DBT I have to execute one command which is the normal command to install any python packages that is PIP install DBT Das SQL Server now as I stated by the time this is getting executed let's get across some background aspect DBT has multiple types of adapters corresponding to the respective sync which we are considering as the warehouse it can be micro in the Microsoft ecosystem it can be a Microsoft SQL Server fabric Azure scaps but outside Microsoft ecosystem it can also consider we can also consider snowflake data bricks gcp hence corresponding to the respective destination DBT has different types of packages since we are focused more towards the fabric SQL database hence we installed DBT D the SQL Server adapter now let's check whether all the corresponding dependencies got installed or not so for that we have the PIP list command that would state that okay I have installed the DBT adapters along with that the DBT SQL Server adapter and all its corresponding dependencies now as DBT is an open-source tool it follows a pattern so here I have set up or installed I have cloned a git repository that contains the respective structure in which one expects to execute the command now one of the main file that acts as a config file for the DBT adapter is the project. yml file so before moving to the actual execution let's get a little bit about the file so within this project. yml file basically we are defining that anything that is within the models folder should be executed as the model path seeds folder should be considered as the seed path and for DBT also serves certain aspects like automated unit testing so in order for test execution DBT has to come and search in the test folder equivalent of the corresponding folder so let's proceed with the execution since this is the location wherein I have all the folders for the corresponding execution present I need to update the directory of my Powershell command line to that path so I'll be executing the change directory command now we are within the fold uh folder location that host all the files which we need to execute via DPT now the first command is we need to establish a connection to a destination that is the fabric SQL database so for that the command is DBT debug command now that tries to create an connection with a destination on which you want to execute a necessary do SQL queries now what this does is it goes across this location and checks for the profiles. yml file that is present within this location now in that profiles. yml file it checks for the corresponding server database detail and the corresponding authentication types so in our demo we have taken the service principal authentication to establish a connection to the fabric SQL database and this states that we you are able to successfully establish the connection to the fabric SQL database nice awesome this seems pretty straightforward to get set up which is great uh one quick question is what what other types of authentication types are supported for fabric SQL database so now the DB DBT SQL Server adapter that we installed supports different types of authentication as it supports different databases within Microsoft ecosystem it can be Microsoft SQL Server across on premises and is as SQL databases and even fabric SQL database now since it supports this set of databases it supports multiple types of authentications which can be Windows authentication SQL Server authentication active directory but since Microsoft fabric is hosted on is a cloud SAS aspect it does not support Windows or software uh SQL authentication so the only authentications that are supported is active directory username and password assuming the username and password have MFA disabled Azure CLI authentication and service principal authentication nice makes sense and and service principle is what you used here correct so this I can check in the uh corresponding execution aspect where I have provided the respective client ID and the active directory service principal authentication now we were able to successfully establish the connection with the uh fabric SQL database now before I proceed with the next command earlier you might have seen that okay let me delete this logs section folder section correct now let me execute a command called DBT seed now what it does is DBT is a tool for analytical team who are not familiar with major uh integration tools for them in case if they want to analyze certain Master data or if they want to inest certain master data within the database ecosystem without having the dependency on the data engineers and assuming that data is a non-sensitive data correct what they can do is they can upload or they can create a file within the folder as Master data files and then a simple command called DBT seed would ensure that all those file get converted as a table within the the respective destination and the data within those files are automatically loaded nice so before I execute it let's go and see the fabric SQL database now this database is an empty database now let me go and execute the command DBT seed that would ensure that the master data files that are present within the seed folder would get converted into tables within the respective database and the corresponding data within those files would also be inserted within those tables so that's the power of DBT which enables analytical team to work on their own for exploratory data analytical purpose now once I executed the DBT seed command a logs folder automatically got created so let's check what does the log file contain so the long file initially since we establish the connection to the SQL a fabric SQL database it provides the authentication type that was used and it was a successful connection now post that we executed the DBT seed command now with the dbtc command it initially checks whether the table is already present in the respective destination or not based on the information schema or the metadata within the respective database and then depending on that it creates the necessary SQL scripts at the back end like create table nice and post that it automatically inserts create scripts to insert the data into the respective tables now since this is a log file it masks all the data that was present within that in the log file nice so now let's go and check our fabric SQL database so let me go and and refresh the tables section and VOA we are able to see there are three tables corresponding to the three files that were created and nice those do contain the necessary data within that so now let me execute the DBT seed command again let's check what happens maybe we would guess that it would skip this because it would see that it was already there right or it would skip the table creation because the table is already present but imagine if someone had updated certain records within the seed file correct so what it does is why the metadata it checks whether the table is already present since the table is already present let me close the log file and reopen that since the table is already present what it would do is and since it's an incremental log file so since this was late it contains all the previous records until unless we have explicitly deleted that log file correct now what it would do is it would check whether the table is already present or not why the information schema correct since the table was already present it to truncate the table ah and then it would again insert the record because DBT tool does not know correct whether there have been any changes made within the existing file or not right then coming to the next major command that is the DBT run now the main purpose of DBT is to convert the SQL statements into the respective tables or views within the destinations so here I have a SQL file that I'll open with in the visual studio now what does that do sqls file contain it contains the respective logic that I the respective transformation that I want to convert into my destination as either a table or a view now I see that there are different types of different SQL statements so in which order does it really execute so for that basic Ally what DBT does it it creates a directed ay graph at the back end wherein it creates all the dependencies within one another and then once we execute the DBT run command it connects those dependencies and execute them in the sequential order in which the dependencies are present so now the DBT run command is initially converting the first three objects into a view since those are present within the staging folder now why did it convert the objects that are present within the staging folder into the view and the outside objects that is customer and orders as a table and inserted the necessary records and I say outside at the model layer I can see that there are customers and orders the everything relies on the project. yml file that we initially so which act as the config file right here we have stated that anything within the japel shop folder should be converted to a table until and unless it is explicitly present within staging folder that should be converted as view so now post that execution earlier post DBT seed command I was able to see only three tables now there would be two additional tables the customers and the orders and there would be two views that would be created nice so this is the power of DBT now the connection details that I used to connect to the fabric SQL database was the SQL database endpoint sorry was the fabric SQL database endpoint under fabric SQL database and the connection strings I was able to get the server name yep and the database name now observe one thing this is database. fabric. microsoft.com now fabric SQL database also provides a SQL analytics endpoint which is equivalent to the SQL Server itself so in that case let me copy the SQL connection string and try connecting to this via this endpoint so for that there is a file wherein I have to go and update that is the profiles. yml file so profiles. yml file acts as the config file that contains connection details to our destination since the name is fabric SQL database for the SQL analytic end point and I updated this I'm updating this server name and you you can observe that it's now data. fabric. microsoft.com and all other connections would remain same so now let me clear the command line and then let me since I have updated the connection string let me reestablish the connection via VIA DBT debug this is again trying to establish a connection to the fabric SQL database but now by the SQL analytics Endo right so by the time this is getting exit uh connected okay so this got connected now let's execute the DBT seed command [Music] H so post executing the DBT seed command I expect the files to be converted within the to the respective tables correct but I got an error stating that table hint no lock is not allowed the reason for that is the Fab in fabric any SQL analytics endpoints are read only they do not support right back and since in case of DBT seed we are creating the table and inserting data within the table it failed stating that it's not supported right so in order to establish a connection to the fabric SQL database one has to leverage this fabri SQL database connection string and not the SQL analytics endpoint connection string got it makes sense and and is that why you know you know if we already have the DBT fabric adapter is that why we need the DBT SQL Server adapter or is there another reason okay now the thing is fabric SQL database is more towards is leveraging the engine of the AO SQL database itself that is a traditional oltp structured database now the DBT fabric adapter that is created is for the fabric Warehouse which is more towards the signup serverless version and as we saw correct whenever we execute any DBT command at the back end within the log file it autog generates certain scripts like trunet table now couple of months back fabric whereof didn't support trunet statement it only supported delete statement so the fabric adapter used to have delete as a statement other than trunet so one can use DBT fabric adapter to access the fabric SQL database but it would not take the full benefits of the fabric SQL database or the Azure SQL database ENT so let's test this out correct so in another server I have installed the DBT fabric adapter so let me quickly open the powers shell here and again let me try establishing the connection or uh update the directory to the corresponding directory now in case if I execute DBT debug this is a fabric adapter that I am using it will still be able to authenticate and connect to the fabric SQL database I would still be able to execute certain commands like DBT seed because now it just do it differently now it does the same because uh trunet statement is Now supported within the fabric Warehouse as well hence it supports but now in case if I execute DBT run the statements or the SQL queries that get created in the back end would be as for the fabric vrow syntax and that might not be fully compatible with the fabric SQL database thereby leading to certain failures as we saw the GitHub repository is the same that I cloned across the fabric SQL Server ad fabric adapter and the SQL Server adapter but in this case it failed because the keyword that I'm using within the SQL file is not compatible with fabric hence I'll have to make tweaks within my logic so the best bifurcation would be in case if you want to leverage fabric warehouse and DBT then use the DBT fabric adapter but in case if one wants to leverage the benefits of DBT tool for fabric SQL database best is to Leverage The DBT SQL Server adapter now one catch is DBT SQL Server adapter is a community adapter that has been built by the community whereas the DBT fabric adapter has been created by the DBT lamps organization hence initially I stated there two versions of DBT correct a cloud version and a CLI version the cloud version a paid version where you need a license and it's only available for the company supported adapters not the community adapters so fabric adapter would have a cloud version and a CLI version but for fabric SQL database since the DBT SQL Server adapter is a community adapter that has been created and maintained by the community it only has a c version so one has to set up everything for himself I see got it awesome cool well n thanks so much for coming on the show I learned a lot about DBT some of the nuances and some of the great benefits that you can get when you're we using this and maybe just getting started uh so thanks so much for coming on the show uh for our viewers if you like this episode go ahead give it a like leave us a comment let us know what you're using DBT for uh we'll put some links in the description for you to learn more and we hope to see you next time on data expose MVP Edition [Music]