Generate a schema from a CSV

Worked example: one CSV, four targets, the same decisions

Here is orders.csv, six rows chosen so that every inference rule fires at least once:

order_id,customer_ref,plan,seats,amount,ordered_on,active
A-1001,00417,pro,12,348.00,2024-01-05,true
A-1002,00892,starter,3,29.00,2024-01-09,true
A-1003,01044,pro,7,203.00,2024-01-14,false
A-1004,00417,starter,1,9.00,2024-01-20,true
A-1005,02310,pro,24,696.00,2024-02-02,
A-1006,00655,starter,2,19.00,2024-02-11,false

Before any code is written, seven decisions get made. order_id is text with a longest value of 6. customer_ref is text because of the padding, longest value 5, and it is not an enum: it has five distinct values across six rows, and the enum rule wants at least three rows per distinct value, which would need fifteen. plan is an enum, two distinct values across six rows, emitted sorted as pro then starter. seats is an integer. amount is a float, on the strength of the decimal point alone. ordered_on is a date. active is a boolean and, because row five is blank, the only optional column in the file.

Pick TypeScript interface with a type name of Order and you get this:

/** Generated from a CSV by ExploreMyData. 7 columns. */
export interface Order {
  order_id: string;
  customer_ref: string;
  plan: "pro" | "starter";
  seats: number;
  amount: number;
  /** YYYY-MM-DD */
  ordered_on: string;
  active?: boolean | null;
}

The enum arrives as a union of string literals rather than a bare string, the date is typed as a string with a comment naming the format, because a CSV carries text and pretending otherwise breaks at the first parse, and the optional column gets both the question mark and the null. Now switch the target to Zod schema without touching anything else:

import { z } from "zod";

export const orderSchema = z.object({
  order_id: z.string().max(6),
  customer_ref: z.string().max(5),
  plan: z.enum(["pro", "starter"]),
  seats: z.coerce.number().int(),
  amount: z.coerce.number(),
  ordered_on: z.string().regex(/^\d{4}-\d{2}-\d{2}$/, "expected YYYY-MM-DD"),
  active: z.coerce.boolean().nullish(),
});

export type Order = z.infer<typeof orderSchema>;

Same seven decisions, expressed in the idiom Zod actually uses. The numbers are coerced, because CSV values arrive as strings and a plain z.number() would reject every row of the file it was generated from. The string columns carry the longest length seen as a max. The date is validated as text against a pattern rather than typed as z.date(), for the same reason. Now SQL CREATE TABLE on Postgres:

-- Generated from a CSV by ExploreMyData. Dialect: postgres.
CREATE TABLE "order" (
  "order_id" VARCHAR(16) NOT NULL,
  "customer_ref" VARCHAR(16) NOT NULL,
  "plan" VARCHAR(16) NOT NULL CHECK ("plan" IN ('pro', 'starter')),
  "seats" BIGINT NOT NULL,
  "amount" DOUBLE PRECISION NOT NULL,
  "ordered_on" DATE NOT NULL,
  "active" BOOLEAN
);

The enum became a CHECK constraint. Every column except active is NOT NULL, which is the same nullability decision the TypeScript question mark expressed. The VARCHAR width is the longest observed value multiplied by 1.5 and rounded up, then floored at 16 and capped at 4000, so a six-character order id gets sixteen characters of room rather than exactly six. Switch the dialect to MySQL and one thing changes:

-- Generated from a CSV by ExploreMyData. Dialect: mysql.
CREATE TABLE `order` (
  `order_id` VARCHAR(16) NOT NULL,
  `customer_ref` VARCHAR(16) NOT NULL,
  `plan` VARCHAR(16) NOT NULL,
  `seats` BIGINT NOT NULL,
  `amount` DOUBLE PRECISION NOT NULL,
  `ordered_on` DATE NOT NULL,
  `active` TINYINT(1)
);

-- MySQL alternative for the small-cardinality columns:
--   ALTER TABLE `order` MODIFY `plan` ENUM('pro', 'starter') NOT NULL;

The CHECK is gone and a commented ALTER TABLE takes its place, because MySQL has a native ENUM type and that is the idiomatic answer there. It stays a comment on purpose: a native ENUM is a decision with migration consequences, and a generator should offer it rather than impose it. Note the identifier quoting changes too, backticks on MySQL, double quotes on Postgres and SQLite, square brackets on SQL Server, and the boolean becomes TINYINT(1) on MySQL, BIT on SQL Server and INTEGER on SQLite. The table name is the type name in snake_case, which is why Order became order.

The nine targets, and what each is for

• TypeScript interface. A plain interface with an enum column as a union of string literals and a doc comment on date and datetime fields naming the format. No runtime cost, no dependency, no validation.
• Zod schema. The same shape with validation attached, plus the inferred TypeScript type at the bottom. Numbers and booleans are coerced so the schema parses the CSV it came from; a datetime column is validated with z.string().datetime({ offset: true }).
• Pydantic v2 model. A BaseModel with real date and datetime annotations, Literal[...] for enums, Optional[...] with default=None for nullable columns, and aliases where the header is not a Python name.
• JSON Schema 2020-12. Not draft-07. Nullable columns get a type array such as ["integer", "null"], an enum on a nullable column gets null appended to its list, dates carry format of date or date-time, text columns carry maxLength, and every non-nullable column is listed in required. additionalProperties is false.
• Go struct. int64, float64, bool, string and time.Time for datetimes, with the time import added only when it is used. A nullable column of any type other than string becomes a pointer, because a Go zero value cannot tell you the cell was empty.
• Rust serde struct. i64, f64, bool and String, deriving Debug, Clone, Serialize and Deserialize, with Option<T> on the nullable columns.
• SQL CREATE TABLE. Postgres, MySQL, SQLite or SQL Server, each with its own quoting, its own boolean spelling, its own timestamp type and its own text type. Enums become CHECK constraints everywhere except MySQL.
• Avro schema. A record with long, double, boolean and string, a { "type": "int", "logicalType": "date" } for dates and timestamp-millis for datetimes. Nullable fields become a union of null and the type with a default of null, in that order, which is what Avro requires. An enum whose values are all valid Avro names becomes a real enum type; if any value is not, it falls back to string rather than emitting a schema that will not load.
• Parquet schema. The message definition, with required or optional on each field, binary (STRING) for text, int32 (DATE) for dates and int64 (TIMESTAMP(MILLIS,true)) for datetimes.

Where this fits in a workflow

A schema is a claim about a file, and a claim is worth more once you have checked it. The generator is the last of three steps, not the first. Run the file through the CSV validator to find the structural problems, ragged rows, mixed line endings, encoding damage, duplicate headers. Then profile it to see the per-column completeness, the mixed value shapes, the case variants and the impossible dates. Fix what those two find, and only then generate a schema, because a schema generated from a broken file faithfully describes the breakage.

After that, the schema and a saved data contract do different jobs. The schema tells your code what shape to expect. A contract, generated from the profiler, tells you whether next month's file still matches, with a pass or a fail and the rows that caused it. If you are not sure what to check for, the data quality audit checklist walks the whole list.

Related