bug: Azure Compliance pack - Issue with Model / View in Snowflake

[KarthikVenkatraman]

Is there an existing issue for this?

• I have searched the existing issues

Current Behavior

I downloaded the Azure Compliance pack and followed the steps to create the necessary model(s) in my snowflake setup, by executing the specified dbt commands. While this succeeded in creating the model / view into my snowflake schema, when I try to query the data in the snowflake view (for any of the models), I get an error that says Boolean value fail is not recognized .

Expected Behavior

The model / view should populate the underlying data.

CloudQuery (redacted) config

  target: prod
  outputs:
    prod:
      type: snowflake
      account: <my snowflake account>
      user: <my snowflake user>
      password: <my snowflake pwd>
      role: <my snowflake role>
      database: <my snowflake db>
      warehouse: <my snowflake warehouse>
      schema: <my snowflake schema>
      threads: 1
      client_session_keep_alive: False
      query_tag: "cloudquery"
      # optional
      connect_retries: 0 # default 0
      connect_timeout: 10 # default: 10
      retry_on_database_errors: False # default: false
      retry_all: False  # default: false
      reuse_connections: False # default: false (available v1.4+)```

### Steps To Reproduce

_No response_

### CloudQuery (redacted) logs

```�[0m12:09:10.901459 [debug] [Thread-1 (]: Opening a new connection, currently in state closed
�[0m12:09:11.892521 [debug] [Thread-1 (]: SQL status: SUCCESS 1 in 1.0 seconds
�[0m12:09:11.897878 [debug] [Thread-1 (]: Timing info for model.azure_compliance.azure_compliance__cis_v2_1_0 (execute): 12:09:10.872199 => 12:09:11.897595
�[0m12:09:11.898441 [debug] [Thread-1 (]: On model.azure_compliance.azure_compliance__cis_v2_1_0: Close
�[0m12:09:11.970444 [debug] [Thread-1 (]: Sending event: {'category': 'dbt', 'action': 'run_model', 'label': '7c3adf80-4383-416b-a288-7e59204eb640', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x1451e6910>]}
�[0m12:09:11.971247 [info ] [Thread-1 (]: 6 of 6 OK created sql view model REPORTING.azure_compliance__cis_v2_1_0 ........ [�[32mSUCCESS 1�[0m in 1.17s]
�[0m12:09:11.972084 [debug] [Thread-1 (]: Finished running node model.azure_compliance.azure_compliance__cis_v2_1_0
�[0m12:09:11.974797 [debug] [MainThread]: Connection 'master' was properly closed.
�[0m12:09:11.975170 [debug] [MainThread]: Connection 'model.azure_compliance.azure_compliance__cis_v2_1_0' was properly closed.
�[0m12:09:11.975534 [info ] [MainThread]: 
�[0m12:09:11.975876 [info ] [MainThread]: Finished running 6 view models in 0 hours 0 minutes and 5.96 seconds (5.96s).
�[0m12:09:11.977364 [debug] [MainThread]: Command end result
�[0m12:09:11.990699 [info ] [MainThread]: 
�[0m12:09:11.990934 [info ] [MainThread]: �[32mCompleted successfully�[0m
�[0m12:09:11.991076 [info ] [MainThread]: 
�[0m12:09:11.991224 [info ] [MainThread]: Done. PASS=6 WARN=0 ERROR=0 SKIP=0 TOTAL=6
�[0m12:09:11.992933 [debug] [MainThread]: Resource report: {"command_name": "run", "command_success": true, "command_wall_clock_time": 6.9598136, "process_user_time": 2.674667, "process_kernel_time": 2.177415, "process_mem_max_rss": "216743936", "process_in_blocks": "0", "process_out_blocks": "0"}
�[0m12:09:11.993155 [debug] [MainThread]: Command `dbt run` succeeded at 12:09:11.993103 after 6.96 seconds
�[0m12:09:11.993310 [debug] [MainThread]: Sending event: {'category': 'dbt', 'action': 'invocation', 'label': 'end', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x10aa08ed0>, <snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x10aa09050>, <snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x109cc90d0>]}
�[0m12:09:11.993453 [debug] [MainThread]: Flushing usage events```

### CloudQuery version

5.15.2

### Additional Context

_No response_

### Pull request (optional)

- [ ] I can submit a pull request

[k-rheinheimer]

hey @KarthikVenkatraman - I am having some trouble reproducing this, can you provide the queries you were trying to run? Also, if possible, can you send the column types of the view(s) that were created?

[KarthikVenkatraman]

Hi @k-rheinheimer

I was just trying to view the data in the following views:

select * from "AZURE_COMPLIANCE__CIS_V2_0_0"
select * from "AZURE_COMPLIANCE__CIS_V2_1_0"
select * from "AZURE_COMPLIANCE__CIS_V1_3_0"

However this does not work successfully.

I keep getting an error like
Boolean value 'fail' is not recognized or Boolean value 'pass' is not recognized

Please find the column types for the respective views:
"AZURE_COMPLIANCE__CIS_V2_0_0" :

"AZURE_COMPLIANCE__CIS_V2_1_0" :

"AZURE_COMPLIANCE__CIS_V1_3_0" :

Please let me know If you require any further info.

[k-rheinheimer]

Thank you for this info! I will look into this and get back to you.

[k-rheinheimer]

Hey @KarthikVenkatraman while I am looking into this, would you mind sharing the redacted azure source config you are using? If you are unable to provide that, can you let me know which version of the azure plugin you are using?

If you could also share a redacted version of the snowflake destination config, I can try to replicate as closely as possible.

[KarthikVenkatraman]

Hi @k-rheinheimer

Please find my azure (source) config below

spec:
  name: "azure"
  path: "cloudquery/azure"

  version: "v13.1.0"
  tables: [
    "*"
    ]
  destinations: ["snowflake"]
  spec:
    subscriptions: ["xxxx-xxxx-xxxx-xxxx-xxxx"]

The destination (snowflake) config is as below:

kind: destination
spec:
  name: snowflake
  path: cloudquery/snowflake
  version: "v3.4.2"
  write_mode: "overwrite-delete-stale"
  # batch_size: 10000 # optional
  # batch_size_bytes: 5242880 # optional
  spec:
    connection_string: XXXX:[email protected]/XXXX/XXXX?role=XXXX&warehouse=XXXXX

On the Azure plugin version I pointed to the latest version (13.1.0) of the Azure source plugin itself and also the latest version of the Azure Compliance pack (cloudquery_transformation_azure-compliance-premium_v0.8.2) just today and still getting the same issue only.