Open-source data observability for modern data teams

Overview

I tried to monitor dbt tests with jaffle_shopw by following the documentation. But, I was not able to upload artifacts due to the lack of the destination table. If I am correct, we have to create the table table_monitors_config a head. But, the documentation doesn't describes table_monitors_config. How can we prepare the table?

Environments

• Python 3.8
• dbt 1.0.3
• elementary 0.3.2.

Error message

09:22:03  Running 2 on-run-end hooks
09:22:33  1 of 2 START hook: jaffle_shop.on-run-end.0..................................... [RUN]
09:22:33  1 of 2 OK hook: jaffle_shop.on-run-end.0........................................ [OK in 0.00s]
09:22:33  2 of 2 START hook: elementary.on-run-end.0...................................... [RUN]
09:22:33  2 of 2 OK hook: elementary.on-run-end.0......................................... [OK in 0.00s]
09:22:33
09:22:33
09:22:33  Finished running 8 view models, 9 incremental models, 2 table models, 3 seeds, 17 tests, 3 hooks in 44.13s.
09:22:33
09:22:33  Completed with 2 errors and 0 warnings:
09:22:33
09:22:33  Runtime Error in model filtered_information_schema_columns (models/edr/metadata_store/filtered_information_schema_columns.sql)
09:22:33    404 Not found: Table sandbox-project:jaffle_shop_elementary.table_monitors_config was not found in location asia-northeast1
09:22:33
09:22:33    (job ID: d0f16aa6-b33d-493f-b0d1-8b934a090682)
09:22:33
09:22:33  Runtime Error in model filtered_information_schema_tables (models/edr/metadata_store/filtered_information_schema_tables.sql)
09:22:33    404 Not found: Table sandbox-project:jaffle_shop_elementary.table_monitors_config was not found in location asia-northeast1
09:22:33
09:22:33    (job ID: 03fc0b75-262f-49f8-8957-96b55268e9ac)

Hey all,

I added the elementary package to the dbt repository and used dbt run to create all required tables. But when I tried to add column-level anomalies, the dbt run gave me an error as

19:10:32    001003 (42000): SQL compilation error:
19:10:32    syntax error line 7 at position 19 unexpected '""'.
19:10:32    syntax error line 10 at position 15 unexpected ''day''.
19:10:32    syntax error line 10 at position 26 unexpected '('.
19:10:32    syntax error line 10 at position 40 unexpected 'as'.
19:10:32    syntax error line 12 at position 1 unexpected ')'.

The configuration I added to the yml file is as:

  - name: table_name
    config:
      elementary:
        timestamp_column: "_inserted_at"
    tests:
      - elementary.table_anomalies:
          table_anomalies:
            - row_count
            - freshness
    columns:
      - name: "id"
        description: " "
        quote: true
        tests:
          - not_null
          - unique
          - elementary.column_anomalies:
              column_anomalies:
                - missing_count
                - min_length

However, table-level anomalies worked as expected. I tried to look up compiled SQL files from target/compiled and target/run, but couldn't find any models relevant to this problem. Any ideas?

Hi team, I tried to run elementary, but it just got stuck after logging into Snowflake. Nothing has changed on my screen at least for an hour.

Do you have any ideas about what could go wrong?

Hi, I'm testing your dbt package for our data warehouse which is placed on Google BigQuery The generated sql scripts are producing erros. One big error is coming from the on-run-end hook:

Database Error
  Syntax error: Illegal escape sequence: \E at [6:566]

I've checked the generated SQL code and it seems that all backslashes coming from the model names are producing an error e.g. this part: ...'models\business_vault\EXCHANGERATESAPI\msrglB\currency_datedexchangerates_xrio_brs.sql','business_vault\EXCHANGERATESAPI\msrglB\currency_datedexchangerates_xrio_brs.sql','2022-04-05 11:04:47')

I think there is some escaping missing.

The current format does not support workflows, as it only supports key-value pairs.

{
  "description": <ALERT_DESCRIPTION>,
  "table": <table_name>,
  "detected_at" <detected_at>
}

Possible solution: Config property where you can set the type of slack integration you are using (either 'workflow' or 'webhook'). If you set the config to 'workflow' the response body would be formatted in the proper way.

Overview

We have to surround the schema information table with back quotes.

Error logs

05:20:19  target not specified in profile 'elementary', using 'default'
Pulling query history from BigQuery (!)  in 0.9s (4.32/s)
Traceback (most recent call last):
  File "/Users/yu/anaconda2/envs/python3.8/bin/edr", line 8, in <module>
    sys.exit(cli())
  File "/Users/yu/anaconda2/envs/python3.8/lib/python3.8/site-packages/click/core.py", line 1128, in __call__
    return self.main(*args, **kwargs)
  File "/Users/yu/anaconda2/envs/python3.8/lib/python3.8/site-packages/click/core.py", line 1053, in main
    rv = self.invoke(ctx)
  File "/Users/yu/anaconda2/envs/python3.8/lib/python3.8/site-packages/click/core.py", line 1659, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/Users/yu/anaconda2/envs/python3.8/lib/python3.8/site-packages/click/core.py", line 1659, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/Users/yu/anaconda2/envs/python3.8/lib/python3.8/site-packages/click/core.py", line 1395, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/Users/yu/anaconda2/envs/python3.8/lib/python3.8/site-packages/click/core.py", line 754, in invoke
    return __callback(*args, **kwargs)
  File "/Users/yu/anaconda2/envs/python3.8/lib/python3.8/site-packages/click/decorators.py", line 26, in new_func
    return f(get_current_context(), *args, **kwargs)
  File "/Users/yu/anaconda2/envs/python3.8/lib/python3.8/site-packages/cli/../lineage/cli.py", line 253, in generate
    queries = query_history_extractor.extract_queries(start_date, end_date)
  File "/Users/yu/anaconda2/envs/python3.8/lib/python3.8/site-packages/lineage/query_history.py", line 85, in extract_queries
    self._query_history_table(start_date, end_date)
  File "/Users/yu/anaconda2/envs/python3.8/lib/python3.8/site-packages/lineage/bigquery_query_history.py", line 98, in _query_history_table
    rows = list(job.result())
  File "/Users/yu/anaconda2/envs/python3.8/lib/python3.8/site-packages/google/cloud/bigquery/job/query.py", line 1447, in result
    do_get_result()
  File "/Users/yu/anaconda2/envs/python3.8/lib/python3.8/site-packages/google/api_core/retry.py", line 286, in retry_wrapped_func
    return retry_target(
  File "/Users/yu/anaconda2/envs/python3.8/lib/python3.8/site-packages/google/api_core/retry.py", line 189, in retry_target
    return target()
  File "/Users/yu/anaconda2/envs/python3.8/lib/python3.8/site-packages/google/cloud/bigquery/job/query.py", line 1437, in do_get_result
    super(QueryJob, self).result(retry=retry, timeout=timeout)
  File "/Users/yu/anaconda2/envs/python3.8/lib/python3.8/site-packages/google/cloud/bigquery/job/base.py", line 727, in result
    return super(_AsyncJob, self).result(timeout=timeout, **kwargs)
  File "/Users/yu/anaconda2/envs/python3.8/lib/python3.8/site-packages/google/api_core/future/polling.py", line 135, in result
    raise self._exception
google.api_core.exceptions.BadRequest: 400 Syntax error: Expected ")" but got "-" at [7:33]

The query on the artifacts uploader on-run-end hook failed with the following error:

Database error while running on-run-end
Encountered an error:
Database Error
  The query is too large. The maximum standard SQL query length is 1024.00K characters, including comments and white space characters.
 

This probably happens on very large dbt projects.

Issue description: On the test 'all_columns_anomalies' the results table is created using the first batch of results (the first column), and the results column sizes are assigned by the content of this batch. If there is a following batch with larger values, the insert of the batch fails.

Solution: Create the table with an empty template (like we do with the incremental tables).

The fix will be deployed as part of the Redshift integration branch: https://github.com/elementary-data/dbt-data-reliability/pull/20

Some data discovery platforms like Amundsen have options to visualize data lineage relationships at a glance on the moment that you are exploring data. But you need to bring your own lineage metadata, Im really interested in an option to export the lineage relations in a file (csv or json) so I can pass that to the amundsen extractor. In the case of the basic lineage extractor of amundsen uses a single .CSV file with the structure: source_table , target_table If this file is a JSON , additional info can put it in there. So we could filter per type: CREATE_VIEW or CREATE_TABLE operations

Today the database and schema for the lineage graph are defined in a yml file. These are used for both the connection and the filtering of the graph/queries. The change is to get these as input in the CLI.

Pros: Would enable easier workflows creation for different datasets using the same configuration file.

Cons: Change from how these are used in dbt, whic is familiar to the users.

Snowflake is about to release a new feature that will enable parsing the query and extracting tables and columns set (probably still need to parse the query to learn the relation between the columns for column level lineage).

Main benefits -

• Make the tool faster (no need to extract tables lineage using our python parser)
• We might be able to simplify the setup (we will consider removing some python dependencies as they won't be needed anymore)

Downsides -

• Requires permissions to account_usage
• For column level lineage parsing the relation between columns is still needed

Open questions / how does this work with the following -

• Views
• Copy into commands
• Subqueries

Open questions for columns -

• Operators / Select * / Cases
• Subqueries
• Relations

See more details about this upcoming release here -

• https://community.snowflake.com/s/article/Pending-Behavior-Change-Log (2021_10)

Currently, we use the timestamp column as a filter, or no filtering at all (run on the entire table). For some use cases, this is not enough.

Use cases:

• Snapshot tables - timestamp is not relevant, you want to filter on rows where 'valid_to' is null
• Big tables with no timestamp column - order by + limit?

dbt supports where conditions using this macro: https://github.com/dbt-labs/dbt-core/blob/main/core/dbt/include/global_project/macros/materializations/tests/where_subquery.sql (Documented here: https://docs.getdbt.com/reference/resource-configs/where)

We need to understand if there are use cases where you need both the timestamp and additional filtering? Should there be a different behaviour for such tests?

Task Overview

• Currently timestamp_column is the only configuration that is needed to be configured globally in the model config section (usually it's being configured in the properties.yml under elementary in the config tag).
• Passing the timestamp_column as a test param will enable running multiple tests with different timestamp columns. For example running a test with updated_at column which represents the update time of the row or running a test with event_time which represents the time the event was sent.

Design

• There are three main files where the test macros are implemented - test_table_anomalies.sql, test_column_anomalies.sql and test_all_columns_anomalies.sql (please note that currently there is some code duplication in these files and in the future we will probably fix it).

• All of these test macros should receive a new parameter (defined at the end) with a default value 'none', called 'timestamp_column'.

• In each test currently there are two lines of code which are responsible for extracting the timestamp_column from the global model config {%- set table_config = elementary.get_table_config_from_graph(model) %} {%- set timestamp_column = elementary.insensitive_get_dict_value(table_config, 'timestamp_column') %}

• The macro 'get_table_config_from_graph' returns the timestamp_column and its normalized data type (called 'timestamp_column_data_type')

• The following code in the macro 'get_table_config_from_graph' that is responsible for finding the timestamp column data type should be extracted to a macro called find_normalized_data_type_for_column - {% set columns_from_relation = adapter.get_columns_in_relation(model_relation) %} {% if columns_from_relation and columns_from_relation is iterable %} {% for column_obj in columns_from_relation %} {% if column_obj.column | lower == timestamp_column | lower %} {% set timestamp_column_data_type = elementary.normalize_data_type(column_obj.dtype) %}

• Then in the test itself if the received timestamp_column new param is not none, use this extracted macro to find the column normalized data type and pass this timestamp_column and timestamp_column_data_type to the relevant functions (get_is_column_timestamp, column_monitoring_query, table_monitoring_query).

• If the timestamp_column is none, use the global timestamp column as it is implemented today

Task Overview

• Currently all anomalies are calculated statistically with a fixed global threshold (it's configurable with a var called anomaly_score_threshold, its default is 3). In some use cases it's better to define a more / less sensitive threshold based on the underlying dataset. Currently all the monitors are implemented as dbt tests and therefore the ideal solution would be to provide an additional test parameter that could receive this custom anomaly threshold for a specific test. If this parameter was not provided to the test, the the default should remain as it's configured with the global var 'anomaly_score_threshold'.

Design

• There are three main files where the test macros are implemented - test_table_anomalies.sql, test_column_anomalies.sql and test_all_columns_anomalies.sql (please note that currently there is some code duplication in these files and in the future we will probably fix it).
• All of these test macros should receive a new parameter (defined at the end) with a default value 'none', called 'anomaly_threshold'.
• Then each test should pass this value to the macro 'get_anomaly_query'
• If the received value of this param is none, the code in 'get_anomaly_query' macro should use the macro elementary.get_config_var to get the global var 'anomaly_score_threshold' and use its value instead (this is the behavior today).
• Then the anomaly query should use this param (or the global value of the var anomaly_score_threshold) to determine if there is an anomaly or not (look for 'where abs(anomaly_score)')

This is a new type of integration that was requested in the Slack community.

• From a quick look it seems like dbt already supports databricks and it seems like most of the features are supported
• The monitoring is implemented as dbt tests and therefore we will need to run the package and its tests on a databricks env to see if the tests are working as expected on this platform

Our current configuration for alerting using the CLI is to configure a profile named 'elementary' and provide it the schema where 'data_monitoring_metrics' is. This means that there can only be one profile for alerting.

The need here is to enable monitoring of two different schemas that are managed separately on the same db.

Hemant - could you confirm that this describes the need well?

The key for choosing specific tests today has the same name as the test. We got feedback that this is confusing and inconsistent between tests.

# Current format:

        - elementary.all_columns_anomalies:
            all_columns_anomalies:
              - null_count

# Suggested format:

        - elementary.all_columns_anomalies:
            monitors:
              - null_count

Also - we should accept the param 'all' as another option to activate all the monitors, as this is more intuitive than all by default.