Microsoft Fabric With dbt
UPDATE 2023-06-28: You can find more complete detailed and optimized examples for this content in the??DBT in Microsoft Fabric: MY JOURNAL
Microsoft Fabric is a Data Cloud Platform that combines Power BI, Synapse, and Data Factory. This platform allows for the creation of an Azure Synapse Data Warehouse within Fabric.
After a two-week Public Preview period, as of today (2023-06-15), we can now utilize dbt in Fabric:
Let's proceed with the following steps:
1: Create a Synapse Data Warehouse named DATAWH and obtain the 'SQL Connect String':
2: This adapter requires ODBC driver 18:? Window
3: Open window PowerShell then execute the following code step by step:
领英推荐
cd D:\PythonEnv # 0. move D Drive
python -m venv fabric-env # 1. create enviroment
fabric-env\Scripts\activate # 2. acitve enviroment
pip install?dbt-fabric # 3. install driver
D:\PythonEnv\fabric-env\Scripts\python.exe -m pip install --upgrade pip # 4.Update
dbt init fabricdemo # 4. Init Project, chose 1
4, I moved profiles.yml file into my new project and then confided it as follows:
fabricdemo:
? target: dev
? outputs:
? ? dev:
? ? ? type: fabric
? ? ? driver: 'ODBC Driver 18 for SQL Server' # (The ODBC Driver installed on your system)
? ? ? server: 'SQL Connect String'
? ? ? port: 1433
? ? ? database: Data Warehouse name
? ? ? schema: dbo
? ? ? authentication: ActiveDirectoryInteractive
? ? ? user: [email protected]
Finally, Test it out. I ran the code: dbt debug -> there were no bugs. Then, I ran: dbt run ->?I encountered the following error:
Maybe I got this error by this features is unsupported Link: "CTAS supports select on views/tables with underlying table definition. CREATE TABLE AS SELECT 1 AS Id - is not supported." . Therefore, I modified the sample code as follows:??
my_first_dbt_model.sql
{{ config(materialized='table') }}
SELECT
*
FROM Date
my_second_dbt_model.sql
select top 100 *
from {{ ref('my_first_dbt_model') }}
Finally, It worked.