-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
-- Generated at 2024-08-25 15:52:41.052926+00:00
WITH 
"facebook_ads_account_history_data_projected" AS (
    -- Projection: Selecting 7 out of 8 columns
    -- Columns projected out: ['id', 'name', '_fivetran_synced', 'account_status', 'business_country_code', 'created_time', 'currency', 'timezone_name']
    SELECT 
        "id",
        "name",
        "account_status",
        "business_country_code",
        "created_time",
        "currency",
        "timezone_name"
    FROM "memory"."main"."facebook_ads_account_history_data"
),

"facebook_ads_account_history_data_projected_renamed" AS (
    -- Rename: Renaming columns
    -- id -> account_id
    -- name -> encrypted_account_name
    -- business_country_code -> country_code
    -- created_time -> creation_timestamp
    -- currency -> account_currency
    -- timezone_name -> account_timezone
    SELECT 
        "id" AS "account_id",
        "name" AS "encrypted_account_name",
        "account_status",
        "business_country_code" AS "country_code",
        "created_time" AS "creation_timestamp",
        "currency" AS "account_currency",
        "timezone_name" AS "account_timezone"
    FROM "facebook_ads_account_history_data_projected"
),

"facebook_ads_account_history_data_projected_renamed_dedup" AS (
    -- Deduplication: Removed 7 duplicated rows
    SELECT DISTINCT *
    FROM "facebook_ads_account_history_data_projected_renamed"
),

"facebook_ads_account_history_data_projected_renamed_dedup_casted" AS (
    -- Column Type Casting: 
    -- account_id: from INT to VARCHAR
    -- creation_timestamp: from VARCHAR to TIMESTAMP
    SELECT
        "encrypted_account_name",
        "account_status",
        "country_code",
        "account_currency",
        "account_timezone",
        CAST("account_id" AS VARCHAR) 
        AS "account_id",
        CAST("creation_timestamp" AS TIMESTAMP) 
        AS "creation_timestamp"
    FROM "facebook_ads_account_history_data_projected_renamed_dedup"
)

-- COCOON BLOCK END
SELECT *
FROM "facebook_ads_account_history_data_projected_renamed_dedup_casted"

version: 2
models:
- name: stg_facebook_ads_account_history_data
  description: The table is about Facebook ad account history. It contains details
    of ad accounts including ID, name, status, country, creation time, currency, and
    timezone. Each row represents an account state at a specific time. Changes in
    account details over time can be tracked, such as status changes from "Active"
    to "Paused" or location/currency updates.
  columns:
  - name: encrypted_account_name
    description: Encrypted name of the ad account
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: account_status
    description: Current status of the ad account
    tests:
    - not_null
    - accepted_values:
        values:
        - Active
        - Paused
    cocoon_meta:
      future_accepted_values:
      - Disabled
      - Pending
      - Closed
      data_type:
        current_data_type: VARCHAR
  - name: country_code
    description: Two-letter country code of the business
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: account_currency
    description: Currency used for the ad account
    tests:
    - not_null
    - accepted_values:
        values:
        - USD
        - GBP
        - JPY
    cocoon_meta:
      future_accepted_values:
      - EUR
      - CAD
      - AUD
      - CHF
      - CNY
      - HKD
      - NZD
      - SEK
      - KRW
      - SGD
      - NOK
      - MXN
      - INR
      - RUB
      - ZAR
      - TRY
      - BRL
      - TWD
      - DKK
      - PLN
      - THB
      - IDR
      data_type:
        current_data_type: VARCHAR
  - name: account_timezone
    description: Timezone associated with the ad account
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: account_id
    description: Unique identifier for the ad account
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: creation_timestamp
    description: Timestamp when the account was created
    tests:
    - not_null
    - unique
    cocoon_meta:
      unique_reason: This column represents the timestamp when the account state was
        recorded or updated. For this table, each row represents an account state
        at a specific time. The creation_timestamp is unique across rows as it captures
        the exact moment of each account state change or creation.
      data_type:
        current_data_type: TIMESTAMP
  tests: []

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
-- Generated at 2024-08-25 15:59:02.558135+00:00
WITH 
"facebook_ads_creative_history_data_projected" AS (
    -- Projection: Selecting 14 out of 15 columns
    -- Columns projected out: ['page_link', 'template_page_link', 'id', 'account_id', 'name', 'url_tags', '_fivetran_synced', 'asset_feed_spec_link_urls', 'object_story_link_data_child_attachments', 'object_story_link_data_caption', 'object_story_link_data_description', 'object_story_link_data_link', 'object_story_link_data_message', 'template_app_link_spec_ios', '_fivetran_id']
    SELECT 
        "page_link",
        "template_page_link",
        "id",
        "account_id",
        "name",
        "url_tags",
        "asset_feed_spec_link_urls",
        "object_story_link_data_child_attachments",
        "object_story_link_data_caption",
        "object_story_link_data_description",
        "object_story_link_data_link",
        "object_story_link_data_message",
        "template_app_link_spec_ios",
        "_fivetran_id"
    FROM "memory"."main"."facebook_ads_creative_history_data"
),

"facebook_ads_creative_history_data_projected_renamed" AS (
    -- Rename: Renaming columns
    -- page_link -> landing_page_url
    -- id -> creative_id
    -- name -> encoded_ad_name
    -- url_tags -> utm_parameters
    -- asset_feed_spec_link_urls -> asset_feed_specs
    -- object_story_link_data_child_attachments -> ad_attachments
    -- object_story_link_data_caption -> ad_caption
    -- object_story_link_data_description -> ad_description
    -- object_story_link_data_link -> ad_link
    -- object_story_link_data_message -> ad_message
    -- template_app_link_spec_ios -> ios_app_link_specs
    -- _fivetran_id -> fivetran_id
    SELECT 
        "page_link" AS "landing_page_url",
        "template_page_link",
        "id" AS "creative_id",
        "account_id",
        "name" AS "encoded_ad_name",
        "url_tags" AS "utm_parameters",
        "asset_feed_spec_link_urls" AS "asset_feed_specs",
        "object_story_link_data_child_attachments" AS "ad_attachments",
        "object_story_link_data_caption" AS "ad_caption",
        "object_story_link_data_description" AS "ad_description",
        "object_story_link_data_link" AS "ad_link",
        "object_story_link_data_message" AS "ad_message",
        "template_app_link_spec_ios" AS "ios_app_link_specs",
        "_fivetran_id" AS "fivetran_id"
    FROM "facebook_ads_creative_history_data_projected"
),

"facebook_ads_creative_history_data_projected_renamed_casted" AS (
    -- Column Type Casting: 
    -- account_id: from DECIMAL to VARCHAR
    -- ad_attachments: from VARCHAR to JSON
    -- asset_feed_specs: from VARCHAR to JSON
    -- creative_id: from INT to VARCHAR
    -- ios_app_link_specs: from VARCHAR to JSON
    -- template_page_link: from DECIMAL to VARCHAR
    -- utm_parameters: from VARCHAR to JSON
    SELECT
        "landing_page_url",
        "encoded_ad_name",
        "ad_caption",
        "ad_description",
        "ad_link",
        "ad_message",
        "fivetran_id",
        CAST("account_id" AS VARCHAR) 
        AS "account_id",
        CAST("ad_attachments" AS JSON) 
        AS "ad_attachments",
        CAST("asset_feed_specs" AS JSON) 
        AS "asset_feed_specs",
        CAST("creative_id" AS VARCHAR) 
        AS "creative_id",
        CAST("ios_app_link_specs" AS JSON) 
        AS "ios_app_link_specs",
        CAST("template_page_link" AS VARCHAR) 
        AS "template_page_link",
        CAST("utm_parameters" AS JSON) 
        AS "utm_parameters"
    FROM "facebook_ads_creative_history_data_projected_renamed"
)

-- COCOON BLOCK END
SELECT *
FROM "facebook_ads_creative_history_data_projected_renamed_casted"

version: 2
models:
- name: stg_facebook_ads_creative_history_data
  description: The table contains details about Facebook ad creatives. It includes
    information such as page links, ad IDs, account IDs, names, URL tags, asset feed
    specifications, object story link data, and app link specifications. The data
    focuses on Fivetran's advertising campaigns, with details on UTM parameters, call-to-action
    buttons, and ad content descriptions. Each row represents a unique ad creative
    with its associated metadata and tracking information.
  columns:
  - name: landing_page_url
    description: Landing page URL for the ad
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: encoded_ad_name
    description: Encoded name or identifier for the ad
    tests:
    - not_null
    cocoon_meta:
      patterns:
      - regex: ^[A-Za-z0-9+/]{22}==|[A-Za-z0-9+/]{23}=$
        summary: Base64 encoded strings
      data_type:
        current_data_type: VARCHAR
  - name: ad_caption
    description: Caption for the ad link
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: ad_description
    description: Description of the ad content
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: ad_link
    description: Main link for the ad
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: ad_message
    description: Main message or copy for the ad
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: fivetran_id
    description: Fivetran's internal identifier
    tests:
    - not_null
    - unique
    cocoon_meta:
      unique_reason: This column is Fivetran's internal identifier. For this table,
        each row represents a unique ad creative. The fivetran_id appears to be unique
        across rows in the sample data, and its description suggests it's intended
        to be a unique identifier.
      patterns:
      - regex: ^[A-Za-z0-9+/]+={0,2}$
        summary: Base64-encoded strings
      data_type:
        current_data_type: VARCHAR
  - name: account_id
    description: Facebook account ID (empty in this data)
    cocoon_meta:
      missing_reason: Unknown
      data_type:
        current_data_type: VARCHAR
  - name: ad_attachments
    description: Details of ad attachments and call-to-action
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: JSON
  - name: asset_feed_specs
    description: Specifications for asset feed links
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: JSON
  - name: creative_id
    description: Unique identifier for the ad creative
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: ios_app_link_specs
    description: iOS app link specifications
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: JSON
  - name: template_page_link
    description: Template page link (empty in this data)
    cocoon_meta:
      missing_reason: Unknown
      data_type:
        current_data_type: VARCHAR
  - name: utm_parameters
    description: UTM parameters for tracking
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: JSON
  tests: []

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
-- Generated at 2024-08-25 15:53:35.459415+00:00
WITH 
"facebook_ads_ad_history_data_projected" AS (
    -- Projection: Selecting 7 out of 8 columns
    -- Columns projected out: ['id', 'account_id', 'ad_set_id', 'campaign_id', 'creative_id', 'name', '_fivetran_synced', 'updated_time']
    SELECT 
        "id",
        "account_id",
        "ad_set_id",
        "campaign_id",
        "creative_id",
        "name",
        "updated_time"
    FROM "memory"."main"."facebook_ads_ad_history_data"
),

"facebook_ads_ad_history_data_projected_renamed" AS (
    -- Rename: Renaming columns
    -- id -> ad_id
    -- name -> ad_name
    -- updated_time -> last_updated
    SELECT 
        "id" AS "ad_id",
        "account_id",
        "ad_set_id",
        "campaign_id",
        "creative_id",
        "name" AS "ad_name",
        "updated_time" AS "last_updated"
    FROM "facebook_ads_ad_history_data_projected"
),

"facebook_ads_ad_history_data_projected_renamed_casted" AS (
    -- Column Type Casting: 
    -- account_id: from INT to VARCHAR
    -- ad_id: from INT to VARCHAR
    -- ad_set_id: from INT to VARCHAR
    -- campaign_id: from INT to VARCHAR
    -- creative_id: from INT to VARCHAR
    -- last_updated: from VARCHAR to TIMESTAMP
    SELECT
        "ad_name",
        CAST("account_id" AS VARCHAR) 
        AS "account_id",
        CAST("ad_id" AS VARCHAR) 
        AS "ad_id",
        CAST("ad_set_id" AS VARCHAR) 
        AS "ad_set_id",
        CAST("campaign_id" AS VARCHAR) 
        AS "campaign_id",
        CAST("creative_id" AS VARCHAR) 
        AS "creative_id",
        CAST("last_updated" AS TIMESTAMP) 
        AS "last_updated"
    FROM "facebook_ads_ad_history_data_projected_renamed"
)

-- COCOON BLOCK END
SELECT *
FROM "facebook_ads_ad_history_data_projected_renamed_casted"

version: 2
models:
- name: stg_facebook_ads_ad_history_data
  description: The table is about Facebook ad history data. It contains details of
    individual ads, including their unique identifiers (id), associated account, ad
    set, campaign, and creative IDs. Each entry also includes the ad name and the
    timestamp of when it was last updated. The table shows multiple entries for the
    same ad ID, suggesting it tracks changes or updates to ads over time.
  columns:
  - name: ad_name
    description: Name of the ad
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: account_id
    description: Facebook account identifier
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: ad_id
    description: Unique identifier for the ad
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: ad_set_id
    description: Identifier for the ad set
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: campaign_id
    description: Identifier for the campaign
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: creative_id
    description: Identifier for the ad creative
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: last_updated
    description: Timestamp of the last update
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: TIMESTAMP
  tests: []

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
-- Generated at 2024-08-25 15:56:52.569745+00:00
WITH 
"facebook_ads_campaign_history_data_projected" AS (
    -- Projection: Selecting 11 out of 12 columns
    -- Columns projected out: ['id', 'account_id', 'name', 'created_time', 'start_time', 'stop_time', 'status', 'daily_budget', 'lifetime_budget', 'budget_remaining', '_fivetran_synced', 'updated_time']
    SELECT 
        "id",
        "account_id",
        "name",
        "created_time",
        "start_time",
        "stop_time",
        "status",
        "daily_budget",
        "lifetime_budget",
        "budget_remaining",
        "updated_time"
    FROM "memory"."main"."facebook_ads_campaign_history_data"
),

"facebook_ads_campaign_history_data_projected_renamed" AS (
    -- Rename: Renaming columns
    -- id -> campaign_id
    -- name -> campaign_name
    -- created_time -> creation_timestamp
    -- start_time -> start_timestamp
    -- stop_time -> end_timestamp
    -- status -> campaign_status
    -- daily_budget -> daily_budget_cents
    -- lifetime_budget -> lifetime_budget_cents
    -- budget_remaining -> remaining_budget_cents
    -- updated_time -> last_update_timestamp
    SELECT 
        "id" AS "campaign_id",
        "account_id",
        "name" AS "campaign_name",
        "created_time" AS "creation_timestamp",
        "start_time" AS "start_timestamp",
        "stop_time" AS "end_timestamp",
        "status" AS "campaign_status",
        "daily_budget" AS "daily_budget_cents",
        "lifetime_budget" AS "lifetime_budget_cents",
        "budget_remaining" AS "remaining_budget_cents",
        "updated_time" AS "last_update_timestamp"
    FROM "facebook_ads_campaign_history_data_projected"
),

"facebook_ads_campaign_history_data_projected_renamed_casted" AS (
    -- Column Type Casting: 
    -- account_id: from INT to VARCHAR
    -- campaign_id: from INT to VARCHAR
    -- creation_timestamp: from VARCHAR to TIMESTAMP
    -- end_timestamp: from VARCHAR to TIMESTAMP
    -- last_update_timestamp: from VARCHAR to TIMESTAMP
    -- start_timestamp: from VARCHAR to TIMESTAMP
    SELECT
        "campaign_name",
        "campaign_status",
        "daily_budget_cents",
        "lifetime_budget_cents",
        "remaining_budget_cents",
        CAST("account_id" AS VARCHAR) 
        AS "account_id",
        CAST("campaign_id" AS VARCHAR) 
        AS "campaign_id",
        CAST("creation_timestamp" AS TIMESTAMP) 
        AS "creation_timestamp",
        CAST("end_timestamp" AS TIMESTAMP) 
        AS "end_timestamp",
        CAST("last_update_timestamp" AS TIMESTAMP) 
        AS "last_update_timestamp",
        CAST("start_timestamp" AS TIMESTAMP) 
        AS "start_timestamp"
    FROM "facebook_ads_campaign_history_data_projected_renamed"
)

-- COCOON BLOCK END
SELECT *
FROM "facebook_ads_campaign_history_data_projected_renamed_casted"

version: 2
models:
- name: stg_facebook_ads_campaign_history_data
  description: The table is about Facebook ad campaigns. It contains historical data
    for each campaign, including its ID, account ID, name, creation time, start and
    stop times, status, budget details, and last update time. Each row represents
    a snapshot of a campaign at a specific point in time, allowing tracking of changes
    in budget and status over the campaign's lifetime.
  columns:
  - name: campaign_name
    description: Encrypted name of the campaign
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: campaign_status
    description: Current status of the campaign (e.g., ACTIVE, PAUSED)
    tests:
    - not_null
    - accepted_values:
        values:
        - ACTIVE
        - DELETED
        - PAUSED
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: daily_budget_cents
    description: Maximum daily spend limit in cents
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: INT
  - name: lifetime_budget_cents
    description: Total budget for the campaign's lifetime in cents
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: INT
  - name: remaining_budget_cents
    description: Remaining budget for the campaign in cents
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: INT
  - name: account_id
    description: Facebook account identifier associated with the campaign
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: campaign_id
    description: Unique identifier for the campaign
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: creation_timestamp
    description: Timestamp when the campaign was created
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: TIMESTAMP
  - name: end_timestamp
    description: Scheduled end time of the campaign
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: TIMESTAMP
  - name: last_update_timestamp
    description: Timestamp of the last update to the campaign
    tests:
    - not_null
    - unique
    cocoon_meta:
      unique_reason: This column represents the timestamp of the last update to the
        campaign. For this table, each row represents a snapshot of a campaign at
        a specific point in time. The last_update_timestamp is unique across rows
        as it captures the exact moment when the campaign data was updated.
      data_type:
        current_data_type: TIMESTAMP
  - name: start_timestamp
    description: Scheduled start time of the campaign
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: TIMESTAMP
  tests: []

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
-- Generated at 2024-08-25 15:55:39.612740+00:00
WITH 
"facebook_ads_basic_ad_data_renamed" AS (
    -- Rename: Renaming columns
    -- date_ -> performance_date
    -- inline_link_clicks -> link_clicks
    -- spend -> ad_spend
    -- reach -> unique_reach
    -- frequency -> average_frequency
    SELECT 
        "ad_id",
        "ad_name",
        "adset_name",
        "date_" AS "performance_date",
        "account_id",
        "impressions",
        "inline_link_clicks" AS "link_clicks",
        "spend" AS "ad_spend",
        "reach" AS "unique_reach",
        "frequency" AS "average_frequency"
    FROM "memory"."main"."facebook_ads_basic_ad_data"
),

"facebook_ads_basic_ad_data_renamed_casted" AS (
    -- Column Type Casting: 
    -- account_id: from INT to VARCHAR
    -- ad_id: from INT to VARCHAR
    -- performance_date: from VARCHAR to DATE
    SELECT
        "ad_name",
        "adset_name",
        "impressions",
        "link_clicks",
        "ad_spend",
        "unique_reach",
        "average_frequency",
        CAST("account_id" AS VARCHAR) 
        AS "account_id",
        CAST("ad_id" AS VARCHAR) 
        AS "ad_id",
        CAST("performance_date" AS DATE) 
        AS "performance_date"
    FROM "facebook_ads_basic_ad_data_renamed"
)

-- COCOON BLOCK END
SELECT *
FROM "facebook_ads_basic_ad_data_renamed_casted"

version: 2
models:
- name: stg_facebook_ads_basic_ad_data
  description: The table is about Facebook ad performance metrics. It contains data
    for individual ads, including ad ID, name, adset name, date, account ID, impressions,
    clicks, spend, reach, and frequency. Each row represents a daily performance record
    for a specific ad. The table provides insights into ad effectiveness, engagement,
    and cost across different adsets and dates.
  columns:
  - name: ad_name
    description: Name or version of the advertisement
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: adset_name
    description: Name of the ad set
    tests:
    - not_null
    - accepted_values:
        values:
        - Adset A
        - Adset B
        - Adset C
        - Adset D
        - Adset E
        - Adset F
        - Adset G
        - Adset H
        - Adset I
        - Adset J
    cocoon_meta:
      future_accepted_values:
      - Adset K
      - Adset L
      - Adset M
      - Adset N
      - Adset O
      - Adset P
      - Adset Q
      - Adset R
      - Adset S
      - Adset T
      - Adset U
      - Adset V
      - Adset W
      - Adset X
      - Adset Y
      - Adset Z
      data_type:
        current_data_type: VARCHAR
  - name: impressions
    description: Number of times the ad was displayed
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: INT
  - name: link_clicks
    description: Number of clicks on links within the ad
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: INT
  - name: ad_spend
    description: Amount spent on the ad in currency units
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: DECIMAL
  - name: unique_reach
    description: Number of unique users who saw the ad
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: INT
  - name: average_frequency
    description: Average number of times each user saw the ad
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: DECIMAL
  - name: account_id
    description: Unique identifier for the advertising account
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: ad_id
    description: Unique identifier for the advertisement
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: performance_date
    description: Date of the performance record
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: DATE
  tests: []

-- Slowly Changing Dimension: Dimension keys are "ad_id"
-- Version columns are last_updated
-- We will create Type 1 SCD (latest snapshot)

SELECT 
    "ad_name",
    "account_id",
    "ad_id",
    "ad_set_id",
    "campaign_id",
    "creative_id"
FROM "stg_facebook_ads_ad_history_data"
QUALIFY ROW_NUMBER() OVER ( 
    PARTITION BY "ad_id"
    ORDER BY
        last_updated DESC
) = 1

version: 2
models:
- name: snapshot_facebook_ads_ad_history_data
  description: The table is about Facebook ad data. It tracks the most recent version
    of each unique ad. It includes details such as ad name, account ID, ad ID, ad
    set ID, campaign ID, and creative ID. Each row represents the latest state of
    a distinct ad. The table provides a current snapshot of Facebook ads, omitting
    historical changes and update timestamps.
  columns:
  - name: ad_name
    description: Name of the ad
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: account_id
    description: Facebook account identifier
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: ad_id
    description: Unique identifier for the ad
    tests:
    - not_null
    - unique
    cocoon_meta:
      unique_reason: Unique dimension key, derived from the slowly changing dimension
      data_type:
        current_data_type: VARCHAR
  - name: ad_set_id
    description: Identifier for the ad set
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: campaign_id
    description: Identifier for the campaign
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: creative_id
    description: Identifier for the ad creative
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  tests: []
cocoon_meta:
  scd_base_table: stg_facebook_ads_ad_history_data
  scd_columns:
  - last_updated

-- Slowly Changing Dimension: Dimension keys are "campaign_id"
-- Version columns are last_update_timestamp, remaining_budget_cents
-- We will create Type 1 SCD (latest snapshot)

SELECT 
    "campaign_name",
    "campaign_status",
    "daily_budget_cents",
    "lifetime_budget_cents",
    "account_id",
    "campaign_id",
    "creation_timestamp",
    "end_timestamp",
    "start_timestamp"
FROM "stg_facebook_ads_campaign_history_data"
QUALIFY ROW_NUMBER() OVER ( 
    PARTITION BY "campaign_id"
    ORDER BY
        last_update_timestamp DESC,
        remaining_budget_cents DESC
) = 1

version: 2
models:
- name: snapshot_facebook_ads_campaign_history_data
  description: The table is about current Facebook ad campaigns. It tracks the most
    recent version of each campaign, including its ID, account ID, name, status, budget
    details, creation time, and start and end dates. Each row represents a unique
    campaign with its latest information. The table excludes historical data and version
    tracking columns, focusing on the current state of active and paused campaigns.
  columns:
  - name: campaign_name
    description: Encrypted name of the campaign
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: campaign_status
    description: Current status of the campaign (e.g., ACTIVE, PAUSED)
    tests:
    - not_null
    - accepted_values:
        values:
        - ACTIVE
        - DELETED
        - PAUSED
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: daily_budget_cents
    description: Maximum daily spend limit in cents
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: INT
  - name: lifetime_budget_cents
    description: Total budget for the campaign's lifetime in cents
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: INT
  - name: account_id
    description: Facebook account identifier associated with the campaign
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: campaign_id
    description: Unique identifier for the campaign
    tests:
    - not_null
    - unique
    cocoon_meta:
      unique_reason: Unique dimension key, derived from the slowly changing dimension
      data_type:
        current_data_type: VARCHAR
  - name: creation_timestamp
    description: Timestamp when the campaign was created
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: TIMESTAMP
  - name: end_timestamp
    description: Scheduled end time of the campaign
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: TIMESTAMP
  - name: start_timestamp
    description: Scheduled start time of the campaign
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: TIMESTAMP
  tests: []
cocoon_meta:
  scd_base_table: stg_facebook_ads_campaign_history_data
  scd_columns:
  - last_update_timestamp
  - remaining_budget_cents

-- Slowly Changing Dimension: Dimension keys are "account_id"
-- Version columns are creation_timestamp
-- We will create Type 1 SCD (latest snapshot)

SELECT 
    "encrypted_account_name",
    "account_status",
    "country_code",
    "account_currency",
    "account_timezone",
    "account_id"
FROM "stg_facebook_ads_account_history_data"
QUALIFY ROW_NUMBER() OVER ( 
    PARTITION BY "account_id"
    ORDER BY
        creation_timestamp DESC
) = 1

version: 2
models:
- name: snapshot_facebook_ads_account_history_data
  description: The table is about Facebook ad accounts. It contains the most recent
    details for each unique account, including ID, encrypted name, status, country,
    currency, and timezone. Each row represents the latest state of a distinct ad
    account. The table provides a current snapshot of Facebook ad account information,
    omitting historical changes and timestamp data.
  columns:
  - name: encrypted_account_name
    description: Encrypted name of the ad account
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: account_status
    description: Current status of the ad account
    tests:
    - not_null
    - accepted_values:
        values:
        - Active
        - Paused
    cocoon_meta:
      future_accepted_values:
      - Disabled
      - Pending
      - Closed
      data_type:
        current_data_type: VARCHAR
  - name: country_code
    description: Two-letter country code of the business
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: account_currency
    description: Currency used for the ad account
    tests:
    - not_null
    - accepted_values:
        values:
        - USD
        - GBP
        - JPY
    cocoon_meta:
      future_accepted_values:
      - EUR
      - CAD
      - AUD
      - CHF
      - CNY
      - HKD
      - NZD
      - SEK
      - KRW
      - SGD
      - NOK
      - MXN
      - INR
      - RUB
      - ZAR
      - TRY
      - BRL
      - TWD
      - DKK
      - PLN
      - THB
      - IDR
      data_type:
        current_data_type: VARCHAR
  - name: account_timezone
    description: Timezone associated with the ad account
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: account_id
    description: Unique identifier for the ad account
    tests:
    - not_null
    - unique
    cocoon_meta:
      unique_reason: Unique dimension key, derived from the slowly changing dimension
      data_type:
        current_data_type: VARCHAR
  tests: []
cocoon_meta:
  scd_base_table: stg_facebook_ads_account_history_data
  scd_columns:
  - creation_timestamp