Dbt build hanging

[rorydcooke]

Hi there,

name = "dbt-adapters"
version = "1.1.1"
name = "dbt-common"
version = "1.0.4"
name = "dbt-core"
version = "1.8.0"
name = "dbt-extractor"
version = "0.5.1"
name = "dbt-fabric"
version = "1.8.4"
name = "dbt-osmosis"
version = "0.12.10"
name = "dbt-semantic-interfaces"
version = "0.5.1"
name = "pyodbc"
version = "5.1.0"

I have connected dbt to my fabric data warehouse. When i run dbt build --log-level debug I can see that my command gets stuck (runs indefinetly) at various points. The command was running fine up until 17/5/24. No changes were made to the fabric tennant or the dbt code/adapters between the last successful attempt and the failed one.

Initally it was stuck at the below step:

USE [ifx-data-warehouse];
with base as (
select
DB_NAME() as [database],
t.name as [name],
SCHEMA_NAME(t.schema_id) as [schema],
'table' as table_type
from sys.tables as t
union all
select
DB_NAME() as [database],
v.name as [name],
SCHEMA_NAME(v.schema_id) as [schema],
'view' as table_type
from sys.views as v
)
select * from base
where [schema] like 'dbo_mart_operations'

When i killed all active sessions on the fabric sql server, paused and resumed the fabric instance it was able to run most of the mosdels before getting stuck here (the view was created sucessfully but we couldnt create the table and insert the data).:

363 of 387 OK created sql view model dbo_intermediate_tearsheet.int_parse_path . [OK in 2.10s]
16:05:17 Finished running node model.inflexion_datalake.int_parse_path
16:05:17 Began running node model.inflexion_datalake.deal_new_deal_date_table
16:05:17 364 of 387 START sql table model dbo.deal_new_deal_date_table .................. [RUN]
16:05:17 Re-using an available connection from the pool (formerly model.inflexion_datalake.int_parse_path, now model.inflexion_datalake.deal_new_deal_date_table)
16:05:17 Began compiling node model.inflexion_datalake.deal_new_deal_date_table
16:05:17 Writing injected SQL for node "model.inflexion_datalake.deal_new_deal_date_table"
16:05:17 Began executing node model.inflexion_datalake.deal_new_deal_date_table
16:05:17 Using fabric connection "model.inflexion_datalake.deal_new_deal_date_table"
16:05:17 On model.inflexion_datalake.deal_new_deal_date_table: /* {"app": "dbt", "dbt_version": "1.8.0", "profile_name": "inflexion_datalake_storage", "target_name": "local_test", "node_id": "model.inflexion_datalake.deal_new_deal_date_table"} */
USE [ifx-data-warehouse];
with base as (
select
DB_NAME() as [database],
t.name as [name],
SCHEMA_NAME(t.schema_id) as [schema],
'table' as table_type
from sys.tables as t
union all
select
DB_NAME() as [database],
v.name as [name],
SCHEMA_NAME(v.schema_id) as [schema],
'view' as table_type
from sys.views as v
)
select * from base
where [schema] like 'dbo'
and [name] like 'deal_new_deal_date_table'

16:05:17 Opening a new connection, currently in state closed
16:05:17 fabric adapter: Using connection string: DRIVER={ODBC Driver 17 for SQL Server};SERVER=zlcm55dk7qhebagj645g7je2tq-n5kxj5f664nunj55gcmsdmhknm.datawarehouse.fabric.microsoft.com;Database=ifx-data-warehouse;encrypt=Yes;TrustServerCertificate=No;APP=dbt-fabric/1.8.4;ConnectRetryCount=1
16:05:17 fabric adapter: Connected to db: ifx-data-warehouse
16:05:17 SQL status: OK in 0.0 seconds
16:05:17 Existing Relation type is
16:05:17 Using fabric connection "model.inflexion_datalake.deal_new_deal_date_table"
16:05:17 On model.inflexion_datalake.deal_new_deal_date_table: /* {"app": "dbt", "dbt_version": "1.8.0", "profile_name": "inflexion_datalake_storage", "target_name": "local_test", "node_id": "model.inflexion_datalake.deal_new_deal_date_table"} */

  USE [ifx-data-warehouse];
  select
      sch.name as schema_name,
      obj.name as view_name
  from sys.sql_expression_dependencies refs
  inner join sys.objects obj
  on refs.referencing_id = obj.object_id
  inner join sys.schemas sch
  on obj.schema_id = sch.schema_id
  where refs.referenced_database_name = 'ifx-data-warehouse'
  and refs.referenced_schema_name = 'dbo'
  and refs.referenced_entity_name = 'deal_new_deal_date_table_temp_view'
  and refs.referencing_class = 1
  and obj.type = 'V'

16:05:17 SQL status: OK in 0.0 seconds
16:05:17 Using fabric connection "model.inflexion_datalake.deal_new_deal_date_table"
16:05:17 On model.inflexion_datalake.deal_new_deal_date_table: /* {"app": "dbt", "dbt_version": "1.8.0", "profile_name": "inflexion_datalake_storage", "target_name": "local_test", "node_id": "model.inflexion_datalake.deal_new_deal_date_table"} */

USE [ifx-data-warehouse];
EXEC('DROP view IF EXISTS "dbo"."deal_new_deal_date_table_temp_view";');

16:05:18 SQL status: OK in 0.0 seconds
16:05:18 Using fabric connection "model.inflexion_datalake.deal_new_deal_date_table"
16:05:18 On model.inflexion_datalake.deal_new_deal_date_table: /* {"app": "dbt", "dbt_version": "1.8.0", "profile_name": "inflexion_datalake_storage", "target_name": "local_test", "node_id": "model.inflexion_datalake.deal_new_deal_date_table"} */

  USE [ifx-data-warehouse];
  select
      sch.name as schema_name,
      obj.name as view_name
  from sys.sql_expression_dependencies refs
  inner join sys.objects obj
  on refs.referencing_id = obj.object_id
  inner join sys.schemas sch
  on obj.schema_id = sch.schema_id
  where refs.referenced_database_name = 'ifx-data-warehouse'
  and refs.referenced_schema_name = 'dbo'
  and refs.referenced_entity_name = 'deal_new_deal_date_table_temp_view'
  and refs.referencing_class = 1
  and obj.type = 'V'

16:05:18 SQL status: OK in 0.0 seconds
16:05:18 Writing runtime sql for node "model.inflexion_datalake.deal_new_deal_date_table"
16:05:18 Using fabric connection "model.inflexion_datalake.deal_new_deal_date_table"
16:05:18 On model.inflexion_datalake.deal_new_deal_date_table: /* {"app": "dbt", "dbt_version": "1.8.0", "profile_name": "inflexion_datalake_storage", "target_name": "local_test", "node_id": "model.inflexion_datalake.deal_new_deal_date_table"} */

USE [ifx-data-warehouse];



EXEC('create view "dbo"."deal_new_deal_date_table_temp_view" as -- depends_on: "ifx-data-warehouse"."dbo_staging_dealcloud"."stg_deal"

WITH date_range AS (
SELECT
MIN(new_deal_date) AS start_date,
MAX(new_deal_date) AS end_date
FROM "ifx-data-warehouse"."dbo_staging_dealcloud"."stg_deal"
),
calendar AS (
SELECT
DATEADD(day, n, start_date) AS date
FROM
date_range, dbo.numbers
WHERE
DATEADD(day, n, start_date) <= end_date
)
SELECT
date,
YEAR(date) AS year,
MONTH(date) AS month,
DAY(date) AS day,
CAST(FORMAT(date, ''dddd'') AS VARCHAR(30)) AS day_of_week, -- Use FORMAT for Azure Synapse instead of DATENAME
CAST(FORMAT(date, ''MMMM'') AS VARCHAR(30)) AS month_name
FROM
calendar
;');

  EXEC('CREATE TABLE [ifx-data-warehouse].[dbo].[deal_new_deal_date_table] AS (SELECT * FROM [ifx-data-warehouse].[dbo].[deal_new_deal_date_table_temp_view]);');



  
  

USE [ifx-data-warehouse];
EXEC('DROP view IF EXISTS "dbo"."deal_new_deal_date_table_temp_view";');

If i can give any other info that may be of use please let me know.

Thank you for your help

[prdpsvs]

@rorydcooke , Can you create a new workspace and try with another data warehouse if possible? Are you still seeing this issue?
Logs do not suggest or show where and how it is stuck.

If you are still seeing the same issue, can you connect with me over email? We may need ODBC trace to figure out what's going on. you can reach me @ [email protected]