Check Types Reference
Complete documentation of all available data quality check types in DataBridge.
Schema-Level Checks
Schema checks validate the structure and presence of columns in your tables.
expect_columns
Validates that all specified columns exist in the dataset.
Syntax:
- schema_check:
expect_columns: [column1, column2, column3]
desc: "Description of what this checks"
Example:
- dataset: pg@[public.users]
checks:
- schema_check:
expect_columns: [user_id, email, created_at, country]
desc: "Users table must have required columns"
Use Cases:
- Validate that critical columns exist after schema migrations
- Ensure data contracts are maintained across teams
- Catch missing columns before downstream processes fail
expect_columns_ordered
Validates that columns exist in the specified order.
Syntax:
- schema_check:
expect_columns_ordered: [column1, column2, column3]
desc: "Description of expected column order"
Example:
- dataset: ch@[nyc_taxi.trips]
checks:
- schema_check:
expect_columns_ordered: [trip_id, pickup_datetime, dropoff_datetime, passenger_count]
desc: "Trip columns must be in the correct order"
Use Cases:
- Ensure consistent column ordering for flat file exports
- Validate schema consistency across environments
- Maintain compatibility with position-based parsers
columns_not_present
Validates that deprecated or unwanted columns do not exist.
Syntax:
- schema_check:
columns_not_present: [old_column1, deprecated_column2]
desc: "These columns should have been removed"
Example:
- dataset: pg@[public.users]
checks:
- schema_check:
columns_not_present: [legacy_user_id, deprecated_field]
desc: "Old columns should be removed after migration"
Use Cases:
- Verify cleanup after schema migrations
- Ensure deprecated fields are removed
- Validate data minimization policies
Table-Level Checks
Table checks validate properties of the entire dataset.
row_count
Validates the number of rows in a table meets expectations.
Syntax:
# Exact count
- row_count equals <number>:
desc: "Description"
# Comparison
- row_count > <number>:
desc: "Description"
- row_count < <number>:
desc: "Description"
# Range
- row_count between <min> and <max>:
desc: "Description"
Examples:
# Minimum threshold
- row_count > 1000:
desc: "Should have at least 1000 records"
# Maximum threshold
- row_count < 1000000:
desc: "Table should not exceed 1M records"
# Range validation
- row_count between 5000 and 50000:
desc: "Daily data should be within normal range"
# Exact count
- row_count equals 100:
desc: "Dimension table should have exactly 100 countries"
Use Cases:
- Detect missing data (row count drops)
- Identify data quality issues (unexpected spikes)
- Validate ETL completeness
- Monitor dimension table stability
- Detect runaway processes
raw_query
Execute custom SQL queries for complex validation logic.
Syntax:
- raw_query: "SELECT COUNT(*) FROM table WHERE condition"
equals: <expected_value>
desc: "Description"
- raw_query: "SELECT AVG(column) FROM table"
> <threshold>
desc: "Description"
Examples:
# Validate no stuck records
- raw_query: "SELECT COUNT(*) FROM orders WHERE status = 'pending' AND created_at < NOW() - INTERVAL '24 hours'"
equals: 0
desc: "No orders should be stuck in pending status"
# Cross-table validation
- raw_query: "SELECT COUNT(*) FROM orders o LEFT JOIN customers c ON o.customer_id = c.id WHERE c.id IS NULL"
equals: 0
desc: "All orders must have valid customer references"
# Business logic validation
- raw_query: "SELECT COUNT(*) FROM transactions WHERE amount < 0 AND type != 'refund'"
equals: 0
desc: "Only refunds should have negative amounts"
# Aggregate validation
- raw_query: "SELECT SUM(quantity) FROM inventory WHERE warehouse_id = 1"
between 10000 and 50000
desc: "Main warehouse inventory should be within capacity"
Use Cases:
- Complex business rule validation
- Cross-table referential integrity checks
- Aggregate value validation
- Custom metric calculations
- Domain-specific logic
Column-Level Checks
Column checks validate properties of individual columns.
not_null
Validates that a column contains no null values.
Syntax:
- not_null(column_name):
desc: "Description"
Examples:
- not_null(user_id):
desc: "User ID is mandatory"
- not_null(email):
desc: "Email is required for all users"
- not_null(transaction_id):
desc: "Every transaction must have an ID"
Use Cases:
- Enforce required fields
- Validate primary keys
- Ensure critical data completeness
- Catch upstream data quality issues
Note: This check fails if ANY null values are found. For partial null tolerance, use raw_query.
uniqueness
Validates that column values are unique (no duplicates).
Syntax:
- uniqueness(column_name):
desc: "Description"
Examples:
- uniqueness(user_id):
desc: "User IDs must be unique"
- uniqueness(email):
desc: "Each email should belong to only one account"
- uniqueness(transaction_id):
desc: "Transaction IDs must be globally unique"
# Composite uniqueness via raw_query
- raw_query: "SELECT COUNT(*) FROM (SELECT user_id, date FROM events GROUP BY user_id, date HAVING COUNT(*) > 1) dup"
equals: 0
desc: "Each user should have only one record per date"
Use Cases:
- Validate primary keys
- Detect duplicate records
- Ensure unique identifiers
- Catch data loading issues
freshness
Validates that data is recent (not stale).
Syntax:
- freshness(timestamp_column) < <time_duration>:
desc: "Description"
Time Units:
m- minutesh- hoursd- days
Examples:
# Hourly updates
- freshness(updated_at) < 1h:
desc: "Data should update every hour"
# Daily updates
- freshness(created_at) < 24h:
desc: "Should receive new records daily"
# Real-time data
- freshness(event_timestamp) < 15m:
desc: "Events should arrive within 15 minutes"
# Weekly updates
- freshness(last_modified) < 7d:
desc: "Data should be refreshed weekly"
Use Cases:
- Detect stale data
- Monitor pipeline health
- Validate ETL schedules
- Ensure timely data delivery
- SLA compliance
min
Validates the minimum value in a column.
Syntax:
- min(column_name) > <threshold>:
desc: "Description"
- min(column_name) >= <threshold>:
desc: "Description"
Examples:
- min(price) > 0:
desc: "Prices must be positive"
- min(quantity) >= 1:
desc: "Quantity must be at least 1"
- min(age) >= 18:
desc: "All users must be 18 or older"
- min(rating) >= 1:
desc: "Ratings should be between 1-5"
- min(created_at) > '2024-01-01':
desc: "No records should predate system launch"
Use Cases:
- Validate value ranges
- Enforce business rules
- Detect data entry errors
- Prevent invalid data propagation
max
Validates the maximum value in a column.
Syntax:
- max(column_name) < <threshold>:
desc: "Description"
- max(column_name) <= <threshold>:
desc: "Description"
Examples:
- max(price) < 1000000:
desc: "Prices should not exceed 1M"
- max(quantity) <= 9999:
desc: "Maximum order quantity is 9999"
- max(discount_percent) <= 100:
desc: "Discount cannot exceed 100%"
- max(rating) <= 5:
desc: "Rating scale is 1-5"
- max(age) <= 120:
desc: "Age values should be realistic"
Use Cases:
- Validate value ranges
- Detect outliers
- Enforce system limits
- Identify data quality issues
sum
Validates the sum of values in a column.
Syntax:
- sum(column_name) <operator> <value>:
desc: "Description"
Examples:
# Exact sum
- sum(allocated_budget) equals 1000000:
desc: "Total allocated budget must equal 1M"
# Minimum sum
- sum(order_total) > 10000:
desc: "Daily revenue should exceed 10K"
# Range validation
- sum(quantity) between 1000 and 5000:
desc: "Total inventory should be within capacity"
# Cross-validation
- raw_query: "SELECT ABS(SUM(debits) - SUM(credits)) FROM ledger"
equals: 0
desc: "Debits and credits must balance"
Use Cases:
- Financial reconciliation
- Inventory validation
- Budget verification
- Aggregate business rules
avg
Validates the average value in a column.
Syntax:
- avg(column_name) <operator> <value>:
desc: "Description"
Examples:
- avg(response_time_ms) < 500:
desc: "Average response time should be under 500ms"
- avg(rating) >= 4.0:
desc: "Average product rating should be 4.0 or higher"
- avg(order_value) > 50:
desc: "Average order value should exceed $50"
- avg(session_duration) between 120 and 600:
desc: "Average session should be 2-10 minutes"
Use Cases:
- Performance monitoring
- Quality thresholds
- Business KPI validation
- Statistical anomaly detection
stddev
Validates the standard deviation of values in a column.
Syntax:
- stddev(column_name) <operator> <value>:
desc: "Description"
Examples:
- stddev(price) < 100:
desc: "Price variance should be low"
- stddev(response_time) < 200:
desc: "Response times should be consistent"
- stddev(order_value) between 10 and 50:
desc: "Order values should have moderate variance"
Use Cases:
- Detect high variance/outliers
- Validate data consistency
- Monitor stability over time
- Statistical quality control
Check Configuration Options
Common Options
All checks support these configuration options:
- <check_definition>:
desc: "Human-readable description"
enabled: true # Set to false to temporarily disable
severity: error # error, warning, info
Dataset Configuration
checks:
- dataset: <datasource_id>@[schema.table]
filter: "WHERE created_at > '2024-01-01'" # Optional row filter
checks:
- <check_definitions>
Real-World Examples
E-commerce Orders Table
- dataset: pg@[ecommerce.orders]
checks:
# Schema validation
- schema_check:
expect_columns: [order_id, customer_id, total, status, created_at]
desc: "Orders table structure"
# Volume monitoring
- row_count between 100 and 10000:
desc: "Daily order volume within normal range"
# Completeness
- not_null(order_id):
desc: "Order ID required"
- not_null(customer_id):
desc: "Customer ID required"
# Uniqueness
- uniqueness(order_id):
desc: "Order IDs must be unique"
# Business rules
- min(total) > 0:
desc: "Order total must be positive"
- max(total) < 100000:
desc: "Order total sanity check"
# Freshness
- freshness(created_at) < 24h:
desc: "Should receive orders daily"
# Status validation
- raw_query: "SELECT COUNT(*) FROM orders WHERE status NOT IN ('pending', 'completed', 'cancelled')"
equals: 0
desc: "All orders should have valid status"
User Analytics Events
- dataset: ch@[analytics.pageviews]
checks:
# Volume monitoring
- row_count > 10000:
desc: "Minimum daily pageviews"
# Completeness
- not_null(user_id):
desc: "User ID required for tracking"
- not_null(page_url):
desc: "Page URL required"
# Freshness
- freshness(event_timestamp) < 1h:
desc: "Events should be near real-time"
# Quality checks
- min(session_duration) >= 0:
desc: "Session duration cannot be negative"
- max(session_duration) < 86400:
desc: "Session duration should be under 24 hours"
# Bot detection
- raw_query: "SELECT COUNT(*) FROM pageviews WHERE page_views_per_session > 1000"
equals: 0
desc: "Flag potential bot traffic"
Next Steps
- User Guide - Learn how to integrate checks into your workflow
- Core Concepts - Understand validation workflows
- Quick Start - Get started with dbqctl