> For the complete documentation index, see [llms.txt](https://upsolver.gitbook.io/content/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://upsolver.gitbook.io/content/articles-1/data/expectations.md). # Expectations Data quality conditions can be added to your job to drop a row or trigger a warning when a column violates a predefined condition. Each expectation name should be unique to the job, and the predicate that determines whether the condition is violated must return a Boolean value. The SQL predicate can be any syntax supported in a `WHERE` clause, however, aggregates are not supported. Each expectation has an action of either `DROP` or `WARN`. If you define the action as `DROP`, any row with a predicate that returns `FALSE`, is dropped from the ingestion stream and is not loaded into the target. Rows violating the condition defined in an expectation with a `WARN` action are loaded into the target. You can add an expectation when you create a new job, or use the [`ALTER JOB`](broken://spaces/WKMq8oT1OPM3KjP8vlg2/pages/ydSAwMs2L7iSSasNgijM) command to update an existing job to add a new expectation. However, you must drop and recreate an expectation to alter an existing rule. A job can have an unlimited number of expectations and each expectation name must be unique to the job. To add an expectation when you create a new job, use the following syntax: ```sql CREATE JOB ... WITH EXPECTATION EXPECT ON VIOLATION { DROP | WARN } ``` You can easily add an expectation to an existing job using the `ALTER JOB` command. The syntax below shows how to alter a job you have already created: ```sql ALTER JOB ADD EXPECTATION EXPECT ON VIOLATION { DROP | WARN } ``` To drop an expectation, use the `ALTER JOB` command using the following syntax: ```sql ALTER JOB DROP EXPECTATION ``` When you drop an expectation, the entry is dropped from the `system.monitoring.expectations` system table, so if you want to retain a count of warnings or dropped rows, ensure you take a copy of these values prior to dropping the expectation. Furthermore, values are removed from the `system.monitoring.jobs` table. {% hint style="warning" %} Dropping a job will also drop the associated expectations, so you will no longer see the count of warnings or dropped rows in the system tables. You can take a backup of this data if you want to retain it for reporting purposes. See the how-to guide [Managing Data Quality - Ingesting Data with Expectations](/content/how-to-guides-1/jobs/advanced-use-cases/managing-data-quality-ingesting-data-with-expectations.md) for instructions on creating a reporting table to record violated conditions. {% endhint %} ### Monitoring data quality with expectations For both `DROP` and `WARN` actions, counters are incremented in the [`system.monitoring.expectations`](/content/reference-1/monitoring/system-catalog/monitoring/expectations.md) and [`system.monitoring.jobs`](/content/reference-1/monitoring/system-catalog/monitoring/jobs.md) tables to monitor the number of rows violating the condition. #### system.monitoring.expectations When a row fails to meet an expectation, the `system.monitoring.expectations` table is updated, and you can use the values in this table to report on the quality of your ingested data: * The `triggered_today` value is incremented each time an ingested row violates the condition, and the count shows all rows that violated the expectation **since midnight (UTC time)**. * The `total_triggered` value is incremented each time an ingested row violates the condition, and the count shows all rows that violated the expectation **since the job started**. * The `action` column indicates the trigger type, either **drop** or **warn**, defined on the expectation. Run the following query against the expectations system table to return the values for an expectation: ```sql SELECT expectation_name, triggered_today, triggered_total, action FROM system.monitoring.expectations WHERE expectation_name = ''; ``` You can also view the counts for all expectations defined in a job using the following query, simply by changing the `WHERE` clause: ```sql SELECT expectation_name, triggered_today, triggered_total, action FROM system.monitoring.expectations WHERE job_name = ''; ``` #### system.monitoring.jobs Furthermore, you can view the sum of all expectations triggered since midnight (UTC time) in the `system.monitoring.jobs` table, which counts all triggered expectations defined in a job: * `drop_expectations_triggered_today` shows the number of `DROP` expectation violations today. * `warn_expectations_triggered_today` counts the total rows that were dropped from the ingestion stream today and were not loaded into the target because they failed to meet a condition. * `rows_filtered_by_expectations_today` the number of rows dropped due to expectation violations today. {% hint style="info" %} A row can violate multiple `DROP` and `WARN` conditions. For example, if a row triggers **two** warn expectations, and **two** drop expectations, the **drop\_expectations\_triggered\_today** column will be incremented by **two**, and the **warn\_expectations\_triggered\_today** column will be incremented by **two**, resulting in **four** violations. However, the **rows\_filtered\_by\_expectations\_today** column will only be incremented by **one**. {% endhint %} Run the following statement to return a count of warnings and dropped rows for a job: ```sql SELECT drop_expectations_triggered_today, warn_expectations_triggered_today, rows_filtered_by_expectations_today FROM system.monitoring.jobs WHERE job_name = ''; ``` --- # Agent Instructions This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com. ## Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter: ``` GET https://upsolver.gitbook.io/content/articles-1/data/expectations.md?ask=&goal= ``` `ask` is the immediate question: it should be specific, self-contained, and written in natural language. `goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.