data.ml
SQL Saas translation

Agencies Site Locales

Agencies Site Locales model for saas translation.

source-preserved

Blocks

agencies_site_locales.sql · 26 lines
inferred 
-- models/agencies_site_locales.sql
{{ config(materialized='view') }}

SELECT t1.site_id, t1.locale_id, t1.invoice_start_date, min(t1.agency_id) as agency_id
FROM
(
  SELECT DISTINCT lt.site_id, lt.locale_id, it.invoice_start_date, it.entity_id AS agency_id
  FROM {{ this }}.invoice_tracking it
    JOIN {{ this }}.invoice_tracking_line_items lt
    ON lt.invoice_tracking_id = it.invoice_tracking_id
  WHERE it.active = 1
   AND it.invoice_status_id = 2 -- success only
   AND it.entity_type_id = 3    -- agency entity
) t1
GROUP BY t1.site_id, t1.locale_id, t1.invoice_start_date
HAVING COUNT(*) = 1

# models/schema.yml
version: 2

models:
  - name: agencies_site_locales
    columns:
      - name: site_id
      - name: locale_id
      - name: agency_id

Assumptions

  • materialized='view' is the default; no persist hint found.
  • schema.yml columns come from view dimensions; a primary_key dimension gets unique + not_null tests.
agencies_site_locales.sql · 13 lines
source-preserved validation: not checked 
SELECT t1.site_id, t1.locale_id, t1.invoice_start_date, min(t1.agency_id) as agency_id
FROM
(
  SELECT DISTINCT lt.site_id, lt.locale_id, it.invoice_start_date, it.entity_id AS agency_id
  FROM ${TABLE}.invoice_tracking it
    JOIN ${TABLE}.invoice_tracking_line_items lt
    ON lt.invoice_tracking_id = it.invoice_tracking_id
  WHERE it.active = 1
   AND it.invoice_status_id = 2 -- success only
   AND it.entity_type_id = 3    -- agency entity
) t1
GROUP BY t1.site_id, t1.locale_id, t1.invoice_start_date
HAVING COUNT(*) = 1
agencies_site_locales.view.lkml · 42 lines
inferred 
view: agencies_site_locales {
  derived_table: {
    sql: SELECT t1.site_id, t1.locale_id, t1.invoice_start_date, min(t1.agency_id) as agency_id
FROM
(
  SELECT DISTINCT lt.site_id, lt.locale_id, it.invoice_start_date, it.entity_id AS agency_id
  FROM ${TABLE}.invoice_tracking it
    JOIN ${TABLE}.invoice_tracking_line_items lt
    ON lt.invoice_tracking_id = it.invoice_tracking_id
  WHERE it.active = 1
   AND it.invoice_status_id = 2 -- success only
   AND it.entity_type_id = 3    -- agency entity
) t1
GROUP BY t1.site_id, t1.locale_id, t1.invoice_start_date
HAVING COUNT(*) = 1 ;;
  }

  dimension: site_id {
    type: int
    sql: ${TABLE}.site_id ;;
  }

  dimension: locale_id {
    type: int
    sql: ${TABLE}.locale_id ;;
  }

  dimension: agency_id {
    type: int
    sql: ${TABLE}.agency_id ;;
  }

  dimension_group: invoice_start_date {
    type: time
    timeframes: [time, date, week, month]
    sql: ${TABLE}.invoice_start_date ;;
  }

  measure: count {
    type: count
  }
}
agencies_site_locales.view.lookml · 35 lines
source-preserved 
- view: agencies_site_locales
  derived_table:
    sql: SELECT t1.site_id, t1.locale_id, t1.invoice_start_date, min(t1.agency_id) as agency_id
FROM
(
  SELECT DISTINCT lt.site_id, lt.locale_id, it.invoice_start_date, it.entity_id AS agency_id
  FROM ${TABLE}.invoice_tracking it
    JOIN ${TABLE}.invoice_tracking_line_items lt
    ON lt.invoice_tracking_id = it.invoice_tracking_id
  WHERE it.active = 1
   AND it.invoice_status_id = 2 -- success only
   AND it.entity_type_id = 3    -- agency entity
) t1
GROUP BY t1.site_id, t1.locale_id, t1.invoice_start_date
HAVING COUNT(*) = 1
  fields:
  - dimension: site_id
    type: int
    sql: ${TABLE}.site_id
  - dimension: locale_id
    type: int
    sql: ${TABLE}.locale_id
  - dimension: agency_id
    type: int
    sql: ${TABLE}.agency_id
  - dimension_group: invoice_start_date
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.invoice_start_date
  - measure: count
    type: count

Contents

  • agencies_site_locales.sql
  • agencies_site_locales.sql
  • agencies_site_locales.view.lkml
  • agencies_site_locales.view.lookml