Structured Dataset 'source' representation

[deepralhan26]

Structured Source Representation

Replace the current simple source handling with a structured source shape that makes the distinction between object-backed and query-backed datasets explicit, instead of leaving it to converter guesswork.

Before

- name: orders
  source: sales.public.orders
- name: high_value_orders
  source: |
    select order_id, customer_id, revenue
    from sales.public.orders
    where revenue > 10000

In both cases, source is just a string, so the mapping layer has to infer whether it represents an object reference or SQL text.

After

- name: orders
  source:
    kind: table
    object: sales.public.orders
- name: high_value_orders
  source:
    kind: query
    sql:
      dialects:
        - dialect: ANSI_SQL
          expression: |
            select order_id, customer_id, revenue
            from sales.public.orders
            where revenue > 10000

Why

This makes the source shape explicit, removes ambiguity, and gives a clearer and more reliable basis for conversion.

	Before	After
Object reference	Inferred from string format	kind: table
SQL text	Inferred from string format	kind: query
Dialect support	Not possible	Per-dialect expressions via dialects list
Converter guesswork	Required	Eliminated

[jochenchrist]

+1 for having a structured source representation.

source should be optional.

FWIW, our requirement would be to refer data products (instead of direct SQL), so supporting different kinds with would be helpful.

Example:

- name: high_value_orders
  source:
    kind: dataproduct
    references: # or a better term for flexible references 
      dataproduct: sales_data
      outputport: bigquery_prod_v1

[KSDaemon]

Just thinking loudly.... Could we simplify it to smth like this:

- name: orders
  source:
    object: sales.public.orders
- name: high_value_orders
  source:
    sql:
      dialects:
        - dialect: ANSI_SQL
          expression: |
            select order_id, customer_id, revenue
            from sales.public.orders
            where revenue > 10000

I mean object | sql are the discriminators here. Only one should be allowed. And it's enough to go to the correct branch.

[ki-kyvos]

Is this aligned to be included in any version ?

[deepralhan26]

+1. This aligns with our design goals. I'd suggest we keep both kind and expression fields explicitly ; it maintains semantic clarity while keeping the schema lean:

- name: orders
  source:
    kind: table
    expression: sales.public.orders

- name: high_value_orders
  source:
    kind: query
    expression: |
      select order_id, customer_id, revenue
      from sales.public.orders
      where revenue > 10000

This pattern:

• Removes converter guesswork
• Keeps intent visible via kind
• Schema stays minimal and clean
• Extends naturally to kind: dataproduct, kind: view, etc.

[krantikumarb]

It is important to specify the SQL dialects that each query adheres to(dialect: ANSI_SQL), since many contemporary database systems incorporate non‑ANSI clauses and proprietary extensions .

sql:
dialects:

• dialect: ANSI_SQL

[deepralhan26]

In that case we can adhere to the original proposal structure and keep adding various kinds with corresponding references tailored to different use cases.

Current Proposal

The original structure with explicit kind discriminator gives us a solid foundation:

- name: orders
  source:
    kind: table
    object: sales.public.orders
- name: high_value_orders
  source:
    kind: query
    sql:
      dialects:
        - dialect: ANSI_SQL
          expression: |
            select order_id, customer_id, revenue
            from sales.public.orders
            where revenue > 10000

Extending with Additional Kinds

As requirements evolve (like jochenchrist's data product use case), we can add new kinds with their own reference semantics:

- name: orders
  source:
    kind: table
    object: sales.public.orders
 
- name: high_value_orders
  source:
    kind: query
    sql:
      dialects:
        - dialect: ANSI_SQL
          expression: |
            select order_id, customer_id, revenue
            from sales.public.orders
            where revenue > 10000
 
- name: sales_summary
  source:
    kind: dataproduct
    references:
      dataproduct: sales_data
      outputport: bigquery_prod_v1
      version: "1.0"

Benefits

	Benefit
Clear Intent	Each kind explicitly documents what type of source it is
Tailored References	Each kind can define its own reference structure and semantics
Extensible	New kinds can be added without modifying existing ones
Type-Safe	Validation can enforce kind-specific reference shapes
Future-Proof	Supports table objects, SQL queries, data products, APIs, or whatever comes next

The kind field remains the discriminator, and each kind owns its own reference contract.

[mergisi]

The kind discriminator pattern is the right call — it eliminates the string-parsing guesswork that inevitably breaks when someone's table name happens to look like SQL (e.g., a table literally named select_results).

One dimension worth considering early: validated vs. raw SQL in kind: query. Right now the proposal assumes the expression field contains trusted, pre-validated SQL. But in practice, datasets authored by different teams will have varying quality levels. A lightweight validation hint could help:

- name: high_value_orders
  source:
    kind: query
    sql:
      dialects:
        - dialect: ANSI_SQL
          expression: |
            select order_id, customer_id, revenue
            from sales.public.orders
            where revenue > 10000
      validation:
        status: verified          # or 'draft', 'generated'
        verified_against: postgres_15

This matters especially as natural language → SQL tools become more common in data workflows. Tools like ai2sql.io generate syntactically correct SQL from plain-English descriptions, but the output often needs dialect-specific adjustment (e.g., ILIKE in Postgres vs. LOWER() + LIKE in MySQL). Having validation.status would let consumers distinguish between human-authored, tool-generated, and verified queries without losing the structured benefits of the kind pattern.

On KSDaemon's simplification proposal — dropping kind and using object vs sql as implicit discriminators is tempting for brevity, but it makes the schema harder to extend. When kind: dataproduct or kind: api arrives (as jochenchrist suggested), the implicit pattern breaks because there's no consistent discriminator field. Keeping kind explicit is worth the extra line.

Disclosure: I work on ai2sql.io — a natural language to SQL tool. The dialect-awareness discussion here maps directly to challenges we see in query generation across database engines.

[deepralhan26]

Thanks @mergisi, that makes sense.
I’ll raise an MR for the initial proposed change, keeping kind as the explicit discriminator with table and query as the first supported values. The optional validation block also sounds useful, especially for query sources, and we can add that later as a follow-up once the base representation is in place.

[spdr870]

I just found this topic, after I posted a very similar one:
#165

We use discriminator column a lot have multiple similar datasets in 1 table. Its a legacy database. What we need is just a simple filter on this discriminator column. This can be done using the full query in souce, but I would prefer to have the filter stored separately. like this:

datasets:
- name: Employee
  source: dba.Person
  constraints
    expression:
      dialects:
        - dialect: ANSI_SQL
          expression: PersonType = 1

[deepralhan26]

Thanks @spdr870, that is a good use case, and I agree it would be useful to model this cleanly.

For the current proposal, I think this can be represented with kind: query for now:

datasets:
  - name: Employee
    source:
      kind: query
      sql:
        dialects:
          - dialect: ANSI_SQL
            expression: |
              select *
              from dba.Person
              where PersonType = 1

[xiedeyantu]

- name: high_value_orders
  source:
    kind: query
    query:
      dialects:
        - dialect: ANSI_SQL
          expression: |
            select order_id, customer_id, revenue
            from sales.public.orders
            where revenue > 10000

It might be better to use query instead of sql, for example, whether non-SQL language queries will be supported in the future, such as Elasticsearch?