db creation in fabric lakehouse

[NikhilMysari]

In the below article while creating a table in silver layer the code is mentioned as follows .  However, nowhere in the article there is a mention of creating the sales db . Is this ecxpected ? Do we have to create the database before ? , if yes what is the step in fabric.

# Define the schema for the sales_silver table 
from pyspark.sql.types import * 
from delta.tables import * 
DeltaTable.createIfNotExists(spark) \ 
.tableName("sales.sales_silver") \ 
.addColumn("SalesOrderNumber", StringType()) \ 
.addColumn("SalesOrderLineNumber", IntegerType()) \ 
.addColumn("OrderDate", DateType()) \ 
.addColumn("CustomerName", StringType()) \ 
.addColumn("Email", StringType()) \ 
.addColumn("Item", StringType()) \ 
.addColumn("Quantity", IntegerType()) \ 
.addColumn("UnitPrice", FloatType()) \ 
.addColumn("Tax", FloatType()) \ 
.addColumn("FileName", StringType()) \ 
.addColumn("IsFlagged", BooleanType()) \ 
.addColumn("CreatedTS", DateType()) \ 
.addColumn("ModifiedTS", DateType()) \ 
.execute()

 When I follow the steps and execute the code, it is trying to write to default database , therefore I get the error that schema is not found.

https://microsoftlearning.github.io/mslearn-fabric/Instructions/Labs/03b-medallion-lakehouse.html 

[NikhilMysari]

Hello @svenchio , 

Thanks for the detailed explanation . I see that even with schema support enabled during lakehouse creation the default db is 'dbo' . Also, the documentation missed this step of manually creating the "sales" schema , I was under the impression that it is auto-created when we run the code mentioned . However, this is a good leanring that schema creation is manual . Thanks again for the repsone !

[svenchio]

Great @NikhilMysari  I'm glad that it was clear ... best of lucks with your exam preps, mark my answer as a solution 😉 for other people to benefit with the explanation. 

[svenchio · Accepted solution]

Hi @NikhilMysari, as I usually do, I'll go answer each of your question one at a time: 

#1. Is this expected?

Answer: That I would expect if for the lab instructions would be a bit more clear on this, step 9 indicates "[...] you’ll define the schema for the sales_silver table in the sales database using Delta Lake format" 

But they are missing and important pre-requisite, that the lakeshouse has schema support, as shown in the image below, when you create a new lakehouse you can choose it to have schema support or not. 

#2. Do we have to create the lakehouse/schema before ? , if yes what is the step in fabric. 

Answer:  First you to need to need to create a lakehouse and check the box Lakehouse schemas (as shown on the 2nd picture from answer #1),  and then, from the lakehouse with schema support, right click on Tables [More options ...]  > New schema, and name as sales, attach the lab notebook to this lakehouse and run, it will work this time! 

 #3.  When I follow the steps and execute the code, it is trying to write to default database , therefore I get the error that schema is not found.

Answer.  In lakehouses with no schema support  the default schema name is dbo, not default; nonehtless, neither of default.sales_silver nor dbo.sales_silver will work, because the syntax with a dot is intereprested by the engine as schema.table, and since your lakehouse do not have schema support, you will get an error... you need to name your tables and use naming conventions with other allows characters like '_' 

Uffff 😅 ! I hope I answer all of your questions! I would appreciate a kudos and mark this as solution, reach out if you have any additional questions. All the very best and best of lucks with your exam preparations 💪