data.ml
SQL Parking reservations

Combined Bookings

Combined Bookings model for parking reservations.

source-preserved

Blocks

combined_bookings.sql · 119 lines
inferred 
-- models/combined_bookings.sql
{{ config(materialized='table') }}

SELECT
id  ,
CASE WHEN event_id is null THEN 'transient' ELSE 'event' END as parking_type,
customer_id ,
location_id ,
seller_id ,
created_at  ,
updated_at  ,
event_id  ,
parking_period_begin,
parking_period_end,
vehicle_license ,
transaction_id  ,
pricing_id  ,
event_package_id  ,
vehicle_id  ,
scan_code ,
disclaimers ,
flat_fee  ,
amenity_set_id  ,
partner_id  ,
fulfilled ,
booking_order_id  ,
paid_to_partner ,
purchase_vector ,
payment_type  ,
payment_reconciled  ,
payment_method  ,
expense_memo  ,
enterprise_account_id ,
portal_affiliate_id ,
search_query  ,
NULL as price ,
NULL as amount_paid ,
NULL as monthly_pricing_id
FROM
{{ this }}.bookings

UNION

SELECT
id,
'monthly' as parking_type,
customer_id ,
location_id ,
seller_id ,
created_at  ,
updated_at  ,
NULL as event_id  ,
start_date as parking_period_begin,
NULL as parking_period_end,
vehicle_license ,
transaction_id  ,
NULL as pricing_id  ,
NULL as event_package_id  ,
NULL as vehicle_id  ,
NULL as scan_code ,
NULL as disclaimers ,
NULL as flat_fee  ,
amenity_set_id  ,
NULL as partner_id  ,
NULL as fulfilled ,
NULL as booking_order_id  ,
NULL as paid_to_partner ,
NULL as purchase_vector ,
NULL as payment_type  ,
NULL as payment_reconciled  ,
NULL as payment_method  ,
NULL as expense_memo  ,
NULL as enterprise_account_id ,
NULL as portal_affiliate_id ,
NULL as search_query  ,
price ,
amount_paid ,
monthly_pricing_id
FROM
{{ this }}.monthly_bookings

# models/schema.yml
version: 2

models:
  - name: combined_bookings
    columns:
      - name: id
        tests:
          - unique
          - not_null
      - name: parking_type
      - name: customer_id
      - name: location_id
      - name: seller_id
      - name: event_id
      - name: parking_period_begin
      - name: parking_period_end
      - name: vehicle_license
      - name: transaction_id
      - name: pricing_id
      - name: event_package_id
      - name: vehicle_id
      - name: scan_code
      - name: disclaimers
      - name: flat_fee
      - name: amenity_set_id
      - name: partner_id
      - name: fulfilled
      - name: booking_order_id
      - name: paid_to_partner
      - name: purchase_vector
      - name: payment_type
      - name: payment_reconciled
      - name: payment_method
      - name: expense_memo
      - name: price
      - name: amount_paid
      - name: monthly_pricing_id

Assumptions

  • materialized='table' inferred from a LookML persist hint (persist_for / datagroup / sql_trigger_value).
  • schema.yml columns come from view dimensions; a primary_key dimension gets unique + not_null tests.
combined_bookings.sql · 77 lines
source-preserved validation: not checked 
SELECT
id  ,
CASE WHEN event_id is null THEN 'transient' ELSE 'event' END as parking_type,
customer_id ,
location_id ,
seller_id ,
created_at  ,
updated_at  ,
event_id  ,
parking_period_begin,
parking_period_end,
vehicle_license ,
transaction_id  ,
pricing_id  ,
event_package_id  ,
vehicle_id  ,
scan_code ,
disclaimers ,
flat_fee  ,
amenity_set_id  ,
partner_id  ,
fulfilled ,
booking_order_id  ,
paid_to_partner ,
purchase_vector ,
payment_type  ,
payment_reconciled  ,
payment_method  ,
expense_memo  ,
enterprise_account_id ,
portal_affiliate_id ,
search_query  ,
NULL as price ,
NULL as amount_paid ,
NULL as monthly_pricing_id
FROM
${TABLE}.bookings

UNION

SELECT
id,
'monthly' as parking_type,
customer_id ,
location_id ,
seller_id ,
created_at  ,
updated_at  ,
NULL as event_id  ,
start_date as parking_period_begin,
NULL as parking_period_end,
vehicle_license ,
transaction_id  ,
NULL as pricing_id  ,
NULL as event_package_id  ,
NULL as vehicle_id  ,
NULL as scan_code ,
NULL as disclaimers ,
NULL as flat_fee  ,
amenity_set_id  ,
NULL as partner_id  ,
NULL as fulfilled ,
NULL as booking_order_id  ,
NULL as paid_to_partner ,
NULL as purchase_vector ,
NULL as payment_type  ,
NULL as payment_reconciled  ,
NULL as payment_method  ,
NULL as expense_memo  ,
NULL as enterprise_account_id ,
NULL as portal_affiliate_id ,
NULL as search_query  ,
price ,
amount_paid ,
monthly_pricing_id
FROM
${TABLE}.monthly_bookings
combined_bookings.view.lkml · 264 lines
inferred 
view: combined_bookings {
  derived_table: {
    sql: SELECT
id  ,
CASE WHEN event_id is null THEN 'transient' ELSE 'event' END as parking_type,
customer_id ,
location_id ,
seller_id ,
created_at  ,
updated_at  ,
event_id  ,
parking_period_begin,
parking_period_end,
vehicle_license ,
transaction_id  ,
pricing_id  ,
event_package_id  ,
vehicle_id  ,
scan_code ,
disclaimers ,
flat_fee  ,
amenity_set_id  ,
partner_id  ,
fulfilled ,
booking_order_id  ,
paid_to_partner ,
purchase_vector ,
payment_type  ,
payment_reconciled  ,
payment_method  ,
expense_memo  ,
enterprise_account_id ,
portal_affiliate_id ,
search_query  ,
NULL as price ,
NULL as amount_paid ,
NULL as monthly_pricing_id
FROM
${TABLE}.bookings

UNION

SELECT
id,
'monthly' as parking_type,
customer_id ,
location_id ,
seller_id ,
created_at  ,
updated_at  ,
NULL as event_id  ,
start_date as parking_period_begin,
NULL as parking_period_end,
vehicle_license ,
transaction_id  ,
NULL as pricing_id  ,
NULL as event_package_id  ,
NULL as vehicle_id  ,
NULL as scan_code ,
NULL as disclaimers ,
NULL as flat_fee  ,
amenity_set_id  ,
NULL as partner_id  ,
NULL as fulfilled ,
NULL as booking_order_id  ,
NULL as paid_to_partner ,
NULL as purchase_vector ,
NULL as payment_type  ,
NULL as payment_reconciled  ,
NULL as payment_method  ,
NULL as expense_memo  ,
NULL as enterprise_account_id ,
NULL as portal_affiliate_id ,
NULL as search_query  ,
price ,
amount_paid ,
monthly_pricing_id
FROM
${TABLE}.monthly_bookings ;;
    persist_for: "12 hours"
  }

  dimension: id {
    type: int
    primary_key: yes
    sql: ${TABLE}.id ;;
  }

  dimension: parking_type {
    type: string
    sql: ${TABLE}.parking_type ;;
  }

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

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

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

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

  dimension: parking_period_begin {
    type: time
    sql: ${TABLE}.parking_period_begin ;;
  }

  dimension: parking_period_end {
    type: time
    sql: ${TABLE}.parking_period_end ;;
  }

  dimension: vehicle_license {
    type: string
    sql: ${TABLE}.vehicle_license ;;
  }

  dimension: transaction_id {
    type: string
    sql: ${TABLE}.transaction_id ;;
  }

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

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

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

  dimension: scan_code {
    type: string
    sql: ${TABLE}.scan_code ;;
  }

  dimension: disclaimers {
    type: string
    sql: ${TABLE}.disclaimers ;;
  }

  dimension: flat_fee {
    type: number
    sql: ${TABLE}.flat_fee ;;
  }

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

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

  dimension: fulfilled {
    type: yesno
    sql: ${TABLE}.fulfilled ;;
  }

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

  dimension: paid_to_partner {
    type: number
    sql: ${TABLE}.paid_to_partner ;;
  }

  dimension: purchase_vector {
    type: string
    sql: ${TABLE}.purchase_vector ;;
  }

  dimension: payment_type {
    type: string
    sql: ${TABLE}.payment_type ;;
  }

  dimension: payment_reconciled {
    type: yesno
    sql: ${TABLE}.payment_reconciled ;;
  }

  dimension: payment_method {
    type: string
    sql: ${TABLE}.payment_method ;;
  }

  dimension: expense_memo {
    sql: ${TABLE}.expense_memo ;;
  }

  dimension: price {
    type: number
    sql: ${TABLE}.price ;;
  }

  dimension: amount_paid {
    type: number
    sql: ${TABLE}.amount_paid ;;
  }

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

  dimension_group: created_at {
    type: time
    timeframes: [time, date, week, month, year, month_num, week_of_year]
    sql: ${TABLE}.created_at ;;
  }

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

  dimension_group: enterprise_account_id {
    type: int
    sql: ${TABLE}.enterprise_account_id ;;
  }

  dimension_group: portal_affiliate_id {
    type: int
    sql: ${TABLE}.portal_affiliate_id ;;
  }

  dimension_group: search_query {
    type: time
    sql: ${TABLE}.search_query ;;
  }

  dimension_group: vehicle_license1 {
    type: time
    sql: ${TABLE}.vehicle_license1 ;;
  }

  measure: count {
    type: count
    drill_fields: [bookings_detail*]
  }
}
combined_bookings.view.lookml · 203 lines
source-preserved 
- view: combined_bookings
  derived_table:
    sql: SELECT
id  ,
CASE WHEN event_id is null THEN 'transient' ELSE 'event' END as parking_type,
customer_id ,
location_id ,
seller_id ,
created_at  ,
updated_at  ,
event_id  ,
parking_period_begin,
parking_period_end,
vehicle_license ,
transaction_id  ,
pricing_id  ,
event_package_id  ,
vehicle_id  ,
scan_code ,
disclaimers ,
flat_fee  ,
amenity_set_id  ,
partner_id  ,
fulfilled ,
booking_order_id  ,
paid_to_partner ,
purchase_vector ,
payment_type  ,
payment_reconciled  ,
payment_method  ,
expense_memo  ,
enterprise_account_id ,
portal_affiliate_id ,
search_query  ,
NULL as price ,
NULL as amount_paid ,
NULL as monthly_pricing_id
FROM
${TABLE}.bookings

UNION

SELECT
id,
'monthly' as parking_type,
customer_id ,
location_id ,
seller_id ,
created_at  ,
updated_at  ,
NULL as event_id  ,
start_date as parking_period_begin,
NULL as parking_period_end,
vehicle_license ,
transaction_id  ,
NULL as pricing_id  ,
NULL as event_package_id  ,
NULL as vehicle_id  ,
NULL as scan_code ,
NULL as disclaimers ,
NULL as flat_fee  ,
amenity_set_id  ,
NULL as partner_id  ,
NULL as fulfilled ,
NULL as booking_order_id  ,
NULL as paid_to_partner ,
NULL as purchase_vector ,
NULL as payment_type  ,
NULL as payment_reconciled  ,
NULL as payment_method  ,
NULL as expense_memo  ,
NULL as enterprise_account_id ,
NULL as portal_affiliate_id ,
NULL as search_query  ,
price ,
amount_paid ,
monthly_pricing_id
FROM
${TABLE}.monthly_bookings
    persist_for: 12 hours
  fields:
  - dimension: id
    type: int
    primary_key: true
    sql: ${TABLE}.id
  - dimension: parking_type
    type: string
    sql: ${TABLE}.parking_type
  - dimension: customer_id
    type: int
    sql: ${TABLE}.customer_id
  - dimension: location_id
    type: int
    sql: ${TABLE}.location_id
  - dimension: seller_id
    type: int
    sql: ${TABLE}.seller_id
  - dimension: event_id
    type: int
    sql: ${TABLE}.event_id
  - dimension: parking_period_begin
    type: time
    sql: ${TABLE}.parking_period_begin
  - dimension: parking_period_end
    type: time
    sql: ${TABLE}.parking_period_end
  - dimension: vehicle_license
    type: string
    sql: ${TABLE}.vehicle_license
  - dimension: transaction_id
    type: string
    sql: ${TABLE}.transaction_id
  - dimension: pricing_id
    type: int
    sql: ${TABLE}.pricing_id
  - dimension: event_package_id
    type: int
    sql: ${TABLE}.event_package_id
  - dimension: vehicle_id
    type: int
    sql: ${TABLE}.vehicle_id
  - dimension: scan_code
    type: string
    sql: ${TABLE}.scan_code
  - dimension: disclaimers
    type: string
    sql: ${TABLE}.disclaimers
  - dimension: flat_fee
    type: number
    sql: ${TABLE}.flat_fee
  - dimension: amenity_set_id
    type: int
    sql: ${TABLE}.amenity_set_id
  - dimension: partner_id
    type: int
    sql: ${TABLE}.partner_id
  - dimension: fulfilled
    type: yesno
    sql: ${TABLE}.fulfilled
  - dimension: booking_order_id
    type: int
    sql: ${TABLE}.booking_order_id
  - dimension: paid_to_partner
    type: number
    sql: ${TABLE}.paid_to_partner
  - dimension: purchase_vector
    type: string
    sql: ${TABLE}.purchase_vector
  - dimension: payment_type
    type: string
    sql: ${TABLE}.payment_type
  - dimension: payment_reconciled
    type: yesno
    sql: ${TABLE}.payment_reconciled
  - dimension: payment_method
    type: string
    sql: ${TABLE}.payment_method
  - dimension: expense_memo
    sql: ${TABLE}.expense_memo
  - dimension: price
    type: number
    sql: ${TABLE}.price
  - dimension: amount_paid
    type: number
    sql: ${TABLE}.amount_paid
  - dimension: monthly_pricing_id
    type: int
    sql: ${TABLE}.monthly_pricing_id
  - dimension_group: created_at
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    - year
    - month_num
    - week_of_year
    sql: ${TABLE}.created_at
  - dimension_group: updated_at
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.updated_at
  - dimension_group: enterprise_account_id
    type: int
    sql: ${TABLE}.enterprise_account_id
  - dimension_group: portal_affiliate_id
    type: int
    sql: ${TABLE}.portal_affiliate_id
  - dimension_group: search_query
    type: time
    sql: ${TABLE}.search_query
  - dimension_group: vehicle_license1
    type: time
    sql: ${TABLE}.vehicle_license1
  - measure: count
    type: count
    drill_fields:
    - bookings_detail*

Contents

  • combined_bookings.sql
  • combined_bookings.sql
  • combined_bookings.view.lkml
  • combined_bookings.view.lookml