You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@ossie.apache.org by jb...@apache.org on 2026/07/14 06:11:57 UTC

(ossie) 03/03: Add Ossie -> wisdom domain export direction

This is an automated email from the ASF dual-hosted git repository.

jbonofre pushed a commit to branch pr-13
in repository https://gitbox.apache.org/repos/asf/ossie.git

commit ba8672bb66582de66b32e7c7c6680acab06cfe2a
Author: Michael Wu <mi...@askwisdom.ai>
AuthorDate: Fri Jul 10 16:12:47 2026 -0700

    Add Ossie -> wisdom domain export direction
    
    Adds osi-to-wisdom, the reverse of the wisdom converter: an Ossie
    document becomes a wisdom domain export (format 1.0) consumable by
    wisdom's importDomain API.
    
    The mapping inverts wisdom-to-osi so round-trips are stable in both
    directions: model ai_context splits back into system instructions and
    knowledge items, fields split into columns and formulas, relationship
    direction is read as many-to-one with ai_context notes restoring
    one-to-one/many-to-many and composite keys becoming compound AND join
    conditions, and metrics attach to the table their expression
    references. IDs are deterministic (derived from names) and connections
    are per-dialect placeholders remapped at import time.
    
    Verified: Ossie -> wisdom -> Ossie round-trips of two real domain
    exports are byte-identical.
    
    Co-Authored-By: Claude Fable 5 <no...@anthropic.com>
---
 converters/wisdom/README.md                        |  32 +-
 converters/wisdom/src/ossie_wisdom/__init__.py     |   2 +
 converters/wisdom/src/ossie_wisdom/cli.py          |  48 ++-
 .../wisdom/src/ossie_wisdom/converter_issues.py    |   6 +
 .../wisdom/src/ossie_wisdom/osi_to_wisdom.py       | 393 +++++++++++++++++++++
 converters/wisdom/tests/test_osi_to_wisdom.py      | 229 ++++++++++++
 6 files changed, 700 insertions(+), 10 deletions(-)

diff --git a/converters/wisdom/README.md b/converters/wisdom/README.md
index 166c356..4b4064f 100644
--- a/converters/wisdom/README.md
+++ b/converters/wisdom/README.md
@@ -17,16 +17,18 @@
   under the License.
 -->
 
-# WisdomAI → Apache Ossie Converter
+# WisdomAI ↔ Apache Ossie Converter
 
-Converts a [WisdomAI](https://wisdom.ai) domain export (format `1.0`, produced by wisdom's
-`exportDomain` API) into an Ossie semantic model YAML document.
+Converts between a [WisdomAI](https://wisdom.ai) domain export (format `1.0`, produced by
+wisdom's `exportDomain` API and consumed by `importDomain`) and an Ossie semantic model
+YAML document.
 
 ## Usage
 
 ```bash
 pip install -e ../../python -e .
 ossie-wisdom wisdom-to-osi -i domain-export.json -o semantic_model.yaml
+ossie-wisdom osi-to-wisdom -i semantic_model.yaml -o domain-export.json
 ```
 
 Conversion warnings (information loss) are printed to stderr; the output YAML validates
@@ -73,9 +75,31 @@ Compound join conditions that are an `AND` of equality conditions are flattened
 positional `from_columns`/`to_columns` arrays; any other compound condition (e.g. `OR`)
 drops the relationship with a `RELATIONSHIP_DROPPED` warning.
 
+## Ossie → wisdom (export direction)
+
+`osi-to-wisdom` emits a domain export mirroring wisdom's `exportDomain` JSON, inverting
+the mapping above so that wisdom → Ossie → wisdom and Ossie → wisdom → Ossie round-trips
+are stable:
+
+- Model `ai_context` splits back into `domainSystemInstructions` (leading text) and one
+  knowledge item per `- ` bullet.
+- A field whose expression is just its own (possibly quoted) name becomes a column;
+  anything else becomes a formula. `dimension.is_time` becomes a `TIMESTAMP` data type
+  (wisdom re-derives exact types from the warehouse).
+- Relationships become `MANY_TO_ONE` edges (Ossie's `from` is the many side); the
+  `ai_context` notes written by `wisdom-to-osi` restore `ONE_TO_ONE`/`MANY_TO_MANY`, and
+  composite keys become compound `AND` join conditions.
+- Metrics attach to the first dataset their expression references (a
+  `METRIC_TABLE_UNRESOLVED` warning falls back to the first dataset).
+- Connections are per-dialect placeholders (`et-connection-snowflake`, ...) expected to be
+  remapped when the domain is imported; all IDs are derived deterministically from names,
+  so re-runs produce identical output.
+- Not representable in wisdom (dropped with warnings): extra semantic models beyond the
+  first, `unique_keys`, `custom_extensions`, and `ai_context` on fields/metrics (plus
+  synonyms/examples anywhere).
+
 ## Known limitations
 
-- One-way only (wisdom → Ossie). No Ossie → wisdom export yet.
 - Hidden columns and stale measures are converted anyway (with a `STALE_MEASURE` warning
   for the latter), so the output may reference columns wisdom itself hides from querying.
 - Out of scope for now: reviewed queries, recommended questions, synonym sets, row-level
diff --git a/converters/wisdom/src/ossie_wisdom/__init__.py b/converters/wisdom/src/ossie_wisdom/__init__.py
index 94153f4..337704d 100644
--- a/converters/wisdom/src/ossie_wisdom/__init__.py
+++ b/converters/wisdom/src/ossie_wisdom/__init__.py
@@ -16,11 +16,13 @@
 # under the License.
 
 from ossie_wisdom.converter_issues import ConverterIssue, ConverterIssueType, ConverterResult
+from ossie_wisdom.osi_to_wisdom import OSIToWisdomConverter
 from ossie_wisdom.wisdom_to_osi import WisdomToOSIConverter
 
 __all__ = [
     "ConverterIssue",
     "ConverterIssueType",
     "ConverterResult",
+    "OSIToWisdomConverter",
     "WisdomToOSIConverter",
 ]
diff --git a/converters/wisdom/src/ossie_wisdom/cli.py b/converters/wisdom/src/ossie_wisdom/cli.py
index 0274e62..6248697 100644
--- a/converters/wisdom/src/ossie_wisdom/cli.py
+++ b/converters/wisdom/src/ossie_wisdom/cli.py
@@ -19,6 +19,7 @@
 
 Usage:
     ossie-wisdom wisdom-to-osi -i domain-export.json -o output.yaml
+    ossie-wisdom osi-to-wisdom -i input.yaml -o domain-export.json
 """
 
 import argparse
@@ -26,24 +27,45 @@ import json
 import sys
 from pathlib import Path
 
+import yaml
+
+from ossie import OSIDocument
 from ossie_wisdom.converter_issues import ConverterIssueType
+from ossie_wisdom.osi_to_wisdom import OSIToWisdomConverter
 from ossie_wisdom.wisdom_to_osi import WisdomToOSIConverter
 
 _ISSUE_REASON: dict[ConverterIssueType, str] = {
     ConverterIssueType.UNSUPPORTED_DIALECT: "the connection dialect has no Ossie equivalent; expressions were emitted verbatim as ANSI_SQL",
     ConverterIssueType.CARDINALITY_LOSS: "Ossie relationships encode cardinality by direction and cannot represent many-to-many",
-    ConverterIssueType.RELATIONSHIP_DROPPED: "the relationship uses a join Ossie cannot represent (non-table source, OR/non-equi condition, or unknown dataset)",
+    ConverterIssueType.RELATIONSHIP_DROPPED: "the relationship uses a join that cannot be represented (non-table source, OR/non-equi condition, or unknown dataset)",
     ConverterIssueType.METRIC_NAME_COLLISION: "another table defines a measure with the same name; this one was prefixed with its table name",
     ConverterIssueType.STALE_MEASURE: "wisdom marked this measure stale; it was converted anyway",
     ConverterIssueType.DUPLICATE_FIELD_DROPPED: "the dataset already has a field with this name",
+    ConverterIssueType.EXTRA_MODEL_DROPPED: "a wisdom domain export holds a single domain; only the first semantic model was converted",
+    ConverterIssueType.AI_CONTEXT_DROPPED: "wisdom has no equivalent for ai_context at this level (or for synonyms/examples)",
+    ConverterIssueType.METRIC_TABLE_UNRESOLVED: "the metric expression references no known dataset; it was attached to the first dataset",
+    ConverterIssueType.MISSING_DIALECT_EXPRESSION: "no expression was available in the dataset's dialect or ANSI_SQL; the first available dialect was used",
+    ConverterIssueType.UNIQUE_KEYS_DROPPED: "wisdom has no unique-key construct",
+    ConverterIssueType.CUSTOM_EXTENSION_DROPPED: "wisdom cannot store Ossie custom extensions",
 }
 
 _DROPPED_ISSUE_TYPES = {
     ConverterIssueType.RELATIONSHIP_DROPPED,
     ConverterIssueType.DUPLICATE_FIELD_DROPPED,
+    ConverterIssueType.EXTRA_MODEL_DROPPED,
+    ConverterIssueType.AI_CONTEXT_DROPPED,
+    ConverterIssueType.UNIQUE_KEYS_DROPPED,
+    ConverterIssueType.CUSTOM_EXTENSION_DROPPED,
 }
 
 
+def _print_issues(result) -> None:
+    for issue in result.issues:
+        verb = "was dropped" if issue.issue_type in _DROPPED_ISSUE_TYPES else "was converted with loss"
+        reason = _ISSUE_REASON[issue.issue_type]
+        print(f"[WARNING] {issue.issue_type.value}: {issue.element_name} {verb} during conversion because {reason}", file=sys.stderr)
+
+
 def _cmd_wisdom_to_osi(args: argparse.Namespace) -> None:
     input_path = Path(args.input)
     output_path = Path(args.output)
@@ -51,15 +73,23 @@ def _cmd_wisdom_to_osi(args: argparse.Namespace) -> None:
     export = json.loads(input_path.read_text())
     result = WisdomToOSIConverter().convert(export)
 
-    for issue in result.issues:
-        verb = "was dropped" if issue.issue_type in _DROPPED_ISSUE_TYPES else "was converted with loss"
-        reason = _ISSUE_REASON[issue.issue_type]
-        print(f"[WARNING] {issue.issue_type.value}: {issue.element_name} {verb} during conversion because {reason}", file=sys.stderr)
-
+    _print_issues(result)
     output_path.write_text(result.output.to_osi_yaml())
     print(f"Written to {output_path}", file=sys.stderr)
 
 
+def _cmd_osi_to_wisdom(args: argparse.Namespace) -> None:
+    input_path = Path(args.input)
+    output_path = Path(args.output)
+
+    document = OSIDocument.model_validate(yaml.safe_load(input_path.read_text()))
+    result = OSIToWisdomConverter().convert(document)
+
+    _print_issues(result)
+    output_path.write_text(json.dumps(result.output, indent=2) + "\n")
+    print(f"Written to {output_path}", file=sys.stderr)
+
+
 def main() -> None:
     parser = argparse.ArgumentParser(
         prog="ossie-wisdom",
@@ -71,9 +101,15 @@ def main() -> None:
     wisdom_to_osi.add_argument("-i", "--input", required=True, metavar="FILE", help="Path to wisdom domain export JSON")
     wisdom_to_osi.add_argument("-o", "--output", required=True, metavar="FILE", help="Path for output Ossie YAML")
 
+    osi_to_wisdom = subparsers.add_parser("osi-to-wisdom", help="Convert Ossie YAML → domain-export.json")
+    osi_to_wisdom.add_argument("-i", "--input", required=True, metavar="FILE", help="Path to Ossie YAML")
+    osi_to_wisdom.add_argument("-o", "--output", required=True, metavar="FILE", help="Path for output wisdom domain export JSON")
+
     args = parser.parse_args()
     if args.command == "wisdom-to-osi":
         _cmd_wisdom_to_osi(args)
+    elif args.command == "osi-to-wisdom":
+        _cmd_osi_to_wisdom(args)
 
 
 if __name__ == "__main__":
diff --git a/converters/wisdom/src/ossie_wisdom/converter_issues.py b/converters/wisdom/src/ossie_wisdom/converter_issues.py
index f8c8fb2..fcef92f 100644
--- a/converters/wisdom/src/ossie_wisdom/converter_issues.py
+++ b/converters/wisdom/src/ossie_wisdom/converter_issues.py
@@ -29,6 +29,12 @@ class ConverterIssueType(Enum):
     METRIC_NAME_COLLISION = "METRIC_NAME_COLLISION"
     STALE_MEASURE = "STALE_MEASURE"
     DUPLICATE_FIELD_DROPPED = "DUPLICATE_FIELD_DROPPED"
+    EXTRA_MODEL_DROPPED = "EXTRA_MODEL_DROPPED"
+    AI_CONTEXT_DROPPED = "AI_CONTEXT_DROPPED"
+    METRIC_TABLE_UNRESOLVED = "METRIC_TABLE_UNRESOLVED"
+    MISSING_DIALECT_EXPRESSION = "MISSING_DIALECT_EXPRESSION"
+    UNIQUE_KEYS_DROPPED = "UNIQUE_KEYS_DROPPED"
+    CUSTOM_EXTENSION_DROPPED = "CUSTOM_EXTENSION_DROPPED"
 
 
 @dataclass(frozen=True)
diff --git a/converters/wisdom/src/ossie_wisdom/osi_to_wisdom.py b/converters/wisdom/src/ossie_wisdom/osi_to_wisdom.py
new file mode 100644
index 0000000..cc245d1
--- /dev/null
+++ b/converters/wisdom/src/ossie_wisdom/osi_to_wisdom.py
@@ -0,0 +1,393 @@
+# Licensed to the Apache Software Foundation (ASF) under one
+# or more contributor license agreements.  See the NOTICE file
+# distributed with this work for additional information
+# regarding copyright ownership.  The ASF licenses this file
+# to you under the Apache License, Version 2.0 (the
+# "License"); you may not use this file except in compliance
+# with the License.  You may obtain a copy of the License at
+#
+#   http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing,
+# software distributed under the License is distributed on an
+# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+# KIND, either express or implied.  See the License for the
+# specific language governing permissions and limitations
+# under the License.
+
+"""Converts an Ossie Document into a WisdomAI domain export (format 1.0).
+
+The output mirrors the JSON produced by wisdom's ``exportDomain`` RPC so it
+can be fed to ``importDomain``. Inverse of :mod:`ossie_wisdom.wisdom_to_osi`:
+model ``ai_context`` splits back into system instructions and knowledge items,
+fields split back into columns and formulas, relationship direction is read as
+many-to-one (with the ai_context notes restoring one-to-one/many-to-many), and
+metrics attach to the table their expression references.
+
+IDs are derived deterministically from element names, and connections are
+per-dialect placeholders expected to be remapped when the domain is imported.
+"""
+
+import hashlib
+import re
+from datetime import datetime, timezone
+from typing import Dict, List, Optional, Tuple
+
+from ossie import (
+    OSIAIContextObject,
+    OSIDataset,
+    OSIDialect,
+    OSIDocument,
+    OSIExpression,
+    OSISemanticModel,
+)
+from ossie_wisdom.converter_issues import ConverterIssue, ConverterIssueType, ConverterResult
+
+_WISDOM_DIALECT = {
+    OSIDialect.SNOWFLAKE: "snowflake",
+    OSIDialect.DATABRICKS: "databricks",
+    OSIDialect.ANSI_SQL: "ansi",
+}
+
+
+def _stable_id(prefix: str, value: str) -> str:
+    return f"{prefix}_{hashlib.md5(value.encode('utf-8')).hexdigest()}"
+
+
+class OSIToWisdomConverter:
+    """Converts an Ossie Document into a wisdom domain export dict."""
+
+    def convert(self, document: OSIDocument, exported_at: Optional[str] = None) -> ConverterResult[dict]:
+        issues: List[ConverterIssue] = []
+
+        model = document.semantic_model[0]
+        for extra in document.semantic_model[1:]:
+            issues.append(ConverterIssue(issue_type=ConverterIssueType.EXTRA_MODEL_DROPPED, element_name=extra.name))
+        self._report_custom_extensions(model, issues)
+
+        domain_uuid = _stable_id("ET_DOMAIN", model.name)
+        zsheet_refs = {
+            dataset.name: {"uuid": _stable_id("ET_ZSHEET", dataset.name), "name": dataset.name, "version": "1"}
+            for dataset in model.datasets
+        }
+        dataset_dialects = {dataset.name: self._infer_dataset_dialect(dataset) for dataset in model.datasets}
+        measures_by_dataset = self._assign_metrics(model, dataset_dialects, issues)
+
+        tables = []
+        table_metadata = []
+        for dataset in model.datasets:
+            zsheet = self._convert_dataset(
+                dataset, zsheet_refs, dataset_dialects[dataset.name], measures_by_dataset.get(dataset.name, []), issues
+            )
+            tables.append({"zsheet_uuid": zsheet_refs[dataset.name]["uuid"], "zsheet_json": zsheet})
+            location = zsheet["location"]
+            table_metadata.append(
+                {
+                    "zsheet_uuid": zsheet_refs[dataset.name]["uuid"],
+                    "connection_id": location["connectionId"],
+                    "database": location["database"],
+                    "schema": location["schema"],
+                    "table_name": location["dbTable"],
+                }
+            )
+
+        instructions, knowledge = self._split_ai_context(model, issues)
+        domain_zsheet: dict = {
+            "ref": {"uuid": domain_uuid, "name": model.name, "version": "1"},
+            "zsheetType": "DOMAIN",
+            "relationshipGraph": {
+                "zsheets": list(zsheet_refs.values()),
+                "relationships": self._convert_relationships(model, zsheet_refs, issues),
+            },
+        }
+        if model.description:
+            domain_zsheet["description"] = model.description
+        if instructions:
+            domain_zsheet["domainSystemInstructions"] = instructions
+        if knowledge:
+            domain_zsheet["knowledge"] = knowledge
+
+        connections = [
+            {"connection_id": f"et-connection-{dialect}", "dialect": dialect, "name": dialect}
+            for dialect in sorted({_WISDOM_DIALECT[d] for d in dataset_dialects.values()})
+        ]
+
+        export = {
+            "version": "1.0",
+            "export_metadata": {
+                "exported_at": exported_at or datetime.now(timezone.utc).isoformat(),
+                "source_domain_id": domain_uuid,
+                "domain_name": model.name,
+            },
+            "domain": {"zsheet_json": domain_zsheet},
+            "tables": tables,
+            "connections": connections,
+            "reviewed_queries": {"ref": None, "items_json": "{}"},
+            "synonym_sets": {"ref": None, "items_json": "{}"},
+            "table_metadata": table_metadata,
+            "recommended_questions": [],
+        }
+        return ConverterResult(output=export, issues=issues)
+
+    def _report_custom_extensions(self, model: OSISemanticModel, issues: List[ConverterIssue]) -> None:
+        elements = [(model.name, model.custom_extensions)]
+        for dataset in model.datasets:
+            elements.append((dataset.name, dataset.custom_extensions))
+            for field in dataset.fields or []:
+                elements.append((f"{dataset.name}.{field.name}", field.custom_extensions))
+        for relationship in model.relationships or []:
+            elements.append((relationship.name, relationship.custom_extensions))
+        for metric in model.metrics or []:
+            elements.append((metric.name, metric.custom_extensions))
+        for name, extensions in elements:
+            if extensions:
+                issues.append(
+                    ConverterIssue(issue_type=ConverterIssueType.CUSTOM_EXTENSION_DROPPED, element_name=name)
+                )
+
+    def _infer_dataset_dialect(self, dataset: OSIDataset) -> OSIDialect:
+        for field in dataset.fields or []:
+            for entry in field.expression.dialects:
+                if entry.dialect in (OSIDialect.SNOWFLAKE, OSIDialect.DATABRICKS):
+                    return entry.dialect
+        return OSIDialect.ANSI_SQL
+
+    def _split_ai_context(self, model: OSISemanticModel, issues: List[ConverterIssue]) -> Tuple[str, List[dict]]:
+        ai_context = model.ai_context
+        if ai_context is None:
+            return "", []
+        if isinstance(ai_context, OSIAIContextObject):
+            if ai_context.synonyms or ai_context.examples:
+                issues.append(ConverterIssue(issue_type=ConverterIssueType.AI_CONTEXT_DROPPED, element_name=model.name))
+            text = ai_context.instructions or ""
+        else:
+            text = ai_context
+
+        instruction_lines: List[str] = []
+        contents: List[str] = []
+        current: Optional[str] = None
+        for line in text.split("\n"):
+            if line.startswith("- "):
+                if current is not None:
+                    contents.append(current)
+                current = line[2:]
+            elif current is not None:
+                current += "\n" + line
+            else:
+                instruction_lines.append(line)
+        if current is not None:
+            contents.append(current)
+
+        knowledge = [
+            {"name": content, "content": content, "id": _stable_id("ET_UNSTRUCTURED_KNOWLEDGE", content)}
+            for content in contents
+            if content.strip()
+        ]
+        return "\n".join(instruction_lines).strip(), knowledge
+
+    def _convert_dataset(
+        self,
+        dataset: OSIDataset,
+        zsheet_refs: Dict[str, dict],
+        dialect: OSIDialect,
+        measures: List[dict],
+        issues: List[ConverterIssue],
+    ) -> dict:
+        ref = zsheet_refs[dataset.name]
+        ref_lite = {"uuid": ref["uuid"], "name": ref["name"]}
+        database, schema, table = self._split_source(dataset.source)
+
+        columns: List[dict] = []
+        formulas: List[dict] = []
+        for field in dataset.fields or []:
+            expression = self._pick_expression(field.expression, dialect, f"{dataset.name}.{field.name}", issues)
+            if field.ai_context is not None:
+                issues.append(
+                    ConverterIssue(
+                        issue_type=ConverterIssueType.AI_CONTEXT_DROPPED, element_name=f"{dataset.name}.{field.name}"
+                    )
+                )
+            properties: dict = {}
+            if field.label:
+                properties["displayName"] = field.label
+            if field.dimension and field.dimension.is_time:
+                properties["dataType"] = "TIMESTAMP"
+            if self._is_bare_column(expression, field.name):
+                column: dict = {"name": field.name}
+                if field.description:
+                    column["description"] = field.description
+                if properties:
+                    column["properties"] = properties
+                column["location"] = {"name": field.name, "zsheetRef": ref_lite}
+                columns.append(column)
+            else:
+                formula: dict = {"name": field.name, "expression": expression}
+                if field.description:
+                    formula["description"] = field.description
+                if properties:
+                    formula["properties"] = properties
+                formula["location"] = {"name": field.name, "zsheetRef": ref_lite}
+                formula["id"] = _stable_id("FORMULA", f"{dataset.name}.{field.name}")
+                formulas.append(formula)
+
+        zsheet: dict = {
+            "ref": ref,
+            "location": {
+                "database": database,
+                "schema": schema,
+                "dbTable": table,
+                "connectionId": f"et-connection-{_WISDOM_DIALECT[dialect]}",
+            },
+            "source": {"alias": dataset.name, "zsheet": ref_lite},
+            "columns": columns,
+        }
+        if dataset.description:
+            zsheet["description"] = dataset.description
+        if formulas:
+            zsheet["formulas"] = formulas
+        if measures:
+            zsheet["measures"] = measures
+        if dataset.primary_key:
+            zsheet["primaryKey"] = {"columns": list(dataset.primary_key)}
+        if dataset.unique_keys:
+            issues.append(ConverterIssue(issue_type=ConverterIssueType.UNIQUE_KEYS_DROPPED, element_name=dataset.name))
+        if dataset.ai_context is not None:
+            content = self._ai_context_text(dataset.ai_context, dataset.name, issues)
+            if content:
+                zsheet["knowledge"] = [
+                    {"name": content, "content": content, "id": _stable_id("ET_UNSTRUCTURED_KNOWLEDGE", content)}
+                ]
+        return zsheet
+
+    def _ai_context_text(self, ai_context, element_name: str, issues: List[ConverterIssue]) -> str:
+        if isinstance(ai_context, OSIAIContextObject):
+            if ai_context.synonyms or ai_context.examples:
+                issues.append(
+                    ConverterIssue(issue_type=ConverterIssueType.AI_CONTEXT_DROPPED, element_name=element_name)
+                )
+            return ai_context.instructions or ""
+        return ai_context
+
+    def _split_source(self, source: str) -> Tuple[str, str, str]:
+        parts = source.split(".")
+        if len(parts) >= 3:
+            return parts[0], parts[1], ".".join(parts[2:])
+        if len(parts) == 2:
+            return "", parts[0], parts[1]
+        return "", "", source
+
+    def _pick_expression(
+        self, expression: OSIExpression, dialect: OSIDialect, element_name: str, issues: List[ConverterIssue]
+    ) -> str:
+        by_dialect = {entry.dialect: entry.expression for entry in expression.dialects}
+        if dialect in by_dialect:
+            return by_dialect[dialect]
+        if OSIDialect.ANSI_SQL in by_dialect:
+            return by_dialect[OSIDialect.ANSI_SQL]
+        issues.append(
+            ConverterIssue(issue_type=ConverterIssueType.MISSING_DIALECT_EXPRESSION, element_name=element_name)
+        )
+        return expression.dialects[0].expression
+
+    def _is_bare_column(self, expression: str, name: str) -> bool:
+        return expression in (name, f'"{name}"', f"`{name}`")
+
+    def _assign_metrics(
+        self, model: OSISemanticModel, dataset_dialects: Dict[str, OSIDialect], issues: List[ConverterIssue]
+    ) -> Dict[str, List[dict]]:
+        measures: Dict[str, List[dict]] = {}
+        dataset_names = [dataset.name for dataset in model.datasets]
+        for metric in model.metrics or []:
+            if metric.ai_context is not None:
+                issues.append(
+                    ConverterIssue(issue_type=ConverterIssueType.AI_CONTEXT_DROPPED, element_name=metric.name)
+                )
+            # Attach to a dataset first so the expression can be picked in that dataset's dialect.
+            reference_text = " ".join(entry.expression for entry in metric.expression.dialects)
+            dataset_name = self._find_referenced_dataset(reference_text, dataset_names)
+            if dataset_name is None:
+                dataset_name = dataset_names[0]
+                issues.append(
+                    ConverterIssue(issue_type=ConverterIssueType.METRIC_TABLE_UNRESOLVED, element_name=metric.name)
+                )
+            expression = self._pick_expression(
+                metric.expression, dataset_dialects[dataset_name], metric.name, issues
+            )
+            ref = {"uuid": _stable_id("ET_ZSHEET", dataset_name), "name": dataset_name}
+            measure: dict = {"name": metric.name, "expression": expression}
+            if metric.description:
+                measure["description"] = metric.description
+            measure["location"] = {"name": metric.name, "zsheetRef": ref}
+            measure["id"] = _stable_id("MEASURE", f"{dataset_name}.{metric.name}")
+            measures.setdefault(dataset_name, []).append(measure)
+        return measures
+
+    def _find_referenced_dataset(self, expression: str, dataset_names: List[str]) -> Optional[str]:
+        best: Optional[str] = None
+        best_position = len(expression) + 1
+        for name in dataset_names:
+            pattern = re.compile(r'(?<![A-Za-z0-9_"`])["`]?' + re.escape(name) + r'["`]?\s*\.')
+            match = pattern.search(expression)
+            if match and match.start() < best_position:
+                best = name
+                best_position = match.start()
+        return best
+
+    def _convert_relationships(
+        self, model: OSISemanticModel, zsheet_refs: Dict[str, dict], issues: List[ConverterIssue]
+    ) -> List[dict]:
+        edges = []
+        for relationship in model.relationships or []:
+            left = relationship.from_dataset
+            right = relationship.to
+            if left not in zsheet_refs or right not in zsheet_refs:
+                issues.append(
+                    ConverterIssue(
+                        issue_type=ConverterIssueType.RELATIONSHIP_DROPPED, element_name=relationship.name
+                    )
+                )
+                continue
+            relationship_type = "MANY_TO_ONE"
+            if isinstance(relationship.ai_context, str):
+                if relationship.ai_context.startswith("one-to-one"):
+                    relationship_type = "ONE_TO_ONE"
+                elif relationship.ai_context.startswith("many-to-many"):
+                    relationship_type = "MANY_TO_MANY"
+                else:
+                    issues.append(
+                        ConverterIssue(
+                            issue_type=ConverterIssueType.AI_CONTEXT_DROPPED, element_name=relationship.name
+                        )
+                    )
+            elif relationship.ai_context is not None:
+                issues.append(
+                    ConverterIssue(issue_type=ConverterIssueType.AI_CONTEXT_DROPPED, element_name=relationship.name)
+                )
+
+            left_ref = {"uuid": zsheet_refs[left]["uuid"], "name": left}
+            right_ref = {"uuid": zsheet_refs[right]["uuid"], "name": right}
+            conditions = [
+                {
+                    "leftColumn": {"name": from_column, "zsheetRef": left_ref},
+                    "rightColumn": {"name": to_column, "zsheetRef": right_ref},
+                }
+                for from_column, to_column in zip(relationship.from_columns, relationship.to_columns)
+            ]
+            properties: dict = {"relationshipType": relationship_type}
+            if len(conditions) == 1:
+                properties["joinCondition"] = conditions[0]
+            else:
+                properties["compoundJoinCondition"] = {
+                    "nestedCondition": {
+                        "logicalOperator": "AND",
+                        "conditions": [{"simpleCondition": condition} for condition in conditions],
+                    }
+                }
+            edges.append(
+                {
+                    "properties": properties,
+                    "leftDataSource": {"zsheet": left_ref},
+                    "rightDataSource": {"zsheet": right_ref},
+                }
+            )
+        return edges
diff --git a/converters/wisdom/tests/test_osi_to_wisdom.py b/converters/wisdom/tests/test_osi_to_wisdom.py
new file mode 100644
index 0000000..1ee1717
--- /dev/null
+++ b/converters/wisdom/tests/test_osi_to_wisdom.py
@@ -0,0 +1,229 @@
+# Licensed to the Apache Software Foundation (ASF) under one
+# or more contributor license agreements.  See the NOTICE file
+# distributed with this work for additional information
+# regarding copyright ownership.  The ASF licenses this file
+# to you under the Apache License, Version 2.0 (the
+# "License"); you may not use this file except in compliance
+# with the License.  You may obtain a copy of the License at
+#
+#   http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing,
+# software distributed under the License is distributed on an
+# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+# KIND, either express or implied.  See the License for the
+# specific language governing permissions and limitations
+# under the License.
+
+import json
+from pathlib import Path
+
+import pytest
+
+from ossie import (
+    OSIDataset,
+    OSIDialect,
+    OSIDialectExpression,
+    OSIDocument,
+    OSIExpression,
+    OSIField,
+    OSIRelationship,
+    OSISemanticModel,
+)
+from ossie_wisdom import ConverterIssueType, OSIToWisdomConverter, WisdomToOSIConverter
+
+FIXTURE = Path(__file__).parent / "fixtures" / "sample_export.json"
+
+
+def _snowflake(expression):
+    return OSIExpression(dialects=[OSIDialectExpression(dialect=OSIDialect.SNOWFLAKE, expression=expression)])
+
+
+@pytest.fixture(scope="module")
+def osi_document():
+    export = json.loads(FIXTURE.read_text())
+    return WisdomToOSIConverter().convert(export).output
+
+
+@pytest.fixture(scope="module")
+def result(osi_document):
+    return OSIToWisdomConverter().convert(osi_document, exported_at="2026-07-10T00:00:00+00:00")
+
+
+@pytest.fixture(scope="module")
+def export(result):
+    return result.output
+
+
+def _issues_of(result, issue_type):
+    return [issue for issue in result.issues if issue.issue_type is issue_type]
+
+
+def _table(export, name):
+    return next(
+        table["zsheet_json"] for table in export["tables"] if table["zsheet_json"]["ref"]["name"] == name
+    )
+
+
+def test_export_envelope(export):
+    assert export["version"] == "1.0"
+    assert export["export_metadata"]["domain_name"] == "Sample Sales"
+    assert export["export_metadata"]["source_domain_id"] == export["domain"]["zsheet_json"]["ref"]["uuid"]
+    domain = export["domain"]["zsheet_json"]
+    assert domain["zsheetType"] == "DOMAIN"
+    assert domain["description"] == "Synthetic sales domain used for converter tests"
+
+
+def test_ai_context_splits_into_instructions_and_knowledge(export):
+    domain = export["domain"]["zsheet_json"]
+    assert domain["domainSystemInstructions"] == "Only answer questions about sales data."
+    assert [knowledge["content"] for knowledge in domain["knowledge"]] == [
+        "The fiscal year starts in February.",
+        "Pipeline refers to open orders expected to close this quarter.",
+    ]
+
+
+def test_tables_and_locations(export):
+    orders = _table(export, "orders")
+    assert orders["location"]["database"] == "analytics"
+    assert orders["location"]["schema"] == "sales"
+    assert orders["location"]["dbTable"] == "orders"
+    assert orders["primaryKey"] == {"columns": ["order_id"]}
+    assert _table(export, "customers")["primaryKey"] == {"columns": ["customer_id"]}
+    metadata = {entry["zsheet_uuid"]: entry for entry in export["table_metadata"]}
+    assert metadata[orders["ref"]["uuid"]]["table_name"] == "orders"
+
+
+def test_fields_split_into_columns_and_formulas(export):
+    orders = _table(export, "orders")
+    column_names = [column["name"] for column in orders["columns"]]
+    assert "order_id" in column_names
+    # A quoted bare-name expression is recognized as a plain column.
+    assert "Discount - Percent" in column_names
+    formulas = {formula["name"]: formula for formula in orders["formulas"]}
+    assert set(formulas) == {"is_large"}
+    assert formulas["is_large"]["expression"] == 'CASE WHEN "orders"."amount" > 100 THEN TRUE ELSE FALSE END'
+    assert formulas["is_large"]["properties"]["displayName"] == "Is Large"
+    status = next(column for column in orders["columns"] if column["name"] == "status")
+    assert status["description"] == "Current order status"
+    assert status["properties"]["displayName"] == "Order Status"
+
+
+def test_metrics_attach_to_referenced_tables(export):
+    orders_measures = {measure["name"] for measure in _table(export, "orders")["measures"]}
+    customers_measures = {measure["name"] for measure in _table(export, "customers")["measures"]}
+    assert orders_measures == {"total_amount"}
+    assert customers_measures == {"customers_total_amount"}
+
+
+def test_relationship_types_restored(export):
+    edges = export["domain"]["zsheet_json"]["relationshipGraph"]["relationships"]
+    types = [edge["properties"]["relationshipType"] for edge in edges]
+    assert types == ["MANY_TO_ONE", "MANY_TO_ONE", "MANY_TO_MANY", "MANY_TO_ONE"]
+    compound = edges[3]["properties"]["compoundJoinCondition"]["nestedCondition"]
+    assert compound["logicalOperator"] == "AND"
+    assert len(compound["conditions"]) == 2
+
+
+def test_connections_are_per_dialect(export):
+    dialects = {connection["dialect"] for connection in export["connections"]}
+    assert dialects == {"snowflake", "ansi"}
+    assert _table(export, "orders")["location"]["connectionId"] == "et-connection-snowflake"
+    assert _table(export, "tags")["location"]["connectionId"] == "et-connection-ansi"
+
+
+def test_round_trip_preserves_osi_document(osi_document, export):
+    round_tripped = WisdomToOSIConverter().convert(export).output
+    assert round_tripped == osi_document
+
+
+def test_deterministic_output(osi_document, export):
+    again = OSIToWisdomConverter().convert(osi_document, exported_at="2026-07-10T00:00:00+00:00").output
+    assert again == export
+
+
+def test_extra_models_and_unrepresentable_elements_are_reported():
+    dataset = OSIDataset(
+        name="orders",
+        source="analytics.sales.orders",
+        unique_keys=[["order_id"]],
+        fields=[
+            OSIField(name="order_id", expression=_snowflake("order_id"), ai_context="the identifier"),
+        ],
+    )
+    second = OSISemanticModel(name="second", datasets=[OSIDataset(name="d", source="a.b.c")])
+    document = OSIDocument(
+        semantic_model=[
+            OSISemanticModel(
+                name="first",
+                datasets=[dataset],
+                relationships=[
+                    OSIRelationship(
+                        name="orders_to_missing",
+                        from_dataset="orders",
+                        to="missing",
+                        from_columns=["x"],
+                        to_columns=["y"],
+                    )
+                ],
+            ),
+            second,
+        ]
+    )
+    result = OSIToWisdomConverter().convert(document, exported_at="2026-07-10T00:00:00+00:00")
+    assert [issue.element_name for issue in _issues_of(result, ConverterIssueType.EXTRA_MODEL_DROPPED)] == ["second"]
+    assert [issue.element_name for issue in _issues_of(result, ConverterIssueType.UNIQUE_KEYS_DROPPED)] == ["orders"]
+    assert [issue.element_name for issue in _issues_of(result, ConverterIssueType.AI_CONTEXT_DROPPED)] == [
+        "orders.order_id"
+    ]
+    assert [issue.element_name for issue in _issues_of(result, ConverterIssueType.RELATIONSHIP_DROPPED)] == [
+        "orders_to_missing"
+    ]
+    assert result.output["domain"]["zsheet_json"]["relationshipGraph"]["relationships"] == []
+
+
+def test_one_to_one_note_restores_relationship_type():
+    document = OSIDocument(
+        semantic_model=[
+            OSISemanticModel(
+                name="m",
+                datasets=[
+                    OSIDataset(name="a", source="db.s.a"),
+                    OSIDataset(name="b", source="db.s.b"),
+                ],
+                relationships=[
+                    OSIRelationship(
+                        name="a_to_b",
+                        from_dataset="a",
+                        to="b",
+                        from_columns=["id"],
+                        to_columns=["id"],
+                        ai_context="one-to-one relationship",
+                    )
+                ],
+            )
+        ]
+    )
+    export = OSIToWisdomConverter().convert(document, exported_at="2026-07-10T00:00:00+00:00").output
+    edges = export["domain"]["zsheet_json"]["relationshipGraph"]["relationships"]
+    assert edges[0]["properties"]["relationshipType"] == "ONE_TO_ONE"
+
+
+def test_unresolved_metric_attaches_to_first_dataset():
+    from ossie import OSIMetric
+
+    document = OSIDocument(
+        semantic_model=[
+            OSISemanticModel(
+                name="m",
+                datasets=[OSIDataset(name="a", source="db.s.a"), OSIDataset(name="b", source="db.s.b")],
+                metrics=[OSIMetric(name="row_count", expression=_snowflake("COUNT(*)"))],
+            )
+        ]
+    )
+    result = OSIToWisdomConverter().convert(document, exported_at="2026-07-10T00:00:00+00:00")
+    export = result.output
+    assert [measure["name"] for measure in _table(export, "a")["measures"]] == ["row_count"]
+    assert [issue.element_name for issue in _issues_of(result, ConverterIssueType.METRIC_TABLE_UNRESOLVED)] == [
+        "row_count"
+    ]