# ruff: noqa: E501 from __future__ import annotations from string import Template def compatibility_table_name(table: str = "order") -> str: return f"{table}_compat_from_nexuscrm" SALESFORCE_ORDER_COMPATIBILITY_SQL = Template( """ WITH order_item_source AS ( SELECT oi.OrderId AS order_id, oi.Id AS sf_id, p.Name AS product_name, p.Family AS product_family, SAFE_CAST(NULLIF(oi.Quantity, '') AS FLOAT64) AS quantity, SAFE_CAST(NULLIF(oi.ServiceDate, '') AS DATE) AS service_start_date, SAFE_CAST(NULLIF(oi.EndDate, '') AS DATE) AS service_end_date, SAFE_CAST(NULLIF(oi.Net_fees__c, '') AS BIGNUMERIC) AS net_fees, SAFE_CAST(NULLIF(oi.UnitPrice, '') AS BIGNUMERIC) AS unit_net_price, p.ProductCode AS product_code FROM `${project}.nexuscrm_final.nexuscrm_orderitem` oi INNER JOIN `${project}.nexuscrm_final.nexuscrm_product2` p ON p.Id = oi.Product2Id WHERE oi.IsDeleted = 0 AND p.IsDeleted = 0 ), order_items AS ( SELECT order_id, COUNT(*) AS count_product_lines, SUM(net_fees) AS net_fees, MIN(service_start_date) AS service_start_date, MAX(service_end_date) AS service_end_date, ARRAY_AGG( STRUCT( sf_id, CASE WHEN product_name IN ( 'TiDB Enterprise - Enterprise Subscription', 'TiDB Enterprise - Pilot Subscription - Cluster', 'TiDB Enterprise - Premium Subscription', 'TiDB Enterprise - Premium Subscription Lite (US Only)', 'TiDB Enterprise - Standard Subscription' ) THEN 'Subscription' WHEN product_name IN ( 'PingCAP Consulting Services', 'PingCAP Professional Services', 'PingCAP Professional Services-Outsourcing' ) THEN 'Professional and others' ELSE product_name END AS name, quantity, service_start_date, service_end_date, net_fees, unit_net_price, product_code ) ORDER BY sf_id ) AS products, ARRAY_AGG( CASE WHEN product_name IN ( 'TiDB Enterprise - Enterprise Subscription', 'TiDB Enterprise - Pilot Subscription - Cluster', 'TiDB Enterprise - Premium Subscription', 'TiDB Enterprise - Premium Subscription Lite (US Only)', 'TiDB Enterprise - Standard Subscription' ) THEN 'TiDB Enterprise Subscription' WHEN product_name IN ( 'PingCAP Consulting Services', 'PingCAP Professional Services', 'PingCAP Professional Services-Outsourcing' ) THEN 'Professional and others' ELSE product_family END IGNORE NULLS ORDER BY sf_id LIMIT 1 )[SAFE_OFFSET(0)] AS product_family FROM order_item_source GROUP BY order_id ), monthly_cloud_orders AS ( SELECT new_order_id, account_region, account_surrogate_key, order_end_date, order_start_date, accumulate_revenue_to_org, account_name, account_number, net_fee, tax, account_id, revrec_entity FROM `${project}.data_platform.monthly_cloud_order` WHERE IFNULL(TRIM(new_order_id), '') != '' QUALIFY ROW_NUMBER() OVER ( PARTITION BY new_order_id ORDER BY updated_at DESC, sync_time DESC, id DESC ) = 1 ), source_rows AS ( SELECT o.*, a.Name AS account_name, a.Account_ID__c AS account_account_id_c, a.AccountNumber AS account_number_from_account, a.Region__c AS account_region, a.PingCAP_Contracting_Entity__c AS account_contracting_entity, a.Accumulate_revenue_to_Org__c AS account_accumulate_revenue_to_org, q.OpportunityId AS quote_opportunity_id, q.Payment_Term__c AS quote_payment_term, q.Payment_Type__c AS quote_payment_type, q.Partner_Name__c AS quote_partner_name, q.Product_Family__c AS quote_product_family, q.Service_Start_Date__c AS quote_service_start_date, q.Service_End_Date__c AS quote_service_end_date, q.Tax__c AS quote_tax, q.Contains_Other_Nonstd_Terms__c AS quote_contains_other_nonstd_terms, opp.CloseDate AS opportunity_close_date, opp.Partner_Name__c AS opportunity_partner_name, opp.Product_Family__c AS opportunity_product_family, opp.Region__c AS opportunity_region, oi.net_fees AS product_net_fees, oi.service_start_date AS product_service_start_date, oi.service_end_date AS product_service_end_date, oi.product_family AS product_product_family, oi.count_product_lines, mco.account_region AS monthly_account_region, mco.account_surrogate_key, mco.order_start_date AS monthly_order_start_date, mco.order_end_date AS monthly_order_end_date, mco.accumulate_revenue_to_org AS monthly_accumulate_revenue_to_org, mco.account_name AS monthly_account_name, mco.account_number AS monthly_account_number, mco.net_fee AS monthly_net_fee, mco.tax AS monthly_tax, mco.account_id AS monthly_account_id, mco.revrec_entity AS monthly_revrec_entity FROM `${project}.nexuscrm_final.nexuscrm_order` o LEFT JOIN `${project}.nexuscrm_final.nexuscrm_account` a ON a.Id = o.AccountId LEFT JOIN `${project}.nexuscrm_final.nexuscrm_quote` q ON q.Id = o.QuoteId LEFT JOIN `${project}.nexuscrm_final.nexuscrm_opportunity` opp ON opp.Id = COALESCE(NULLIF(o.OpportunityId, ''), NULLIF(q.OpportunityId, '')) LEFT JOIN order_items oi ON oi.order_id = o.Id LEFT JOIN monthly_cloud_orders mco ON mco.new_order_id = o.Id ), derived AS ( SELECT *, COALESCE(SAFE_CAST(NULLIF(EffectiveDate, '') AS DATE), monthly_order_start_date, product_service_start_date) AS effective_date_value, COALESCE( SAFE_CAST(NULLIF(Order_End_Date__c, '') AS DATE), SAFE_CAST(NULLIF(EndDate, '') AS DATE), monthly_order_end_date, product_service_end_date ) AS end_date_value, COALESCE(SAFE_CAST(NULLIF(overall_start_date__c, '') AS DATE), product_service_start_date, monthly_order_start_date) AS overall_start_date_value, COALESCE(SAFE_CAST(NULLIF(overall_end_date__c, '') AS DATE), product_service_end_date, monthly_order_end_date) AS overall_end_date_value, COALESCE( SAFE_CAST(NULLIF(net_fees__c, '') AS BIGNUMERIC), product_net_fees, SAFE_CAST(monthly_net_fee AS BIGNUMERIC), SAFE_CAST(NULLIF(TotalAmount, '') AS BIGNUMERIC) ) AS net_fees_value, COALESCE(SAFE_CAST(NULLIF(Tax__c, '') AS FLOAT64), SAFE_CAST(NULLIF(quote_tax, '') AS FLOAT64), SAFE_CAST(monthly_tax AS FLOAT64)) AS tax_value, COALESCE(NULLIF(TRIM(Product_Family__c), ''), NULLIF(TRIM(product_product_family), ''), NULLIF(TRIM(quote_product_family), ''), NULLIF(TRIM(opportunity_product_family), '')) AS product_family_value, COALESCE( SAFE_CAST(NULLIF(Exchange_Rate_to_Use__c, '') AS FLOAT64), SAFE_CAST(NULLIF(Exchange_Rate_from_RevRec__c, '') AS FLOAT64), SAFE_CAST(NULLIF(Exchange_Rate__c, '') AS FLOAT64), CASE WHEN CurrencyIsoCode = 'USD' THEN 1.0 END ) AS exchange_rate_to_use_value, COALESCE( SAFE_CAST(NULLIF(Term__c, '') AS FLOAT64), CASE WHEN COALESCE( SAFE_CAST(NULLIF(Order_End_Date__c, '') AS DATE), SAFE_CAST(NULLIF(EndDate, '') AS DATE), monthly_order_end_date, product_service_end_date ) IS NOT NULL AND COALESCE(SAFE_CAST(NULLIF(EffectiveDate, '') AS DATE), monthly_order_start_date, product_service_start_date) IS NOT NULL THEN ROUND(DATE_DIFF( COALESCE( SAFE_CAST(NULLIF(Order_End_Date__c, '') AS DATE), SAFE_CAST(NULLIF(EndDate, '') AS DATE), monthly_order_end_date, product_service_end_date ), COALESCE(SAFE_CAST(NULLIF(EffectiveDate, '') AS DATE), monthly_order_start_date, product_service_start_date), DAY ) / 30.4375, 1) END ) AS term_value FROM source_rows ), finance_compatible AS ( SELECT *, COALESCE(SAFE_CAST(NULLIF(TCV_Amount__c, '') AS BIGNUMERIC), net_fees_value) AS tcv_amount_value, COALESCE(SAFE_CAST(NULLIF(TotalAmount, '') AS BIGNUMERIC), net_fees_value) AS total_amount_value, COALESCE( SAFE_CAST(NULLIF(Real_Booking_Date__c, '') AS DATE), SAFE_CAST(NULLIF(Write_Off_Date__c, '') AS DATE), CASE WHEN product_family_value = 'TiDB Cloud' AND term_value < 1.5 THEN effective_date_value ELSE SAFE_CAST(NULLIF(opportunity_close_date, '') AS DATE) END ) AS real_booking_date_value FROM derived ) SELECT Id AS id, _record_loaded_at AS _fivetran_synced, COALESCE(NULLIF(AccountId, ''), NULLIF(monthly_account_id, '')) AS account_id, ActivatedById AS activated_by_id, ActivatedDate AS activated_date, Applicable_Law__c AS applicable_law_c, Approval_Status__c AS approval_status_c, Archived__c != 0 AS archived_c, BillToContactId AS bill_to_contact_id, CAST(NULL AS STRING) AS billing_city, CAST(NULL AS STRING) AS billing_country, CAST(NULL AS STRING) AS billing_country_code, CAST(NULL AS STRING) AS billing_geocode_accuracy, CAST(NULL AS FLOAT64) AS billing_latitude, CAST(NULL AS FLOAT64) AS billing_longitude, CAST(NULL AS STRING) AS billing_postal_code, CAST(NULL AS STRING) AS billing_state, CAST(NULL AS STRING) AS billing_state_code, CAST(NULL AS STRING) AS billing_street, CompanyAuthorizedById AS company_authorized_by_id, ContractId AS contract_id, CreatedById AS created_by_id, CreatedDate AS created_date, CurrencyIsoCode AS currency_iso_code, CustomerAuthorizedById AS customer_authorized_by_id, Description AS description, effective_date_value AS effective_date, Email__c AS email_c, end_date_value AS end_date, IsDeleted != 0 AS is_deleted, CAST(NULL AS BOOL) AS is_reduction_order, LastModifiedById AS last_modified_by_id, LastModifiedDate AS last_modified_date, CAST(NULL AS TIMESTAMP) AS last_referenced_date, CAST(NULL AS TIMESTAMP) AS last_viewed_date, SAFE_CAST(NULLIF(Monthly_Cost__c, '') AS BIGNUMERIC) AS monthly_cost_c, COALESCE(name, OrderNumber) AS name, Non_Standard_Type__c AS non_standard_type_c, NonStandardUploadUrl__c AS non_standard_upload_url_c, COALESCE(NULLIF(OpportunityId, ''), NULLIF(quote_opportunity_id, '')) AS opportunity_id, end_date_value AS order_end_date_c, Order_Expiration_Alert__c != 0 AS order_expiration_alert_c, OrderNumber AS order_number, OriginalOrderId AS original_order_id, Owned_DM__c AS owned_dm_c, OwnerId AS owner_id, COALESCE(NULLIF(Partner_Name__c, ''), NULLIF(quote_partner_name, ''), NULLIF(opportunity_partner_name, '')) AS partner_name_c, COALESCE(NULLIF(Payment_Term__c, ''), NULLIF(quote_payment_term, '')) AS payment_term_c, COALESCE(NULLIF(Payment_Type__c, ''), NULLIF(quote_payment_type, '')) AS payment_type_c, CAST(NULL AS STRING) AS po_number, Pricebook2Id AS pricebook_2_id, product_family_value AS product_family_c, QuoteId AS quote_id, RecordTypeId AS record_type_id, CAST(NULL AS STRING) AS shipping_city, CAST(NULL AS STRING) AS shipping_country, CAST(NULL AS STRING) AS shipping_country_code, CAST(NULL AS STRING) AS shipping_geocode_accuracy, CAST(NULL AS FLOAT64) AS shipping_latitude, CAST(NULL AS FLOAT64) AS shipping_longitude, CAST(NULL AS STRING) AS shipping_postal_code, CAST(NULL AS STRING) AS shipping_state, CAST(NULL AS STRING) AS shipping_state_code, CAST(NULL AS STRING) AS shipping_street, StandardUploadUrl__c AS standard_upload_url_c, Status AS status, StatusCode AS status_code, Stripe_Bill_ID__c AS stripe_bill_id_c, SystemModstamp AS system_modstamp, tax_value AS tax_c, total_amount_value AS total_amount, Type AS type, SAFE_CAST(NULLIF(X30_Alert_Arranged_Date__c, '') AS DATE) AS x_30_alert_arranged_date_c, SAFE_CAST(NULLIF(X60_Alert_Arranged_Date__c, '') AS DATE) AS x_60_alert_arranged_date_c, SAFE_CAST(NULLIF(X90_Alert_Arranged_Date__c, '') AS DATE) AS x_90_alert_arranged_date_c, Order_to_be_deleted__c != 0 AS order_to_be_deleted_c, exchange_rate_to_use_value AS exchange_rate_to_use_c, SAFE_CAST(NULLIF(Exchange_Rate_from_RevRec__c, '') AS FLOAT64) AS exchange_rate_from_rev_rec_c, CAST(NULL AS STRING) AS marketplace_c, COALESCE(NULLIF(PingCAP_RevRec_Entity__c, ''), NULLIF(monthly_revrec_entity, '')) AS ping_cap_rev_rec_entity_c, Cloud_Registration_Source__c AS cloud_registration_source_c, IsDeleted != 0 AS _fivetran_deleted, SAFE_CAST(NULLIF(ACV_Amount_do_not_use__c, '') AS BIGNUMERIC) AS acv_amount_do_not_use_c, SAFE_CAST(NULLIF(Total_Fees_New__c, '') AS BIGNUMERIC) AS total_fees_new_c, AutoNumberText__c AS auto_number_text_c, SAFE_CAST(NULLIF(AutoNumber__c, '') AS FLOAT64) AS auto_number_c, Related_Original_Order__c AS related_original_order_c, Reason__c AS reason_c, Order_is_Terminated__c != 0 AS order_is_terminated_c, SAFE_CAST(NULLIF(Termination_Date__c, '') AS DATE) AS termination_date_c, Currency_for_Finance__c AS currency_for_finance_c, COALESCE(NULLIF(Account_Name_for_email_template__c, ''), NULLIF(account_name, ''), NULLIF(monthly_account_name, '')) AS account_name_for_email_template_c, SAFE_CAST(NULLIF(vCPU__c, '') AS FLOAT64) AS v_cpu_c, PLG_BD_Pool__c != 0 AS plg_bd_pool_c, SAFE_CAST(NULLIF(Write_Off_Date__c, '') AS DATE) AS write_off_date_c, Related_Original_Order__c AS related_original_order_1_c, SPIFF_Program__c AS spiff_program_c, SPIFF_Justification__c AS spiff_justification_c, Org__c AS org_c, OP_Paid_via_Marketplace__c != 0 AS op_via_marketplace_c, COALESCE(NULLIF(Accumulate_revenue_to_Org__c, ''), NULLIF(monthly_accumulate_revenue_to_org, ''), NULLIF(account_accumulate_revenue_to_org, '')) AS accumulate_revenue_to_org_c, OP_Paid_via_Marketplace__c != 0 AS op_paid_via_marketplace_c, Contain_Earlier_Termination_Clause__c != 0 AS contain_earlier_termination_clause_c, Is_Stripe__c != 0 AS is_stripe_c, COALESCE(NULLIF(Region__c, ''), NULLIF(account_region, ''), NULLIF(monthly_account_region, ''), NULLIF(opportunity_region, '')) AS region_c, tcv_amount_value AS tcv_amount_c, Cloud_Order_Type__c AS cloud_order_type_c, Deal_Registration__c AS deal_registration_c, SAFE_CAST(NULLIF(CD_Rate__c, '') AS FLOAT64) AS cd_rate_c, SAFE_CAST(NULLIF(Gross_Margin__c, '') AS FLOAT64) AS gross_margin_c, SAFE_DIVIDE(SAFE_CAST(NULLIF(ACV_Amount__c, '') AS FLOAT64), exchange_rate_to_use_value) AS acv_amount_usd_c, SAFE_CAST(NULLIF(accumulated_payable_amount__c, '') AS BIGNUMERIC) AS accumulated_payable_amount_c, Version_of_TiDB__c AS version_of_ti_db_c, overall_start_date_value AS overall_start_date_c, SAFE_CAST(NULLIF(Rebate_Fee_Rate__c, '') AS FLOAT64) AS rebate_fee_rate_c, SAFE_CAST(NULLIF(Deferred_Revenue_USD__c, '') AS FLOAT64) AS deferred_revenue_usd_c, SAFE_CAST(NULLIF(latest_payable_date__c, '') AS DATE) AS latest_payable_date_c, COALESCE(NULLIF(Account_ID__c, ''), NULLIF(account_account_id_c, ''), NULLIF(monthly_account_number, ''), NULLIF(account_number_from_account, '')) AS account_id_c, SAFE_CAST(NULLIF(Exchange_Rate__c, '') AS FLOAT64) AS exchange_rate_c, COALESCE(Is_Nonstandard_Order__c != 0, quote_contains_other_nonstd_terms != 0) AS is_nonstandard_order_c, Fiscal_Period__c AS fiscal_period_c, SAFE_DIVIDE(CAST(net_fees_value AS FLOAT64), exchange_rate_to_use_value) AS net_fees_usd_c, COALESCE( SAFE_CAST(NULLIF(Deferred_Revenue__c, '') AS BIGNUMERIC), CASE WHEN net_fees_value IS NOT NULL AND SAFE_CAST(NULLIF(Recognized_Revenue__c, '') AS BIGNUMERIC) IS NOT NULL THEN net_fees_value - SAFE_CAST(NULLIF(Recognized_Revenue__c, '') AS BIGNUMERIC) END ) AS deferred_revenue_c, SAFE_DIVIDE(SAFE_CAST(NULLIF(Recognized_Revenue__c, '') AS FLOAT64), exchange_rate_to_use_value) AS recognized_revenue_usd_c, SAFE_CAST(NULLIF(ValidDays__c, '') AS FLOAT64) AS valid_days_c, real_booking_date_value AS real_booking_date_c, COALESCE(NULLIF(Order_ID_18_digitals__c, ''), Id) AS order_id_18_digitals_c, SAFE_CAST(NULLIF(payable_amount_usd_exc_tax__c, '') AS FLOAT64) AS payable_amount_usd_exc_tax_c, SAFE_CAST(NULLIF(Cash_Collected__c, '') AS BIGNUMERIC) AS cash_collected_c, Closed_Days_In_61__c != 0 AS closed_days_in_61_c, COALESCE(NULLIF(Order_Record_Type__c, ''), RecordTypeId) AS order_record_type_c, COALESCE( SAFE_CAST(NULLIF(Total_Fees__c, '') AS BIGNUMERIC), CASE WHEN net_fees_value IS NOT NULL AND tax_value IS NOT NULL THEN SAFE_CAST(net_fees_value * SAFE_CAST(1 + IF(ABS(tax_value) > 1, tax_value / 100, tax_value) AS BIGNUMERIC) AS BIGNUMERIC) END ) AS total_fees_c, Closed_Days_Out_61__c != 0 AS closed_days_out_61_c, SAFE_CAST(NULLIF(recognized_revenue_amount_usd__c, '') AS FLOAT64) AS recognized_revenue_amount_usd_c, overall_end_date_value AS overall_end_date_c, SAFE_CAST(NULLIF(recognized_revenue_amount__c, '') AS BIGNUMERIC) AS recognized_revenue_amount_c, SAFE_CAST(NULLIF(Referral_Fee_Rate__c, '') AS FLOAT64) AS referral_fee_rate_c, SAFE_DIVIDE( CAST(COALESCE( SAFE_CAST(NULLIF(Total_Fees__c, '') AS BIGNUMERIC), CASE WHEN net_fees_value IS NOT NULL AND tax_value IS NOT NULL THEN SAFE_CAST(net_fees_value * SAFE_CAST(1 + IF(ABS(tax_value) > 1, tax_value / 100, tax_value) AS BIGNUMERIC) AS BIGNUMERIC) END ) AS FLOAT64), exchange_rate_to_use_value ) AS total_fees_usd_c, Sales_Channel_Type__c AS sales_channel_type_c, SAFE_CAST(NULLIF(Average_Discount__c, '') AS FLOAT64) AS average_discount_c, SAFE_CAST(NULLIF(Cash_Collected_USD__c, '') AS FLOAT64) AS cash_collected_usd_c, SAFE_CAST(NULLIF(total_list_price__c, '') AS BIGNUMERIC) AS total_list_price_c, SAFE_CAST(NULLIF(accumulated_credit_amount__c, '') AS FLOAT64) AS accumulated_credit_amount_c, SAFE_CAST(count_product_lines AS FLOAT64) AS count_product_lines_c, PingCAP_Contracting_Entity_EMEA_only__c AS ping_cap_contracting_entity_emea_only_c, SAFE_CAST(NULLIF(Cash_Uncollected__c, '') AS BIGNUMERIC) AS cash_uncollected_c, SAFE_CAST(NULLIF(Cash_Uncollected_USD__c, '') AS FLOAT64) AS cash_uncollected_usd_c, net_fees_value AS net_fees_c, COALESCE( SAFE_CAST(NULLIF(ACV_Amount__c, '') AS BIGNUMERIC), CASE WHEN SAFE_CAST(NULLIF(ACV_Amount_do_not_use__c, '') AS BIGNUMERIC) = 99999999 THEN 0 WHEN SAFE_CAST(NULLIF(ACV_Amount_do_not_use__c, '') AS BIGNUMERIC) IS NOT NULL AND SAFE_CAST(NULLIF(ACV_Amount_do_not_use__c, '') AS BIGNUMERIC) != 0 THEN SAFE_CAST(NULLIF(ACV_Amount_do_not_use__c, '') AS BIGNUMERIC) WHEN term_value > 12 THEN tcv_amount_value / CAST(term_value AS BIGNUMERIC) * 12 ELSE tcv_amount_value END ) AS acv_amount_c, SAFE_CAST(NULLIF(Recognized_Revenue__c, '') AS BIGNUMERIC) AS recognized_revenue_c, SAFE_CAST(NULLIF(accumulated_revenue_before_adjust__c, '') AS FLOAT64) AS accumulated_revenue_before_adjust_c, SAFE_CAST(NULLIF(cash_collected_amount__c, '') AS BIGNUMERIC) AS cash_collected_amount_c, term_value AS term_c, SAFE_CAST(NULLIF(cash_collected_usd_exc_tax__c, '') AS FLOAT64) AS cash_collected_usd_exc_tax_c, SAFE_DIVIDE(CAST(tcv_amount_value AS FLOAT64), exchange_rate_to_use_value) AS tcv_amount_usd_c, CAST(NULL AS STRING) AS non_standard_order_form_c, SAFE_CAST(NULLIF(Partner_Margin__c, '') AS FLOAT64) AS partner_margin_c, SAFE_CAST(NULLIF(Closed_Days__c, '') AS FLOAT64) AS closed_days_c, Prepayment_or_Not__c AS prepayment_or_not_c, SAFE_CAST(NULLIF(cash_collected_exc_tax_amount__c, '') AS BIGNUMERIC) AS cash_collected_exc_tax_amount_c, Order_Number__c AS order_number_c, SAFE_CAST(NULLIF(cash_collected_amount_usd__c, '') AS FLOAT64) AS cash_collected_amount_usd_c, SAFE_CAST(NULLIF(ACV_USD_with_CD_Rate__c, '') AS FLOAT64) AS acv_usd_with_cd_rate_c, Contract_Number__c AS contract_number_c, COALESCE(NULLIF(Account_Country_Code__c, ''), CAST(NULL AS STRING)) AS account_country_code_c, Archived_Order_Form__c AS archived_order_form_c, COALESCE( NULLIF(PingCAP_Contracting_Entity__c, ''), NULLIF(PingCAP_Contracting_Entity_EMEA_only__c, ''), NULLIF(account_contracting_entity, '') ) AS ping_cap_contracting_entity_c FROM finance_compatible WHERE IsDeleted = 0 """ ) def render_salesforce_order_compatibility_sql(project: str = "gcp-digital-bi") -> str: return SALESFORCE_ORDER_COMPATIBILITY_SQL.substitute(project=project).strip() + "\n" def render_salesforce_order_materialize_sql( project: str = "gcp-digital-bi", *, dataset: str = "salesforce", table: str = "order", ) -> str: compat_table = compatibility_table_name(table) query = render_salesforce_order_compatibility_sql(project=project).rstrip() return "\n".join( [ f"CREATE OR REPLACE TABLE `{project}.{dataset}.{compat_table}` AS", query, ";", f"ALTER TABLE `{project}.{dataset}.{compat_table}` ADD PRIMARY KEY(id) NOT ENFORCED;", "", ] ) def render_salesforce_order_cutover_sql( project: str = "gcp-digital-bi", *, dataset: str = "salesforce", table: str = "order", backup_suffix: str, ) -> str: if not backup_suffix or not backup_suffix.replace("_", "").isalnum(): raise ValueError("backup_suffix must contain only letters, numbers, and underscores") compat_table = compatibility_table_name(table) backup_table = f"{table}_legacy_backup_{backup_suffix}" return "\n".join( [ f"CREATE OR REPLACE TABLE `{project}.{dataset}.{backup_table}` AS", f"SELECT * FROM `{project}.{dataset}.{table}`;", "", render_salesforce_order_materialize_sql(project=project, dataset=dataset, table=table).rstrip(), "", f"CREATE OR REPLACE TABLE `{project}.{dataset}.{table}` AS", f"SELECT * FROM `{project}.{dataset}.{compat_table}`;", f"ALTER TABLE `{project}.{dataset}.{table}` ADD PRIMARY KEY(id) NOT ENFORCED;", "", "-- Rollback:", f"-- CREATE OR REPLACE TABLE `{project}.{dataset}.{table}` AS", f"-- SELECT * FROM `{project}.{dataset}.{backup_table}`;", f"-- ALTER TABLE `{project}.{dataset}.{table}` ADD PRIMARY KEY(id) NOT ENFORCED;", "", ] )