Clickzetta Semantic View

ClickZetta Semantic View

A Semantic View is a schema-level logical data model in ClickZetta Lakehouse. It encapsulates multi-table relationships, dimensions, and metrics into a business semantic layer so that the whole organization queries consistent, reusable definitions instead of re-writing JOINs and metric logic every time.

• For analysis: business users query cross-table data with business terms — no manual JOIN or GROUP BY.
• For governance: metric definitions are managed centrally, avoiding "same metric name, different numbers".

Reference docs (read on demand):

• references/semantic-view-reference.md — complete CREATE / query / management syntax.
• references/metrics-and-modeling.md — advanced metrics (conditional, arithmetic, derived, window, FACTS, PRIVATE), relationship modeling and aggregation grain, NULL handling.
• references/capabilities-limits.md — capability/limit table, troubleshooting by symptom, advanced queries (subquery/CTE/JOIN/CTAS), AI integration.

When to use a semantic view

Modeling has a cost — it is not always the right tool.

Rule of thumb: build a semantic view when the payoff of consistent definitions and repeated reuse outweighs the modeling cost.

Core components

Any dimension / metric / fact can be prefixed with PRIVATE to hide it from direct query — it can only be composed into other PUBLIC objects (encapsulate intermediate calculations).

Creating a semantic view

CREATE [ OR REPLACE ] SEMANTIC VIEW [ IF NOT EXISTS ] <view_name>
TABLES (
    <table_alias> AS <schema>.<physical_table>
        PRIMARY KEY ( <column> [ , ... ] )
        [ FOREIGN KEY ( <column> ) REFERENCES <other_alias> [ ( <ref_column> ) ] ]
        [ WITH SYNONYMS ( '<synonym>' [ , ... ] ) ]
        [ COMMENT = '<description>' ]
    [ , ... ]
)
[ RELATIONSHIPS (
    <ref_alias> ( <fk_column> [ , ... ] ) REFERENCES <referenced_alias> [ ( <ref_column> [ , ... ] ) ]
    [ , ... ]
) ]
[ VARIABLES (
    <var_name> <data_type> [ { DEFAULT | = } <default_value> ] [ COMMENT = '<description>' ]
    [ , ... ]
) ]
[ FACTS (
    [ PRIVATE ] <alias>.<fact_name> AS { <column_expr> | <aggregate_expr> }
    [ , ... ]
) ]
[ DIMENSIONS (
    [ PRIVATE ] { <alias>.<dim_name> | <dim_name> } AS <expression>
        [ WITH SYNONYMS = ( '<synonym>' [ , ... ] ) ]
        [ is_unique = { true | false } ] [ is_time = { true | false } ]
        [ enum_values = [ <v1>, <v2>, ... ] ]
        [ COMMENT = '<description>' ]
    [ , ... ]
) ]
[ METRICS (
    [ PRIVATE ] <alias>.<metric_name> AS <aggregate_expression>
        [ COMMENT = '<description>' ]
    [ , ... ]
) ]
[ COMMENT = '<view_description>' ];

⚠️ Clause order is fixed: TABLES → RELATIONSHIPS → VARIABLES → FACTS → DIMENSIONS → METRICS. Only TABLES is required; the rest are optional. An out-of-order clause raises Syntax error at or near '<clause>' (e.g. VARIABLES before RELATIONSHIPS, or FACTS after DIMENSIONS). Dimension metadata order is also fixed: WITH SYNONYMS must come before is_unique/is_time/enum_values.

Foreign keys have two equivalent forms: the inline FOREIGN KEY inside a logical table, or the top-level RELATIONSHIPS clause. Both are accepted on create; SHOW CREATE SEMANTIC VIEW / DESC EXTENDED always read them back normalized as a RELATIONSHIPS clause. CREATE ... IF NOT EXISTS silently skips when the view exists (mutually exclusive with OR REPLACE).

Complete example

DROP SEMANTIC VIEW IF EXISTS doc_test.emp_dept_analysis;
CREATE SEMANTIC VIEW doc_test.emp_dept_analysis
TABLES (
    depts AS doc_test.departments
        PRIMARY KEY (dept_name),
    emps AS doc_test.employees
        PRIMARY KEY (id)
        FOREIGN KEY (dept) REFERENCES depts (dept_name)
)
DIMENSIONS (
    emps.employee_name AS emps.name
        WITH SYNONYMS = ('staff name')
        is_unique = true
        COMMENT = 'Employee name',
    emps.department AS emps.dept
        COMMENT = 'Department',
    emps.hire_year AS YEAR(emps.hire_date)
        is_time = true
        COMMENT = 'Hire year',
    depts.manager_name AS depts.manager
        COMMENT = 'Department manager'
)
METRICS (
    emps.total_employees AS COUNT(emps.id)
        COMMENT = 'Employee count',
    emps.avg_salary AS AVG(emps.salary)
        COMMENT = 'Average salary',
    emps.max_salary AS MAX(emps.salary)
        COMMENT = 'Max salary'
)
COMMENT = 'Employee & department analysis';

Notes:

• FOREIGN KEY (dept) REFERENCES depts (dept_name) — when the FK column name differs from the referenced primary key, name the referenced column explicitly. FK and referenced column types must match, or CREATE fails.
• hire_year is a computed dimension derived from a date via YEAR().
• The table referenced by a foreign key must be defined before the referencing table in TABLES.

Metric capabilities (brief)

Metrics are standard aggregate expressions — far beyond COUNT/SUM/AVG/MIN/MAX:

METRICS (
    -- Conditional (segmented KPI): each FILTER is independent
    orders.open_revenue  AS SUM(o_totalprice) FILTER (WHERE o_status = 'O'),
    -- Arithmetic expression
    emps.salary_range    AS MAX(salary) - MIN(salary),
    -- Same-table derived (reference other named metrics)
    emps.total_salary    AS SUM(salary),
    emps.headcount       AS COUNT(id),
    emps.avg_salary      AS emps.total_salary / emps.headcount,
    -- Window (share/running total/rank); PARTITION BY must use a dimension's qualified alias
    orders.pct_of_region AS SUM(o_totalprice) * 100.0
        / SUM(SUM(o_totalprice)) OVER (PARTITION BY orders.region)
)

Also supported: COUNT(DISTINCT ...), APPROX_COUNT_DISTINCT, STDDEV, VARIANCE, MEDIAN, PERCENTILE, GROUP_CONCAT, etc. Cross-table metric division (referencing an unrelated table's columns) is not supported — do that in the outer SQL. Full rules and verified outputs: references/metrics-and-modeling.md.

Querying a semantic view

Use the semantic_view() table function — the engine auto-JOINs by foreign keys and groups by the requested dimensions:

SELECT * FROM semantic_view(
    <view_name>
    [ DIMENSIONS <name> [ , <name> ... ] ]
    [ METRICS    <name> [ , <name> ... ] ]
    [ FACTS      <name> [ , <name> ... ] ]
    [ WHERE <predicate> ]
    [ VARIABLES <var_name> => <value> [ , ... ] ]
);

⚠️ No comma after the view name — the first clause keyword follows directly. DIMENSIONS/METRICS/FACTS may each appear at most once; list multiple items under one keyword separated by commas (DIMENSIONS a, b, not DIMENSIONS a DIMENSIONS b). A comma after the view name or a repeated keyword raises a syntax error (duplicate METRICS clause in semantic_view(); each of DIMENSIONS, METRICS and FACTS may appear at most once).

-- Group metrics by dimension
SELECT * FROM semantic_view(
    doc_test.emp_dept_analysis
    DIMENSIONS emps.department
    METRICS emps.total_employees, emps.avg_salary
);

-- Cross-table dimension (auto JOIN) — group by the manager from depts
SELECT * FROM semantic_view(
    doc_test.emp_dept_analysis
    DIMENSIONS depts.manager_name
    METRICS emps.avg_salary
);

-- Short names (alias prefix optional when unique)
SELECT * FROM semantic_view(
    doc_test.emp_dept_analysis
    DIMENSIONS department
    METRICS total_employees
);

-- Filtering: an inner trailing WHERE, or an outer WHERE — both work
SELECT * FROM semantic_view(
    doc_test.emp_dept_analysis
    DIMENSIONS emps.department
    METRICS emps.avg_salary
) WHERE department = 'Engineering';

• Must specify at least one DIMENSIONS, METRICS, or FACTS, or you get table or view not found - semantic_view.
• DIMENSIONS/METRICS/FACTS are order-independent (METRICS ... DIMENSIONS ... equals DIMENSIONS ... METRICS ...); output columns follow item write order. WHERE and VARIABLES are trailing clauses, after the three keywords, with WHERE before VARIABLES.
• Only METRICS → single-row global aggregate. Only DIMENSIONS → deduplicated dimension list.
• Filter dimensions with an inner trailing WHERE or an outer WHERE; ORDER BY / LIMIT and SELECT col1, col2 only go on the outer query.
• Neither WHERE takes a physical column name — use the dimension. The inner WHERE accepts a qualified alias (emps.department) or short name (department); the outer WHERE references output columns, so short name only (emps.department there raises cannot resolve column).
• Inner WHERE runs before metric aggregation, so it cannot reference a metric — WHERE total_employees > 1 raises a METRIC '...' is not allowed in the semantic_view() WHERE clause. Filter on aggregates in an outer query: SELECT * FROM (SELECT * FROM semantic_view(...)) t WHERE t.total_employees > 1.

Traditional SQL vs semantic view

-- Traditional (manual JOIN + GROUP BY)
SELECT e.dept, d.manager AS manager_name, COUNT(e.id), AVG(e.salary)
FROM doc_test.employees e
JOIN doc_test.departments d ON e.dept = d.dept_name
GROUP BY e.dept, d.manager;

-- Semantic view (JOIN + aggregation automatic, grain-correct)
SELECT * FROM semantic_view(
    doc_test.emp_dept_analysis
    DIMENSIONS emps.department, depts.manager_name
    METRICS emps.total_employees, emps.avg_salary
);

Grain matters: query grain is driven by the metric's table. Orphan child rows appear with a NULL dimension; dimension members with no fact rows do not appear. Metrics from two sibling one-to-many branches (chasm-trap fan-out) can be combined in one query — the engine aggregates each branch at its own grain and aligns on the dimension, without inflating. See references/metrics-and-modeling.md.

Managing a semantic view

• ALTER cannot add/drop dimensions or metrics — use CREATE OR REPLACE to replay the full definition (recommended: SHOW CREATE → edit → CREATE OR REPLACE).
• Semantic views are not in information_schema.tables; use SHOW SEMANTIC VIEWS and DESC EXTENDED.
• SHOW SEMANTIC VIEWS does not support LIKE, and has no global cross-schema listing.

Important notes

1. No FILTERS clause: named filters were removed. To filter, define a conditional metric with FILTER (WHERE ...), or use an outer WHERE with a dimension short name.
2. TABLES order: a referenced table must be defined before the table whose FK references it.
3. FK type match: FK column and referenced column must have the same type, or CREATE raises type ... does not match.
4. Idempotent scripts: DROP ... IF EXISTS before CREATE, or use CREATE OR REPLACE.
5. Metadata is declarative: is_unique / is_time / enum_values are annotations for AI/metadata tools — they do not affect SQL results, optimization, or value validation. Note DESC EXTENDED reads is_unique/is_time back as true whenever the clause was written at all (value not faithful); synonyms and enum_values read back faithfully.
6. Window metrics: PARTITION BY / ORDER BY must reference a dimension's qualified alias (e.g. orders.region), same-table only, and that dimension must appear in the query's DIMENSIONS.
7. PRIVATE objects cannot be queried/filtered directly — only composed into a PUBLIC fact/metric.
8. VARIABLES: declared right after TABLES (before FACTS/DIMENSIONS/METRICS, else Syntax error at or near 'VARIABLES'). Dimension/metric expressions reference a variable by its bare name (no alias. prefix). Bind at query time with semantic_view(... VARIABLES <name> => <value>) (=> or =, constant only); unbound variables use their default. DEFAULT and = are equivalent and both read back as DEFAULT.