Auto-Ingestion: From Database to Semantic Models¶

conn = duckdb.connect(DB_PATH, read_only=True)
for table in ["customers", "stores", "products", "orders", "items", "supplies", "tweets"]:
    count = conn.execute(f"SELECT COUNT(*) FROM {table}").fetchone()[0]
    print(f"{table:>15}: {count:>8,} rows")
conn.close()

from slayer.core.models import DatasourceConfig
from slayer.engine.ingestion import _build_fk_graph, _compute_transitive_closure

ds = DatasourceConfig(name="jaffle_shop", type="duckdb", database=DB_PATH)
sa_engine = sa.create_engine(ds.resolve_env_vars().get_connection_string())
inspector = sa.inspect(sa_engine)
table_names = inspector.get_table_names()

fk_graph = _build_fk_graph(inspector=inspector, table_names=table_names, schema=None)

print("FK Graph (table -> tables it references):")
for table in sorted(fk_graph):
    refs = sorted(fk_graph[table])
    print(f"  {table} -> {refs}")

print()
print("Tables with no FK references (leaf tables):")
for t in sorted(table_names):
    if t not in fk_graph:
        print(f"  {t}")

# Transitive closure: what tables are reachable from items?
for table in ["orders", "items"]:
    reachable = _compute_transitive_closure(fk_graph, table)
    print(f"{table} can reach: {sorted(reachable)}")

sa_engine.dispose()

orders_model = next(m for m in models if m.name == "orders")
items_model = next(m for m in models if m.name == "items")

print("=== orders model joins (direct FKs only) ===")
for j in orders_model.joins:
    src, tgt = j.join_pairs[0]
    print(f"  -> {j.target_model}  ON {src} = {tgt}")

print()
print("=== items model joins (direct FKs only) ===")
for j in items_model.joins:
    src, tgt = j.join_pairs[0]
    print(f"  -> {j.target_model}  ON {src} = {tgt}")

print()
print("Note: items has no join to customers or stores —")
print("those are reached at query time via orders.joins.")

# Iterate the unified `columns` list — v2 merges v1's separate dimensions and
# row-level measures into one list. Each Column carries data type, primary_key,
# allowed_aggregations (whitelist), and filter (CASE-WHEN at aggregation time).
print("=== orders model columns ===")
print(f"{'name':<25} {'sql':<25} {'type':<10}")
print("-" * 70)
for col in orders_model.columns:
    pk = " [PK]" if col.primary_key else ""
    sql_str = col.sql or col.name
    print(f"{col.name:<25} {sql_str:<25} {str(col.type):<10}{pk}")

# In v2 `model.measures` holds *named formulas* (ModelMeasure objects), not
# row-level measure definitions. After auto-ingest the list is empty by
# default; agents/users populate it via edit_model with reusable formulas
# like `{"name": "aov", "formula": "revenue:sum / *:count"}`.
print("=== orders model measures (named formulas) ===")
print(f"{len(orders_model.measures)} formula(s) defined.")

# 1-hop: items -> products
result = engine.execute_sync(
    query={
        "source_model": "items",
        "measures": ["*:count"],
        "dimensions": ["products.name"],
        "order": [{"column": {"name": "_count"}, "direction": "desc"}],
        "limit": 5,
    }
)

print("=== 1-hop: items -> products ===")
for row in result.data:
    print(
        f"  {row['items.products.name']}: {row['items._count']} items"
    )

# Multi-hop: items -> orders -> customers
result = engine.execute_sync(
    query={
        "source_model": "items",
        "measures": ["*:count"],
        "dimensions": ["orders.customers.name"],
        "order": [{"column": {"name": "_count"}, "direction": "desc"}],
        "limit": 5,
    }
)

print("\n=== Multi-hop: items -> orders -> customers ===")
for row in result.data:
    print(
        f"  {row['items.orders.customers.name']}: {row['items._count']} items"
    )