Strata SQL

Import from SQL scripts

A cold start from the SQL you already have: a pg_dump --schema-only, a mysqldump --no-data, SSMS "Script Table as", Snowflake GET_DDL, a Databricks SHOW CREATE TABLE, or the CREATE TABLE files in your repository.

DDL fills the physical level (tables, columns, keys, relationships, views). Queries fill the semantic layer (metrics, joins no foreign key declares, lineage).

What you need#

One or more .sql files or folders of them. No database connection, no account: nothing runs. Which file you give as DDL and which as queries is for your own tidiness; every file is read the same way, so a dump with tables, views, inserts and reports in one script works. UTF-8 and UTF-16 (what SSMS and PowerShell write) are both read.

How to import#

  • New project: Strata SQL: New Project → From DDL / SQL scripts…, then the DDL files or folders, optionally query files, and the SQL dialect. The dialect Strata SQL detected is listed first, marked (detected) or (best guess, please check); you can always choose another.
  • Into the open model: Strata SQL: Import from DDL… shows a preview of what would change and writes only when you click Apply. If the scripts contain INSERT ... VALUES rows, it also offers Apply with sample data.
  • Re-sync: Strata SQL: Re-sync from SQL scripts reuses the files and dialect stored in the manifest's sql block and asks only for the final Apply.
  • Terminal:
strata-sql import-sql --ddl schema.sql db/tables/ --dml reports.sql --dir . --name "Shop"
strata-sql import-sql --dir .            # re-sync a one-model project

Options: --dialect postgres|mysql|sqlserver|oracle|snowflake|bigquery|databricks|trino|sqlite|duckdb|redshift, --sample-rows 100, --model <path>, and --dry-run to list what would be created and write nothing.

A dry run on two tiny files prints:

New project from SQL scripts: 2 tables added, 7 columns added,
1 relationship added, 1 tag added, 2 metrics added, 1 schema added
  + schema public
  + table public.customers (3 columns)
  + table public.orders (4 columns)
  + relationship public.customers.customer_id -> public.orders.customer_id
    (declared foreign key)
  + metric Revenue (derived from SQL)
  + metric Orders (derived from SQL)
  + tag sql-derived

What is imported#

From DDL

  • CREATE TABLE, CREATE VIEW, CREATE SCHEMA and CREATE DATABASE, plus ALTER TABLE ... ADD CONSTRAINT, CREATE INDEX and COMMENT ON.
  • Column types, NOT NULL, defaults, primary and unique keys (inline, table-level and by ALTER).
  • Foreign keys become relationships, 1..1 when the child is unique, else 1..M. Composite keys become one relationship per column.
  • Bridge tables: a table whose primary key is exactly two foreign-key columns to two different tables, which nothing references, gets a many-to-many relationship through it.
  • Comments become descriptions. Indexes, CHECK constraints, engines, clustering, partitioning and other options are kept as sql_* properties, as written.

From queries (SELECT, views, CREATE TABLE AS, INSERT ... SELECT, MERGE, UPDATE ... FROM)

  • Each aggregate becomes a starter metric tagged sql-derived, with its grain, group-by and filter in the description, for you to review.
  • Joins between two tables become relationships marked inferred.
  • Lineage is kept as sql.depends_on on the table a statement loads. Views and CREATE TABLE AS tables get the columns of their query.
  • INSERT ... VALUES rows become sample data only on request, never over an existing file.

Dialects#

PostgreSQL, Redshift, MySQL / MariaDB, SQL Server, Oracle, Snowflake, BigQuery, Databricks / Spark / Hive, Trino / Athena, SQLite and DuckDB. The dialect decides the platform and so two- or three-part names.

Re-sync: what the scripts own#

Scripts own a table's kind, a view's definition, a column's type and default, and the sql_* properties. They only add catalogs, schemas, tables, columns, key flags, relationships, metrics and the tag. Descriptions are set only when empty, so a description you edited is never replaced. A table that left the scripts is kept and reported. A metric you deleted is never recreated and one you edited is never touched. Nothing changes, and no version is written, when nothing changed in the scripts.

Strata SQL refuses to re-sync when the model differs from its version log, when the log has an unresolved fork, and for a project split by domain, an archived model or a project-index file.

Limits, honestly#

  • Stored procedures, functions, triggers and other procedural code are listed, not read, because their bodies may build tables dynamically.
  • T-SQL with neither ; nor GO cannot be split.
  • ALTER TABLE ... RENAME is not followed.
  • View column types that come from expressions are unknown (STRING).
  • COPY data is skipped.
  • Bridge tables with a three-column key are not recognised as many-to-many.
  • The fixtures are hand-written in the style of each tool's output. No dialect has been tried against a real production dump. If a real dump reads wrongly, that is a bug worth reporting.