Goal
Adding comments would help us improve downstream consumption of the extract outputs by large language models.
DuckDB's COMMENT ON TABLE and COMMENT ON COLUMN statements persist inside the .db file and surface in every schema introspection path. Adding them turns the extract into a self-describing artifact.
Problems
There is no single definition site for extract schemas across the seven supported profiler umbrellas.
1. Paired DDL files (MSSQL, Oracle)
Each table has a *_ddl.sql file with a bare CREATE TABLE IF NOT EXISTS statement. Comments would need to be bolted on separately, with no co-location between the type definition and the description.
2. Python Schema dict (Synapse)
schemas.py defines SYNAPSE_SCHEMAS: dict[str, str] — a mapping from table name to a raw DuckDB column-definition string (e.g. "NAME STRING, DATABASE_ID BIGINT, ..."). There is nowhere in this format to attach a description to a column without inventing a new convention.
3. JSON Schema File (BigQuery)
analysis_types.json defines column names and types per analysis table. It has no description field and no table-level metadata.
4. No Explicit Schema (Snowflake, Legacy Synapse, Redshift)
These umbrellas have no DDL step at all. DuckDB infers the schema from the query result. There is no definition file to annotate.
Any approach that patches one pattern in isolation (e.g. appending COMMENT ON to DDL files) does not transfer to the other three.
What We Need
A single, standardized schema definition format that:
- Lives alongside the extractor files for each umbrella (co-located with the source)
- Expresses column name, type, and description in one place
- Is consumed by a shared utility in
duckdb_helpers.py that handles both table creation and comment application in one shot
- Covers all seven profilers.
The existing DDL files, SYNAPSE_SCHEMAS dict, and BigQuery JSON schema would be superseded by this format. Snowflake, Legacy Synapse, and Redshift would gain explicit schema definitions for the first time.
Goal
Adding comments would help us improve downstream consumption of the extract outputs by large language models.
DuckDB's
COMMENT ON TABLEandCOMMENT ON COLUMNstatements persist inside the.dbfile and surface in every schema introspection path. Adding them turns the extract into a self-describing artifact.Problems
There is no single definition site for extract schemas across the seven supported profiler umbrellas.
1. Paired DDL files (MSSQL, Oracle)
Each table has a
*_ddl.sqlfile with a bareCREATE TABLE IF NOT EXISTSstatement. Comments would need to be bolted on separately, with no co-location between the type definition and the description.2. Python Schema
dict(Synapse)schemas.pydefinesSYNAPSE_SCHEMAS: dict[str, str]— a mapping from table name to a raw DuckDB column-definition string (e.g."NAME STRING, DATABASE_ID BIGINT, ..."). There is nowhere in this format to attach a description to a column without inventing a new convention.3. JSON Schema File (BigQuery)
analysis_types.jsondefines column names and types per analysis table. It has nodescriptionfield and no table-level metadata.4. No Explicit Schema (Snowflake, Legacy Synapse, Redshift)
These umbrellas have no DDL step at all. DuckDB infers the schema from the query result. There is no definition file to annotate.
Any approach that patches one pattern in isolation (e.g. appending
COMMENT ONto DDL files) does not transfer to the other three.What We Need
A single, standardized schema definition format that:
duckdb_helpers.pythat handles both table creation and comment application in one shotThe existing DDL files,
SYNAPSE_SCHEMASdict, and BigQuery JSON schema would be superseded by this format. Snowflake, Legacy Synapse, and Redshift would gain explicit schema definitions for the first time.