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 @ pvenkat@microsoft.com