How do I pull opportunity data from Salesforce?

-- This Transform represents an individual opportunity, which is a sale or a pending deal in Salesforce -- This Transform pulls out some of the most important fields from the "opportunity" table in Salesforce.

with opportunity as (
    
    select 

        _fivetran_synced,
        account_id,
        amount,
        campaign_id,
        close_date,
        created_date,
        description,
        fiscal,
        fiscal_quarter,
        fiscal_year,
        forecast_category,
        forecast_category_name,
        has_open_activity,
        has_opportunity_line_item,
        has_overdue_task,
        id as opportunity_id,
        is_closed,
        is_deleted,
        is_won,
        last_activity_date,
        last_referenced_date,
        last_viewed_date,
        lead_source,
        name as opportunity_name,
        next_step,
        owner_id,
        probability,
        record_type_id,
        stage_name,
        synced_quote_id,
        type

    from salesforce.opportunity

), opportunity_calculated as (
        
    select 
        *,
        created_date >= date_trunc('month', current_timestamp()) as is_created_this_month,
        created_date >= date_trunc('quarter',current_timestamp()) as is_created_this_quarter,
        datediff('day', created_date, current_timestamp()) as days_since_created,
        datediff('day', created_date, close_date) as days_to_close,
        date_trunc('month', close_date) = date_trunc('month', current_timestamp()) as is_closed_this_month,
        date_trunc('quarter', close_date) = date_trunc('quarter', current_timestamp()) as is_closed_this_quarter

    from opportunity

)

select * 
from opportunity_calculated
where not coalesce(is_deleted, false)
LIMIT 100

Learn more about the WITH clause and common table expressions (CTEs) through Snowflake (our data warehouse partner) or Mode (a business intelligence tool partner).