Real Estate JSON Quick Start

Pancake's application package includes REALESTATE_JSON, a sample data source designed to show you the scanning capabilities of the app.

Below is a script you can copy and paste into a new Worksheet after you have installed Pancake from the Snowflake Marketplace.

After following the instructions and running the script you will see the REALESTATE_JSON data source added to Pancake, and you will be able to view Schema Summary information, a sample of Schema Analysis information, and the Dynamic Tables the data sources flattens out into.

• Script #1 will set up Pancake using our sample real estate data provided with the Pancake install which includes only two records.

• Script #1A - will create a new database and schema in your Snowflake Account with Pancake dynamic tables to extract, flatten, and relate the real estate sample dataset. A sample select statement is also provided to show you how much better it is to process data using dynamic tables instead of parsing raw JSON.

/* ********** SCRIPT #1 - Sample Data (Real Estate)

Script Placeholders:

(For convenience, you can use the built in find/replace feature in Snowflake for the following placeholders)

<application_install_role> (x2) - Use the name of the role you used to install Pancake. 

In order to run this script you will also need to have access to the ACCOUNTADMIN role. 
After running the following SQL statements you can open Pancake and go to the Data Source Overview to see the new data source.

*/

USE ROLE <application_install_role>;
GRANT APPLICATION ROLE PANCAKE.APP_PUBLIC TO ROLE ACCOUNTADMIN;

USE ROLE ACCOUNTADMIN;

GRANT EXECUTE TASK ON ACCOUNT TO APPLICATION PANCAKE;

USE ROLE <application_install_role>;

CREATE OR REPLACE WAREHOUSE PANCAKE_X_SMALL_01 WITH
  WAREHOUSE_SIZE = 'X-Small';

GRANT USAGE on WAREHOUSE PANCAKE_X_SMALL_01 to APPLICATION PANCAKE;

DECLARE
    warehouse_name VARCHAR DEFAULT 'PANCAKE_X_SMALL_01';
    warehouse_size VARCHAR DEFAULT 'X-Small';
    call_result VARCHAR;
BEGIN
    CALL PANCAKE.CORE.ADD_WAREHOUSE(:warehouse_name, :warehouse_size) into :call_result;
    return call_result;
END;

DECLARE 
	datasource_name VARCHAR DEFAULT 'PANCAKE.SAMPLE_DATA.REALESTATE_JSON'; 
	object_type VARCHAR DEFAULT 'Table'; 
	database_name VARCHAR DEFAULT 'PANCAKE'; 
	schema_name VARCHAR DEFAULT 'SAMPLE_DATA'; 
	object_name VARCHAR DEFAULT 'REALESTATE_JSON'; 
	column_name VARCHAR DEFAULT 'JSON_DATA'; 
	datasource_tag VARCHAR DEFAULT ''; 
	warehouse_name VARCHAR DEFAULT 'PANCAKE_X_SMALL_01'; 
	record_limit DEFAULT 150; 
	initiate_scan DEFAULT TRUE; 
	call_result VARCHAR;
BEGIN 
	CALL PANCAKE.core.add_datasource_with_scan( 
:datasource_name,:object_type,:database_name,:schema_name,:object_name,:column_name, 
:warehouse_name, :record_limit, :initiate_scan, :datasource_tag) into
:call_result;

return call_result;

END;

Script #1A

/* ********** SCRIPT #1A - Dynamic Table Creation For Sample Data (Real Estate) - Optional

The following SQL statements will create a new database and schema for the purpose of evaluating the use of dynamic tables for the Pancake Sample Real Estate dataset.  
After the database and schema has been created, the next set of SQL statements will create the dynamic tables required to extract, flatten, and relate the data found in the REALESTATE_JSON table provided in the Pancake.Sample_Data schema included with the Pancake install. The final SQL statement is an example of how the now relational data in the dynamic tables can easily be selected from or joined. 

Script Placeholders:

(For convenience, you can use the built in find/replace feature in Snowflake for the following placeholders)

<database_name> (x8) - the name of the database you want to create to host the dynamic tables.
<create_database_role> (x2) - the name of a role that can create a database in your account that can also access the Pancake App.
<application_install_role> (x1) - the name of the role you used to install Pancake. 

*/


--optional if the create database role does not have access to Pancake
-- USE ROLE <application_install_role>;
-- GRANT APPLICATION ROLE PANCAKE.APP_PUBLIC TO ROLE <create_database_role>;

USE ROLE <create_database_role>;

create database if not exists <database_name>;
create schema if not exists <database_name>.SAMPLE_DATA;
USE DATABASE <database_name>;

CREATE OR REPLACE DYNAMIC TABLE SAMPLE_DATA.raw_realestate
    TARGET_LAG = '5 minutes'
    WAREHOUSE = PANCAKE_X_SMALL_01

    AS
    SELECT
    CASE WHEN typeof(JSON_DATA:"_id") = 'VARCHAR' THEN JSON_DATA:"_id"::VARCHAR ELSE NULL END AS "_id_str",
    CASE WHEN typeof(JSON_DATA:"address") = 'ARRAY' THEN JSON_DATA:"address" ELSE NULL END AS "address_array",
    CASE WHEN typeof(JSON_DATA:"address") = 'VARCHAR' THEN JSON_DATA:"address"::VARCHAR ELSE NULL END AS "address_str",
    CASE WHEN typeof(JSON_DATA:"amenities") = 'ARRAY' THEN JSON_DATA:"amenities" ELSE NULL END AS "amenities_array",
    CASE WHEN typeof(JSON_DATA:"maintenance_log") = 'ARRAY' THEN JSON_DATA:"maintenance_log" ELSE NULL END AS "maintenance_log_array",
    CASE WHEN typeof(JSON_DATA:"owner"."contact"."email") = 'VARCHAR' THEN JSON_DATA:"owner"."contact"."email"::VARCHAR ELSE NULL END AS "owner_contact_email_str",
    CASE WHEN typeof(JSON_DATA:"owner"."contact"."name") = 'VARCHAR' THEN JSON_DATA:"owner"."contact"."name"::VARCHAR ELSE NULL END AS "owner_contact_name_str",
    CASE WHEN typeof(JSON_DATA:"owner"."contact"."phone") = 'VARCHAR' THEN JSON_DATA:"owner"."contact"."phone"::VARCHAR ELSE NULL END AS "owner_contact_phone_str",
    CASE WHEN typeof(JSON_DATA:"owner"."name") = 'VARCHAR' THEN JSON_DATA:"owner"."name"::VARCHAR ELSE NULL END AS "owner_name_str",
    CASE WHEN typeof(JSON_DATA:"owner"."ownerships"."percentage_owned") = 'INTEGER' THEN JSON_DATA:"owner"."ownerships"."percentage_owned"::INTEGER ELSE NULL END AS "owner_ownerships_percentage_owned_int",
    CASE WHEN typeof(JSON_DATA:"property_id") = 'VARCHAR' THEN JSON_DATA:"property_id"::VARCHAR ELSE NULL END AS "property_id_str",
    CASE WHEN typeof(JSON_DATA:"property_management"."company_name") = 'VARCHAR' THEN JSON_DATA:"property_management"."company_name"::VARCHAR ELSE NULL END AS "property_management_company_name_str",
    CASE WHEN typeof(JSON_DATA:"property_management"."contact"."email") = 'VARCHAR' THEN JSON_DATA:"property_management"."contact"."email"::VARCHAR ELSE NULL END AS "property_management_contact_email_str",
    CASE WHEN typeof(JSON_DATA:"property_management"."contact"."name") = 'VARCHAR' THEN JSON_DATA:"property_management"."contact"."name"::VARCHAR ELSE NULL END AS "property_management_contact_name_str",
    CASE WHEN typeof(JSON_DATA:"property_management"."contact"."phone") = 'VARCHAR' THEN JSON_DATA:"property_management"."contact"."phone"::VARCHAR ELSE NULL END AS "property_management_contact_phone_str",
    CASE WHEN typeof(JSON_DATA:"property_management"."notes") = 'ARRAY' THEN JSON_DATA:"property_management"."notes" ELSE NULL END AS "property_management_notes_array",
    CASE WHEN typeof(JSON_DATA:"property_management"."services_offered") = 'ARRAY' THEN JSON_DATA:"property_management"."services_offered" ELSE NULL END AS "property_management_services_offered_array",
    CASE WHEN typeof(JSON_DATA:"property_type") = 'VARCHAR' THEN JSON_DATA:"property_type"::VARCHAR ELSE NULL END AS "property_type_str",
    CASE WHEN typeof(JSON_DATA:"property_type"."sq_ft") = 'INTEGER' THEN JSON_DATA:"property_type"."sq_ft"::INTEGER ELSE NULL END AS "property_type_sq_ft_int",
    CASE WHEN typeof(JSON_DATA:"property_type"."usage") = 'VARCHAR' THEN JSON_DATA:"property_type"."usage"::VARCHAR ELSE NULL END AS "property_type_usage_str",
    CASE WHEN typeof(JSON_DATA:"units") = 'ARRAY' THEN JSON_DATA:"units" ELSE NULL END AS "units_array"
    FROM PANCAKE.SAMPLE_DATA.REALESTATE_JSON;


CREATE OR REPLACE DYNAMIC TABLE SAMPLE_DATA.raw_realestate_address_obj_arr
    TARGET_LAG = '5 minutes'
    WAREHOUSE = PANCAKE_X_SMALL_01
    AS
    SELECT 
    "property_id_str" AS "property_id", 
    CASE WHEN typeof(value:"city") = 'VARCHAR' THEN value:"city"::VARCHAR ELSE NULL END AS "city_str",
    CASE WHEN typeof(value:"state") = 'VARCHAR' THEN value:"state"::VARCHAR ELSE NULL END AS "state_str",
    CASE WHEN typeof(value:"street") = 'VARCHAR' THEN value:"street"::VARCHAR ELSE NULL END AS "street_str",
    CASE WHEN typeof(value:"zip_code") = 'VARCHAR' THEN value:"zip_code"::VARCHAR ELSE NULL END AS "zip_code_str" 
    FROM
    SAMPLE_DATA.raw_realestate,
    LATERAL FLATTEN(INPUT => SAMPLE_DATA.raw_realestate."address_array")
    WHERE typeof(value) = 'OBJECT';


CREATE OR REPLACE DYNAMIC TABLE SAMPLE_DATA.raw_realestate_amenities_obj_arr
    TARGET_LAG = '5 minutes'
    WAREHOUSE = PANCAKE_X_SMALL_01
    AS
    SELECT 
    "property_id_str" AS "property_id", 
    CASE WHEN typeof(value:"availability") = 'VARCHAR' THEN value:"availability"::VARCHAR ELSE NULL END AS "availability_str",
    CASE WHEN typeof(value:"capacity") = 'INTEGER' THEN value:"capacity"::INTEGER ELSE NULL END AS "capacity_int",
    CASE WHEN typeof(value:"name") = 'VARCHAR' THEN value:"name"::VARCHAR ELSE NULL END AS "name_str" 
    FROM
    SAMPLE_DATA.raw_realestate,
    LATERAL FLATTEN(INPUT => SAMPLE_DATA.raw_realestate."amenities_array")
    WHERE typeof(value) = 'OBJECT';


CREATE OR REPLACE DYNAMIC TABLE SAMPLE_DATA.raw_realestate_amenities_array_primitive
    TARGET_LAG = '5 minutes'
    WAREHOUSE = PANCAKE_X_SMALL_01
    AS
    SELECT 

    "property_id_str" AS "property_id", value::VARCHAR AS "amenities_str"
    FROM
    SAMPLE_DATA.raw_realestate,
    LATERAL FLATTEN(INPUT => SAMPLE_DATA.raw_realestate."amenities_array")
    WHERE typeof(value) = 'VARCHAR';


CREATE OR REPLACE DYNAMIC TABLE SAMPLE_DATA.raw_realestate_maintenance_log_obj_arr
    TARGET_LAG = '5 minutes'
    WAREHOUSE = PANCAKE_X_SMALL_01
    AS
    SELECT 
    "property_id_str" AS "property_id", 
    CASE WHEN typeof(value:"action") = 'VARCHAR' THEN value:"action"::VARCHAR ELSE NULL END AS "action_str",
    CASE WHEN typeof(value:"area") = 'VARCHAR' THEN value:"area"::VARCHAR ELSE NULL END AS "area_str",
    CASE WHEN typeof(value:"contractor") = 'VARCHAR' THEN value:"contractor"::VARCHAR ELSE NULL END AS "contractor_str",
    CASE WHEN typeof(value:"cost") = 'INTEGER' THEN value:"cost"::INTEGER ELSE NULL END AS "cost_int",
    CASE WHEN TRY_TO_DATE(value:"date"::VARCHAR,'YYYYMMDD') IS NOT NULL THEN TO_DATE((value:"date"::VARCHAR),'YYYYMMDD') ELSE NULL END AS "date_str" 
    FROM
    SAMPLE_DATA.raw_realestate,
    LATERAL FLATTEN(INPUT => SAMPLE_DATA.raw_realestate."maintenance_log_array")
    WHERE typeof(value) = 'OBJECT';


CREATE OR REPLACE DYNAMIC TABLE SAMPLE_DATA.raw_realestate_property_management_obj_notes_obj_arr
    TARGET_LAG = '5 minutes'
    WAREHOUSE = PANCAKE_X_SMALL_01
    AS
    SELECT 

    CASE WHEN TRY_TO_DATE(value:"date"::VARCHAR,'YYYYMMDD') IS NOT NULL THEN TO_DATE((value:"date"::VARCHAR),'YYYYMMDD') ELSE NULL END AS "date_str",
    CASE WHEN typeof(value:"note") = 'VARCHAR' THEN value:"note"::VARCHAR ELSE NULL END AS "note_str" 
    FROM
    SAMPLE_DATA.raw_realestate,
    LATERAL FLATTEN(INPUT => SAMPLE_DATA.raw_realestate."property_management_notes_array")
    WHERE typeof(value) = 'OBJECT';


CREATE OR REPLACE DYNAMIC TABLE SAMPLE_DATA.raw_realestate_property_management_obj_notes_array_primitive
    TARGET_LAG = '5 minutes'
    WAREHOUSE = PANCAKE_X_SMALL_01
    AS
    SELECT 

    value::VARCHAR AS "notes_str"
    FROM
    SAMPLE_DATA.raw_realestate,
    LATERAL FLATTEN(INPUT => SAMPLE_DATA.raw_realestate."property_management_notes_array")
    WHERE typeof(value) = 'VARCHAR';


CREATE OR REPLACE DYNAMIC TABLE SAMPLE_DATA.raw_realestate_property_management_obj_services_offered_array_primitive
    TARGET_LAG = '5 minutes'
    WAREHOUSE = PANCAKE_X_SMALL_01
    AS
    SELECT 

    value::VARCHAR AS "services_offered_str"
    FROM
    SAMPLE_DATA.raw_realestate,
    LATERAL FLATTEN(INPUT => SAMPLE_DATA.raw_realestate."property_management_services_offered_array")
    WHERE typeof(value) = 'VARCHAR';


CREATE OR REPLACE DYNAMIC TABLE SAMPLE_DATA.raw_realestate_units_obj_arr
    TARGET_LAG = '5 minutes'
    WAREHOUSE = PANCAKE_X_SMALL_01
    AS
    SELECT 
    "property_id_str" AS "property_id", 
    CASE WHEN typeof(value:"sqft") = 'DECIMAL' THEN value:"sqft"::DECIMAL(6,2) ELSE NULL END AS "sqft_float",
    CASE WHEN typeof(value:"sqft") = 'INTEGER' THEN value:"sqft"::INTEGER ELSE NULL END AS "sqft_int",
    CASE WHEN typeof(value:"tenants") = 'ARRAY' THEN value:"tenants" ELSE NULL END AS "tenants_array",
    CASE WHEN typeof(value:"unit_id") = 'VARCHAR' THEN value:"unit_id"::VARCHAR ELSE NULL END AS "unit_id_str",
    CASE WHEN typeof(value:"unit_type") = 'VARCHAR' THEN value:"unit_type"::VARCHAR ELSE NULL END AS "unit_type_str",
    CASE WHEN typeof(value:"utilities"."electricity") = 'BOOLEAN' THEN value:"utilities"."electricity"::BOOLEAN ELSE NULL END AS "utilities_electricity_bool",
    CASE WHEN typeof(value:"utilities"."heating") = 'VARCHAR' THEN value:"utilities"."heating"::VARCHAR ELSE NULL END AS "utilities_heating_str",
    CASE WHEN typeof(value:"utilities"."internet_service") = 'ARRAY' THEN value:"utilities"."internet_service" ELSE NULL END AS "utilities_internet_service_array",
    CASE WHEN typeof(value:"utilities"."water") = 'BOOLEAN' THEN value:"utilities"."water"::BOOLEAN ELSE NULL END AS "utilities_water_bool" 
    FROM
    SAMPLE_DATA.raw_realestate,
    LATERAL FLATTEN(INPUT => SAMPLE_DATA.raw_realestate."units_array")
    WHERE typeof(value) = 'OBJECT';


CREATE OR REPLACE DYNAMIC TABLE SAMPLE_DATA.raw_realestate_units_obj_arr_tenants_obj_arr
    TARGET_LAG = '5 minutes'
    WAREHOUSE = PANCAKE_X_SMALL_01
    AS
    SELECT 
    "property_id" AS "property_id", "unit_id_str" AS "unit_id", 
    CASE WHEN typeof(value:"company_name") = 'VARCHAR' THEN value:"company_name"::VARCHAR ELSE NULL END AS "company_name_str",
    CASE WHEN typeof(value:"contacts") = 'ARRAY' THEN value:"contacts" ELSE NULL END AS "contacts_array",
    CASE WHEN TRY_TO_DATE(value:"lease"."end_date"::VARCHAR,'YYYYMMDD') IS NOT NULL THEN TO_DATE((value:"lease"."end_date"::VARCHAR),'YYYYMMDD') ELSE NULL END AS "lease_end_date_str",
    CASE WHEN typeof(value:"lease"."maintenance") = 'ARRAY' THEN value:"lease"."maintenance" ELSE NULL END AS "lease_maintenance_array",
    CASE WHEN typeof(value:"lease"."maintenance_requests") = 'ARRAY' THEN value:"lease"."maintenance_requests" ELSE NULL END AS "lease_maintenance_requests_array",
    CASE WHEN typeof(value:"lease"."monthly_rent") = 'INTEGER' THEN value:"lease"."monthly_rent"::INTEGER ELSE NULL END AS "lease_monthly_rent_int",
    CASE WHEN typeof(value:"lease"."renewal_option"."rent_increase_percentage") = 'INTEGER' THEN value:"lease"."renewal_option"."rent_increase_percentage"::INTEGER ELSE NULL END AS "lease_renewal_option_rent_increase_percentage_int",
    CASE WHEN typeof(value:"lease"."renewal_option"."term") = 'VARCHAR' THEN value:"lease"."renewal_option"."term"::VARCHAR ELSE NULL END AS "lease_renewal_option_term_str",
    CASE WHEN typeof(value:"lease"."renewal_options") = 'ARRAY' THEN value:"lease"."renewal_options" ELSE NULL END AS "lease_renewal_options_array",
    CASE WHEN typeof(value:"lease"."security_deposit") = 'INTEGER' THEN value:"lease"."security_deposit"::INTEGER ELSE NULL END AS "lease_security_deposit_int",
    CASE WHEN TRY_TO_DATE(value:"lease"."start_date"::VARCHAR,'YYYYMMDD') IS NOT NULL THEN TO_DATE((value:"lease"."start_date"::VARCHAR),'YYYYMMDD') ELSE NULL END AS "lease_start_date_str",
    CASE WHEN typeof(value:"name") = 'VARCHAR' THEN value:"name"::VARCHAR ELSE NULL END AS "name_str",
    CASE WHEN TRY_TO_DATE(value:"profile"."background_check"."date"::VARCHAR,'YYYYMMDD') IS NOT NULL THEN TO_DATE((value:"profile"."background_check"."date"::VARCHAR),'YYYYMMDD') ELSE NULL END AS "profile_background_check_date_str",
    CASE WHEN typeof(value:"profile"."background_check"."passed") = 'BOOLEAN' THEN value:"profile"."background_check"."passed"::BOOLEAN ELSE NULL END AS "profile_background_check_passed_bool",
    CASE WHEN TRY_TO_DATE(value:"profile"."credit_check"."date"::VARCHAR,'YYYYMMDD') IS NOT NULL THEN TO_DATE((value:"profile"."credit_check"."date"::VARCHAR),'YYYYMMDD') ELSE NULL END AS "profile_credit_check_date_str",
    CASE WHEN typeof(value:"profile"."credit_check"."notes") = 'ARRAY' THEN value:"profile"."credit_check"."notes" ELSE NULL END AS "profile_credit_check_notes_array",
    CASE WHEN typeof(value:"profile"."credit_check"."score") = 'VARCHAR' THEN value:"profile"."credit_check"."score"::VARCHAR ELSE NULL END AS "profile_credit_check_score_str",
    CASE WHEN typeof(value:"profile"."employment"."employer") = 'VARCHAR' THEN value:"profile"."employment"."employer"::VARCHAR ELSE NULL END AS "profile_employment_employer_str",
    CASE WHEN typeof(value:"profile"."employment"."income") = 'INTEGER' THEN value:"profile"."employment"."income"::INTEGER ELSE NULL END AS "profile_employment_income_int",
    CASE WHEN typeof(value:"profile"."employment"."position") = 'VARCHAR' THEN value:"profile"."employment"."position"::VARCHAR ELSE NULL END AS "profile_employment_position_str",
    CASE WHEN typeof(value:"profile"."references") = 'ARRAY' THEN value:"profile"."references" ELSE NULL END AS "profile_references_array",
    CASE WHEN typeof(value:"tenant_id") = 'VARCHAR' THEN value:"tenant_id"::VARCHAR ELSE NULL END AS "tenant_id_str" 
    FROM
    SAMPLE_DATA.raw_realestate_units_obj_arr,
    LATERAL FLATTEN(INPUT => SAMPLE_DATA.raw_realestate_units_obj_arr."tenants_array")
    WHERE typeof(value) = 'OBJECT';


CREATE OR REPLACE DYNAMIC TABLE SAMPLE_DATA.raw_realestate_units_obj_arr_tenants_obj_arr_contacts_obj_arr
    TARGET_LAG = '5 minutes'
    WAREHOUSE = PANCAKE_X_SMALL_01
    AS
    SELECT 

    CASE WHEN typeof(value:"email") = 'VARCHAR' THEN value:"email"::VARCHAR ELSE NULL END AS "email_str",
    CASE WHEN typeof(value:"name") = 'VARCHAR' THEN value:"name"::VARCHAR ELSE NULL END AS "name_str",
    CASE WHEN typeof(value:"phone") = 'VARCHAR' THEN value:"phone"::VARCHAR ELSE NULL END AS "phone_str",
    CASE WHEN typeof(value:"title") = 'VARCHAR' THEN value:"title"::VARCHAR ELSE NULL END AS "title_str" 
    FROM
    SAMPLE_DATA.raw_realestate_units_obj_arr_tenants_obj_arr,
    LATERAL FLATTEN(INPUT => SAMPLE_DATA.raw_realestate_units_obj_arr_tenants_obj_arr."contacts_array")
    WHERE typeof(value) = 'OBJECT';


CREATE OR REPLACE DYNAMIC TABLE SAMPLE_DATA.raw_realestate_units_obj_arr_tenants_obj_arr_lease_obj_maintenance_obj_arr
    TARGET_LAG = '5 minutes'
    WAREHOUSE = PANCAKE_X_SMALL_01
    AS
    SELECT 
    "tenant_id_str" AS "tenant_id", "property_id" AS "property_id", "unit_id" AS "unit_id", 
    CASE WHEN typeof(value:"contractor") = 'VARCHAR' THEN value:"contractor"::VARCHAR ELSE NULL END AS "contractor_str",
    CASE WHEN typeof(value:"cost") = 'INTEGER' THEN value:"cost"::INTEGER ELSE NULL END AS "cost_int",
    CASE WHEN TRY_TO_DATE(value:"date"::VARCHAR,'YYYYMMDD') IS NOT NULL THEN TO_DATE((value:"date"::VARCHAR),'YYYYMMDD') ELSE NULL END AS "date_str",
    CASE WHEN typeof(value:"issue") = 'VARCHAR' THEN value:"issue"::VARCHAR ELSE NULL END AS "issue_str",
    CASE WHEN typeof(value:"resolved") = 'BOOLEAN' THEN value:"resolved"::BOOLEAN ELSE NULL END AS "resolved_bool" 
    FROM
    SAMPLE_DATA.raw_realestate_units_obj_arr_tenants_obj_arr,
    LATERAL FLATTEN(INPUT => SAMPLE_DATA.raw_realestate_units_obj_arr_tenants_obj_arr."lease_maintenance_array")
    WHERE typeof(value) = 'OBJECT';


CREATE OR REPLACE DYNAMIC TABLE SAMPLE_DATA.raw_realestate_units_obj_arr_tenants_obj_arr_lease_obj_maintenance_requests_obj_arr
    TARGET_LAG = '5 minutes'
    WAREHOUSE = PANCAKE_X_SMALL_01
    AS
    SELECT 

    CASE WHEN TRY_TO_DATE(value:"date"::VARCHAR,'YYYYMMDD') IS NOT NULL THEN TO_DATE((value:"date"::VARCHAR),'YYYYMMDD') ELSE NULL END AS "date_str",
    CASE WHEN typeof(value:"issue") = 'VARCHAR' THEN value:"issue"::VARCHAR ELSE NULL END AS "issue_str",
    CASE WHEN typeof(value:"resolved") = 'BOOLEAN' THEN value:"resolved"::BOOLEAN ELSE NULL END AS "resolved_bool" 
    FROM
    SAMPLE_DATA.raw_realestate_units_obj_arr_tenants_obj_arr,
    LATERAL FLATTEN(INPUT => SAMPLE_DATA.raw_realestate_units_obj_arr_tenants_obj_arr."lease_maintenance_requests_array")
    WHERE typeof(value) = 'OBJECT';


CREATE OR REPLACE DYNAMIC TABLE SAMPLE_DATA.raw_realestate_units_obj_arr_tenants_obj_arr_lease_obj_renewal_options_obj_arr
    TARGET_LAG = '5 minutes'
    WAREHOUSE = PANCAKE_X_SMALL_01
    AS
    SELECT 

    CASE WHEN typeof(value:"rent_increase_percentage") = 'INTEGER' THEN value:"rent_increase_percentage"::INTEGER ELSE NULL END AS "rent_increase_percentage_int",
    CASE WHEN typeof(value:"term") = 'VARCHAR' THEN value:"term"::VARCHAR ELSE NULL END AS "term_str" 
    FROM
    SAMPLE_DATA.raw_realestate_units_obj_arr_tenants_obj_arr,
    LATERAL FLATTEN(INPUT => SAMPLE_DATA.raw_realestate_units_obj_arr_tenants_obj_arr."lease_renewal_options_array")
    WHERE typeof(value) = 'OBJECT';


CREATE OR REPLACE DYNAMIC TABLE SAMPLE_DATA.raw_realestate_units_obj_arr_tenants_obj_arr_profile_obj_credit_check_obj_notes_array_primitive
    TARGET_LAG = '5 minutes'
    WAREHOUSE = PANCAKE_X_SMALL_01
    AS
    SELECT 

    value::VARCHAR AS "notes_str"
    FROM
    SAMPLE_DATA.raw_realestate_units_obj_arr_tenants_obj_arr,
    LATERAL FLATTEN(INPUT => SAMPLE_DATA.raw_realestate_units_obj_arr_tenants_obj_arr."profile_credit_check_notes_array")
    WHERE typeof(value) = 'VARCHAR';


CREATE OR REPLACE DYNAMIC TABLE SAMPLE_DATA.raw_realestate_units_obj_arr_tenants_obj_arr_profile_obj_references_obj_arr
    TARGET_LAG = '5 minutes'
    WAREHOUSE = PANCAKE_X_SMALL_01
    AS
    SELECT 

    CASE WHEN typeof(value:"name") = 'VARCHAR' THEN value:"name"::VARCHAR ELSE NULL END AS "name_str",
    CASE WHEN typeof(value:"phone") = 'VARCHAR' THEN value:"phone"::VARCHAR ELSE NULL END AS "phone_str",
    CASE WHEN typeof(value:"relationship") = 'VARCHAR' THEN value:"relationship"::VARCHAR ELSE NULL END AS "relationship_str" 
    FROM
    SAMPLE_DATA.raw_realestate_units_obj_arr_tenants_obj_arr,
    LATERAL FLATTEN(INPUT => SAMPLE_DATA.raw_realestate_units_obj_arr_tenants_obj_arr."profile_references_array")
    WHERE typeof(value) = 'OBJECT';


CREATE OR REPLACE DYNAMIC TABLE SAMPLE_DATA.raw_realestate_units_obj_arr_utilities_obj_internet_service_obj_arr
    TARGET_LAG = '5 minutes'
    WAREHOUSE = PANCAKE_X_SMALL_01
    AS
    SELECT 

    CASE WHEN typeof(value:"price") = 'DECIMAL' THEN value:"price"::DECIMAL(8,4) ELSE NULL END AS "price_float",
    CASE WHEN typeof(value:"provider") = 'VARCHAR' THEN value:"provider"::VARCHAR ELSE NULL END AS "provider_str",
    CASE WHEN typeof(value:"speed") = 'VARCHAR' THEN value:"speed"::VARCHAR ELSE NULL END AS "speed_str" 
    FROM
    SAMPLE_DATA.raw_realestate_units_obj_arr,
    LATERAL FLATTEN(INPUT => SAMPLE_DATA.raw_realestate_units_obj_arr."utilities_internet_service_array")
    WHERE typeof(value) = 'OBJECT';



select * from sample_data.raw_realestate r 
left outer join sample_data.raw_realestate_address_obj_arr ra on r."property_id_str" = ra."property_id"
left outer join sample_data.raw_realestate_units_obj_arr ru on r."property_id_str" = ru."property_id"
left outer join sample_data.raw_realestate_units_obj_arr_tenants_obj_arr rut on ru."property_id" = rut."property_id" and ru."unit_id_str" = rut."unit_id"
left outer join sample_data.raw_realestate_units_obj_arr_tenants_obj_arr_lease_obj_maintenance_obj_arr rtl on rut."tenant_id_str" = rtl."tenant_id"
;