Best Practices

When importing from SQL into Single Origin, there are best practices you can follow to make importing views, dimensions, and data entities smoother and more efficient.

• Select Items
  • Specify the full path of the table. For example: DB_NAME.SCHEMA_NAME.TABLE_NAME
  • Ensure every item has an alias, including selected group by items. The alias will be used as the name for the imported entity.
  • Use snake casing for the name of the alias.
  • Every item that is not an aggregate is considered a dimension.
  • Every item that is an aggregate is considered a data entity.
• The first timestamp expression in the filter clause is used as the time dimension for the view imported (if the time dimension is not defined on an existing view).
• Ensure your outermost SQL Query is an aggregation query. We can recursively search for aggregation queries, but setting it as your outermost query will ensure we’re importing the right data entities.
• If any entity imported from the SQL is a duplicate of an existing entity or another imported SQL in the same import (same formula/SQL), it will be reused.
  • Duplicated views will have their dimension lists merged from the imported dimension list.
  • Duplicated data entities will not be imported.

Example

SQL

SELECT
  CAST(store_id AS STRING) AS store,
  transaction_type AS transaction_type,
  CAST(placed_at AS TIMESTAMP) AS time,
  SUM(cart_total) AS total_revenue
FROM
  company.orders
GROUP BY
  1, 2, 3
WHERE
  CAST(placed_at AS TIMESTAMP) > TIMESTAMP(“2021-01-01”)
  AND CAST(placed_at AS TIMESTAMP) < TIMESTAMP(“2021-02-01”)

Imported View & Data Entities

SELECT
  *
FROM
  company.orders;