18 SQL Data Validation Queries for Parquet, CSV & More

What is SQL data validation?

A file can open successfully and still be unsafe to use. A customer ID may occur twice. A date may contain month 13. An amount may contain the word unknown. A shipment may appear to happen before its order was created. Those problems are not specific to CSV or Parquet: they can quietly corrupt a dashboard, join, invoice, model, or migration regardless of the source format.

This guide gives you 18 practical SQL data validation queries for the checks that catch those problems most often. ParquetReader converts every supported upload into a queryable, Parquet-backed dataset, so the same workflow works for Parquet, CSV, Excel, JSON, JSONL, Avro, ORC, Feather, Arrow/IPC, GeoJSON, NetCDF, and Vortex files. Every example queries that normalized table as dataset using DuckDB SQL.

The 18 SQL data quality checks at a glance

The checks are grouped by the question they answer. Start with the baseline checks even when you already know which column looks wrong: totals and cardinality often reveal that the problem is larger than the first bad row suggests.

Sample data and a 60-second quick start

The examples use a deliberately messy order file. It contains duplicate order IDs, one completely duplicated row, missing values, malformed data, inconsistent categories, a negative amount, a future timestamp, an impossible event sequence, and one large outlier.

1. Download the sample CSV or choose one of your own supported data files.
2. Open ParquetReader and upload the file with the standard uploader.
3. Inspect the inferred schema, then open Advanced: Run SQL Query.
4. Select DuckDB as the SQL dialect.
5. Paste a query below and press Ctrl/⌘ + Enter.

After upload, ParquetReader normalizes the source into an internal Parquet-backed dataset and makes it available as dataset. Running safe read-only SQL over the complete dataset is free and does not change the original file. Replace the example column names with the exact names from your schema; quote unusual names such as "Order ID" with double quotes.

Checks 1–4: establish the shape, completeness, and cardinality

These four queries establish the denominator for everything that follows. Run them before investigating individual bad rows.

SELECT COUNT(*) AS total_rows
FROM dataset;

SELECT
  COUNT(*) AS total_rows,
  SUM(
    CASE
      WHEN email IS NULL OR TRIM(email) = '' THEN 1
      ELSE 0
    END
  ) AS missing_email
FROM dataset;

SELECT
  ROUND(
    100.0 * SUM(
      CASE
        WHEN email IS NOT NULL AND TRIM(email) <> '' THEN 1
        ELSE 0
      END
    ) / NULLIF(COUNT(*), 0),
    2
  ) AS email_completeness_pct
FROM dataset;

SELECT
  COUNT(*) AS total_rows,
  COUNT(customer_id) AS populated_customer_ids,
  COUNT(DISTINCT customer_id) AS distinct_customer_ids,
  ROUND(
    100.0 * COUNT(DISTINCT customer_id)
    / NULLIF(COUNT(*), 0),
    2
  ) AS distinct_customer_pct
FROM dataset;

Checks 5–7: find duplicate IDs and duplicate records

Duplicate keys and duplicate rows are not the same problem. A repeated ID may represent legitimate updates; an identical repeated row more often indicates a duplicated export or ingestion step.

SELECT
  order_id,
  COUNT(*) AS occurrences
FROM dataset
GROUP BY order_id
HAVING COUNT(*) > 1
ORDER BY occurrences DESC, order_id;

SELECT
  order_id,
  customer_id,
  email,
  status,
  amount,
  created_at,
  shipped_at,
  country,
  updated_at,
  COUNT(*) AS copies
FROM dataset
GROUP BY
  order_id,
  customer_id,
  email,
  status,
  amount,
  created_at,
  shipped_at,
  country,
  updated_at
HAVING COUNT(*) > 1;

SELECT
  COUNT(*) AS total_rows,
  COUNT(order_id) AS populated_ids,
  COUNT(DISTINCT order_id) AS unique_ids,
  CASE
    WHEN COUNT(*) = COUNT(order_id)
     AND COUNT(*) = COUNT(DISTINCT order_id)
    THEN 'PASS'
    ELSE 'FAIL'
  END AS order_id_check
FROM dataset;

Checks 8–11: catch invalid numbers, dates, emails, and text

CSV and JSON fields are often inferred as text when even one value has the wrong format. TRY_CAST is ideal for validation because it returns NULL for values it cannot convert instead of stopping the whole query.

SELECT
  order_id,
  amount
FROM dataset
WHERE amount IS NOT NULL
  AND TRIM(CAST(amount AS VARCHAR)) <> ''
  AND TRY_CAST(amount AS DECIMAL(18, 2)) IS NULL;

SELECT
  order_id,
  created_at
FROM dataset
WHERE created_at IS NOT NULL
  AND TRIM(CAST(created_at AS VARCHAR)) <> ''
  AND TRY_CAST(created_at AS TIMESTAMP) IS NULL;

SELECT
  order_id,
  email
FROM dataset
WHERE email IS NOT NULL
  AND TRIM(email) <> ''
  AND NOT REGEXP_MATCHES(
    LOWER(TRIM(email)),
    '^[^[:space:]@]+@[^[:space:]@]+[.][^[:space:]@]+$'
  );

SELECT
  status AS raw_status,
  LOWER(TRIM(status)) AS normalized_status,
  COUNT(*) AS rows
FROM dataset
GROUP BY raw_status, normalized_status
ORDER BY normalized_status, raw_status;

Checks 12–15: test the rules that make your data believable

Type checks answer “can this value be parsed?” Business-rule checks answer “could this value be true in our process?” Those rules are specific to your organization, which makes them some of the highest-value checks you can write.

SELECT
  order_id,
  status
FROM dataset
WHERE status IS NULL
   OR LOWER(TRIM(status)) NOT IN (
     'pending',
     'paid',
     'shipped',
     'cancelled'
   );

SELECT
  order_id,
  amount
FROM dataset
WHERE TRY_CAST(amount AS DECIMAL(18, 2)) < 0;

SELECT
  order_id,
  created_at
FROM dataset
WHERE TRY_CAST(created_at AS TIMESTAMP) > CURRENT_TIMESTAMP;

SELECT
  order_id,
  created_at,
  shipped_at
FROM dataset
WHERE TRY_CAST(shipped_at AS TIMESTAMP)
    < TRY_CAST(created_at AS TIMESTAMP);

Checks 16–17: detect outliers and suspicious volume changes

Outliers are observations to investigate, not automatically errors. A large enterprise order may be completely valid. Statistical checks should surface records for review; business context decides what happens next.

WITH numeric_amounts AS (
  SELECT
    order_id,
    TRY_CAST(amount AS DECIMAL(18, 2)) AS amount
  FROM dataset
  WHERE TRY_CAST(amount AS DECIMAL(18, 2)) IS NOT NULL
),
bounds AS (
  SELECT
    QUANTILE_CONT(amount, 0.25) AS q1,
    QUANTILE_CONT(amount, 0.75) AS q3
  FROM numeric_amounts
)
SELECT
  n.order_id,
  n.amount
FROM numeric_amounts AS n
CROSS JOIN bounds AS b
WHERE n.amount < b.q1 - 1.5 * (b.q3 - b.q1)
   OR n.amount > b.q3 + 1.5 * (b.q3 - b.q1)
ORDER BY n.amount DESC;

WITH daily AS (
  SELECT
    CAST(TRY_CAST(created_at AS TIMESTAMP) AS DATE) AS order_day,
    COUNT(*) AS order_count
  FROM dataset
  WHERE TRY_CAST(created_at AS TIMESTAMP) IS NOT NULL
  GROUP BY order_day
),
scored AS (
  SELECT
    *,
    AVG(order_count) OVER () AS average_daily_orders
  FROM daily
)
SELECT
  order_day,
  order_count,
  ROUND(average_daily_orders, 2) AS average_daily_orders,
  CASE
    WHEN order_count > 2 * average_daily_orders THEN 'CHECK'
    ELSE 'OK'
  END AS volume_check
FROM scored
ORDER BY order_day;

Check 18: build a typed, normalized, deduplicated result

After diagnosing the file, combine the rules into one read-only query. This example parses types, normalizes text, rejects records that fail the chosen rules, and keeps the most recently updated row for each order.

WITH typed AS (
  SELECT
    order_id,
    customer_id,
    LOWER(TRIM(email)) AS email,
    LOWER(TRIM(status)) AS status,
    TRY_CAST(amount AS DECIMAL(18, 2)) AS amount,
    TRY_CAST(created_at AS TIMESTAMP) AS created_at,
    TRY_CAST(shipped_at AS TIMESTAMP) AS shipped_at,
    UPPER(TRIM(country)) AS country,
    TRY_CAST(updated_at AS TIMESTAMP) AS updated_at
  FROM dataset
),
ranked AS (
  SELECT
    *,
    ROW_NUMBER() OVER (
      PARTITION BY order_id
      ORDER BY updated_at DESC NULLS LAST
    ) AS row_number
  FROM typed
  WHERE order_id IS NOT NULL
    AND amount IS NOT NULL
    AND amount >= 0
    AND created_at IS NOT NULL
    AND created_at <= CURRENT_TIMESTAMP
    AND email IS NOT NULL
    AND REGEXP_MATCHES(
      email,
      '^[^[:space:]@]+@[^[:space:]@]+[.][^[:space:]@]+$'
    )
    AND status IN ('pending', 'paid', 'shipped', 'cancelled')
    AND (shipped_at IS NULL OR shipped_at >= created_at)
)
SELECT * EXCLUDE (row_number)
FROM ranked
WHERE row_number = 1
ORDER BY order_id;

Treat this as a starting point, not a universal definition of clean data. For example, you may allow future bookings, negative refund amounts, missing emails, or additional status values. Validation rules should encode the contract your dataset is supposed to meet.

When the result looks right, ParquetReader can export the SQL result as CSV, JSON, or Parquet. You export the rows produced by the query—not the rejected source rows. That is often safer and much smaller than converting the entire raw file.

When to use the Data Quality Report and when to use SQL

The two tools solve different parts of the same job. The free Data Quality Report profiles every column and quickly surfaces null rates, duplicate rows and keys, invalid structured text, mixed date or numeric formats, ranges, distributions, and likely key columns. It is the fastest place to start with an unfamiliar file.

A strong workflow is therefore: run the report, inspect the flagged columns, use targeted SQL to confirm the affected records, and then export only the accepted result.

One validation workflow for every supported upload format

The upload format changes what you should inspect first, but it does not change the overall workflow. ParquetReader converts the uploaded source into the same Parquet-backed query layer and exposes it as dataset. That gives Parquet, CSV, Excel, JSON, JSONL, Avro, ORC, Feather, Arrow/IPC, GeoJSON, NetCDF, and Vortex files one consistent SQL validation path.

• Parquet, Avro, ORC, Feather, Arrow/IPC, and Vortex carry type information. Start with nulls, uniqueness, ranges, business rules, and semantic problems.
• CSV has no embedded type system. A single malformed value can make an otherwise numeric column appear as text, which is why TRY_CAST is especially valuable.
• Excel can mix displayed values, underlying values, formulas, and cell types. Validate inferred dates and numeric columns after upload.
• JSON and JSONL may contain missing properties and nested values. Inspect the inferred schema and flatten or extract the fields you want to validate.
• GeoJSON and NetCDF combine attributes with spatial or scientific dimensions. These general checks still apply to the tabular columns; geometry, coordinates, units, and dimensions may need additional domain-specific rules.

The examples in this article use DuckDB SQL because it is the clearest copy-and-paste option in ParquetReader. The editor also accepts BigQuery, Snowflake, Postgres, MySQL, and SQLite dialects and translates supported read-only queries for execution. Date parsing, regular expressions, quoting, and JSON functions vary most between dialects, so use the dialect selector that matches the syntax you write.

For focused guides, see how to run SQL on Parquet, how to run SQL on CSV, how to query JSON files, or the complete supported-format overview.

Six common data validation mistakes

1. Checking only nulls. Populated values can still be malformed, duplicated, impossible, or semantically wrong.
2. Deleting every duplicate. Repeated keys can represent versions or valid one-to-many relationships. Inspect them first.
3. Treating every outlier as an error. Statistical rarity is a review signal, not proof.
4. Letting casts fail the whole query. Use TRY_CAST to isolate malformed values safely.
5. Changing data before measuring the problem. Preserve the source, run read-only checks, and create a separate cleaned result.
6. Using rules without thresholds or owners. Document what passes, why, and who decides when an exception is valid.

For repeatable work, save the final query with a descriptive name such as “Orders — pre-import validation v1”. Saved SQL queries and persistent files let you rerun the same contract against later deliveries instead of rebuilding the checks from memory.

A practical validation checklist before you use or export a file

• Confirm the expected row count and file period.
• Review the schema and inferred column types.
• Measure missingness for required fields.
• Check key columns for nulls and duplicates.
• Validate dates, numbers, emails, and identifiers.
• Normalize categorical values before grouping or joining.
• Test allowed ranges and event sequences.
• Review outliers instead of deleting them automatically.
• Record the query, thresholds, and accepted exceptions.
• Export a new result; keep the original file unchanged.

If you receive the same kind of file repeatedly, convert this checklist into saved queries or an API workflow. ParquetReader's API can query persistent files using the same dataset table, which makes these checks reusable in n8n, scheduled jobs, internal tools, and AI-assisted workflows. For data that cannot leave your environment, the self-hosted edition supports private deployments and S3-compatible storage.

Run the checks on your own file

Choose any supported upload: Parquet, CSV, Excel, JSON/JSONL, Avro, ORC, Feather, Arrow/IPC, GeoJSON, NetCDF, or Vortex. ParquetReader normalizes it into the same queryable dataset so you can run these checks over every row. Browsing, schema inspection, search, sorting, the Data Quality Report, and read-only SQL are free—without a row limit or required account.

When you need to download the full file or a filtered SQL result, a Day Pass or Pro plan unlocks export to CSV, JSON, or Parquet, plus saved files, saved queries, secure sharing, and API access. The plans unlock the same capabilities for different lengths of time.

Frequently asked questions about SQL data validation

Related guides

• Create a free Data Quality Report for Parquet, CSV, and Excel
• Run SQL queries on Parquet files
• Run SQL on a CSV file without a database
• Convert Parquet to CSV after filtering with SQL
• Convert CSV to analytics-ready Parquet