Transformations

Data passes through three layers. Each one has a different purpose and a different level of intervention.

Raw data

The raw layer aims to preserve the source. The only changes applied are those needed to store the data in an open format that can be queried with SQL:

• Normalized column names: lowercase, without accents, and special characters are replaced with _. If two columns end up with the same name, a suffix is added to tell them apart.
• Dates and times in UTC with microsecond precision. A datetime without a time zone is interpreted as UTC.
• Types with no direct equivalent, such as standalone times, durations, and very large integers, are stored as text so no precision is lost.
• Nested structures from APIs (JSON) are stored as JSON text.
• Date partition columns to speed up queries.

The types declared in the source, and its primary and foreign keys, are kept as metadata.

Schema changes

• A new column in the source is added to the table without rebuilding it.
• An incompatible change (for example, a type change) in a full load recreates the table with the new schema.
• Every schema change is recorded per run: tables or columns added, removed, and type changes.

Write protections

Each write is an Iceberg atomic transaction: the table is seen fully before or fully after, never halfway. In addition, in full loads:

• An empty extraction never replaces a table that has data.
• If the number of rows drops sharply compared with the previous load, the load is discarded and the previous version is kept.
• If the load fails midway, it is rolled back.

Processed data

The processed layer is where data is cleaned. For each table, an AI agent writes a SELECT query that produces the clean version. The result is standard SQL, visible and auditable.

What kind of cleaning is applied

• Trimming extra whitespace.
• Converting empty values or “no data” markers to NULL.
• Converting text to number or date when the content allows it, using safe conversions that do not fail on invalid values.
• Normalizing date formats.
• Keeping identifiers in their original type, so leading zeros and code formats are not lost.

How a transformation is validated

No query proposed by the AI is applied without passing automated checks. Each proposal:

1. Is run against the real data before it is accepted.
2. Cannot lose rows. Filters, aggregations, deduplications, and limits that shrink the table are rejected.
3. Cannot lose columns. All the source columns must be present.
4. Cannot leave a column empty that had data in the source.
5. Can only use type conversions that are compatible with the storage format.

If a proposal does not pass the checks, the agent has a limited number of attempts to fix it. If it does not succeed, the processed table is left as an unchanged copy of the raw table. A transformation that has not passed validation is never published.

Transparency and control

• Per-column change report: each modified column shows what was changed and why.
• Version history: each accepted query is saved with a version and its origin: generated by the AI or corrected by a user.
• Manual correction: a user with the right permissions can replace a table’s cleaning query. The new query goes through the same validation, and the system reports which results tables would be affected before applying it.
• Reconciliation across tables: the columns used to join tables are aligned to the same type, so that joins work.

Data-quality findings

During profiling, the following are detected and reported:

• Outliers.
• Inconsistencies in uppercase and lowercase.
• Duplicates.
• Candidate keys.
• Probable relationships between tables, with their percentage of orphan records.

Findings are shown next to each table. They do not modify the data on their own.

Results tables

Results tables are saved analyses: a metric, a join across systems, a report. They are built on top of processed data, and also on top of other results tables.

A results table can be created in two ways:

• In natural language: the user describes what they need and an AI agent writes the SQL query.
• With your own SQL: the user, or their AI assistant via MCP, provides the query. Teramot validates it and can test it without creating anything.

In both cases:

• The query stays visible and editable.
• It can only read tables from the same project, or tables explicitly shared from another project.
• The table is materialized in the data lake. Querying it is fast and always returns the same result until the next refresh.
• It is recalculated automatically when the processed data it depends on is refreshed.
• Its lineage (which tables it depends on) can be viewed in the application.

If a source table is deleted, the results tables that depended on it are marked as outdated. If a schema change breaks a results table, the repair waits for the user’s confirmation instead of being retried indefinitely.