Bellow is a number of SQL views that will help you work better with Facebook Ads data and Amazon Redshift. If you want to work only with the latest values then it is helpful to create a view where only the latest values are maintained. For reference here is an overview of the expected Facebook Ads data into your data warehouse.
SQL View (Amazon Redshift): Latest Events for Facebook Ads ads table
CREATE VIEW AdsView AS SELECT a.account_id, a.ad_review_feedback_global_text_overlay, a.ad_review_feedback_placement_specific_facebook_text_overlay, a.adset_id, a.bid_amount, a.bid_info_actions, a.bid_info_reach, a.bid_type, a.blendo_id, a.blendo_imported_at, a.campaign_id, a.configured_status, a.created_time, a.creative_id, a.effective_status, a.id, a.last_updated_by_app_id, a.name, a.recommendations_blame_field, a.recommendations_code, a.recommendations_confidence, a.recommendations_importance, a.recommendations_message, a.recommendations_title, a.status, a.tracking_specs_action_type, a.tracking_specs_application, a.tracking_specs_creative, a.tracking_specs_event, a.tracking_specs_fb_pixel, a.tracking_specs_page, a.tracking_specs_post, a.id, a.blendo_id, tmp.MostRecent FROM "ads" AS a, ( SELECT id, max(to_timestamp(updated_time, 'YYYY-MM-DD"T"HH24:MI:SS')) AS MostRecent FROM "ads" GROUP BY id)tmp WHERE (a.id = tmp.id) AND (tmp.MostRecent=to_timestamp(updated_time, 'YYYY-MM-DD"T"HH24:MI:SS'))
SQL View (Amazon Redshift): Latest Events for Facebook Ads adsets table
CREATE VIEW AdsetView AS SELECT a.blendo_id, a.account_id, a.id, a.attribution_window_days, a.billing_event, a.budget_remaining, a.campaign_id, a.configured_status, a.created_time, a.daily_budget, a.effective_status, a.end_time, a.frequency_cap_reset_period, a.is_autobid, a.is_average_price_pacing, a.lifetime_budget, a.lifetime_imps, a.name, a.optimization_goal, a.pacing_type, a.recurring_budget_semantics, a.rtb_flag, a.start_time, a.status, a.updated_time, a.use_new_app_click, tmp.MostRecent FROM "adsets" AS a, ( SELECT id, max(to_timestamp(updated_time, 'YYYY-MM-DD"T"HH24:MI:SS')) AS MostRecent FROM "adsets" GROUP BY id)tmp WHERE (a.id = tmp.id) AND (tmp.MostRecent=to_timestamp(updated_time, 'YYYY-MM-DD"T"HH24:MI:SS'))
SQL View (Amazon Redshift): Latest Events for Facebook Ads ad_insights table
CREATE VIEW InsightsView AS SELECT a.blendo_id, a.impressions, a.reach, a.adset_name, a.social_spend, a.unique_inline_link_click_ctr, a.total_action_value, a.unique_clicks, a.unique_social_clicks, a.app_store_clicks, a.unique_link_clicks_ctr, a.cost_per_estimated_ad_recallers, a.spend, a.cost_per_unique_inline_link_click, a.social_reach, a.relevance_score_negative_feedback, a.relevance_score_positive_feedback, a.cpp, a.newsfeed_impressions, a.unique_inline_link_clicks, a.cost_per_inline_link_click, a.campaign_name, a.inline_link_clicks, a.canvas_avg_view_time, a.unique_impressions, a.newsfeed_clicks, a.inline_link_click_ctr, a.cpc, a.date_start, a.cpm, a.adset_id, a.objective, a.newsfeed_avg_position, a.account_id, a.cost_per_unique_click, a.call_to_action_clicks, a.estimated_ad_recallers, a.ad_name, a.website_clicks, a.account_name, a.cost_per_inline_post_engagement, a.clicks, a.social_clicks, a.social_impressions, a.ad_id, a.frequency, a.estimated_ad_recall_rate, a.unique_social_impressions, a.ctr, a.inline_post_engagement, a.relevance_score_score, a.relevance_score_status, a.deeplink_clicks, a.campaign_id, a.unique_ctr, a.canvas_avg_view_percent, tmp.MostRecentStop, tmp.MostRecentImp FROM "ad_insights" AS a, ( SELECT max(date_stop) AS MostRecentStop, max(blendo_imported_at) AS MostRecentImp, blendo_id FROM "ad_insights" GROUP BY blendo_id)tmp WHERE (a.blendo_id = tmp.blendo_id) AND a.date_stop=tmp.MostRecentStop AND a.blendo_imported_at = tmp.MostRecentImp
SQL View (Amazon Redshift): Latest Events for Facebook Ads ad_placement_device table
CREATE VIEW InsightsPlacementDeviceView AS SELECT a.account_id, a.account_name, a.action_values_action_type, a.action_values_value, a.actions_action_type, a.actions_value, a.ad_id, a.ad_name, a.adset_id, a.adset_name, a.app_store_clicks, a.blendo_id, a.blendo_imported_at, a.call_to_action_clicks, a.campaign_id, a.campaign_name, a.canvas_avg_view_percent, a.canvas_avg_view_time, a.clicks, a.cost_per_10_sec_video_view_action_type, a.cost_per_10_sec_video_view_value, a.cost_per_estimated_ad_recallers, a.cost_per_inline_link_click, a.cost_per_inline_post_engagement, a.cost_per_unique_action_type_action_type, a.cost_per_unique_action_type_value, a.cost_per_unique_click, a.cost_per_unique_inline_link_click, a.cpc, a.cpm, a.cpp, a.ctr, a.date_start, a.date_stop, a.deeplink_clicks, a.estimated_ad_recall_rate, a.estimated_ad_recall_rate_lower_bound, a.estimated_ad_recall_rate_upper_bound, a.estimated_ad_recallers, a.frequency, a.impression_device, a.impressions, a.inline_link_click_ctr, a.inline_link_clicks, a.inline_post_engagement, a.objective, a.placement, a.reach, a.social_clicks, a.social_impressions, a.social_reach, a.spend, a.total_action_value, a.unique_actions_action_type, a.unique_actions_value, a.unique_clicks, a.unique_ctr, a.unique_impressions, a.unique_inline_link_click_ctr, a.unique_inline_link_clicks, a.unique_link_clicks_ctr, a.unique_social_clicks, a.unique_social_impressions, a.video_10_sec_watched_actions_action_type, a.video_10_sec_watched_actions_value, a.video_15_sec_watched_actions_action_type, a.video_15_sec_watched_actions_value, a.video_30_sec_watched_actions_action_type, a.video_30_sec_watched_actions_value, a.video_avg_pct_watched_actions_action_type, a.video_avg_pct_watched_actions_value, a.video_avg_sec_watched_actions_action_type, a.video_avg_sec_watched_actions_value, a.video_complete_watched_actions_action_type, a.video_complete_watched_actions_value, a.video_p100_watched_actions_action_type, a.video_p100_watched_actions_value, a.video_p25_watched_actions_action_type, a.video_p25_watched_actions_value, a.video_p50_watched_actions_action_type, a.video_p50_watched_actions_value, a.video_p75_watched_actions_action_type, a.video_p75_watched_actions_value, a.video_p95_watched_actions_action_type, a.video_p95_watched_actions_value, a.website_clicks, tmp.MostRecentStop, tmp.MostRecentImp FROM "ad_placement_device" AS a, ( SELECT max(date_stop) AS MostRecentStop, max(blendo_imported_at) AS MostRecentImp, blendo_id FROM "ad_placement_device" GROUP BY blendo_id)tmp WHERE (a.blendo_id = tmp.blendo_id) AND a.date_stop=tmp.MostRecentStop AND a.blendo_imported_at = tmp.MostRecentImp
SQL View (Amazon Redshift): Latest Events for Facebook Ads ad_age_gender table
CREATE VIEW InsightsAgeGenderView AS SELECT a.account_id, a.account_name, a.action_values_action_type, a.action_values_value, a.actions_action_type, a.actions_value, a.ad_id, a.ad_name, a.adset_id, a.adset_name, a.app_store_clicks, a.blendo_id, a.blendo_imported_at, a.call_to_action_clicks, a.campaign_id, a.campaign_name, a.canvas_avg_view_percent, a.canvas_avg_view_time, a.clicks, a.cost_per_10_sec_video_view_action_type, a.cost_per_10_sec_video_view_value, a.cost_per_estimated_ad_recallers, a.cost_per_inline_link_click, a.cost_per_inline_post_engagement, a.cost_per_unique_action_type_action_type, a.cost_per_unique_action_type_value, a.cost_per_unique_click, a.cost_per_unique_inline_link_click, a.cpc, a.cpm, a.cpp, a.ctr, a.date_start, a.date_stop, a.deeplink_clicks, a.estimated_ad_recall_rate, a.estimated_ad_recall_rate_lower_bound, a.estimated_ad_recall_rate_upper_bound, a.estimated_ad_recallers, a.frequency, a.impressions, a.inline_link_click_ctr, a.inline_link_clicks, a.inline_post_engagement, a.objective, a.gender, a.age, a.reach, a.social_clicks, a.social_impressions, a.social_reach, a.spend, a.total_action_value, a.unique_actions_action_type, a.unique_actions_value, a.unique_clicks, a.unique_ctr, a.unique_impressions, a.unique_inline_link_click_ctr, a.unique_inline_link_clicks, a.unique_link_clicks_ctr, a.unique_social_clicks, a.unique_social_impressions, a.video_10_sec_watched_actions_action_type, a.video_10_sec_watched_actions_value, a.video_15_sec_watched_actions_action_type, a.video_15_sec_watched_actions_value, a.video_30_sec_watched_actions_action_type, a.video_30_sec_watched_actions_value, a.video_avg_pct_watched_actions_action_type, a.video_avg_pct_watched_actions_value, a.video_avg_sec_watched_actions_action_type, a.video_avg_sec_watched_actions_value, a.video_complete_watched_actions_action_type, a.video_complete_watched_actions_value, a.video_p100_watched_actions_action_type, a.video_p100_watched_actions_value, a.video_p25_watched_actions_action_type, a.video_p25_watched_actions_value, a.video_p50_watched_actions_action_type, a.video_p50_watched_actions_value, a.video_p75_watched_actions_action_type, a.video_p75_watched_actions_value, a.video_p95_watched_actions_action_type, a.video_p95_watched_actions_value, a.website_clicks, tmp.MostRecentStop, tmp.MostRecentImp FROM "ad_age_gender" AS a, ( SELECT max(date_stop) AS MostRecentStop, max(blendo_imported_at) AS MostRecentImp, blendo_id FROM "ad_age_gender" GROUP BY blendo_id)tmp WHERE (a.blendo_id = tmp.blendo_id) AND a.date_stop = tmp.MostRecentStop AND a.blendo_imported_at=tmp.MostRecentImp
SQL View (Amazon Redshift): Latest Events for Facebook Ads ad_country table
CREATE VIEW InsightsCountryView AS SELECT a.account_id, a.account_name, a.action_values_action_type, a.action_values_value, a.actions_action_type, a.actions_value, a.ad_id, a.ad_name, a.adset_id, a.adset_name, a.app_store_clicks, a.blendo_id, a.blendo_imported_at, a.call_to_action_clicks, a.campaign_id, a.campaign_name, a.canvas_avg_view_percent, a.canvas_avg_view_time, a.clicks, a.cost_per_10_sec_video_view_action_type, a.cost_per_10_sec_video_view_value, a.cost_per_estimated_ad_recallers, a.cost_per_inline_link_click, a.cost_per_inline_post_engagement, a.cost_per_unique_action_type_action_type, a.cost_per_unique_action_type_value, a.cost_per_unique_click, a.cost_per_unique_inline_link_click, a.cpc, a.cpm, a.cpp, a.ctr, a.date_start, a.date_stop, a.deeplink_clicks, a.estimated_ad_recall_rate, a.estimated_ad_recall_rate_lower_bound, a.estimated_ad_recall_rate_upper_bound, a.estimated_ad_recallers, a.frequency, a.impressions, a.inline_link_click_ctr, a.inline_link_clicks, a.inline_post_engagement, a.objective, a.country, a.reach, a.social_clicks, a.social_impressions, a.social_reach, a.spend, a.total_action_value, a.unique_actions_action_type, a.unique_actions_value, a.unique_clicks, a.unique_ctr, a.unique_impressions, a.unique_inline_link_click_ctr, a.unique_inline_link_clicks, a.unique_link_clicks_ctr, a.unique_social_clicks, a.unique_social_impressions, a.video_10_sec_watched_actions_action_type, a.video_10_sec_watched_actions_value, a.video_15_sec_watched_actions_action_type, a.video_15_sec_watched_actions_value, a.video_30_sec_watched_actions_action_type, a.video_30_sec_watched_actions_value, a.video_avg_pct_watched_actions_action_type, a.video_avg_pct_watched_actions_value, a.video_avg_sec_watched_actions_action_type, a.video_avg_sec_watched_actions_value, a.video_complete_watched_actions_action_type, a.video_complete_watched_actions_value, a.video_p100_watched_actions_action_type, a.video_p100_watched_actions_value, a.video_p25_watched_actions_action_type, a.video_p25_watched_actions_value, a.video_p50_watched_actions_action_type, a.video_p50_watched_actions_value, a.video_p75_watched_actions_action_type, a.video_p75_watched_actions_value, a.video_p95_watched_actions_action_type, a.video_p95_watched_actions_value, a.website_clicks, tmp.MostRecentStop, tmp.MostRecentImp FROM "ad_country" AS a, ( SELECT max(date_stop) AS MostRecentStop, max(blendo_imported_at) AS MostRecentImp, blendo_id FROM "ad_country" GROUP BY blendo_id)tmp WHERE (a.blendo_id = tmp.blendo_id) AND a.date_stop = tmp.MostRecentStop AND a.blendo_imported_at=tmp.MostRecentImp
SQL View (Amazon Redshift): Latest Events for Facebook Ads account_insights table
CREATE VIEW insightscountryview AS SELECT a.account_id, a.account_name, a.actions_action_type, a.actions_value, a.app_store_clicks, a.blendo_id, a.blendo_imported_at, a.call_to_action_clicks, a.canvas_avg_view_percent, a.canvas_avg_view_time, a.clicks, a.cost_per_10_sec_video_view_action_type, a.cost_per_10_sec_video_view_value, a.cost_per_estimated_ad_recallers, a.cost_per_inline_link_click, a.cost_per_inline_post_engagement, a.cost_per_unique_click, a.cost_per_unique_inline_link_click, a.cpc, a.cpm, a.cpp, a.ctr, a.date_start, a.date_stop, a.deeplink_clicks, a.estimated_ad_recall_rate, a.estimated_ad_recallers, a.frequency, a.impressions, a.inline_link_click_ctr, a.inline_link_clicks, a.inline_post_engagement, a.objective, a.reach, a.social_clicks, a.social_impressions, a.social_reach, a.social_spend, a.spend, a.total_action_value, a.unique_clicks, a.unique_ctr, a.unique_impressions, a.unique_inline_link_click_ctr, a.unique_inline_link_clicks, a.unique_link_clicks_ctr, a.unique_social_clicks, a.unique_social_impressions, a.video_10_sec_watched_actions_action_type, a.video_10_sec_watched_actions_value, a.video_15_sec_watched_actions_action_type, a.video_15_sec_watched_actions_value, a.video_30_sec_watched_actions_action_type, a.video_30_sec_watched_actions_value, a.video_avg_pct_watched_actions_action_type, a.video_avg_pct_watched_actions_value, a.video_avg_sec_watched_actions_action_type, a.video_avg_sec_watched_actions_value, a.video_complete_watched_actions_action_type, a.video_complete_watched_actions_value, a.video_p100_watched_actions_action_type, a.video_p100_watched_actions_value, a.video_p25_watched_actions_action_type, a.video_p25_watched_actions_value, a.video_p50_watched_actions_action_type, a.video_p50_watched_actions_value, a.video_p75_watched_actions_action_type, a.video_p75_watched_actions_value, a.video_p95_watched_actions_action_type, a.video_p95_watched_actions_value, a.website_clicks FROM "account_insights" AS a, (SELECT Max(date_stop) AS MostRecentStop, Max(blendo_imported_at) AS MostRecentImp, blendo_id FROM "account_insights" GROUP BY blendo_id)tmp WHERE ( a.blendo_id = tmp.blendo_id ) AND a.date_stop = tmp.mostrecentstop AND a.blendo_imported_at = tmp.mostrecentimp
SQL View (Amazon Redshift): Latest Events for Facebook Ads account_insights_actions table
CREATE VIEW AccInsightsActionsView AS SELECT a.action_cost, a.action_value, a.blendo_imported_at, a.account_id, a.account_name, a.action_type, a.ad_id, a.adset_id, a.blendo_id, a.campaign_id, a.date_start, a.date_stop FROM "account_insights_actions" AS a, ( SELECT max(date_stop) AS MostRecentStop, max(blendo_imported_at) AS MostRecentImp, blendo_id FROM "account_insights_actions" GROUP BY blendo_id)tmp WHERE (a.blendo_id = tmp.blendo_id) AND a.date_stop=tmp.MostRecentStop AND a.blendo_imported_at=tmp.MostRecentImp
SQL View (Amazon Redshift): Latest Events for Facebook Ads account_insights_unique_actions table
CREATE VIEW InsightUniqueActionsView AS SELECT a.action_cost, a.action_value, a.blendo_imported_at, a.account_id, a.account_name, a.action_type, a.blendo_id, a.date_start, a.date_stop FROM "account_insights_unique_actions" AS a, ( SELECT max(date_stop) AS MostRecentStop, max(blendo_imported_at) AS MostRecentImp, blendo_id FROM "account_insights_unique_actions" GROUP BY blendo_id)tmp WHERE (a.blendo_id = tmp.blendo_id) AND a.date_stop = tmp.MostRecentStop AND a.blendo_imported_at = tmp.MostRecentImp
SQL View (Amazon Redshift): Latest Events for Facebook Ads ad_conversion_specs table
CREATE VIEW ConversionSpecsView AS SELECT a.blendo_imported_at, a.action_type, a.application, a.blendo_id, a.conversion_id, a.event, a.object, a.page, a.parent_id, tmp.MostRecent, a.post, a.post_wall, a.response FROM "ad_conversion_specs" AS a, ( SELECT max(parent_updated_at) AS MostRecent, blendo_id FROM "ad_conversion_specs" GROUP BY blendo_id)tmp WHERE a.blendo_id=tmp.blendo_id AND a.parent_updated_at = tmp.MostRecent
SQL View (Amazon Redshift): Latest Events for Facebook Ads insights_actions table
CREATE VIEW InsightsActionsView AS SELECT tmp.MostRecentImp, a.action_cost, a.action_type, a.action_value, a.ad_id, a.adset_id, a.blendo_id, a.campaign_id, a.date_start, tmp.MostRecentStop FROM "insights_actions" AS a, ( SELECT max(date_stop) AS MostRecentStop, max(blendo_imported_at) AS MostRecentImp, blendo_id FROM "insights_actions" GROUP BY blendo_id)tmp WHERE (a.blendo_id = tmp.blendo_id) AND a.date_stop = tmp.MostRecentStop AND a.blendo_imported_at= tmp.MostRecentImp
SQL View (Amazon Redshift): Latest Events for Facebook Ads insights_unique_actions table
CREATE VIEW UniqueInsightsView AS SELECT a.action_cost, a.action_value, tmp.MostRecentImp, a.account_id, a.account_name, a.action_type, a.ad_id, a.adset_id, a.blendo_id, a.campaign_id, a.date_start, tmp.MostRecentStop FROM "insights_unique_actions" AS a, ( SELECT max(date_stop) AS MostRecentStop, max(blendo_imported_at) AS MostRecentImp, blendo_id FROM "insights_unique_actions" GROUP BY blendo_id)tmp WHERE (a.blendo_id = tmp.blendo_id) AND a.date_stop = tmp.MostRecentStop AND a.blendo_imported_At = tmp.MostRecentImp
SQL View (Amazon Redshift): Latest Events for Facebook Ads campaigns table
CREATE VIEW CampaignsView AS SELECT a.blendo_imported_at, a.account_id, a.budget_rebalance_flag, a.buying_type, a.can_use_spend_cap, a.configured_status, a.created_time, a.effective_status, a.id, a.name, a.objective, a.spend_cap, a.start_time, a.status, a.stop_time, tmp.MostRecent FROM "campaigns" AS a, (SELECT max(updated_time) AS MostRecent, id FROM "campaigns" GROUP BY id)tmp WHERE a.id=tmp.id AND a.updated_time=tmp.MostRecent
