Schema Genie

Every time a team lands a new dataset in a warehouse, someone has to sit down and decide which columns are measures, which are dimensions, which table is the fact table, where the foreign keys are, and whether the dimensions should stay flat or be normalised. That work is repetitive and almost entirely mechanical, but it blocks everything downstream — nobody can write a query or train a model until the tables exist. The usual coping strategy is a data engineer hand-writing CREATE TABLE statements from a spreadsheet of column names, then discovering three weeks in that the dimension tables have no history columns and point-in-time joins are impossible.

It had to work on data as it actually arrives — a pandas DataFrame or a CSV with no schema file, no dictionary, no annotations. Nothing but column names, dtypes and values to reason from, which rules out anything that needs metadata and forces heuristics. It had to emit DDL for four warehouses whose dialects disagree on surrogate keys, type names, constraint support and physical layout, without asking the caller to know any of that. And it had to be a library other people install, so the hard dependency list needed to stay small (pandas, numpy, sqlglot, pyyaml) with connectors pushed to optional extras — a user targeting Postgres should not be made to install the Snowflake connector.

The centre of the design is a warehouse-neutral intermediate representation. Inference never produces SQL; it produces dataclasses — a TableDefinition holding ColumnDefinitions tagged with one of six *semantic* types (id, date, currency, measure, category, text) plus ForeignKey edges. Only the generator layer knows SQL exists. The alternative was to let each stage emit dialect-aware fragments and concatenate them, which would have been fewer lines but would have meant every new warehouse touched every inference module. With the IR, adding a target means one file: a TYPE_MAP dict and a _table_ddl method on a BaseDDLGenerator subclass. SchemaGenie.__init__ resolves the target through a GENERATORS registry and fails loudly on an unknown name rather than falling back to a default dialect.

The semantic-type layer is the other deliberate choice. It would have been simpler to map pandas dtypes straight to SQL types, but dtypes cannot tell you that revenue should be NUMBER(18,2) and not FLOAT, or that a three-value string column is a category rather than free text. So detection runs name regex → dtype → cardinality ratio, in that order, first match wins, and every downstream decision keys off the semantic type instead of the dtype. That single indirection is what lets the same inferred schema produce NUMBER(18,2) on Snowflake, DECIMAL(18,2) on Redshift and NUMERIC on BigQuery, and it is what makes the fact-table scorer possible at all — the score is a function of how many columns are measures, which is a semantic question.

I started this because I had written the same CREATE TABLE block by hand too many times, and the third time I typed _valid_from TIMESTAMP I realised the whole thing was a function of the DataFrame I already had open. The first version was much smaller than what shipped — type detection and a single Snowflake generator, no relationships, no fact selection. It worked on one table and was useless on real projects, because a real project is five tables and the interesting question is which one is the fact.

The thing I got wrong early was believing the fact table was the biggest table. That is true often enough to be seductive and wrong often enough to be dangerous — a date dimension is the obvious counterexample and it broke the first version immediately. Rewriting select_fact_table around measure density, with row count damped through a log, is when the inference stopped feeling like a guess. The related mistake was letting relationship detection compare every column against every column; on anything wider than a toy dataset it crawled, and the name-stem prefilter is a patch on a design I would think about differently now.

The moment it clicked was writing the second generator. I had built Snowflake first with the SQL strings inline, and porting it to Redshift meant either duplicating the whole thing or extracting an IR. Extracting the IR took an afternoon and the third and fourth generators each took under an hour — which is the clearest evidence I have that the abstraction boundary was in the right place. It also told me exactly where the boundary is *not*: physical design (distribution, partitioning, indexes) does not generalise, and I stopped fighting it.

What I would do differently: I let the repo drift in ways that are small but embarrassing. sqlglot>=10.0 is a hard dependency that nothing in the package imports — it was in the plan for SQL validation and never got wired in, but it still forces itself on every install. __init__.py still reports __version__ = "1.0.0" while pyproject.toml is at 1.1.0. primary_key_strategy is accepted, documented in the docstring, and never read — the generators always emit a surrogate key. numpy is imported in type_detector.py and unused. None of these break anything, and all of them are the kind of thing a reviewer catches in ten seconds; shipping solo with no second reader is precisely how they survived to release.

The other thing I would change is the v1.1.0 commit. It rewrote the README around cost-savings tables — dollar figures per project, monthly BigQuery savings — built entirely from published industry rates and pricing pages, not from anything this code was measured doing. The caveat line is there, but the tables read as results. I would rather have spent that commit writing a benchmark that produces one honest number than writing six persuasive ones.

Shipped as an installable Python package. Two releases are built in dist/: schema_genie-1.0.0 (7 Mar 2026, the full package — 28 files, ~2,000 lines) and schema_genie-1.1.0 (8 Mar 2026, documentation plus version bump), each as a wheel and an sdist. What ships: a six-stage inference pipeline over pandas DataFrames, DDL generation for Snowflake, Redshift, BigQuery and PostgreSQL, SCD Type 2 audit columns on every generated dimension table, YAML-driven configuration via SchemaGenie.from_config, direct deployment to a live warehouse connection, and optional Graphviz ER-diagram export. 68 tests collected and passing under pytest, concentrated on type detection, fact selection and the four generators.

schema-genie eliminates 80–90% of manual data-modelling work. Given one or more pandas DataFrames, its 6-stage inference pipeline classifies column semantic types, detects FK relationships, scores and selects fact tables, recommends star vs snowflake topology, detects SCD Type 2 candidates, and emits fully-annotated, warehouse-specific DDL — complete with surrogate keys, audit columns, distribution keys, indexes, and FK constraints. Supports live deployment, ER diagram export, and YAML configuration.