# SEMANTIC VIEW

Config path: `/<database>/<schema>/semantic_view/<name>.yaml`

Example:

```yaml
tables:
  - table_alias: tb1
    table_name: sv001_tb1
    primary_key: [author_id, book_id]
    with_synonyms: [aaa, bbb]
    comment: "Link between authors and books"

  - table_alias: tb2
    table_name: sv001_tb2
    primary_key: [author_id]
    with_synonyms: [ccc, ddd]
    comment: "List of authors"

  - table_alias: tb3
    table_name: sv001_tb3
    primary_key: [book_id]
    unique:
      - [book_isbn]
    comment: "List of books"

relationships:
  - table_alias: tb1
    columns: [author_id]
    ref_table_alias: tb2
    ref_columns: [author_id]

  - table_alias: tb1
    columns: [book_id]
    ref_table_alias: tb3
    ref_columns: [book_id]

facts:
  - table_alias: tb1
    name: unique_id
    sql: CONCAT(author_id, '-', book_id)
    with_synonyms: [aaa, bbb]
    comment: "Unique ID for link between authors and books"

  - table_alias: tb3
    name: number_of_pages
    sql: number_of_pages
    comment: "Number of pages in a book"

dimensions:
  - table_alias: tb1
    name: author_book_link_create_dt
    sql: CAST(create_ts AS DATE)
    comment: "Date when link between author and book was established"

  - table_alias: tb2
    name: author_name
    sql: author_name
    comment: "Name of author"

  - table_alias: tb3
    name: book_name
    sql: book_name
    comment: "Name of book"

metrics:
  - table_alias: tb1
    name: count_distinct_author
    sql: count(distinct author_id)
    comment: "Number of unique authors"

  - table_alias: tb1
    name: count_distinct_book
    sql: count(distinct book_id)
    comment: "Number of unique books"

comment: abc

```

## Schema

* <mark style="background-color:red;">**tables**</mark> (list)
  * *{items}* (dict)
    * **table\_alias** (ident) - optional alias for logical table
    * <mark style="background-color:red;">**table\_name**</mark> (ident)
    * **primary\_key** (list) - columns defining primary key
      * *{items}* (ident) - column name
    * **unique** (list) - columns defining unique keys
      * *{items}* (list)
        * *{items}* (ident) - column name
    * **with\_synonyms** (list) - synonyms for logical table
      * *{items}* (str)
    * **comment** (str)
* **relationships** (list)
  * {items} (dict)
    * **relationship\_identifier** (str) - optional alias for relationship
    * <mark style="background-color:red;">**table\_alias**</mark> (ident) - first logical table
    * <mark style="background-color:red;">**columns**</mark> (list) - columns of first logical table
      * *{items}* (ident)
    * <mark style="background-color:red;">**ref\_table\_alias**</mark> (ident) - second logical table
    * <mark style="background-color:red;">**ref\_columns**</mark> (list) - columns of second logical table
      * *{items}* (ident)
* **facts** (list)
  * *{items}* (dict)
    * <mark style="background-color:red;">**table\_alias**</mark> (ident) - name or alias for logical table
    * <mark style="background-color:red;">**name**</mark> (ident) - name of fact
    * <mark style="background-color:red;">**sql**</mark> (str) - SQL expression defining fact
    * **with\_synonyms** (list) - synonyms for fact
      * *{items}* (str)
    * **comment** (str)
* **dimensions** (list)
  * *{items}* (dict)
    * <mark style="background-color:red;">**table\_alias**</mark> (ident) - name or alias for logical table
    * <mark style="background-color:red;">**name**</mark> (ident) - name of dimension
    * <mark style="background-color:red;">**sql**</mark> (str) - SQL expression defining dimension
    * **with\_synonyms** (list) - synonyms for dimension
      * *{items}* (str)
    * **comment** (str)
* **metrics** (list)
  * *{items}* (dict)
    * <mark style="background-color:red;">**table\_alias**</mark> (ident) - name or alias for logical table
    * <mark style="background-color:red;">**name**</mark> (ident) - name of metric
    * <mark style="background-color:red;">**sql**</mark> (str) - SQL expression defining metric
    * **with\_synonyms** (list) - synonyms for metric
      * *{items}* (str)
    * **comment** (str)
* **comment** (str)

## Links

* [CREATE SEMANTIC VIEW](https://docs.snowflake.com/en/sql-reference/sql/create-semantic-view)
* [SHOW SEMANTIC VIEWS](https://docs.snowflake.com/en/sql-reference/sql/show-semantic-views)
* [DESC SEMANTIC VIEW](https://docs.snowflake.com/en/sql-reference/sql/desc-semantic-view)
* [Parser & JSON Schema (GitHub)](https://github.com/littleK0i/SnowDDL/blob/master/snowddl/parser/semantic_view.py)