Every Data Validation Rule You Will Ever Need

Most teams treat data validation as something that happens after the import is complete. A user uploads a CSV, the system parses it, the rows land in the database, and then someone discovers that half the email addresses are malformed, phone numbers are missing country codes, and dates are in three different formats. By that point, the damage is done: bad data has already propagated through your application, triggered downstream errors, and eroded your users' trust.

The fix is not more validation after the fact. It is catching every invalid value during the import itself, before a single row reaches your database. When validation rules run inline as part of the import flow, users see exactly what is wrong, fix it in context, and submit clean data on the first try. That single architectural decision eliminates the most common data import errors and saves engineering teams from building post-import cleanup pipelines that never quite keep up.

This guide covers every validation rule category you will encounter when building a data import system or configuring one for production. Whether you are building an importer in-house or evaluating commercial CSV import solutions, these are the rules that separate clean imports from data quality nightmares.

Format validation is the first line of defense. These rules verify that each value matches the expected pattern and data type before any business logic runs.

Email addresses are among the most commonly imported fields and among the most frequently malformed. A robust email validation rule checks for the presence of an @ symbol, a valid domain with at least one dot, no spaces or illegal characters, and a TLD that meets minimum length requirements. Simple regex catches the obvious errors, but production-grade validation also needs to handle edge cases like plus-addressing, subdomains, and internationalized domain names. Teams that skip thorough email validation during import end up with bounced campaigns, failed notifications, and corrupted contact databases.

Phone numbers are deceptively complex because the same number can appear in dozens of formats: with or without country codes, parentheses, hyphens, dots, spaces, or the international plus prefix. Effective phone validation normalizes all these variants into a consistent format (typically E.164) during import rather than storing whatever format the user happened to paste. Without normalization, your application ends up with duplicate contacts that look different but represent the same person.

URLs require validation for protocol prefix, domain structure, valid characters, and optionally for reachability. The complete guide to URL validation covers the edge cases that catch most implementations off guard, including encoded characters, internationalized domain names, and localhost references that should never appear in production data.

Dates and timestamps are the single most error-prone data type in CSV imports. Users submit dates as MM/DD/YYYY, DD/MM/YYYY, YYYY-MM-DD, "January 5, 2024", "5-Jan-24", and dozens of other formats, often mixing multiple formats within the same column. Validation needs to detect the format, verify that the date actually exists (no February 30th), and normalize to a consistent ISO 8601 representation. Time zones add another layer: an import without timezone-aware date parsing will silently shift events by hours.

Numeric values split into several distinct types. Integers need checks for non-numeric characters and range boundaries. Decimals need locale-aware parsing because some regions use commas as decimal separators while others use periods. Currency values need all of the above plus consistent precision (typically two decimal places) and optional currency symbol handling. Percentage values need normalization between 0-1 and 0-100 representations. Every numeric field should also validate against reasonable ranges: a price of negative fifty dollars or an age of nine hundred suggests a data entry error, not a valid record.

Postal and ZIP codes vary dramatically by country. US ZIP codes are five digits with an optional four-digit extension. Canadian postal codes alternate letters and numbers in a specific pattern. UK postcodes follow an entirely different structure. If your application serves multiple countries, postal code validation needs to be country-aware, which means it depends on another field in the same row. This is where format validation starts overlapping with cross-field logic.

Boolean values seem simple until you see how users actually represent them in spreadsheets: "yes", "no", "true", "false", "1", "0", "Y", "N", "on", "off", and blank cells that might mean false or might mean unknown. A good boolean validation rule defines which representations are acceptable and maps all of them to a consistent true/false value during import.

Enum and allowed-value fields restrict input to a predefined set of options such as status codes, country abbreviations, product categories, or role names. Validation checks that each value matches the allowed set, ideally with fuzzy matching that catches common misspellings and case variations. AI-assisted column mapping can extend this to automatically suggest corrections when a submitted value is close but not exact, a capability that is becoming increasingly central to modern data import tools.

Format validation catches malformed data. Business logic validation catches data that is well-formed but wrong in context. These rules encode your application's domain knowledge and are the ones most likely to be missing from a homegrown importer.

Required fields are the simplest constraint but need careful handling for edge cases. An empty string, a string containing only whitespace, and the literal text "null" or "N/A" should all be treated as missing when a field is required. Many importers only check for truly empty cells and let placeholder text through.

Uniqueness constraints verify that a column (or combination of columns) contains no duplicate values within the import and, ideally, no conflicts with existing records in your database. Email addresses, product SKUs, employee IDs, and invoice numbers are common fields that require uniqueness validation. Without it, imports silently create duplicate records that compound into data integrity problems downstream.

Cross-field dependencies are validation rules that span multiple columns in the same row. If the country field is "US", then the state field must be a valid two-letter US state abbreviation. If the payment method is "credit card", then the card number, expiration, and CVV fields become required. If the start date is populated, the end date must be after it. These conditional rules are where most DIY importers fall apart because they require validation logic that has access to the entire row, not just individual cells.

Referential integrity means that a value in one field must correspond to an existing record in your system. A department code must match a department that actually exists. A manager email must belong to a real user. A product category must be one you have defined. This type of validation requires a lookup against your live data during the import, which adds complexity but prevents orphaned references that break reports and workflows.

Range and threshold constraints go beyond basic numeric ranges to encode business rules. A discount percentage between 0 and 100 is a format rule. A discount percentage above 40 requiring manager approval is a business rule. Salary values outside two standard deviations of the department average might indicate a data entry error worth flagging. These rules turn your importer from a dumb pipe into a quality gate that catches human mistakes before they become database problems.

For teams handling sensitive data, validation rules also intersect with GDPR compliance and data privacy obligations. Validation can flag or reject fields that contain data your application should not be collecting, such as social security numbers appearing in a general contact import, or protected health information submitted through a non-compliant channel.

Beyond checking whether data is valid, your import flow should also determine whether data is clean, consistent, and ready to use without manual intervention.

Whitespace normalization trims leading and trailing spaces, collapses multiple internal spaces to one, and removes non-printable characters like zero-width spaces and byte order marks. These invisible characters are endemic in data exported from spreadsheets and legacy systems, and they cause silent matching failures when the imported data is compared against existing records.

Case normalization standardizes text fields to a consistent case format. Email addresses should be lowercased. Country codes should be uppercased. Name fields might use title case. Without normalization, your database accumulates variants like "united states", "UNITED STATES", and "United States" that fragment queries and reports.

Duplicate detection within the import identifies rows that appear to be duplicates based on exact matches, fuzzy string similarity, or composite key comparison. This is distinct from uniqueness constraints: a uniqueness rule rejects the second occurrence of a duplicate, while duplicate detection flags both rows and lets the user decide which to keep. Sophisticated duplicate detection uses similarity scoring across multiple fields to catch near-duplicates like "Jon Smith" and "John Smith" at the same address.

Encoding and character set validation catches files that contain mixed encodings, mojibake (garbled text from encoding mismatches), or characters outside the expected character set. A CSV exported from a European version of Excel might use Windows-1252 encoding with characters that break when your application expects UTF-8. Detecting and converting encoding issues during import, rather than storing corrupted text, is a validation rule that most teams only add after they have already shipped garbled data to production. Reliable CSV parsing handles some of these issues at the parser level, but encoding validation should also run at the field level for completeness.

Default value and coercion rules define what happens when a field is empty or contains a value in an unexpected type. Should an empty price field default to zero, or should it be flagged for review? Should the string "123" be coerced to the integer 123 automatically? Should "N/A" in a date field be treated as null? These rules need to be explicit because implicit coercion is one of the leading causes of silent data quality degradation in SaaS applications.

Knowing which validation rules to apply is half the problem. The other half is implementing them in a way that scales across file sizes, schema complexity, and user experience expectations.

Client-side vs. server-side validation is the first architectural decision. Running validation in the browser gives users instant feedback and keeps sensitive data off your servers. Server-side validation enables lookups against your database for referential integrity checks. The best implementations use a hybrid approach: format and pattern rules run client-side for speed, while business logic rules that require database access run server-side. Dromo's embedded import component handles this split automatically, running most validation in the browser while supporting server-side hooks through its headless mode for rules that need your backend.

Inline error correction is what separates a good import experience from one that frustrates users into abandoning the upload. When validation catches an error, the user should see exactly which cell is invalid, why it failed, and ideally a suggested correction, all within the import flow itself rather than in a downloaded error report they have to fix offline and re-upload. This is the approach that dramatically reduces import abandonment rates and support tickets.

Validation rule ordering matters for performance and user experience. Run cheap format checks first to catch the bulk of errors quickly. Run expensive cross-field and referential checks after the simple rules pass so you are not querying your database for rows that would have been rejected anyway. Group related errors together in the UI so users can fix systematic issues (like a wrong date format across an entire column) in a single action rather than cell by cell.

For teams evaluating how to add comprehensive validation to their import flows, the data mapping best practices guide covers the column mapping layer that feeds into validation, while the large file handling guide addresses performance considerations when validating imports with hundreds of thousands of rows. If you are comparing framework-specific import libraries or weighing commercial alternatives against each other, the depth of built-in validation rules should be a primary evaluation criterion.

Building and maintaining a complete validation engine in-house is possible, but the total engineering cost typically exceeds the price of a purpose-built solution within the first year. If your team needs production-grade validation across all the rule categories covered in this guide, try Dromo free or request a quote to see how it handles your specific validation requirements.