DA @data_apps
Showing posts older than #205 · Back to latest
from {{ ref('stg_orders_tmp') }} as orders
{% if is_incremental() %}
left join {{ this }}
on orders.request_id = {{ this }}.request_id
and orders.__metadata_timestamp = {{ this }}.__metadata_timestamp
{% endif %}
left join {{ ref('stg_zones_tmp') }} as pickup
on ST_Intersects(
ST_Point(orders.pickup_position_lon, orders.pickup_position_lat), pickup.geometry)
left join {{ ref('stg_zones_tmp') }} as dropoff
on ST_Intersects(
ST_Point(orders.dropoff_position_lon, orders.dropoff_position_lat), dropoff.geometry)
{% if is_incremental() %}
where {{ this }}.request_id is null
{% endif %}{% if is_incremental() %}
left join {{ this }}
on orders.request_id = {{ this }}.request_id
and orders.__metadata_timestamp = {{ this }}.__metadata_timestamp
where {{ this }}.request_id is null
{% endif %} NOT EXISTS clause:where true
{% if is_incremental() %}
and not exists (
select 1
from {{ this }}
where orders.request_id = {{ this }}.request_id
and orders.__metadata_timestamp = {{ this }}.__metadata_timestamp
)
{% endif %}
where true
{% if is_incremental() %}
and orders.__metadata_timestamp >=
(select max(__metadata_timestamp) as high_watermark from {{ this }})
{% endif %}
where true
{% if is_incremental() %}
and orders.__metadata_timestamp >=
(select max(__metadata_timestamp) as high_watermark from {{ this }}) - interval '3 hours'
{% endif %}
CREATE TABLE dbt.margin_rate (4. Query real-time in sub-second manner!
`date_month` Date
, `margin_rate` Float64
)
ENGINE=URL('https://docs.google.com/spreadsheets/d/{key}/gviz/tq?tqx=out:csv&sheet=margin_rate', CSVWithNames)
;
SELECT * FROM dbt.margin_rate LIMIT 200 ;
Data Warehouses have never been such interactive.