I'm trying to run a dbt project which reads data from ADLS and writes back to ADLS using a Databricks Workflow. When I run the same project from my local machine (using python virtual environment from Visual Studio Code), it's running perfectly fine but it's throwing an error while running it from Databricks Workflow.
Versions-
dbt version: 1.6.0
python version: 3.9.5
databricks adapter version: 1.6.1
Databricks Workflow Details-
Type: dbt
Source: GitHub Enterprise
Commands: dbt deps, dbt debug, dbt run --threads 16 --select tag:<model_tag>
Dependent Libraries: dbt-databricks>=1.0.0,<2.0.0, databricks-sql-connector==2.7.0
profiles.yml file (in the project folder on Git):
**using Job Cluster here and not SQL Warehouse/Endpoint
host: adb-****************.**.azuredatabricks.net
http_path: /sql/protocolv1/o/****************/****-******-********
http_path: /sql/protocolv1/o/****************/****-******-********
token: dapi********************************************
Error Message:
While running the Databricks workflow, I'm getting the below error:
15:59:28 Configuration:
15:59:28 profiles.yml file [OK found and valid]
15:59:28 dbt_project.yml file [OK found and valid]
15:59:28 Required dependencies:
15:59:28 - git [OK found]
15:59:28 Connection:
15:59:28 host: [REDACTED]
15:59:28 http_path: [REDACTED]
15:59:28 schema: default
15:59:28 Registered adapter: databricks=1.6.1
15:59:29 databricks-sql-connector adapter: Error during request to server: {"method": "OpenSession", "session-id": null, "query-id": null, "http-code": 403, "error-message": "", "original-exception": "", "no-retry-reason": "non-retryable error", "bounded-retry-delay": null, "attempt": "1/30", "elapsed-seconds": "0.7869570255279541/900.0"}
15:59:29 Connection test: [ERROR]
15:59:28 Configuration:
15:59:28 profiles.yml file [OK found and valid]
15:59:28 dbt_project.yml file [OK found and valid]
15:59:28 Required dependencies:
15:59:28 - git [OK found]
15:59:28 Connection:
15:59:28 host: [REDACTED]
15:59:28 http_path: [REDACTED]
15:59:28 schema: default
15:59:28 Registered adapter: databricks=1.6.1
15:59:29 databricks-sql-connector adapter: Error during request to server: {"method": "OpenSession", "session-id": null, "query-id": null, "http-code": 403, "error-message": "", "original-exception": "", "no-retry-reason": "non-retryable error", "bounded-retry-delay": null, "attempt": "1/30", "elapsed-seconds": "0.7869570255279541/900.0"}
15:59:29 Connection test: [ERROR]
Detailed Error after running with verbose logs:
07:24:58 Acquiring new databricks connection 'master'
07:24:58 Acquiring new databricks connection 'list_schemas'
07:24:58 Acquiring new databricks connection 'list_schemas'
07:24:58 Acquiring new databricks connection 'list_schemas'
07:24:58 Using databricks connection "list_schemas"
07:24:58 Using databricks connection "list_schemas"
07:24:58 Using databricks connection "list_schemas"
07:24:58 Acquiring new databricks connection 'list_schemas'
07:24:58 On list_schemas: /* {"app": "dbt", "dbt_version": "1.6.0", "dbt_databricks_version": "1.6.1", "databricks_sql_connector_version": "2.7.0", "profile_name": "analytics_engineering_pipeline_synapse_app_sp", "target_name": "databricks_job", "connection_name": "list_schemas"} */
show databases
07:24:58 Opening a new connection, currently in state init
07:24:58 Using databricks connection "list_schemas"
07:24:58 On list_schemas: /* {"app": "dbt", "dbt_version": "1.6.0", "dbt_databricks_version": "1.6.1", "databricks_sql_connector_version": "2.7.0", "profile_name": "analytics_engineering_pipeline_synapse_app_sp", "target_name": "databricks_job", "connection_name": "list_schemas"} */
show databases
07:24:58 Opening a new connection, currently in state init
07:24:59 databricks-sql-connector adapter: Error during request to server: {"method": "OpenSession", "session-id": null, "query-id": null, "http-code": 403, "error-message": "", "original-exception": "", "no-retry-reason": "non-retryable error", "bounded-retry-delay": null, "attempt": "1/30", "elapsed-seconds": "1.3058898448944092/900.0"}
07:25:00 Databricks adapter: <class 'databricks.sql.exc.RequestError'>: Error during request to server
07:25:00 databricks-sql-connector adapter: Error during request to server: {"method": "OpenSession", "session-id": null, "query-id": null, "http-code": 403, "error-message": "", "original-exception": "", "no-retry-reason": "non-retryable error", "bounded-retry-delay": null, "attempt": "1/30", "elapsed-seconds": "2.635369300842285/900.0"}
07:25:00 Databricks adapter: Database Error
Error during request to server
07:25:00 Databricks adapter: Error while running:
macro list_schemas
07:25:00 Databricks adapter: session-id: None
07:25:00 Databricks adapter: Database Error
Error during request to server
07:25:00 Databricks adapter: Error while running:
macro list_schemas
07:25:00 Databricks adapter: Runtime Error
Database Error
Error during request to server
07:25:00 Databricks adapter: Error while running:
/* {"app": "dbt", "dbt_version": "1.6.0", "dbt_databricks_version": "1.6.1", "databricks_sql_connector_version": "2.7.0", "profile_name": "analytics_engineering_pipeline_synapse_app_sp", "target_name": "databricks_job", "connection_name": "list_schemas"} */
show databases
Tried installing an older version (2.1.0) of databricks-sql-connector (instead of 2.7.0) and surprisingly a new error message appeared. Don't know how to fix this now.
