vvgsrk

INSERT INTO product_history (product_id, product_name, price_per_unit, basic_unit, is_stock_limited, is_active_for_sale, __event_ts, __load_ts)
VALUES(1009, 'Paris Coffee', '3 EUR', '1 piece', 'No', 'Yes', DATEADD(hour, -1, DATEADD(Day ,-1, CURRENT_TIMESTAMP())), CURRENT_TIMESTAMP());

vvgsrk

CREATE TABLE product_history (
	product_id NUMBER,
	product_name VARCHAR,
	price_per_unit VARCHAR,
	basic_unit VARCHAR,
	is_stock_limited VARCHAR,
	is_active_for_sale VARCHAR,
	__event_ts TIMESTAMP_NTZ(9), 
	__load_ts TIMESTAMP_NTZ(9)
);

vvgsrk

{#
- This macro generate hash value to compare row rank and incremental data 
#}

{%- macro compare_row_rank_query_result_with_incremental_table(history_model, incremental_model) -%}
{%- set history_columns = adapter.get_columns_in_relation(history_model) -%}
{%- set inc_columns = adapter.get_columns_in_relation(incremental_model) -%}
WITH ranked_rows AS
(
SELECT

vvgsrk

{% test validate_row_rank_and_incremental_query_result(model, incremental_model) %}

    {{ compare_row_rank_query_result_with_incremental_table(model, ref(incremental_model)) }}
    WHERE result > 0

{% endtest %}

vvgsrk

select
      count(*) as failures,
      count(*) != 0 as should_warn,
      count(*) != 0 as should_error
    from (   
    WITH ranked_rows AS
(
SELECT
    ROW_NUMBER() OVER (
        PARTITION BY "PRODUCT_ID" 

vvgsrk

{#
- This macro generate row counts for lattest data based on history data and incremental data 
- Compares both the row counts and outputs the diffrence
#}

{%- macro compare_lattest_and_incremental_row_count(history_model, incremental_model) -%}
{%- set columns = adapter.get_columns_in_relation(history_model) -%}
WITH snap AS
(
SELECT

vvgsrk

{% test validate_row_count_for_lattest_and_incremental(model, incremental_model) %}

    {{ compare_lattest_and_incremental_row_count(model, ref(incremental_model)) }}
    WHERE diff_count > 0

{% endtest %}

vvgsrk

version: 2

models:
  - name: src_product_management_product_history
    tests:
      - validate_row_rank_and_incremental_query_result:
          incremental_model: src_product_management_product_latest_incremental
          tags: ["nightly"]
      - validate_row_count_for_lattest_and_incremental:
          incremental_model: src_product_management_product_latest_incremental

vvgsrk

select
      count(*) as failures,
      count(*) != 0 as should_warn,
      count(*) != 0 as should_error
    from (
    WITH snap AS
(
SELECT
    ROW_NUMBER() OVER (
        PARTITION BY "PRODUCT_ID" 

vvgsrk

-- A database, schema and table are required before you can load data

CREATE DATABASE amadeus_dynamic_pricing;

CREATE SCHEMA amadeus_dynamic_pricing.dynamic_pricing;

-- Create a table in newly created permanent database as same as the table from the share
CREATE TABLE amadeus_dynamic_pricing.dynamic_pricing.dp_logs
AS 
SELECT * FROM dynamic_pricing_data_share_by_amadeus.dynamic_pricing.dp_logs WHERE 1=2;