dbt Amazon Ads
1. model.amazon_ads.amazon_ads__account_report
This SQL query combines data from three sources: campaign level reports, profile information, and campaign history. It filters for non-deleted profiles and the most recent campaign records. The query then joins these tables to create a summarized account-level report, aggregating metrics like cost, clicks, and impressions by account, date, and location. The result provides a daily overview of advertising performance for each Amazon account.
FilteringIntegrationAggregation
SQL Query
with report as (
select *
--use campaign report since account report not provided
from TEST.PUBLIC_amazon_ads_source.stg_amazon_ads__campaign_level_report
),
account_info as (
select *
from TEST.PUBLIC_amazon_ads_source.stg_amazon_ads__profile
where _fivetran_deleted = False
),
campaigns as (
select *
from TEST.PUBLIC_amazon_ads_source.stg_amazon_ads__campaign_history
where is_most_recent_record = True
),
fields as (
select
report.source_relation,
report.date_day,
account_info.account_name,
account_info.account_id,
account_info.country_code,
account_info.profile_id,
sum(report.cost) as cost,
sum(report.clicks) as clicks,
sum(report.impressions) as impressions
--use campaign report since portfolio report not provided
from report
left join campaigns
on campaigns.campaign_id = report.campaign_id
and campaigns.source_relation = report.source_relation
left join account_info
on account_info.profile_id = campaigns.profile_id
and account_info.source_relation = campaigns.source_relation
group by 1,2,3,4,5,6
)
select *
from fields
| Name | Type | Comment |
|---|---|---|
| date_day | date | None |
| account_name | text | None |
| account_id | text | None |
| country_code | text | None |
| profile_id | text | None |
| cost | double precision | None |
| clicks | bigint | None |
| impressions | bigint | None |
Column Lineage
17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report
-
Input Column: 18. model.amazon_ads_source.stg_amazon_ads__campaign_level_report_tmp[date]
- Cleaning: The column is renamed to date_day in the final output Output Columns:
- date_day: Directly mapped and renamed
1. model.amazon_ads.amazon_ads__account_report
-
Input Column: 17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report[date_day]
- Direct: Copied without transformation
- Aggregation: Used in the GROUP BY clause Output Columns:
- date_day: Directly copied
Column Lineage
25. model.amazon_ads_source.stg_amazon_ads__profile
-
Input Column: 26. model.amazon_ads_source.stg_amazon_ads__profile_tmp[account_name]
- Direct: The column is directly copied without transformation Output Columns:
- account_name: Directly copied
1. model.amazon_ads.amazon_ads__account_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[account_name]
- Direct: Directly copied to the output Output Columns:
- account_name: Directly copied
Column Lineage
25. model.amazon_ads_source.stg_amazon_ads__profile
-
Input Column: 26. model.amazon_ads_source.stg_amazon_ads__profile_tmp[account_id]
- Cleaning: The column is cast to TEXT Output Columns:
- account_id: Directly mapped after casting to TEXT
1. model.amazon_ads.amazon_ads__account_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[account_id]
- Direct: Directly copied to the output Output Columns:
- account_id: Directly copied
Column Lineage
25. model.amazon_ads_source.stg_amazon_ads__profile
-
Input Column: 26. model.amazon_ads_source.stg_amazon_ads__profile_tmp[country_code]
- Direct: The column is directly copied without transformation Output Columns:
- country_code: Directly copied
1. model.amazon_ads.amazon_ads__account_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[country_code]
- Direct: Directly copied to the output Output Columns:
- country_code: Directly copied
Column Lineage
15. model.amazon_ads_source.stg_amazon_ads__campaign_history
-
Input Column: 16. model.amazon_ads_source.stg_amazon_ads__campaign_history_tmp[profile_id]
- Cleaning: Cast to TEXT data type Output Columns:
- profile_id: Directly mapped after casting to TEXT
25. model.amazon_ads_source.stg_amazon_ads__profile
-
Input Column: 26. model.amazon_ads_source.stg_amazon_ads__profile_tmp[id]
- Cleaning: The column is cast to TEXT and renamed to profile_id Output Columns:
- profile_id: Directly mapped after casting to TEXT
1. model.amazon_ads.amazon_ads__account_report
-
Input Column: 15. model.amazon_ads_source.stg_amazon_ads__campaign_history[profile_id]
- Integration: used as a key to join with the account_info table Output Columns:
- profile_id: Directly copied to the output
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[profile_id]
- Integration: Used as a key to join with other tables
- Direct: Directly copied to the output Output Columns:
- profile_id: Directly copied
Column Lineage
17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report
-
Input Column: 18. model.amazon_ads_source.stg_amazon_ads__campaign_level_report_tmp[cost]
- Direct: The column is directly copied without transformation Output Columns:
- cost: Directly mapped
1. model.amazon_ads.amazon_ads__account_report
-
Input Column: 17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report[cost]
- Aggregation: Summed up in the fields CTE Output Columns:
- cost: Directly mapped after aggregation
Column Lineage
17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report
-
Input Column: 18. model.amazon_ads_source.stg_amazon_ads__campaign_level_report_tmp[clicks]
- Direct: The column is directly copied without transformation Output Columns:
- clicks: Directly mapped
1. model.amazon_ads.amazon_ads__account_report
-
Input Column: 17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report[clicks]
- Aggregation: Summed up in the fields CTE Output Columns:
- clicks: Directly mapped after aggregation
Column Lineage
17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report
-
Input Column: 18. model.amazon_ads_source.stg_amazon_ads__campaign_level_report_tmp[impressions]
- Direct: The column is directly copied without transformation Output Columns:
- impressions: Directly mapped
1. model.amazon_ads.amazon_ads__account_report
-
Input Column: 17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report[impressions]
- Aggregation: Summed up in the fields CTE Output Columns:
- impressions: Directly mapped after aggregation
2. model.amazon_ads.amazon_ads__ad_group_report
This query integrates data from multiple Amazon Ads-related tables, including ad group reports, account information, portfolios, campaigns, and ad groups. It joins these tables to create a comprehensive view of ad performance metrics (cost, clicks, impressions) at the ad group level, including associated account, portfolio, and campaign details. The query filters for the most recent records in some tables and aggregates the performance metrics.
FilteringIntegrationAggregation
SQL Query
with report as (
select *
from TEST.PUBLIC_amazon_ads_source.stg_amazon_ads__ad_group_level_report
),
account_info as (
select *
from TEST.PUBLIC_amazon_ads_source.stg_amazon_ads__profile
where _fivetran_deleted = False
),
portfolios as (
select *
from TEST.PUBLIC_amazon_ads.int_amazon_ads__portfolio_history
),
campaigns as (
select *
from TEST.PUBLIC_amazon_ads_source.stg_amazon_ads__campaign_history
where is_most_recent_record = True
),
ad_groups as (
select *
from TEST.PUBLIC_amazon_ads_source.stg_amazon_ads__ad_group_history
where is_most_recent_record = True
),
fields as (
select
report.source_relation,
report.date_day,
account_info.account_name,
account_info.account_id,
account_info.country_code,
account_info.profile_id,
portfolios.portfolio_name,
portfolios.portfolio_id,
campaigns.campaign_name,
campaigns.campaign_id,
ad_groups.ad_group_name,
report.ad_group_id,
ad_groups.serving_status,
ad_groups.state,
ad_groups.default_bid,
report.campaign_bidding_strategy,
sum(report.cost) as cost,
sum(report.clicks) as clicks,
sum(report.impressions) as impressions
from report
left join ad_groups
on ad_groups.ad_group_id = report.ad_group_id
and ad_groups.source_relation = report.source_relation
left join campaigns
on campaigns.campaign_id = ad_groups.campaign_id
and campaigns.source_relation = ad_groups.source_relation
left join portfolios
on portfolios.portfolio_id = campaigns.portfolio_id
and portfolios.source_relation = campaigns.source_relation
left join account_info
on account_info.profile_id = campaigns.profile_id
and account_info.source_relation = campaigns.source_relation
group by 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16
)
select *
from fields
| Name | Type | Comment |
|---|---|---|
| date_day | date | None |
| account_name | text | None |
| account_id | text | None |
| country_code | text | None |
| profile_id | text | None |
| portfolio_name | text | None |
| portfolio_id | text | None |
| campaign_name | text | None |
| campaign_id | text | None |
| ad_group_name | text | None |
| ad_group_id | text | None |
| serving_status | text | None |
| state | text | None |
| default_bid | double precision | None |
| campaign_bidding_strategy | text | None |
| cost | double precision | None |
| clicks | bigint | None |
| impressions | bigint | None |
Column Lineage
11. model.amazon_ads_source.stg_amazon_ads__ad_group_level_report
-
Input Column: 12. model.amazon_ads_source.stg_amazon_ads__ad_group_level_report_tmp[date]
- Cleaning: The column is cast to DATE type and renamed Output Columns:
- date_day: Directly mapped after casting to DATE
2. model.amazon_ads.amazon_ads__ad_group_report
-
Input Column: 11. model.amazon_ads_source.stg_amazon_ads__ad_group_level_report[date_day]
- Direct: directly selected in the output without transformation
- Aggregation: used in the GROUP BY clause Output Columns:
- date_day: directly copied
Column Lineage
25. model.amazon_ads_source.stg_amazon_ads__profile
-
Input Column: 26. model.amazon_ads_source.stg_amazon_ads__profile_tmp[account_name]
- Direct: The column is directly copied without transformation Output Columns:
- account_name: Directly copied
2. model.amazon_ads.amazon_ads__ad_group_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[account_name]
- Direct: Directly copied to the output Output Columns:
- account_name: Directly copied
Column Lineage
25. model.amazon_ads_source.stg_amazon_ads__profile
-
Input Column: 26. model.amazon_ads_source.stg_amazon_ads__profile_tmp[account_id]
- Cleaning: The column is cast to TEXT Output Columns:
- account_id: Directly mapped after casting to TEXT
2. model.amazon_ads.amazon_ads__ad_group_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[account_id]
- Direct: Directly copied to the output Output Columns:
- account_id: Directly copied
Column Lineage
25. model.amazon_ads_source.stg_amazon_ads__profile
-
Input Column: 26. model.amazon_ads_source.stg_amazon_ads__profile_tmp[country_code]
- Direct: The column is directly copied without transformation Output Columns:
- country_code: Directly copied
2. model.amazon_ads.amazon_ads__ad_group_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[country_code]
- Direct: Directly copied to the output Output Columns:
- country_code: Directly copied
Column Lineage
25. model.amazon_ads_source.stg_amazon_ads__profile
-
Input Column: 26. model.amazon_ads_source.stg_amazon_ads__profile_tmp[id]
- Cleaning: The column is cast to TEXT and renamed to profile_id Output Columns:
- profile_id: Directly mapped after casting to TEXT
2. model.amazon_ads.amazon_ads__ad_group_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[profile_id]
- Integration: Used as a key to join with the campaigns table
- Direct: Directly copied to the output Output Columns:
- profile_id: Directly copied
Column Lineage
21. model.amazon_ads_source.stg_amazon_ads__portfolio_history
-
Input Column: 22. model.amazon_ads_source.stg_amazon_ads__portfolio_history_tmp[name]
- Direct: Copied without transformation Output Columns:
- portfolio_name: Directly copied and renamed
8. model.amazon_ads.int_amazon_ads__portfolio_history
-
Input Column: 21. model.amazon_ads_source.stg_amazon_ads__portfolio_history[portfolio_name]
- Direct: This column is directly copied without any transformation Output Columns:
- portfolio_name: Directly copied
2. model.amazon_ads.amazon_ads__ad_group_report
-
Input Column: 8. model.amazon_ads.int_amazon_ads__portfolio_history[portfolio_name]
- Direct: directly copied to the output Output Columns:
- portfolio_name: directly copied
Column Lineage
21. model.amazon_ads_source.stg_amazon_ads__portfolio_history
-
Input Column: 22. model.amazon_ads_source.stg_amazon_ads__portfolio_history_tmp[id]
- Cleaning: Cast to TEXT data type Output Columns:
- portfolio_id: Directly mapped after casting
8. model.amazon_ads.int_amazon_ads__portfolio_history
-
Input Column: 21. model.amazon_ads_source.stg_amazon_ads__portfolio_history[portfolio_id]
- Direct: This column is directly copied without any transformation Output Columns:
- portfolio_id: Directly copied
2. model.amazon_ads.amazon_ads__ad_group_report
-
Input Column: 8. model.amazon_ads.int_amazon_ads__portfolio_history[portfolio_id]
- Integration: used as a key to join with the campaigns table
- Direct: directly copied to the output Output Columns:
- portfolio_id: directly copied
Column Lineage
15. model.amazon_ads_source.stg_amazon_ads__campaign_history
-
Input Column: 16. model.amazon_ads_source.stg_amazon_ads__campaign_history_tmp[name]
- Direct: Copied without transformation Output Columns:
- campaign_name: Directly copied and renamed
2. model.amazon_ads.amazon_ads__ad_group_report
-
Input Column: 15. model.amazon_ads_source.stg_amazon_ads__campaign_history[campaign_name]
- Direct: The column is directly copied without any transformation Output Columns:
- campaign_name: Directly copied
Column Lineage
15. model.amazon_ads_source.stg_amazon_ads__campaign_history
-
Input Column: 16. model.amazon_ads_source.stg_amazon_ads__campaign_history_tmp[id]
- Cleaning: Cast to TEXT data type
- Deduplication: Used in the window function to identify the most recent record Output Columns:
- campaign_id: Directly mapped after casting to TEXT
2. model.amazon_ads.amazon_ads__ad_group_report
-
Input Column: 15. model.amazon_ads_source.stg_amazon_ads__campaign_history[campaign_id]
- Direct: The column is directly copied without any transformation
- Integration: The column is used as a key to join with ad_groups table Output Columns:
- campaign_id: Directly copied
Column Lineage
9. model.amazon_ads_source.stg_amazon_ads__ad_group_history
-
Input Column: 10. model.amazon_ads_source.stg_amazon_ads__ad_group_history_tmp[name]
- Cleaning: cast to TEXT data type Output Columns:
- ad_group_name: directly copied after casting and renaming
2. model.amazon_ads.amazon_ads__ad_group_report
-
Input Column: 9. model.amazon_ads_source.stg_amazon_ads__ad_group_history[ad_group_name]
- Direct: directly copied to the output Output Columns:
- ad_group_name: directly copied
Column Lineage
9. model.amazon_ads_source.stg_amazon_ads__ad_group_history
-
Input Column: 10. model.amazon_ads_source.stg_amazon_ads__ad_group_history_tmp[id]
- Cleaning: cast to TEXT data type Output Columns:
- ad_group_id: directly copied after casting
11. model.amazon_ads_source.stg_amazon_ads__ad_group_level_report
-
Input Column: 12. model.amazon_ads_source.stg_amazon_ads__ad_group_level_report_tmp[ad_group_id]
- Cleaning: The column is cast to TEXT in the final selection Output Columns:
- ad_group_id: Directly mapped after casting to TEXT
2. model.amazon_ads.amazon_ads__ad_group_report
-
Input Column: 9. model.amazon_ads_source.stg_amazon_ads__ad_group_history[ad_group_id]
- Integration: used as a key to join with the report table
- Direct: directly copied to the output Output Columns:
- ad_group_id: directly copied
-
Input Column: 11. model.amazon_ads_source.stg_amazon_ads__ad_group_level_report[ad_group_id]
- Integration: used as a key to join with the ad_groups table
- Aggregation: used in the GROUP BY clause Output Columns:
- ad_group_id: directly copied
Column Lineage
9. model.amazon_ads_source.stg_amazon_ads__ad_group_history
-
Input Column: 10. model.amazon_ads_source.stg_amazon_ads__ad_group_history_tmp[serving_status]
- Cleaning: cast to TEXT data type Output Columns:
- serving_status: directly copied after casting
2. model.amazon_ads.amazon_ads__ad_group_report
-
Input Column: 9. model.amazon_ads_source.stg_amazon_ads__ad_group_history[serving_status]
- Direct: directly copied to the output Output Columns:
- serving_status: directly copied
Column Lineage
9. model.amazon_ads_source.stg_amazon_ads__ad_group_history
-
Input Column: 10. model.amazon_ads_source.stg_amazon_ads__ad_group_history_tmp[state]
- Cleaning: cast to TEXT data type Output Columns:
- state: directly copied after casting
2. model.amazon_ads.amazon_ads__ad_group_report
-
Input Column: 9. model.amazon_ads_source.stg_amazon_ads__ad_group_history[state]
- Direct: directly copied to the output Output Columns:
- state: directly copied
Column Lineage
9. model.amazon_ads_source.stg_amazon_ads__ad_group_history
-
Input Column: 10. model.amazon_ads_source.stg_amazon_ads__ad_group_history_tmp[default_bid]
- Cleaning: cast to float data type Output Columns:
- default_bid: directly copied after casting
2. model.amazon_ads.amazon_ads__ad_group_report
-
Input Column: 9. model.amazon_ads_source.stg_amazon_ads__ad_group_history[default_bid]
- Direct: directly copied to the output Output Columns:
- default_bid: directly copied
Column Lineage
11. model.amazon_ads_source.stg_amazon_ads__ad_group_level_report
-
Input Column: 12. model.amazon_ads_source.stg_amazon_ads__ad_group_level_report_tmp[campaign_bidding_strategy]
- Cleaning: The column is cast to TEXT Output Columns:
- campaign_bidding_strategy: Directly mapped after casting to TEXT
2. model.amazon_ads.amazon_ads__ad_group_report
-
Input Column: 11. model.amazon_ads_source.stg_amazon_ads__ad_group_level_report[campaign_bidding_strategy]
- Direct: directly selected in the output without transformation
- Aggregation: used in the GROUP BY clause Output Columns:
- campaign_bidding_strategy: directly copied
Column Lineage
11. model.amazon_ads_source.stg_amazon_ads__ad_group_level_report
-
Input Column: 12. model.amazon_ads_source.stg_amazon_ads__ad_group_level_report_tmp[cost]
- Cleaning: The column is cast to FLOAT Output Columns:
- cost: Directly mapped after casting to FLOAT
2. model.amazon_ads.amazon_ads__ad_group_report
-
Input Column: 11. model.amazon_ads_source.stg_amazon_ads__ad_group_level_report[cost]
- Aggregation: summed up in the SELECT clause Output Columns:
- cost: aggregated sum
Column Lineage
11. model.amazon_ads_source.stg_amazon_ads__ad_group_level_report
-
Input Column: 12. model.amazon_ads_source.stg_amazon_ads__ad_group_level_report_tmp[clicks]
- Cleaning: The column is cast to INTEGER Output Columns:
- clicks: Directly mapped after casting to INTEGER
2. model.amazon_ads.amazon_ads__ad_group_report
-
Input Column: 11. model.amazon_ads_source.stg_amazon_ads__ad_group_level_report[clicks]
- Aggregation: summed up in the SELECT clause Output Columns:
- clicks: aggregated sum
Column Lineage
11. model.amazon_ads_source.stg_amazon_ads__ad_group_level_report
-
Input Column: 12. model.amazon_ads_source.stg_amazon_ads__ad_group_level_report_tmp[impressions]
- Cleaning: The column is cast to INTEGER Output Columns:
- impressions: Directly mapped after casting to INTEGER
2. model.amazon_ads.amazon_ads__ad_group_report
-
Input Column: 11. model.amazon_ads_source.stg_amazon_ads__ad_group_level_report[impressions]
- Aggregation: summed up in the SELECT clause Output Columns:
- impressions: aggregated sum
3. model.amazon_ads.amazon_ads__ad_report
This SQL query integrates data from multiple Amazon Ads-related tables to create a comprehensive ad report. It joins information from advertised product reports, account profiles, portfolios, campaigns, ad groups, and product ads. The query filters for the most recent records in some tables, aggregates cost, clicks, and impressions data, and combines various attributes such as account details, campaign information, and ad specifics into a single result set.
FilteringIntegrationAggregation
SQL Query
with report as (
select *
from TEST.PUBLIC_amazon_ads_source.stg_amazon_ads__advertised_product_report
),
account_info as (
select *
from TEST.PUBLIC_amazon_ads_source.stg_amazon_ads__profile
where _fivetran_deleted = False
),
portfolios as (
select *
from TEST.PUBLIC_amazon_ads.int_amazon_ads__portfolio_history
),
campaigns as (
select *
from TEST.PUBLIC_amazon_ads_source.stg_amazon_ads__campaign_history
where is_most_recent_record = True
),
ad_groups as (
select *
from TEST.PUBLIC_amazon_ads_source.stg_amazon_ads__ad_group_history
where is_most_recent_record = True
),
ads as (
select *
from TEST.PUBLIC_amazon_ads_source.stg_amazon_ads__product_ad_history
where is_most_recent_record = True
),
fields as (
select
report.source_relation,
report.date_day,
account_info.account_name,
account_info.account_id,
account_info.country_code,
account_info.profile_id,
portfolios.portfolio_name,
portfolios.portfolio_id,
campaigns.campaign_name,
report.campaign_id,
ad_groups.ad_group_name,
report.ad_group_id,
report.ad_id,
ads.serving_status,
ads.state,
report.advertised_asin,
report.advertised_sku,
report.campaign_budget_amount,
report.campaign_budget_currency_code,
report.campaign_budget_type,
sum(report.cost) as cost,
sum(report.clicks) as clicks,
sum(report.impressions) as impressions
from report
left join ads
on ads.ad_id = report.ad_id
and ads.source_relation = report.source_relation
left join ad_groups
on ad_groups.ad_group_id = report.ad_group_id
and ad_groups.source_relation = report.source_relation
left join campaigns
on campaigns.campaign_id = report.campaign_id
and campaigns.source_relation = report.source_relation
left join portfolios
on portfolios.portfolio_id = campaigns.portfolio_id
and portfolios.source_relation = campaigns.source_relation
left join account_info
on account_info.profile_id = campaigns.profile_id
and account_info.source_relation = campaigns.source_relation
group by 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20
)
select *
from fields
| Name | Type | Comment |
|---|---|---|
| date_day | date | None |
| account_name | text | None |
| account_id | text | None |
| country_code | text | None |
| profile_id | text | None |
| portfolio_name | text | None |
| portfolio_id | text | None |
| campaign_name | text | None |
| campaign_id | text | None |
| ad_group_name | text | None |
| ad_group_id | text | None |
| ad_id | text | None |
| serving_status | text | None |
| state | text | None |
| advertised_asin | integer | None |
| advertised_sku | integer | None |
| campaign_budget_amount | double precision | None |
| campaign_budget_currency_code | text | None |
| campaign_budget_type | text | None |
| cost | double precision | None |
| clicks | bigint | None |
| impressions | bigint | None |
Column Lineage
13. model.amazon_ads_source.stg_amazon_ads__advertised_product_report
-
Input Column: 14. model.amazon_ads_source.stg_amazon_ads__advertised_product_report_tmp[date]
- Cleaning: The column is cast to DATE type Output Columns:
- date_day: Directly mapped after casting
3. model.amazon_ads.amazon_ads__ad_report
-
Input Column: 13. model.amazon_ads_source.stg_amazon_ads__advertised_product_report[date_day]
- Direct: The column is directly copied without any transformation
- Aggregation: Used in the group by clause Output Columns:
- date_day: Directly copied
Column Lineage
25. model.amazon_ads_source.stg_amazon_ads__profile
-
Input Column: 26. model.amazon_ads_source.stg_amazon_ads__profile_tmp[account_name]
- Direct: The column is directly copied without transformation Output Columns:
- account_name: Directly copied
3. model.amazon_ads.amazon_ads__ad_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[account_name]
- Direct: directly copied to the output Output Columns:
- account_name: directly copied
Column Lineage
25. model.amazon_ads_source.stg_amazon_ads__profile
-
Input Column: 26. model.amazon_ads_source.stg_amazon_ads__profile_tmp[account_id]
- Cleaning: The column is cast to TEXT Output Columns:
- account_id: Directly mapped after casting to TEXT
3. model.amazon_ads.amazon_ads__ad_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[account_id]
- Direct: directly copied to the output Output Columns:
- account_id: directly copied
Column Lineage
25. model.amazon_ads_source.stg_amazon_ads__profile
-
Input Column: 26. model.amazon_ads_source.stg_amazon_ads__profile_tmp[country_code]
- Direct: The column is directly copied without transformation Output Columns:
- country_code: Directly copied
3. model.amazon_ads.amazon_ads__ad_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[country_code]
- Direct: directly copied to the output Output Columns:
- country_code: directly copied
Column Lineage
25. model.amazon_ads_source.stg_amazon_ads__profile
-
Input Column: 26. model.amazon_ads_source.stg_amazon_ads__profile_tmp[id]
- Cleaning: The column is cast to TEXT and renamed to profile_id Output Columns:
- profile_id: Directly mapped after casting to TEXT
3. model.amazon_ads.amazon_ads__ad_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[profile_id]
- Integration: used as a key to join with the campaigns table
- Direct: directly copied to the output Output Columns:
- profile_id: directly copied
Column Lineage
21. model.amazon_ads_source.stg_amazon_ads__portfolio_history
-
Input Column: 22. model.amazon_ads_source.stg_amazon_ads__portfolio_history_tmp[name]
- Direct: Copied without transformation Output Columns:
- portfolio_name: Directly copied and renamed
8. model.amazon_ads.int_amazon_ads__portfolio_history
-
Input Column: 21. model.amazon_ads_source.stg_amazon_ads__portfolio_history[portfolio_name]
- Direct: This column is directly copied without any transformation Output Columns:
- portfolio_name: Directly copied
3. model.amazon_ads.amazon_ads__ad_report
-
Input Column: 8. model.amazon_ads.int_amazon_ads__portfolio_history[portfolio_name]
- Direct: The column is directly copied without any transformation Output Columns:
- portfolio_name: Directly copied
Column Lineage
21. model.amazon_ads_source.stg_amazon_ads__portfolio_history
-
Input Column: 22. model.amazon_ads_source.stg_amazon_ads__portfolio_history_tmp[id]
- Cleaning: Cast to TEXT data type Output Columns:
- portfolio_id: Directly mapped after casting
8. model.amazon_ads.int_amazon_ads__portfolio_history
-
Input Column: 21. model.amazon_ads_source.stg_amazon_ads__portfolio_history[portfolio_id]
- Direct: This column is directly copied without any transformation Output Columns:
- portfolio_id: Directly copied
3. model.amazon_ads.amazon_ads__ad_report
-
Input Column: 8. model.amazon_ads.int_amazon_ads__portfolio_history[portfolio_id]
- Direct: The column is directly copied without any transformation
- Integration: The column is used as a key to join with the campaigns table Output Columns:
- portfolio_id: Directly copied
Column Lineage
15. model.amazon_ads_source.stg_amazon_ads__campaign_history
-
Input Column: 16. model.amazon_ads_source.stg_amazon_ads__campaign_history_tmp[name]
- Direct: Copied without transformation Output Columns:
- campaign_name: Directly copied and renamed
3. model.amazon_ads.amazon_ads__ad_report
-
Input Column: 15. model.amazon_ads_source.stg_amazon_ads__campaign_history[campaign_name]
- Direct: directly copied to the output Output Columns:
- campaign_name: directly copied
Column Lineage
13. model.amazon_ads_source.stg_amazon_ads__advertised_product_report
-
Input Column: 14. model.amazon_ads_source.stg_amazon_ads__advertised_product_report_tmp[campaign_id]
- Cleaning: The column is cast to TEXT type Output Columns:
- campaign_id: Directly mapped after casting
15. model.amazon_ads_source.stg_amazon_ads__campaign_history
-
Input Column: 16. model.amazon_ads_source.stg_amazon_ads__campaign_history_tmp[id]
- Cleaning: Cast to TEXT data type
- Deduplication: Used in the window function to identify the most recent record Output Columns:
- campaign_id: Directly mapped after casting to TEXT
3. model.amazon_ads.amazon_ads__ad_report
-
Input Column: 13. model.amazon_ads_source.stg_amazon_ads__advertised_product_report[campaign_id]
- Direct: The column is directly copied without any transformation
- Integration: Used as a key to join with the campaigns table Output Columns:
- campaign_id: Directly copied
-
Input Column: 15. model.amazon_ads_source.stg_amazon_ads__campaign_history[campaign_id]
- Integration: used to join with the report table
- Direct: directly copied to the output Output Columns:
- campaign_id: directly copied
Column Lineage
9. model.amazon_ads_source.stg_amazon_ads__ad_group_history
-
Input Column: 10. model.amazon_ads_source.stg_amazon_ads__ad_group_history_tmp[name]
- Cleaning: cast to TEXT data type Output Columns:
- ad_group_name: directly copied after casting and renaming
3. model.amazon_ads.amazon_ads__ad_report
-
Input Column: 9. model.amazon_ads_source.stg_amazon_ads__ad_group_history[ad_group_name]
- Direct: Directly copied to the output Output Columns:
- ad_group_name: Directly copied
Column Lineage
9. model.amazon_ads_source.stg_amazon_ads__ad_group_history
-
Input Column: 10. model.amazon_ads_source.stg_amazon_ads__ad_group_history_tmp[id]
- Cleaning: cast to TEXT data type Output Columns:
- ad_group_id: directly copied after casting
13. model.amazon_ads_source.stg_amazon_ads__advertised_product_report
-
Input Column: 14. model.amazon_ads_source.stg_amazon_ads__advertised_product_report_tmp[ad_group_id]
- Cleaning: The column is cast to TEXT type Output Columns:
- ad_group_id: Directly mapped after casting
3. model.amazon_ads.amazon_ads__ad_report
-
Input Column: 9. model.amazon_ads_source.stg_amazon_ads__ad_group_history[ad_group_id]
- Integration: Used as a key to join with the report table
- Direct: Directly copied to the output Output Columns:
- ad_group_id: Directly copied
-
Input Column: 13. model.amazon_ads_source.stg_amazon_ads__advertised_product_report[ad_group_id]
- Direct: The column is directly copied without any transformation
- Integration: Used as a key to join with the ad_groups table Output Columns:
- ad_group_id: Directly copied
Column Lineage
13. model.amazon_ads_source.stg_amazon_ads__advertised_product_report
-
Input Column: 14. model.amazon_ads_source.stg_amazon_ads__advertised_product_report_tmp[ad_id]
- Cleaning: The column is cast to TEXT type Output Columns:
- ad_id: Directly mapped after casting
23. model.amazon_ads_source.stg_amazon_ads__product_ad_history
-
Input Column: 24. model.amazon_ads_source.stg_amazon_ads__product_ad_history_tmp[id]
- Cleaning: Cast to TEXT data type
- Deduplication: Used in row_number() function for identifying the most recent record Output Columns:
- ad_id: Directly mapped after casting to TEXT
3. model.amazon_ads.amazon_ads__ad_report
-
Input Column: 13. model.amazon_ads_source.stg_amazon_ads__advertised_product_report[ad_id]
- Direct: The column is directly copied without any transformation
- Integration: Used as a key to join with the ads table Output Columns:
- ad_id: Directly copied
-
Input Column: 23. model.amazon_ads_source.stg_amazon_ads__product_ad_history[ad_id]
- Direct: The column is directly copied without any transformation
- Integration: The column is used as a key to join with the report table Output Columns:
- ad_id: Directly mapped
Column Lineage
23. model.amazon_ads_source.stg_amazon_ads__product_ad_history
-
Input Column: 24. model.amazon_ads_source.stg_amazon_ads__product_ad_history_tmp[serving_status]
- Direct: Copied without transformation Output Columns:
- serving_status: Directly mapped
3. model.amazon_ads.amazon_ads__ad_report
-
Input Column: 23. model.amazon_ads_source.stg_amazon_ads__product_ad_history[serving_status]
- Direct: The column is directly copied without any transformation Output Columns:
- serving_status: Directly mapped
Column Lineage
23. model.amazon_ads_source.stg_amazon_ads__product_ad_history
-
Input Column: 24. model.amazon_ads_source.stg_amazon_ads__product_ad_history_tmp[state]
- Direct: Copied without transformation Output Columns:
- state: Directly mapped
3. model.amazon_ads.amazon_ads__ad_report
-
Input Column: 23. model.amazon_ads_source.stg_amazon_ads__product_ad_history[state]
- Direct: The column is directly copied without any transformation Output Columns:
- state: Directly mapped
Column Lineage
13. model.amazon_ads_source.stg_amazon_ads__advertised_product_report
-
Input Column: 14. model.amazon_ads_source.stg_amazon_ads__advertised_product_report_tmp[advertised_asin]
- Cleaning: The column is cast to TEXT type Output Columns:
- advertised_asin: Directly mapped after casting
3. model.amazon_ads.amazon_ads__ad_report
-
Input Column: 13. model.amazon_ads_source.stg_amazon_ads__advertised_product_report[advertised_asin]
- Direct: The column is directly copied without any transformation Output Columns:
- advertised_asin: Directly copied
Column Lineage
13. model.amazon_ads_source.stg_amazon_ads__advertised_product_report
-
Input Column: 14. model.amazon_ads_source.stg_amazon_ads__advertised_product_report_tmp[advertised_sku]
- Cleaning: The column is cast to TEXT type Output Columns:
- advertised_sku: Directly mapped after casting
3. model.amazon_ads.amazon_ads__ad_report
-
Input Column: 13. model.amazon_ads_source.stg_amazon_ads__advertised_product_report[advertised_sku]
- Direct: The column is directly copied without any transformation Output Columns:
- advertised_sku: Directly copied
Column Lineage
13. model.amazon_ads_source.stg_amazon_ads__advertised_product_report
-
Input Column: 14. model.amazon_ads_source.stg_amazon_ads__advertised_product_report_tmp[campaign_budget_amount]
- Cleaning: The column is cast to FLOAT type Output Columns:
- campaign_budget_amount: Directly mapped after casting
3. model.amazon_ads.amazon_ads__ad_report
-
Input Column: 13. model.amazon_ads_source.stg_amazon_ads__advertised_product_report[campaign_budget_amount]
- Direct: The column is directly copied without any transformation Output Columns:
- campaign_budget_amount: Directly copied
Column Lineage
13. model.amazon_ads_source.stg_amazon_ads__advertised_product_report
-
Input Column: 14. model.amazon_ads_source.stg_amazon_ads__advertised_product_report_tmp[campaign_budget_currency_code]
- Cleaning: The column is cast to TEXT type Output Columns:
- campaign_budget_currency_code: Directly mapped after casting
3. model.amazon_ads.amazon_ads__ad_report
-
Input Column: 13. model.amazon_ads_source.stg_amazon_ads__advertised_product_report[campaign_budget_currency_code]
- Direct: The column is directly copied without any transformation Output Columns:
- campaign_budget_currency_code: Directly copied
Column Lineage
13. model.amazon_ads_source.stg_amazon_ads__advertised_product_report
-
Input Column: 14. model.amazon_ads_source.stg_amazon_ads__advertised_product_report_tmp[campaign_budget_type]
- Cleaning: The column is cast to TEXT type Output Columns:
- campaign_budget_type: Directly mapped after casting
3. model.amazon_ads.amazon_ads__ad_report
-
Input Column: 13. model.amazon_ads_source.stg_amazon_ads__advertised_product_report[campaign_budget_type]
- Direct: The column is directly copied without any transformation Output Columns:
- campaign_budget_type: Directly copied
Column Lineage
13. model.amazon_ads_source.stg_amazon_ads__advertised_product_report
-
Input Column: 14. model.amazon_ads_source.stg_amazon_ads__advertised_product_report_tmp[cost]
- Cleaning: The column is cast to FLOAT type Output Columns:
- cost: Directly mapped after casting
3. model.amazon_ads.amazon_ads__ad_report
-
Input Column: 13. model.amazon_ads_source.stg_amazon_ads__advertised_product_report[cost]
- Aggregation: The column is summed up in the group by clause Output Columns:
- cost: Aggregated sum
Column Lineage
13. model.amazon_ads_source.stg_amazon_ads__advertised_product_report
-
Input Column: 14. model.amazon_ads_source.stg_amazon_ads__advertised_product_report_tmp[clicks]
- Cleaning: The column is cast to INTEGER type Output Columns:
- clicks: Directly mapped after casting
3. model.amazon_ads.amazon_ads__ad_report
-
Input Column: 13. model.amazon_ads_source.stg_amazon_ads__advertised_product_report[clicks]
- Aggregation: The column is summed up in the group by clause Output Columns:
- clicks: Aggregated sum
Column Lineage
13. model.amazon_ads_source.stg_amazon_ads__advertised_product_report
-
Input Column: 14. model.amazon_ads_source.stg_amazon_ads__advertised_product_report_tmp[impressions]
- Cleaning: The column is cast to INTEGER type Output Columns:
- impressions: Directly mapped after casting
3. model.amazon_ads.amazon_ads__ad_report
-
Input Column: 13. model.amazon_ads_source.stg_amazon_ads__advertised_product_report[impressions]
- Aggregation: The column is summed up in the group by clause Output Columns:
- impressions: Aggregated sum
4. model.amazon_ads.amazon_ads__campaign_report
This SQL query integrates data from multiple Amazon Ads-related tables (campaign reports, account information, portfolios, and campaigns) to create a comprehensive campaign performance report. It joins these tables based on various ID fields and source relations, and then aggregates metrics such as cost, clicks, and impressions at the campaign level. The query also includes relevant information like account details, portfolio names, and campaign attributes.
IntegrationAggregationFiltering
SQL Query
with report as (
select *
from TEST.PUBLIC_amazon_ads_source.stg_amazon_ads__campaign_level_report
),
account_info as (
select *
from TEST.PUBLIC_amazon_ads_source.stg_amazon_ads__profile
where _fivetran_deleted = False
),
portfolios as (
select *
from TEST.PUBLIC_amazon_ads.int_amazon_ads__portfolio_history
),
campaigns as (
select *
from TEST.PUBLIC_amazon_ads_source.stg_amazon_ads__campaign_history
where is_most_recent_record = True
),
fields as (
select
report.source_relation,
report.date_day,
account_info.account_name,
account_info.account_id,
account_info.country_code,
account_info.profile_id,
portfolios.portfolio_name,
portfolios.portfolio_id,
campaigns.campaign_name,
report.campaign_id,
report.campaign_bidding_strategy,
report.campaign_budget_amount,
report.campaign_budget_currency_code,
report.campaign_budget_type,
sum(report.cost) as cost,
sum(report.clicks) as clicks,
sum(report.impressions) as impressions
from report
left join campaigns
on campaigns.campaign_id = report.campaign_id
and campaigns.source_relation = report.source_relation
left join portfolios
on portfolios.portfolio_id = campaigns.portfolio_id
and portfolios.source_relation = campaigns.source_relation
left join account_info
on account_info.profile_id = campaigns.profile_id
and account_info.source_relation = campaigns.source_relation
group by 1,2,3,4,5,6,7,8,9,10,11,12,13,14
)
select *
from fields
| Name | Type | Comment |
|---|---|---|
| date_day | date | None |
| account_name | text | None |
| account_id | text | None |
| country_code | text | None |
| profile_id | text | None |
| portfolio_name | text | None |
| portfolio_id | text | None |
| campaign_name | text | None |
| campaign_id | text | None |
| campaign_bidding_strategy | text | None |
| campaign_budget_amount | double precision | None |
| campaign_budget_currency_code | text | None |
| campaign_budget_type | text | None |
| cost | double precision | None |
| clicks | bigint | None |
| impressions | bigint | None |
Column Lineage
17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report
-
Input Column: 18. model.amazon_ads_source.stg_amazon_ads__campaign_level_report_tmp[date]
- Cleaning: The column is renamed to date_day in the final output Output Columns:
- date_day: Directly mapped and renamed
4. model.amazon_ads.amazon_ads__campaign_report
-
Input Column: 17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report[date_day]
- Direct: The column is directly copied without any transformation
- Aggregation: Used in the group by clause Output Columns:
- date_day: Directly copied
Column Lineage
25. model.amazon_ads_source.stg_amazon_ads__profile
-
Input Column: 26. model.amazon_ads_source.stg_amazon_ads__profile_tmp[account_name]
- Direct: The column is directly copied without transformation Output Columns:
- account_name: Directly copied
4. model.amazon_ads.amazon_ads__campaign_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[account_name]
- Direct: Directly copied to the output Output Columns:
- account_name: Directly copied
Column Lineage
25. model.amazon_ads_source.stg_amazon_ads__profile
-
Input Column: 26. model.amazon_ads_source.stg_amazon_ads__profile_tmp[account_id]
- Cleaning: The column is cast to TEXT Output Columns:
- account_id: Directly mapped after casting to TEXT
4. model.amazon_ads.amazon_ads__campaign_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[account_id]
- Direct: Directly copied to the output Output Columns:
- account_id: Directly copied
Column Lineage
25. model.amazon_ads_source.stg_amazon_ads__profile
-
Input Column: 26. model.amazon_ads_source.stg_amazon_ads__profile_tmp[country_code]
- Direct: The column is directly copied without transformation Output Columns:
- country_code: Directly copied
4. model.amazon_ads.amazon_ads__campaign_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[country_code]
- Direct: Directly copied to the output Output Columns:
- country_code: Directly copied
Column Lineage
25. model.amazon_ads_source.stg_amazon_ads__profile
-
Input Column: 26. model.amazon_ads_source.stg_amazon_ads__profile_tmp[id]
- Cleaning: The column is cast to TEXT and renamed to profile_id Output Columns:
- profile_id: Directly mapped after casting to TEXT
4. model.amazon_ads.amazon_ads__campaign_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[profile_id]
- Integration: Used as a key to join with the campaigns table
- Direct: Directly copied to the output Output Columns:
- profile_id: Directly copied
Column Lineage
21. model.amazon_ads_source.stg_amazon_ads__portfolio_history
-
Input Column: 22. model.amazon_ads_source.stg_amazon_ads__portfolio_history_tmp[name]
- Direct: Copied without transformation Output Columns:
- portfolio_name: Directly copied and renamed
8. model.amazon_ads.int_amazon_ads__portfolio_history
-
Input Column: 21. model.amazon_ads_source.stg_amazon_ads__portfolio_history[portfolio_name]
- Direct: This column is directly copied without any transformation Output Columns:
- portfolio_name: Directly copied
4. model.amazon_ads.amazon_ads__campaign_report
-
Input Column: 8. model.amazon_ads.int_amazon_ads__portfolio_history[portfolio_name]
- Direct: Directly copied to the output Output Columns:
- portfolio_name: Directly copied
Column Lineage
21. model.amazon_ads_source.stg_amazon_ads__portfolio_history
-
Input Column: 22. model.amazon_ads_source.stg_amazon_ads__portfolio_history_tmp[id]
- Cleaning: Cast to TEXT data type Output Columns:
- portfolio_id: Directly mapped after casting
8. model.amazon_ads.int_amazon_ads__portfolio_history
-
Input Column: 21. model.amazon_ads_source.stg_amazon_ads__portfolio_history[portfolio_id]
- Direct: This column is directly copied without any transformation Output Columns:
- portfolio_id: Directly copied
4. model.amazon_ads.amazon_ads__campaign_report
-
Input Column: 8. model.amazon_ads.int_amazon_ads__portfolio_history[portfolio_id]
- Integration: Used as a key to join with the campaigns table
- Direct: Directly copied to the output Output Columns:
- portfolio_id: Directly copied
Column Lineage
15. model.amazon_ads_source.stg_amazon_ads__campaign_history
-
Input Column: 16. model.amazon_ads_source.stg_amazon_ads__campaign_history_tmp[name]
- Direct: Copied without transformation Output Columns:
- campaign_name: Directly copied and renamed
4. model.amazon_ads.amazon_ads__campaign_report
-
Input Column: 15. model.amazon_ads_source.stg_amazon_ads__campaign_history[campaign_name]
- Direct: Directly copied to the output Output Columns:
- campaign_name: Directly copied
Column Lineage
15. model.amazon_ads_source.stg_amazon_ads__campaign_history
-
Input Column: 16. model.amazon_ads_source.stg_amazon_ads__campaign_history_tmp[id]
- Cleaning: Cast to TEXT data type
- Deduplication: Used in the window function to identify the most recent record Output Columns:
- campaign_id: Directly mapped after casting to TEXT
17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report
-
Input Column: 18. model.amazon_ads_source.stg_amazon_ads__campaign_level_report_tmp[campaign_id]
- Cleaning: The column is cast as TEXT in the final output Output Columns:
- campaign_id: Directly mapped after casting to TEXT
4. model.amazon_ads.amazon_ads__campaign_report
-
Input Column: 15. model.amazon_ads_source.stg_amazon_ads__campaign_history[campaign_id]
- Integration: Used to join with the report table
- Direct: Directly copied to the output Output Columns:
- campaign_id: Directly copied
-
Input Column: 17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report[campaign_id]
- Direct: The column is directly copied without any transformation
- Integration: Used as a key to join with the campaigns table Output Columns:
- campaign_id: Directly copied
Column Lineage
17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report
-
Input Column: 18. model.amazon_ads_source.stg_amazon_ads__campaign_level_report_tmp[campaign_bidding_strategy]
- Direct: The column is directly copied without transformation Output Columns:
- campaign_bidding_strategy: Directly mapped
4. model.amazon_ads.amazon_ads__campaign_report
-
Input Column: 17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report[campaign_bidding_strategy]
- Direct: The column is directly copied without any transformation Output Columns:
- campaign_bidding_strategy: Directly copied
Column Lineage
17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report
-
Input Column: 18. model.amazon_ads_source.stg_amazon_ads__campaign_level_report_tmp[campaign_budget_amount]
- Direct: The column is directly copied without transformation Output Columns:
- campaign_budget_amount: Directly mapped
4. model.amazon_ads.amazon_ads__campaign_report
-
Input Column: 17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report[campaign_budget_amount]
- Direct: The column is directly copied without any transformation Output Columns:
- campaign_budget_amount: Directly copied
Column Lineage
17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report
-
Input Column: 18. model.amazon_ads_source.stg_amazon_ads__campaign_level_report_tmp[campaign_budget_currency_code]
- Direct: The column is directly copied without transformation Output Columns:
- campaign_budget_currency_code: Directly mapped
4. model.amazon_ads.amazon_ads__campaign_report
-
Input Column: 17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report[campaign_budget_currency_code]
- Direct: The column is directly copied without any transformation Output Columns:
- campaign_budget_currency_code: Directly copied
Column Lineage
17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report
-
Input Column: 18. model.amazon_ads_source.stg_amazon_ads__campaign_level_report_tmp[campaign_budget_type]
- Direct: The column is directly copied without transformation Output Columns:
- campaign_budget_type: Directly mapped
4. model.amazon_ads.amazon_ads__campaign_report
-
Input Column: 17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report[campaign_budget_type]
- Direct: The column is directly copied without any transformation Output Columns:
- campaign_budget_type: Directly copied
Column Lineage
17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report
-
Input Column: 18. model.amazon_ads_source.stg_amazon_ads__campaign_level_report_tmp[cost]
- Direct: The column is directly copied without transformation Output Columns:
- cost: Directly mapped
4. model.amazon_ads.amazon_ads__campaign_report
-
Input Column: 17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report[cost]
- Aggregation: The column is summed up in the group by clause Output Columns:
- cost: Aggregated sum
Column Lineage
17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report
-
Input Column: 18. model.amazon_ads_source.stg_amazon_ads__campaign_level_report_tmp[clicks]
- Direct: The column is directly copied without transformation Output Columns:
- clicks: Directly mapped
4. model.amazon_ads.amazon_ads__campaign_report
-
Input Column: 17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report[clicks]
- Aggregation: The column is summed up in the group by clause Output Columns:
- clicks: Aggregated sum
Column Lineage
17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report
-
Input Column: 18. model.amazon_ads_source.stg_amazon_ads__campaign_level_report_tmp[impressions]
- Direct: The column is directly copied without transformation Output Columns:
- impressions: Directly mapped
4. model.amazon_ads.amazon_ads__campaign_report
-
Input Column: 17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report[impressions]
- Aggregation: The column is summed up in the group by clause Output Columns:
- impressions: Aggregated sum
5. model.amazon_ads.amazon_ads__keyword_report
This SQL query combines data from multiple Amazon Ads-related tables to create a comprehensive keyword report. It joins keyword performance data with account, portfolio, campaign, ad group, and keyword information. The query filters for the most recent records in some tables, aggregates cost, clicks, and impressions data, and provides a detailed view of keyword performance across different levels of the advertising structure.
FilteringIntegrationAggregation
SQL Query
with report as (
select *
from TEST.PUBLIC_amazon_ads_source.stg_amazon_ads__targeting_keyword_report
),
account_info as (
select *
from TEST.PUBLIC_amazon_ads_source.stg_amazon_ads__profile
where _fivetran_deleted = False
),
portfolios as (
select *
from TEST.PUBLIC_amazon_ads.int_amazon_ads__portfolio_history
),
campaigns as (
select *
from TEST.PUBLIC_amazon_ads_source.stg_amazon_ads__campaign_history
where is_most_recent_record = True
),
ad_groups as (
select *
from TEST.PUBLIC_amazon_ads_source.stg_amazon_ads__ad_group_history
where is_most_recent_record = True
),
keywords as (
select *
from TEST.PUBLIC_amazon_ads_source.stg_amazon_ads__keyword_history
where is_most_recent_record = True
),
fields as (
select
report.source_relation,
report.date_day,
account_info.account_name,
account_info.account_id,
account_info.country_code,
account_info.profile_id,
portfolios.portfolio_name,
portfolios.portfolio_id,
campaigns.campaign_name,
report.campaign_id,
ad_groups.ad_group_name,
report.ad_group_id,
report.keyword_id,
keywords.keyword_text,
keywords.serving_status,
keywords.state,
report.keyword_bid,
report.keyword_type,
report.match_type,
sum(report.cost) as cost,
sum(report.clicks) as clicks,
sum(report.impressions) as impressions
from report
left join keywords
on keywords.keyword_id = report.keyword_id
and keywords.source_relation = report.source_relation
left join ad_groups
on ad_groups.ad_group_id = report.ad_group_id
and ad_groups.source_relation = report.source_relation
left join campaigns
on campaigns.campaign_id = report.campaign_id
and campaigns.source_relation = report.source_relation
left join portfolios
on portfolios.portfolio_id = campaigns.portfolio_id
and portfolios.source_relation = campaigns.source_relation
left join account_info
on account_info.profile_id = campaigns.profile_id
and account_info.source_relation = campaigns.source_relation
group by 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19
)
select *
from fields
| Name | Type | Comment |
|---|---|---|
| date_day | date | None |
| account_name | text | None |
| account_id | text | None |
| country_code | text | None |
| profile_id | text | None |
| portfolio_name | text | None |
| portfolio_id | text | None |
| campaign_name | text | None |
| campaign_id | text | None |
| ad_group_name | text | None |
| ad_group_id | text | None |
| keyword_id | text | None |
| keyword_text | text | None |
| serving_status | text | None |
| state | text | None |
| keyword_bid | double precision | None |
| keyword_type | text | None |
| match_type | text | None |
| cost | double precision | None |
| clicks | bigint | None |
| impressions | bigint | None |
Column Lineage
29. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report
-
Input Column: 30. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report_tmp[date]
- Cleaning: The column is renamed in the final output Output Columns:
- date_day: Directly mapped and renamed
5. model.amazon_ads.amazon_ads__keyword_report
-
Input Column: 29. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report[date_day]
- Direct: The column is directly copied without transformation Output Columns:
- date_day: Directly copied
Column Lineage
25. model.amazon_ads_source.stg_amazon_ads__profile
-
Input Column: 26. model.amazon_ads_source.stg_amazon_ads__profile_tmp[account_name]
- Direct: The column is directly copied without transformation Output Columns:
- account_name: Directly copied
5. model.amazon_ads.amazon_ads__keyword_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[account_name]
- Direct: directly copied to the output Output Columns:
- account_name: directly copied
Column Lineage
25. model.amazon_ads_source.stg_amazon_ads__profile
-
Input Column: 26. model.amazon_ads_source.stg_amazon_ads__profile_tmp[account_id]
- Cleaning: The column is cast to TEXT Output Columns:
- account_id: Directly mapped after casting to TEXT
5. model.amazon_ads.amazon_ads__keyword_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[account_id]
- Direct: directly copied to the output Output Columns:
- account_id: directly copied
Column Lineage
25. model.amazon_ads_source.stg_amazon_ads__profile
-
Input Column: 26. model.amazon_ads_source.stg_amazon_ads__profile_tmp[country_code]
- Direct: The column is directly copied without transformation Output Columns:
- country_code: Directly copied
5. model.amazon_ads.amazon_ads__keyword_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[country_code]
- Direct: directly copied to the output Output Columns:
- country_code: directly copied
Column Lineage
25. model.amazon_ads_source.stg_amazon_ads__profile
-
Input Column: 26. model.amazon_ads_source.stg_amazon_ads__profile_tmp[id]
- Cleaning: The column is cast to TEXT and renamed to profile_id Output Columns:
- profile_id: Directly mapped after casting to TEXT
5. model.amazon_ads.amazon_ads__keyword_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[profile_id]
- Integration: used as a key to join with the campaigns table
- Direct: directly copied to the output Output Columns:
- profile_id: directly copied
Column Lineage
21. model.amazon_ads_source.stg_amazon_ads__portfolio_history
-
Input Column: 22. model.amazon_ads_source.stg_amazon_ads__portfolio_history_tmp[name]
- Direct: Copied without transformation Output Columns:
- portfolio_name: Directly copied and renamed
8. model.amazon_ads.int_amazon_ads__portfolio_history
-
Input Column: 21. model.amazon_ads_source.stg_amazon_ads__portfolio_history[portfolio_name]
- Direct: This column is directly copied without any transformation Output Columns:
- portfolio_name: Directly copied
5. model.amazon_ads.amazon_ads__keyword_report
-
Input Column: 8. model.amazon_ads.int_amazon_ads__portfolio_history[portfolio_name]
- Direct: Directly copied to the output Output Columns:
- portfolio_name: Directly copied
Column Lineage
21. model.amazon_ads_source.stg_amazon_ads__portfolio_history
-
Input Column: 22. model.amazon_ads_source.stg_amazon_ads__portfolio_history_tmp[id]
- Cleaning: Cast to TEXT data type Output Columns:
- portfolio_id: Directly mapped after casting
8. model.amazon_ads.int_amazon_ads__portfolio_history
-
Input Column: 21. model.amazon_ads_source.stg_amazon_ads__portfolio_history[portfolio_id]
- Direct: This column is directly copied without any transformation Output Columns:
- portfolio_id: Directly copied
5. model.amazon_ads.amazon_ads__keyword_report
-
Input Column: 8. model.amazon_ads.int_amazon_ads__portfolio_history[portfolio_id]
- Integration: Used as a key to join with the campaigns table
- Direct: Directly copied to the output Output Columns:
- portfolio_id: Directly copied
Column Lineage
15. model.amazon_ads_source.stg_amazon_ads__campaign_history
-
Input Column: 16. model.amazon_ads_source.stg_amazon_ads__campaign_history_tmp[name]
- Direct: Copied without transformation Output Columns:
- campaign_name: Directly copied and renamed
5. model.amazon_ads.amazon_ads__keyword_report
-
Input Column: 15. model.amazon_ads_source.stg_amazon_ads__campaign_history[campaign_name]
- Direct: directly copied to the output Output Columns:
- campaign_name: directly copied
Column Lineage
15. model.amazon_ads_source.stg_amazon_ads__campaign_history
-
Input Column: 16. model.amazon_ads_source.stg_amazon_ads__campaign_history_tmp[id]
- Cleaning: Cast to TEXT data type
- Deduplication: Used in the window function to identify the most recent record Output Columns:
- campaign_id: Directly mapped after casting to TEXT
29. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report
-
Input Column: 30. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report_tmp[campaign_id]
- Cleaning: The column is cast to TEXT in the final output Output Columns:
- campaign_id: Directly mapped after casting to TEXT
5. model.amazon_ads.amazon_ads__keyword_report
-
Input Column: 15. model.amazon_ads_source.stg_amazon_ads__campaign_history[campaign_id]
- Integration: used as a key to join with the report table
- Direct: directly copied to the output Output Columns:
- campaign_id: directly copied
-
Input Column: 29. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report[campaign_id]
- Direct: The column is directly copied without transformation
- Integration: Used as a key to join with the campaigns table Output Columns:
- campaign_id: Directly copied
Column Lineage
9. model.amazon_ads_source.stg_amazon_ads__ad_group_history
-
Input Column: 10. model.amazon_ads_source.stg_amazon_ads__ad_group_history_tmp[name]
- Cleaning: cast to TEXT data type Output Columns:
- ad_group_name: directly copied after casting and renaming
5. model.amazon_ads.amazon_ads__keyword_report
-
Input Column: 9. model.amazon_ads_source.stg_amazon_ads__ad_group_history[ad_group_name]
- Direct: directly copied to the output Output Columns:
- ad_group_name: directly copied
Column Lineage
9. model.amazon_ads_source.stg_amazon_ads__ad_group_history
-
Input Column: 10. model.amazon_ads_source.stg_amazon_ads__ad_group_history_tmp[id]
- Cleaning: cast to TEXT data type Output Columns:
- ad_group_id: directly copied after casting
29. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report
-
Input Column: 30. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report_tmp[ad_group_id]
- Cleaning: The column is cast to TEXT in the final output Output Columns:
- ad_group_id: Directly mapped after casting to TEXT
5. model.amazon_ads.amazon_ads__keyword_report
-
Input Column: 9. model.amazon_ads_source.stg_amazon_ads__ad_group_history[ad_group_id]
- Integration: used as a key to join with the report table
- Direct: directly copied to the output Output Columns:
- ad_group_id: directly copied
-
Input Column: 29. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report[ad_group_id]
- Direct: The column is directly copied without transformation
- Integration: Used as a key to join with the ad_groups table Output Columns:
- ad_group_id: Directly copied
Column Lineage
19. model.amazon_ads_source.stg_amazon_ads__keyword_history
-
Input Column: 20. model.amazon_ads_source.stg_amazon_ads__keyword_history_tmp[id]
- Cleaning: Cast to TEXT data type
- Deduplication: Used in the window function to identify the most recent record Output Columns:
- keyword_id: Directly mapped after casting to TEXT
29. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report
-
Input Column: 30. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report_tmp[keyword_id]
- Cleaning: The column is cast to TEXT in the final output Output Columns:
- keyword_id: Directly mapped after casting to TEXT
5. model.amazon_ads.amazon_ads__keyword_report
-
Input Column: 19. model.amazon_ads_source.stg_amazon_ads__keyword_history[keyword_id]
- Direct: The column is directly copied without any transformation
- Integration: Used as a key to join with the report table Output Columns:
- keyword_id: Directly copied
-
Input Column: 29. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report[keyword_id]
- Direct: The column is directly copied without transformation
- Integration: Used as a key to join with the keywords table Output Columns:
- keyword_id: Directly copied
Column Lineage
19. model.amazon_ads_source.stg_amazon_ads__keyword_history
-
Input Column: 20. model.amazon_ads_source.stg_amazon_ads__keyword_history_tmp[keyword_text]
- Cleaning: Cast to TEXT data type Output Columns:
- keyword_text: Directly mapped after casting to TEXT
5. model.amazon_ads.amazon_ads__keyword_report
-
Input Column: 19. model.amazon_ads_source.stg_amazon_ads__keyword_history[keyword_text]
- Direct: The column is directly copied without any transformation Output Columns:
- keyword_text: Directly copied
Column Lineage
19. model.amazon_ads_source.stg_amazon_ads__keyword_history
-
Input Column: 20. model.amazon_ads_source.stg_amazon_ads__keyword_history_tmp[serving_status]
- Cleaning: Cast to TEXT data type Output Columns:
- serving_status: Directly mapped after casting to TEXT
5. model.amazon_ads.amazon_ads__keyword_report
-
Input Column: 19. model.amazon_ads_source.stg_amazon_ads__keyword_history[serving_status]
- Direct: The column is directly copied without any transformation Output Columns:
- serving_status: Directly copied
Column Lineage
19. model.amazon_ads_source.stg_amazon_ads__keyword_history
-
Input Column: 20. model.amazon_ads_source.stg_amazon_ads__keyword_history_tmp[state]
- Cleaning: Cast to TEXT data type Output Columns:
- state: Directly mapped after casting to TEXT
5. model.amazon_ads.amazon_ads__keyword_report
-
Input Column: 19. model.amazon_ads_source.stg_amazon_ads__keyword_history[state]
- Direct: The column is directly copied without any transformation Output Columns:
- state: Directly copied
Column Lineage
29. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report
-
Input Column: 30. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report_tmp[keyword_bid]
- Direct: The column is directly copied without transformation Output Columns:
- keyword_bid: Directly mapped
5. model.amazon_ads.amazon_ads__keyword_report
-
Input Column: 29. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report[keyword_bid]
- Direct: The column is directly copied without transformation Output Columns:
- keyword_bid: Directly copied
Column Lineage
29. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report
-
Input Column: 30. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report_tmp[keyword_type]
- Direct: The column is directly copied without transformation Output Columns:
- keyword_type: Directly mapped
5. model.amazon_ads.amazon_ads__keyword_report
-
Input Column: 29. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report[keyword_type]
- Direct: The column is directly copied without transformation Output Columns:
- keyword_type: Directly copied
Column Lineage
29. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report
-
Input Column: 30. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report_tmp[match_type]
- Direct: The column is directly copied without transformation Output Columns:
- match_type: Directly mapped
5. model.amazon_ads.amazon_ads__keyword_report
-
Input Column: 29. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report[match_type]
- Direct: The column is directly copied without transformation Output Columns:
- match_type: Directly copied
Column Lineage
29. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report
-
Input Column: 30. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report_tmp[cost]
- Direct: The column is directly copied without transformation Output Columns:
- cost: Directly mapped
5. model.amazon_ads.amazon_ads__keyword_report
-
Input Column: 29. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report[cost]
- Aggregation: The column is summed up in the group by clause Output Columns:
- cost: Aggregated sum
Column Lineage
29. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report
-
Input Column: 30. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report_tmp[clicks]
- Direct: The column is directly copied without transformation Output Columns:
- clicks: Directly mapped
5. model.amazon_ads.amazon_ads__keyword_report
-
Input Column: 29. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report[clicks]
- Aggregation: The column is summed up in the group by clause Output Columns:
- clicks: Aggregated sum
Column Lineage
29. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report
-
Input Column: 30. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report_tmp[impressions]
- Direct: The column is directly copied without transformation Output Columns:
- impressions: Directly mapped
5. model.amazon_ads.amazon_ads__keyword_report
-
Input Column: 29. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report[impressions]
- Aggregation: The column is summed up in the group by clause Output Columns:
- impressions: Aggregated sum
6. model.amazon_ads.amazon_ads__portfolio_report
This SQL query integrates data from multiple Amazon Ads-related tables to create a comprehensive portfolio report. It joins information from campaign reports, account profiles, portfolio history, and campaign history. The query filters for the most recent records in portfolio and campaign history, and aggregates metrics like cost, clicks, and impressions at the portfolio level. The result provides a detailed view of portfolio performance including budget details, serving status, and associated account information.
FilteringIntegrationAggregation
SQL Query
with report as (
select *
--use campaign report since portfolio report not provided
from TEST.PUBLIC_amazon_ads_source.stg_amazon_ads__campaign_level_report
),
account_info as (
select *
from TEST.PUBLIC_amazon_ads_source.stg_amazon_ads__profile
where _fivetran_deleted = False
),
portfolios as (
select *
from TEST.PUBLIC_amazon_ads_source.stg_amazon_ads__portfolio_history
where is_most_recent_record = True
),
campaigns as (
select *
from TEST.PUBLIC_amazon_ads_source.stg_amazon_ads__campaign_history
where is_most_recent_record = True
),
fields as (
select
report.source_relation,
report.date_day,
account_info.account_name,
account_info.account_id,
account_info.country_code,
account_info.profile_id,
portfolios.portfolio_name,
portfolios.portfolio_id,
portfolios.budget_amount,
portfolios.budget_currency_code,
portfolios.budget_start_date,
portfolios.budget_end_date,
portfolios.budget_policy,
portfolios.in_budget,
portfolios.serving_status,
portfolios.state,
sum(report.cost) as cost,
sum(report.clicks) as clicks,
sum(report.impressions) as impressions
--use campaign report since portfolio report not provided
from portfolios
left join campaigns
on campaigns.portfolio_id = portfolios.portfolio_id
and campaigns.source_relation = portfolios.source_relation
left join account_info
on account_info.profile_id = campaigns.profile_id
and account_info.source_relation = campaigns.source_relation
left join report
on report.campaign_id = campaigns.campaign_id
and report.source_relation = campaigns.source_relation
group by 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16
)
select *
from fields
| Name | Type | Comment |
|---|---|---|
| date_day | date | None |
| account_name | text | None |
| account_id | text | None |
| country_code | text | None |
| profile_id | text | None |
| portfolio_name | text | None |
| portfolio_id | text | None |
| budget_amount | integer | None |
| budget_currency_code | integer | None |
| budget_start_date | integer | None |
| budget_end_date | integer | None |
| budget_policy | integer | None |
| in_budget | boolean | None |
| serving_status | text | None |
| state | text | None |
| cost | double precision | None |
| clicks | bigint | None |
| impressions | bigint | None |
Column Lineage
17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report
-
Input Column: 18. model.amazon_ads_source.stg_amazon_ads__campaign_level_report_tmp[date]
- Cleaning: The column is renamed to date_day in the final output Output Columns:
- date_day: Directly mapped and renamed
6. model.amazon_ads.amazon_ads__portfolio_report
-
Input Column: 17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report[date_day]
- Direct: Directly selected in the final output Output Columns:
- date_day: Directly copied to output
Column Lineage
25. model.amazon_ads_source.stg_amazon_ads__profile
-
Input Column: 26. model.amazon_ads_source.stg_amazon_ads__profile_tmp[account_name]
- Direct: The column is directly copied without transformation Output Columns:
- account_name: Directly copied
6. model.amazon_ads.amazon_ads__portfolio_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[account_name]
- Direct: directly copied to the output Output Columns:
- account_name: directly copied
Column Lineage
25. model.amazon_ads_source.stg_amazon_ads__profile
-
Input Column: 26. model.amazon_ads_source.stg_amazon_ads__profile_tmp[account_id]
- Cleaning: The column is cast to TEXT Output Columns:
- account_id: Directly mapped after casting to TEXT
6. model.amazon_ads.amazon_ads__portfolio_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[account_id]
- Direct: directly copied to the output Output Columns:
- account_id: directly copied
Column Lineage
25. model.amazon_ads_source.stg_amazon_ads__profile
-
Input Column: 26. model.amazon_ads_source.stg_amazon_ads__profile_tmp[country_code]
- Direct: The column is directly copied without transformation Output Columns:
- country_code: Directly copied
6. model.amazon_ads.amazon_ads__portfolio_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[country_code]
- Direct: directly copied to the output Output Columns:
- country_code: directly copied
Column Lineage
25. model.amazon_ads_source.stg_amazon_ads__profile
-
Input Column: 26. model.amazon_ads_source.stg_amazon_ads__profile_tmp[id]
- Cleaning: The column is cast to TEXT and renamed to profile_id Output Columns:
- profile_id: Directly mapped after casting to TEXT
6. model.amazon_ads.amazon_ads__portfolio_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[profile_id]
- Integration: used as a key to join with the campaigns table
- Direct: directly copied to the output Output Columns:
- profile_id: directly copied
Column Lineage
21. model.amazon_ads_source.stg_amazon_ads__portfolio_history
-
Input Column: 22. model.amazon_ads_source.stg_amazon_ads__portfolio_history_tmp[name]
- Direct: Copied without transformation Output Columns:
- portfolio_name: Directly copied and renamed
6. model.amazon_ads.amazon_ads__portfolio_report
-
Input Column: 21. model.amazon_ads_source.stg_amazon_ads__portfolio_history[portfolio_name]
- Direct: The column is directly copied without any transformation Output Columns:
- portfolio_name: Directly copied
Column Lineage
21. model.amazon_ads_source.stg_amazon_ads__portfolio_history
-
Input Column: 22. model.amazon_ads_source.stg_amazon_ads__portfolio_history_tmp[id]
- Cleaning: Cast to TEXT data type Output Columns:
- portfolio_id: Directly mapped after casting
6. model.amazon_ads.amazon_ads__portfolio_report
-
Input Column: 21. model.amazon_ads_source.stg_amazon_ads__portfolio_history[portfolio_id]
- Direct: The column is directly copied without any transformation
- Integration: The column is used as a key to join with other tables Output Columns:
- portfolio_id: Directly copied
Column Lineage
21. model.amazon_ads_source.stg_amazon_ads__portfolio_history
-
Input Column: 22. model.amazon_ads_source.stg_amazon_ads__portfolio_history_tmp[budget_amount]
- Direct: Copied without transformation Output Columns:
- budget_amount: Directly copied
6. model.amazon_ads.amazon_ads__portfolio_report
-
Input Column: 21. model.amazon_ads_source.stg_amazon_ads__portfolio_history[budget_amount]
- Direct: The column is directly copied without any transformation Output Columns:
- budget_amount: Directly copied
Column Lineage
21. model.amazon_ads_source.stg_amazon_ads__portfolio_history
-
Input Column: 22. model.amazon_ads_source.stg_amazon_ads__portfolio_history_tmp[budget_currency_code]
- Direct: Copied without transformation Output Columns:
- budget_currency_code: Directly copied
6. model.amazon_ads.amazon_ads__portfolio_report
-
Input Column: 21. model.amazon_ads_source.stg_amazon_ads__portfolio_history[budget_currency_code]
- Direct: The column is directly copied without any transformation Output Columns:
- budget_currency_code: Directly copied
Column Lineage
21. model.amazon_ads_source.stg_amazon_ads__portfolio_history
-
Input Column: 22. model.amazon_ads_source.stg_amazon_ads__portfolio_history_tmp[budget_start_date]
- Direct: Copied without transformation Output Columns:
- budget_start_date: Directly copied
6. model.amazon_ads.amazon_ads__portfolio_report
-
Input Column: 21. model.amazon_ads_source.stg_amazon_ads__portfolio_history[budget_start_date]
- Direct: The column is directly copied without any transformation Output Columns:
- budget_start_date: Directly copied
Column Lineage
21. model.amazon_ads_source.stg_amazon_ads__portfolio_history
-
Input Column: 22. model.amazon_ads_source.stg_amazon_ads__portfolio_history_tmp[budget_end_date]
- Direct: Copied without transformation Output Columns:
- budget_end_date: Directly copied
6. model.amazon_ads.amazon_ads__portfolio_report
-
Input Column: 21. model.amazon_ads_source.stg_amazon_ads__portfolio_history[budget_end_date]
- Direct: The column is directly copied without any transformation Output Columns:
- budget_end_date: Directly copied
Column Lineage
21. model.amazon_ads_source.stg_amazon_ads__portfolio_history
-
Input Column: 22. model.amazon_ads_source.stg_amazon_ads__portfolio_history_tmp[budget_policy]
- Direct: Copied without transformation Output Columns:
- budget_policy: Directly copied
6. model.amazon_ads.amazon_ads__portfolio_report
-
Input Column: 21. model.amazon_ads_source.stg_amazon_ads__portfolio_history[budget_policy]
- Direct: The column is directly copied without any transformation Output Columns:
- budget_policy: Directly copied
Column Lineage
21. model.amazon_ads_source.stg_amazon_ads__portfolio_history
-
Input Column: 22. model.amazon_ads_source.stg_amazon_ads__portfolio_history_tmp[in_budget]
- Direct: Copied without transformation Output Columns:
- in_budget: Directly copied
6. model.amazon_ads.amazon_ads__portfolio_report
-
Input Column: 21. model.amazon_ads_source.stg_amazon_ads__portfolio_history[in_budget]
- Direct: The column is directly copied without any transformation Output Columns:
- in_budget: Directly copied
Column Lineage
21. model.amazon_ads_source.stg_amazon_ads__portfolio_history
-
Input Column: 22. model.amazon_ads_source.stg_amazon_ads__portfolio_history_tmp[serving_status]
- Direct: Copied without transformation Output Columns:
- serving_status: Directly copied
6. model.amazon_ads.amazon_ads__portfolio_report
-
Input Column: 21. model.amazon_ads_source.stg_amazon_ads__portfolio_history[serving_status]
- Direct: The column is directly copied without any transformation Output Columns:
- serving_status: Directly copied
Column Lineage
21. model.amazon_ads_source.stg_amazon_ads__portfolio_history
-
Input Column: 22. model.amazon_ads_source.stg_amazon_ads__portfolio_history_tmp[state]
- Direct: Copied without transformation Output Columns:
- state: Directly copied
6. model.amazon_ads.amazon_ads__portfolio_report
-
Input Column: 21. model.amazon_ads_source.stg_amazon_ads__portfolio_history[state]
- Direct: The column is directly copied without any transformation Output Columns:
- state: Directly copied
Column Lineage
17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report
-
Input Column: 18. model.amazon_ads_source.stg_amazon_ads__campaign_level_report_tmp[cost]
- Direct: The column is directly copied without transformation Output Columns:
- cost: Directly mapped
6. model.amazon_ads.amazon_ads__portfolio_report
-
Input Column: 17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report[cost]
- Aggregation: Summed up in the final select statement Output Columns:
- cost: Aggregated sum of cost
Column Lineage
17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report
-
Input Column: 18. model.amazon_ads_source.stg_amazon_ads__campaign_level_report_tmp[clicks]
- Direct: The column is directly copied without transformation Output Columns:
- clicks: Directly mapped
6. model.amazon_ads.amazon_ads__portfolio_report
-
Input Column: 17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report[clicks]
- Aggregation: Summed up in the final select statement Output Columns:
- clicks: Aggregated sum of clicks
Column Lineage
17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report
-
Input Column: 18. model.amazon_ads_source.stg_amazon_ads__campaign_level_report_tmp[impressions]
- Direct: The column is directly copied without transformation Output Columns:
- impressions: Directly mapped
6. model.amazon_ads.amazon_ads__portfolio_report
-
Input Column: 17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report[impressions]
- Aggregation: Summed up in the final select statement Output Columns:
- impressions: Aggregated sum of impressions
7. model.amazon_ads.amazon_ads__search_report
This SQL query integrates data from multiple Amazon Ads-related tables to create a comprehensive search report. It joins information from search term reports, account profiles, portfolios, campaigns, ad groups, and keywords. The query filters for the most recent records in some tables, aggregates cost, clicks, and impressions, and provides a detailed view of search performance across various dimensions of the advertising structure.
FilteringIntegrationAggregation
SQL Query
with report as (
select *
from TEST.PUBLIC_amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report
),
account_info as (
select *
from TEST.PUBLIC_amazon_ads_source.stg_amazon_ads__profile
where _fivetran_deleted = False
),
portfolios as (
select *
from TEST.PUBLIC_amazon_ads.int_amazon_ads__portfolio_history
),
campaigns as (
select *
from TEST.PUBLIC_amazon_ads_source.stg_amazon_ads__campaign_history
where is_most_recent_record = True
),
ad_groups as (
select *
from TEST.PUBLIC_amazon_ads_source.stg_amazon_ads__ad_group_history
where is_most_recent_record = True
),
keywords as (
select *
from TEST.PUBLIC_amazon_ads_source.stg_amazon_ads__keyword_history
where is_most_recent_record = True
),
fields as (
select
report.source_relation,
report.date_day,
account_info.account_name,
account_info.account_id,
account_info.country_code,
account_info.profile_id,
portfolios.portfolio_name,
portfolios.portfolio_id,
campaigns.campaign_name,
report.campaign_id,
ad_groups.ad_group_name,
report.ad_group_id,
report.keyword_id,
keywords.keyword_text,
keywords.match_type,
keywords.serving_status,
keywords.state,
report.search_term,
report.targeting,
sum(report.cost) as cost,
sum(report.clicks) as clicks,
sum(report.impressions) as impressions
from report
left join keywords
on keywords.keyword_id = report.keyword_id
and keywords.source_relation = report.source_relation
left join ad_groups
on ad_groups.ad_group_id = report.ad_group_id
and ad_groups.source_relation = report.source_relation
left join campaigns
on campaigns.campaign_id = report.campaign_id
and campaigns.source_relation = report.source_relation
left join portfolios
on portfolios.portfolio_id = campaigns.portfolio_id
and portfolios.source_relation = campaigns.source_relation
left join account_info
on account_info.profile_id = campaigns.profile_id
and account_info.source_relation = campaigns.source_relation
group by 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19
)
select *
from fields
| Name | Type | Comment |
|---|---|---|
| date_day | date | None |
| account_name | text | None |
| account_id | text | None |
| country_code | text | None |
| profile_id | text | None |
| portfolio_name | text | None |
| portfolio_id | text | None |
| campaign_name | text | None |
| campaign_id | text | None |
| ad_group_name | text | None |
| ad_group_id | text | None |
| keyword_id | text | None |
| keyword_text | text | None |
| match_type | text | None |
| serving_status | text | None |
| state | text | None |
| search_term | text | None |
| targeting | text | None |
| cost | double precision | None |
| clicks | bigint | None |
| impressions | bigint | None |
Column Lineage
27. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report
-
Input Column: 28. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report_tmp[date]
- Cleaning: The column is cast to DATE data type Output Columns:
- date_day: Directly mapped after casting and renamed
7. model.amazon_ads.amazon_ads__search_report
-
Input Column: 27. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report[date_day]
- Direct: directly copied to the output Output Columns:
- date_day: directly copied
Column Lineage
25. model.amazon_ads_source.stg_amazon_ads__profile
-
Input Column: 26. model.amazon_ads_source.stg_amazon_ads__profile_tmp[account_name]
- Direct: The column is directly copied without transformation Output Columns:
- account_name: Directly copied
7. model.amazon_ads.amazon_ads__search_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[account_name]
- Direct: directly copied to the output Output Columns:
- account_name: directly copied
Column Lineage
25. model.amazon_ads_source.stg_amazon_ads__profile
-
Input Column: 26. model.amazon_ads_source.stg_amazon_ads__profile_tmp[account_id]
- Cleaning: The column is cast to TEXT Output Columns:
- account_id: Directly mapped after casting to TEXT
7. model.amazon_ads.amazon_ads__search_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[account_id]
- Direct: directly copied to the output Output Columns:
- account_id: directly copied
Column Lineage
25. model.amazon_ads_source.stg_amazon_ads__profile
-
Input Column: 26. model.amazon_ads_source.stg_amazon_ads__profile_tmp[country_code]
- Direct: The column is directly copied without transformation Output Columns:
- country_code: Directly copied
7. model.amazon_ads.amazon_ads__search_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[country_code]
- Direct: directly copied to the output Output Columns:
- country_code: directly copied
Column Lineage
25. model.amazon_ads_source.stg_amazon_ads__profile
-
Input Column: 26. model.amazon_ads_source.stg_amazon_ads__profile_tmp[id]
- Cleaning: The column is cast to TEXT and renamed to profile_id Output Columns:
- profile_id: Directly mapped after casting to TEXT
7. model.amazon_ads.amazon_ads__search_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[profile_id]
- Integration: used as a key to join with the campaigns table
- Direct: directly copied to the output Output Columns:
- profile_id: directly copied
Column Lineage
21. model.amazon_ads_source.stg_amazon_ads__portfolio_history
-
Input Column: 22. model.amazon_ads_source.stg_amazon_ads__portfolio_history_tmp[name]
- Direct: Copied without transformation Output Columns:
- portfolio_name: Directly copied and renamed
8. model.amazon_ads.int_amazon_ads__portfolio_history
-
Input Column: 21. model.amazon_ads_source.stg_amazon_ads__portfolio_history[portfolio_name]
- Direct: This column is directly copied without any transformation Output Columns:
- portfolio_name: Directly copied
7. model.amazon_ads.amazon_ads__search_report
-
Input Column: 8. model.amazon_ads.int_amazon_ads__portfolio_history[portfolio_name]
- Direct: directly copied to the output Output Columns:
- portfolio_name: directly copied
Column Lineage
21. model.amazon_ads_source.stg_amazon_ads__portfolio_history
-
Input Column: 22. model.amazon_ads_source.stg_amazon_ads__portfolio_history_tmp[id]
- Cleaning: Cast to TEXT data type Output Columns:
- portfolio_id: Directly mapped after casting
8. model.amazon_ads.int_amazon_ads__portfolio_history
-
Input Column: 21. model.amazon_ads_source.stg_amazon_ads__portfolio_history[portfolio_id]
- Direct: This column is directly copied without any transformation Output Columns:
- portfolio_id: Directly copied
7. model.amazon_ads.amazon_ads__search_report
-
Input Column: 8. model.amazon_ads.int_amazon_ads__portfolio_history[portfolio_id]
- Integration: used as a key to join with campaigns table
- Direct: directly copied to the output Output Columns:
- portfolio_id: directly copied
Column Lineage
15. model.amazon_ads_source.stg_amazon_ads__campaign_history
-
Input Column: 16. model.amazon_ads_source.stg_amazon_ads__campaign_history_tmp[name]
- Direct: Copied without transformation Output Columns:
- campaign_name: Directly copied and renamed
7. model.amazon_ads.amazon_ads__search_report
-
Input Column: 15. model.amazon_ads_source.stg_amazon_ads__campaign_history[campaign_name]
- Direct: The column is directly copied without any transformation Output Columns:
- campaign_name: Directly copied
Column Lineage
15. model.amazon_ads_source.stg_amazon_ads__campaign_history
-
Input Column: 16. model.amazon_ads_source.stg_amazon_ads__campaign_history_tmp[id]
- Cleaning: Cast to TEXT data type
- Deduplication: Used in the window function to identify the most recent record Output Columns:
- campaign_id: Directly mapped after casting to TEXT
27. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report
-
Input Column: 28. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report_tmp[campaign_id]
- Cleaning: The column is cast to TEXT data type Output Columns:
- campaign_id: Directly mapped after casting
7. model.amazon_ads.amazon_ads__search_report
-
Input Column: 15. model.amazon_ads_source.stg_amazon_ads__campaign_history[campaign_id]
- Direct: The column is directly copied without any transformation
- Integration: Used as a key to join with other tables Output Columns:
- campaign_id: Directly copied
-
Input Column: 27. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report[campaign_id]
- Direct: directly copied to the output
- Integration: used to join with campaigns table Output Columns:
- campaign_id: directly copied
Column Lineage
9. model.amazon_ads_source.stg_amazon_ads__ad_group_history
-
Input Column: 10. model.amazon_ads_source.stg_amazon_ads__ad_group_history_tmp[name]
- Cleaning: cast to TEXT data type Output Columns:
- ad_group_name: directly copied after casting and renaming
7. model.amazon_ads.amazon_ads__search_report
-
Input Column: 9. model.amazon_ads_source.stg_amazon_ads__ad_group_history[ad_group_name]
- Direct: directly copied to the output Output Columns:
- ad_group_name: directly copied
Column Lineage
9. model.amazon_ads_source.stg_amazon_ads__ad_group_history
-
Input Column: 10. model.amazon_ads_source.stg_amazon_ads__ad_group_history_tmp[id]
- Cleaning: cast to TEXT data type Output Columns:
- ad_group_id: directly copied after casting
27. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report
-
Input Column: 28. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report_tmp[ad_group_id]
- Cleaning: The column is cast to TEXT data type Output Columns:
- ad_group_id: Directly mapped after casting
7. model.amazon_ads.amazon_ads__search_report
-
Input Column: 9. model.amazon_ads_source.stg_amazon_ads__ad_group_history[ad_group_id]
- Integration: used as a key to join with the report table
- Direct: directly copied to the output Output Columns:
- ad_group_id: directly copied
-
Input Column: 27. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report[ad_group_id]
- Direct: directly copied to the output
- Integration: used to join with ad_groups table Output Columns:
- ad_group_id: directly copied
Column Lineage
19. model.amazon_ads_source.stg_amazon_ads__keyword_history
-
Input Column: 20. model.amazon_ads_source.stg_amazon_ads__keyword_history_tmp[id]
- Cleaning: Cast to TEXT data type
- Deduplication: Used in the window function to identify the most recent record Output Columns:
- keyword_id: Directly mapped after casting to TEXT
27. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report
-
Input Column: 28. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report_tmp[keyword_id]
- Cleaning: The column is cast to TEXT data type Output Columns:
- keyword_id: Directly mapped after casting
7. model.amazon_ads.amazon_ads__search_report
-
Input Column: 19. model.amazon_ads_source.stg_amazon_ads__keyword_history[keyword_id]
- Integration: used to join with the report table
- Direct: directly copied to the output Output Columns:
- keyword_id: directly copied
-
Input Column: 27. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report[keyword_id]
- Direct: directly copied to the output
- Integration: used to join with keywords table Output Columns:
- keyword_id: directly copied
Column Lineage
19. model.amazon_ads_source.stg_amazon_ads__keyword_history
-
Input Column: 20. model.amazon_ads_source.stg_amazon_ads__keyword_history_tmp[keyword_text]
- Cleaning: Cast to TEXT data type Output Columns:
- keyword_text: Directly mapped after casting to TEXT
7. model.amazon_ads.amazon_ads__search_report
-
Input Column: 19. model.amazon_ads_source.stg_amazon_ads__keyword_history[keyword_text]
- Direct: directly copied to the output Output Columns:
- keyword_text: directly copied
Column Lineage
19. model.amazon_ads_source.stg_amazon_ads__keyword_history
-
Input Column: 20. model.amazon_ads_source.stg_amazon_ads__keyword_history_tmp[match_type]
- Cleaning: Cast to TEXT data type Output Columns:
- match_type: Directly mapped after casting to TEXT
7. model.amazon_ads.amazon_ads__search_report
-
Input Column: 19. model.amazon_ads_source.stg_amazon_ads__keyword_history[match_type]
- Direct: directly copied to the output Output Columns:
- match_type: directly copied
Column Lineage
19. model.amazon_ads_source.stg_amazon_ads__keyword_history
-
Input Column: 20. model.amazon_ads_source.stg_amazon_ads__keyword_history_tmp[serving_status]
- Cleaning: Cast to TEXT data type Output Columns:
- serving_status: Directly mapped after casting to TEXT
7. model.amazon_ads.amazon_ads__search_report
-
Input Column: 19. model.amazon_ads_source.stg_amazon_ads__keyword_history[serving_status]
- Direct: directly copied to the output Output Columns:
- serving_status: directly copied
Column Lineage
19. model.amazon_ads_source.stg_amazon_ads__keyword_history
-
Input Column: 20. model.amazon_ads_source.stg_amazon_ads__keyword_history_tmp[state]
- Cleaning: Cast to TEXT data type Output Columns:
- state: Directly mapped after casting to TEXT
7. model.amazon_ads.amazon_ads__search_report
-
Input Column: 19. model.amazon_ads_source.stg_amazon_ads__keyword_history[state]
- Direct: directly copied to the output Output Columns:
- state: directly copied
Column Lineage
27. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report
-
Input Column: 28. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report_tmp[search_term]
- Cleaning: The column is cast to TEXT data type Output Columns:
- search_term: Directly mapped after casting
7. model.amazon_ads.amazon_ads__search_report
-
Input Column: 27. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report[search_term]
- Direct: directly copied to the output Output Columns:
- search_term: directly copied
Column Lineage
27. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report
-
Input Column: 28. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report_tmp[targeting]
- Cleaning: The column is cast to TEXT data type Output Columns:
- targeting: Directly mapped after casting
7. model.amazon_ads.amazon_ads__search_report
-
Input Column: 27. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report[targeting]
- Direct: directly copied to the output Output Columns:
- targeting: directly copied
Column Lineage
27. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report
-
Input Column: 28. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report_tmp[cost]
- Cleaning: The column is cast to FLOAT data type Output Columns:
- cost: Directly mapped after casting
7. model.amazon_ads.amazon_ads__search_report
-
Input Column: 27. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report[cost]
- Aggregation: summed up in the final select statement Output Columns:
- cost: aggregated sum
Column Lineage
27. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report
-
Input Column: 28. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report_tmp[clicks]
- Cleaning: The column is cast to INTEGER data type Output Columns:
- clicks: Directly mapped after casting
7. model.amazon_ads.amazon_ads__search_report
-
Input Column: 27. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report[clicks]
- Aggregation: summed up in the final select statement Output Columns:
- clicks: aggregated sum
Column Lineage
27. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report
-
Input Column: 28. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report_tmp[impressions]
- Cleaning: The column is cast to INTEGER data type Output Columns:
- impressions: Directly mapped after casting
7. model.amazon_ads.amazon_ads__search_report
-
Input Column: 27. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report[impressions]
- Aggregation: summed up in the final select statement Output Columns:
- impressions: aggregated sum
8. model.amazon_ads.int_amazon_ads__portfolio_history
This SQL query selects all columns from the most recent records in the amazon_ads_source.stg_amazon_ads__portfolio_history table. It filters the data to include only the rows where 'is_most_recent_record' is True, effectively retrieving the latest snapshot of portfolio data.
Filtering
SQL Query
with portfolios as (
select
*
from TEST.PUBLIC_amazon_ads_source.stg_amazon_ads__portfolio_history
where is_most_recent_record = True
)
select *
from portfolios
| Name | Type | Comment |
|---|---|---|
| portfolio_id | text | None |
| budget_amount | integer | None |
| budget_currency_code | integer | None |
| budget_end_date | integer | None |
| budget_policy | integer | None |
| budget_start_date | integer | None |
| creation_date | text | None |
| in_budget | boolean | None |
| last_updated_date | text | None |
| portfolio_name | text | None |
| profile_id | text | None |
| serving_status | text | None |
| state | text | None |
| is_most_recent_record | boolean | None |
Column Lineage
21. model.amazon_ads_source.stg_amazon_ads__portfolio_history
-
Input Column: 22. model.amazon_ads_source.stg_amazon_ads__portfolio_history_tmp[id]
- Cleaning: Cast to TEXT data type Output Columns:
- portfolio_id: Directly mapped after casting
8. model.amazon_ads.int_amazon_ads__portfolio_history
-
Input Column: 21. model.amazon_ads_source.stg_amazon_ads__portfolio_history[portfolio_id]
- Direct: This column is directly copied without any transformation Output Columns:
- portfolio_id: Directly copied
2. model.amazon_ads.amazon_ads__ad_group_report
-
Input Column: 8. model.amazon_ads.int_amazon_ads__portfolio_history[portfolio_id]
- Integration: used as a key to join with the campaigns table
- Direct: directly copied to the output Output Columns:
- portfolio_id: directly copied
3. model.amazon_ads.amazon_ads__ad_report
-
Input Column: 8. model.amazon_ads.int_amazon_ads__portfolio_history[portfolio_id]
- Direct: The column is directly copied without any transformation
- Integration: The column is used as a key to join with the campaigns table Output Columns:
- portfolio_id: Directly copied
4. model.amazon_ads.amazon_ads__campaign_report
-
Input Column: 8. model.amazon_ads.int_amazon_ads__portfolio_history[portfolio_id]
- Integration: Used as a key to join with the campaigns table
- Direct: Directly copied to the output Output Columns:
- portfolio_id: Directly copied
5. model.amazon_ads.amazon_ads__keyword_report
-
Input Column: 8. model.amazon_ads.int_amazon_ads__portfolio_history[portfolio_id]
- Integration: Used as a key to join with the campaigns table
- Direct: Directly copied to the output Output Columns:
- portfolio_id: Directly copied
7. model.amazon_ads.amazon_ads__search_report
-
Input Column: 8. model.amazon_ads.int_amazon_ads__portfolio_history[portfolio_id]
- Integration: used as a key to join with campaigns table
- Direct: directly copied to the output Output Columns:
- portfolio_id: directly copied
Column Lineage
21. model.amazon_ads_source.stg_amazon_ads__portfolio_history
-
Input Column: 22. model.amazon_ads_source.stg_amazon_ads__portfolio_history_tmp[budget_amount]
- Direct: Copied without transformation Output Columns:
- budget_amount: Directly copied
8. model.amazon_ads.int_amazon_ads__portfolio_history
-
Input Column: 21. model.amazon_ads_source.stg_amazon_ads__portfolio_history[budget_amount]
- Direct: This column is directly copied without any transformation Output Columns:
- budget_amount: Directly copied
Column Lineage
21. model.amazon_ads_source.stg_amazon_ads__portfolio_history
-
Input Column: 22. model.amazon_ads_source.stg_amazon_ads__portfolio_history_tmp[budget_currency_code]
- Direct: Copied without transformation Output Columns:
- budget_currency_code: Directly copied
8. model.amazon_ads.int_amazon_ads__portfolio_history
-
Input Column: 21. model.amazon_ads_source.stg_amazon_ads__portfolio_history[budget_currency_code]
- Direct: This column is directly copied without any transformation Output Columns:
- budget_currency_code: Directly copied
Column Lineage
21. model.amazon_ads_source.stg_amazon_ads__portfolio_history
-
Input Column: 22. model.amazon_ads_source.stg_amazon_ads__portfolio_history_tmp[budget_end_date]
- Direct: Copied without transformation Output Columns:
- budget_end_date: Directly copied
8. model.amazon_ads.int_amazon_ads__portfolio_history
-
Input Column: 21. model.amazon_ads_source.stg_amazon_ads__portfolio_history[budget_end_date]
- Direct: This column is directly copied without any transformation Output Columns:
- budget_end_date: Directly copied
Column Lineage
21. model.amazon_ads_source.stg_amazon_ads__portfolio_history
-
Input Column: 22. model.amazon_ads_source.stg_amazon_ads__portfolio_history_tmp[budget_policy]
- Direct: Copied without transformation Output Columns:
- budget_policy: Directly copied
8. model.amazon_ads.int_amazon_ads__portfolio_history
-
Input Column: 21. model.amazon_ads_source.stg_amazon_ads__portfolio_history[budget_policy]
- Direct: This column is directly copied without any transformation Output Columns:
- budget_policy: Directly copied
Column Lineage
21. model.amazon_ads_source.stg_amazon_ads__portfolio_history
-
Input Column: 22. model.amazon_ads_source.stg_amazon_ads__portfolio_history_tmp[budget_start_date]
- Direct: Copied without transformation Output Columns:
- budget_start_date: Directly copied
8. model.amazon_ads.int_amazon_ads__portfolio_history
-
Input Column: 21. model.amazon_ads_source.stg_amazon_ads__portfolio_history[budget_start_date]
- Direct: This column is directly copied without any transformation Output Columns:
- budget_start_date: Directly copied
Column Lineage
21. model.amazon_ads_source.stg_amazon_ads__portfolio_history
-
Input Column: 22. model.amazon_ads_source.stg_amazon_ads__portfolio_history_tmp[creation_date]
- Direct: Copied without transformation Output Columns:
- creation_date: Directly copied
8. model.amazon_ads.int_amazon_ads__portfolio_history
-
Input Column: 21. model.amazon_ads_source.stg_amazon_ads__portfolio_history[creation_date]
- Direct: This column is directly copied without any transformation Output Columns:
- creation_date: Directly copied
Column Lineage
21. model.amazon_ads_source.stg_amazon_ads__portfolio_history
-
Input Column: 22. model.amazon_ads_source.stg_amazon_ads__portfolio_history_tmp[in_budget]
- Direct: Copied without transformation Output Columns:
- in_budget: Directly copied
8. model.amazon_ads.int_amazon_ads__portfolio_history
-
Input Column: 21. model.amazon_ads_source.stg_amazon_ads__portfolio_history[in_budget]
- Direct: This column is directly copied without any transformation Output Columns:
- in_budget: Directly copied
Column Lineage
21. model.amazon_ads_source.stg_amazon_ads__portfolio_history
-
Input Column: 22. model.amazon_ads_source.stg_amazon_ads__portfolio_history_tmp[last_updated_date]
- Direct: Copied without transformation
- Deduplication: Used in window function to determine the most recent record Output Columns:
- last_updated_date: Directly copied
- is_most_recent_record: Used in window function to determine this
8. model.amazon_ads.int_amazon_ads__portfolio_history
-
Input Column: 21. model.amazon_ads_source.stg_amazon_ads__portfolio_history[last_updated_date]
- Direct: This column is directly copied without any transformation Output Columns:
- last_updated_date: Directly copied
Column Lineage
21. model.amazon_ads_source.stg_amazon_ads__portfolio_history
-
Input Column: 22. model.amazon_ads_source.stg_amazon_ads__portfolio_history_tmp[name]
- Direct: Copied without transformation Output Columns:
- portfolio_name: Directly copied and renamed
8. model.amazon_ads.int_amazon_ads__portfolio_history
-
Input Column: 21. model.amazon_ads_source.stg_amazon_ads__portfolio_history[portfolio_name]
- Direct: This column is directly copied without any transformation Output Columns:
- portfolio_name: Directly copied
2. model.amazon_ads.amazon_ads__ad_group_report
-
Input Column: 8. model.amazon_ads.int_amazon_ads__portfolio_history[portfolio_name]
- Direct: directly copied to the output Output Columns:
- portfolio_name: directly copied
3. model.amazon_ads.amazon_ads__ad_report
-
Input Column: 8. model.amazon_ads.int_amazon_ads__portfolio_history[portfolio_name]
- Direct: The column is directly copied without any transformation Output Columns:
- portfolio_name: Directly copied
4. model.amazon_ads.amazon_ads__campaign_report
-
Input Column: 8. model.amazon_ads.int_amazon_ads__portfolio_history[portfolio_name]
- Direct: Directly copied to the output Output Columns:
- portfolio_name: Directly copied
5. model.amazon_ads.amazon_ads__keyword_report
-
Input Column: 8. model.amazon_ads.int_amazon_ads__portfolio_history[portfolio_name]
- Direct: Directly copied to the output Output Columns:
- portfolio_name: Directly copied
7. model.amazon_ads.amazon_ads__search_report
-
Input Column: 8. model.amazon_ads.int_amazon_ads__portfolio_history[portfolio_name]
- Direct: directly copied to the output Output Columns:
- portfolio_name: directly copied
Column Lineage
21. model.amazon_ads_source.stg_amazon_ads__portfolio_history
-
Input Column: 22. model.amazon_ads_source.stg_amazon_ads__portfolio_history_tmp[profile_id]
- Cleaning: Cast to TEXT data type Output Columns:
- profile_id: Directly mapped after casting
8. model.amazon_ads.int_amazon_ads__portfolio_history
-
Input Column: 21. model.amazon_ads_source.stg_amazon_ads__portfolio_history[profile_id]
- Direct: This column is directly copied without any transformation Output Columns:
- profile_id: Directly copied
Column Lineage
21. model.amazon_ads_source.stg_amazon_ads__portfolio_history
-
Input Column: 22. model.amazon_ads_source.stg_amazon_ads__portfolio_history_tmp[serving_status]
- Direct: Copied without transformation Output Columns:
- serving_status: Directly copied
8. model.amazon_ads.int_amazon_ads__portfolio_history
-
Input Column: 21. model.amazon_ads_source.stg_amazon_ads__portfolio_history[serving_status]
- Direct: This column is directly copied without any transformation Output Columns:
- serving_status: Directly copied
Column Lineage
21. model.amazon_ads_source.stg_amazon_ads__portfolio_history
-
Input Column: 22. model.amazon_ads_source.stg_amazon_ads__portfolio_history_tmp[state]
- Direct: Copied without transformation Output Columns:
- state: Directly copied
8. model.amazon_ads.int_amazon_ads__portfolio_history
-
Input Column: 21. model.amazon_ads_source.stg_amazon_ads__portfolio_history[state]
- Direct: This column is directly copied without any transformation Output Columns:
- state: Directly copied
Column Lineage
21. model.amazon_ads_source.stg_amazon_ads__portfolio_history
-
Input Column: 22. model.amazon_ads_source.stg_amazon_ads__portfolio_history_tmp[last_updated_date]
- Direct: Copied without transformation
- Deduplication: Used in window function to determine the most recent record Output Columns:
- last_updated_date: Directly copied
- is_most_recent_record: Used in window function to determine this
8. model.amazon_ads.int_amazon_ads__portfolio_history
-
Input Column: 21. model.amazon_ads_source.stg_amazon_ads__portfolio_history[is_most_recent_record]
- Filtering: This column is used to filter the data to include only the most recent records Output Columns:
- is_most_recent_record: Directly copied after filtering
9. model.amazon_ads_source.stg_amazon_ads__ad_group_history
This SQL query stages data from an Amazon Ads source table, focusing on ad group history. It casts various fields to specific data types, renames some columns, and adds a source relation field. The query then selects and transforms these fields, including adding a flag to identify the most recent record for each ad group. This staging process prepares the data for further use in the data pipeline.
CleaningDeduplicationOther
SQL Query
with base as (
select *
from TEST.PUBLIC_amazon_ads_source.stg_amazon_ads__ad_group_history_tmp
),
fields as (
select
cast(null as integer) as
campaign_id
,
cast(null as timestamp) as
creation_date
,
cast(null as float) as
default_bid
,
cast(null as TEXT) as
id
,
cast(null as timestamp) as
last_updated_date
,
cast(null as TEXT) as
name
,
cast(null as TEXT) as
serving_status
,
cast(null as TEXT) as
state
, cast('' as TEXT) as source_relation
from base
),
final as (
select
source_relation,
cast(id as TEXT) as ad_group_id,
cast(campaign_id as TEXT) as campaign_id,
creation_date,
default_bid,
last_updated_date,
name as ad_group_name,
serving_status,
state,
row_number() over (partition by source_relation, id order by last_updated_date desc) = 1 as is_most_recent_record
from fields
)
select *
from final
| Name | Type | Comment |
|---|---|---|
| ad_group_id | text | None |
| campaign_id | text | None |
| creation_date | text | None |
| default_bid | double precision | None |
| last_updated_date | text | None |
| ad_group_name | text | None |
| serving_status | text | None |
| state | text | None |
| is_most_recent_record | boolean | None |
Column Lineage
9. model.amazon_ads_source.stg_amazon_ads__ad_group_history
-
Input Column: 10. model.amazon_ads_source.stg_amazon_ads__ad_group_history_tmp[id]
- Cleaning: cast to TEXT data type Output Columns:
- ad_group_id: directly copied after casting
2. model.amazon_ads.amazon_ads__ad_group_report
-
Input Column: 9. model.amazon_ads_source.stg_amazon_ads__ad_group_history[ad_group_id]
- Integration: used as a key to join with the report table
- Direct: directly copied to the output Output Columns:
- ad_group_id: directly copied
3. model.amazon_ads.amazon_ads__ad_report
-
Input Column: 9. model.amazon_ads_source.stg_amazon_ads__ad_group_history[ad_group_id]
- Integration: Used as a key to join with the report table
- Direct: Directly copied to the output Output Columns:
- ad_group_id: Directly copied
5. model.amazon_ads.amazon_ads__keyword_report
-
Input Column: 9. model.amazon_ads_source.stg_amazon_ads__ad_group_history[ad_group_id]
- Integration: used as a key to join with the report table
- Direct: directly copied to the output Output Columns:
- ad_group_id: directly copied
7. model.amazon_ads.amazon_ads__search_report
-
Input Column: 9. model.amazon_ads_source.stg_amazon_ads__ad_group_history[ad_group_id]
- Integration: used as a key to join with the report table
- Direct: directly copied to the output Output Columns:
- ad_group_id: directly copied
Column Lineage
9. model.amazon_ads_source.stg_amazon_ads__ad_group_history
-
Input Column: 10. model.amazon_ads_source.stg_amazon_ads__ad_group_history_tmp[campaign_id]
- Cleaning: cast to TEXT data type Output Columns:
- campaign_id: directly copied after casting
2. model.amazon_ads.amazon_ads__ad_group_report
-
Input Column: 9. model.amazon_ads_source.stg_amazon_ads__ad_group_history[campaign_id]
- Integration: used as a key to join with the campaigns table
5. model.amazon_ads.amazon_ads__keyword_report
-
Input Column: 9. model.amazon_ads_source.stg_amazon_ads__ad_group_history[campaign_id]
- Integration: used as a key to join with the campaigns table
7. model.amazon_ads.amazon_ads__search_report
-
Input Column: 9. model.amazon_ads_source.stg_amazon_ads__ad_group_history[campaign_id]
- Integration: used as a key to join with the campaigns table
Column Lineage
9. model.amazon_ads_source.stg_amazon_ads__ad_group_history
-
Input Column: 10. model.amazon_ads_source.stg_amazon_ads__ad_group_history_tmp[creation_date]
- Cleaning: cast to timestamp data type Output Columns:
- creation_date: directly copied after casting
Column Lineage
9. model.amazon_ads_source.stg_amazon_ads__ad_group_history
-
Input Column: 10. model.amazon_ads_source.stg_amazon_ads__ad_group_history_tmp[default_bid]
- Cleaning: cast to float data type Output Columns:
- default_bid: directly copied after casting
2. model.amazon_ads.amazon_ads__ad_group_report
-
Input Column: 9. model.amazon_ads_source.stg_amazon_ads__ad_group_history[default_bid]
- Direct: directly copied to the output Output Columns:
- default_bid: directly copied
Column Lineage
9. model.amazon_ads_source.stg_amazon_ads__ad_group_history
-
Input Column: 10. model.amazon_ads_source.stg_amazon_ads__ad_group_history_tmp[last_updated_date]
- Cleaning: cast to timestamp data type
- Deduplication: used in window function to determine the most recent record Output Columns:
- last_updated_date: directly copied after casting
- is_most_recent_record: used to determine this flag
Column Lineage
9. model.amazon_ads_source.stg_amazon_ads__ad_group_history
-
Input Column: 10. model.amazon_ads_source.stg_amazon_ads__ad_group_history_tmp[name]
- Cleaning: cast to TEXT data type Output Columns:
- ad_group_name: directly copied after casting and renaming
2. model.amazon_ads.amazon_ads__ad_group_report
-
Input Column: 9. model.amazon_ads_source.stg_amazon_ads__ad_group_history[ad_group_name]
- Direct: directly copied to the output Output Columns:
- ad_group_name: directly copied
3. model.amazon_ads.amazon_ads__ad_report
-
Input Column: 9. model.amazon_ads_source.stg_amazon_ads__ad_group_history[ad_group_name]
- Direct: Directly copied to the output Output Columns:
- ad_group_name: Directly copied
5. model.amazon_ads.amazon_ads__keyword_report
-
Input Column: 9. model.amazon_ads_source.stg_amazon_ads__ad_group_history[ad_group_name]
- Direct: directly copied to the output Output Columns:
- ad_group_name: directly copied
7. model.amazon_ads.amazon_ads__search_report
-
Input Column: 9. model.amazon_ads_source.stg_amazon_ads__ad_group_history[ad_group_name]
- Direct: directly copied to the output Output Columns:
- ad_group_name: directly copied
Column Lineage
9. model.amazon_ads_source.stg_amazon_ads__ad_group_history
-
Input Column: 10. model.amazon_ads_source.stg_amazon_ads__ad_group_history_tmp[serving_status]
- Cleaning: cast to TEXT data type Output Columns:
- serving_status: directly copied after casting
2. model.amazon_ads.amazon_ads__ad_group_report
-
Input Column: 9. model.amazon_ads_source.stg_amazon_ads__ad_group_history[serving_status]
- Direct: directly copied to the output Output Columns:
- serving_status: directly copied
Column Lineage
9. model.amazon_ads_source.stg_amazon_ads__ad_group_history
-
Input Column: 10. model.amazon_ads_source.stg_amazon_ads__ad_group_history_tmp[state]
- Cleaning: cast to TEXT data type Output Columns:
- state: directly copied after casting
2. model.amazon_ads.amazon_ads__ad_group_report
-
Input Column: 9. model.amazon_ads_source.stg_amazon_ads__ad_group_history[state]
- Direct: directly copied to the output Output Columns:
- state: directly copied
Column Lineage
9. model.amazon_ads_source.stg_amazon_ads__ad_group_history
-
Input Column: 10. model.amazon_ads_source.stg_amazon_ads__ad_group_history_tmp[last_updated_date]
- Cleaning: cast to timestamp data type
- Deduplication: used in window function to determine the most recent record Output Columns:
- last_updated_date: directly copied after casting
- is_most_recent_record: used to determine this flag
2. model.amazon_ads.amazon_ads__ad_group_report
-
Input Column: 9. model.amazon_ads_source.stg_amazon_ads__ad_group_history[is_most_recent_record]
- Filtering: used to filter for the most recent records (where is_most_recent_record = True)
3. model.amazon_ads.amazon_ads__ad_report
-
Input Column: 9. model.amazon_ads_source.stg_amazon_ads__ad_group_history[is_most_recent_record]
- Filtering: Used to filter for the most recent records (where is_most_recent_record = True)
5. model.amazon_ads.amazon_ads__keyword_report
-
Input Column: 9. model.amazon_ads_source.stg_amazon_ads__ad_group_history[is_most_recent_record]
- Filtering: the column is filtered to include only the most recent records (where is_most_recent_record = True)
7. model.amazon_ads.amazon_ads__search_report
-
Input Column: 9. model.amazon_ads_source.stg_amazon_ads__ad_group_history[is_most_recent_record]
- Filtering: the column is filtered to include only the most recent records
10. model.amazon_ads_source.stg_amazon_ads__ad_group_history_tmp
This SQL query creates an empty result set with a single column named '_dbt_source_relation' of TEXT data type. It's likely used as a placeholder or template for further development or testing purposes.
Other
SQL Query
select
cast(null as TEXT) as _dbt_source_relation
limit 0
| Name | Type | Comment |
|---|---|---|
| id | integer | None |
| last_updated_date | text | None |
| _fivetran_synced | timestamp without time zone | None |
| campaign_id | integer | None |
| creation_date | text | None |
| default_bid | double precision | None |
| name | text | None |
| serving_status | text | None |
| state | text | None |
Column Lineage
9. model.amazon_ads_source.stg_amazon_ads__ad_group_history
-
Input Column: 10. model.amazon_ads_source.stg_amazon_ads__ad_group_history_tmp[id]
- Cleaning: cast to TEXT data type Output Columns:
- ad_group_id: directly copied after casting
2. model.amazon_ads.amazon_ads__ad_group_report
-
Input Column: 9. model.amazon_ads_source.stg_amazon_ads__ad_group_history[ad_group_id]
- Integration: used as a key to join with the report table
- Direct: directly copied to the output Output Columns:
- ad_group_id: directly copied
3. model.amazon_ads.amazon_ads__ad_report
-
Input Column: 9. model.amazon_ads_source.stg_amazon_ads__ad_group_history[ad_group_id]
- Integration: Used as a key to join with the report table
- Direct: Directly copied to the output Output Columns:
- ad_group_id: Directly copied
5. model.amazon_ads.amazon_ads__keyword_report
-
Input Column: 9. model.amazon_ads_source.stg_amazon_ads__ad_group_history[ad_group_id]
- Integration: used as a key to join with the report table
- Direct: directly copied to the output Output Columns:
- ad_group_id: directly copied
7. model.amazon_ads.amazon_ads__search_report
-
Input Column: 9. model.amazon_ads_source.stg_amazon_ads__ad_group_history[ad_group_id]
- Integration: used as a key to join with the report table
- Direct: directly copied to the output Output Columns:
- ad_group_id: directly copied
Column Lineage
9. model.amazon_ads_source.stg_amazon_ads__ad_group_history
-
Input Column: 10. model.amazon_ads_source.stg_amazon_ads__ad_group_history_tmp[last_updated_date]
- Cleaning: cast to timestamp data type
- Deduplication: used in window function to determine the most recent record Output Columns:
- last_updated_date: directly copied after casting
- is_most_recent_record: used to determine this flag
2. model.amazon_ads.amazon_ads__ad_group_report
-
Input Column: 9. model.amazon_ads_source.stg_amazon_ads__ad_group_history[is_most_recent_record]
- Filtering: used to filter for the most recent records (where is_most_recent_record = True)
3. model.amazon_ads.amazon_ads__ad_report
-
Input Column: 9. model.amazon_ads_source.stg_amazon_ads__ad_group_history[is_most_recent_record]
- Filtering: Used to filter for the most recent records (where is_most_recent_record = True)
5. model.amazon_ads.amazon_ads__keyword_report
-
Input Column: 9. model.amazon_ads_source.stg_amazon_ads__ad_group_history[is_most_recent_record]
- Filtering: the column is filtered to include only the most recent records (where is_most_recent_record = True)
7. model.amazon_ads.amazon_ads__search_report
-
Input Column: 9. model.amazon_ads_source.stg_amazon_ads__ad_group_history[is_most_recent_record]
- Filtering: the column is filtered to include only the most recent records
Column Lineage
9. model.amazon_ads_source.stg_amazon_ads__ad_group_history
-
Input Column: 10. model.amazon_ads_source.stg_amazon_ads__ad_group_history_tmp[campaign_id]
- Cleaning: cast to TEXT data type Output Columns:
- campaign_id: directly copied after casting
2. model.amazon_ads.amazon_ads__ad_group_report
-
Input Column: 9. model.amazon_ads_source.stg_amazon_ads__ad_group_history[campaign_id]
- Integration: used as a key to join with the campaigns table
5. model.amazon_ads.amazon_ads__keyword_report
-
Input Column: 9. model.amazon_ads_source.stg_amazon_ads__ad_group_history[campaign_id]
- Integration: used as a key to join with the campaigns table
7. model.amazon_ads.amazon_ads__search_report
-
Input Column: 9. model.amazon_ads_source.stg_amazon_ads__ad_group_history[campaign_id]
- Integration: used as a key to join with the campaigns table
Column Lineage
9. model.amazon_ads_source.stg_amazon_ads__ad_group_history
-
Input Column: 10. model.amazon_ads_source.stg_amazon_ads__ad_group_history_tmp[creation_date]
- Cleaning: cast to timestamp data type Output Columns:
- creation_date: directly copied after casting
Column Lineage
9. model.amazon_ads_source.stg_amazon_ads__ad_group_history
-
Input Column: 10. model.amazon_ads_source.stg_amazon_ads__ad_group_history_tmp[default_bid]
- Cleaning: cast to float data type Output Columns:
- default_bid: directly copied after casting
2. model.amazon_ads.amazon_ads__ad_group_report
-
Input Column: 9. model.amazon_ads_source.stg_amazon_ads__ad_group_history[default_bid]
- Direct: directly copied to the output Output Columns:
- default_bid: directly copied
Column Lineage
9. model.amazon_ads_source.stg_amazon_ads__ad_group_history
-
Input Column: 10. model.amazon_ads_source.stg_amazon_ads__ad_group_history_tmp[name]
- Cleaning: cast to TEXT data type Output Columns:
- ad_group_name: directly copied after casting and renaming
2. model.amazon_ads.amazon_ads__ad_group_report
-
Input Column: 9. model.amazon_ads_source.stg_amazon_ads__ad_group_history[ad_group_name]
- Direct: directly copied to the output Output Columns:
- ad_group_name: directly copied
3. model.amazon_ads.amazon_ads__ad_report
-
Input Column: 9. model.amazon_ads_source.stg_amazon_ads__ad_group_history[ad_group_name]
- Direct: Directly copied to the output Output Columns:
- ad_group_name: Directly copied
5. model.amazon_ads.amazon_ads__keyword_report
-
Input Column: 9. model.amazon_ads_source.stg_amazon_ads__ad_group_history[ad_group_name]
- Direct: directly copied to the output Output Columns:
- ad_group_name: directly copied
7. model.amazon_ads.amazon_ads__search_report
-
Input Column: 9. model.amazon_ads_source.stg_amazon_ads__ad_group_history[ad_group_name]
- Direct: directly copied to the output Output Columns:
- ad_group_name: directly copied
Column Lineage
9. model.amazon_ads_source.stg_amazon_ads__ad_group_history
-
Input Column: 10. model.amazon_ads_source.stg_amazon_ads__ad_group_history_tmp[serving_status]
- Cleaning: cast to TEXT data type Output Columns:
- serving_status: directly copied after casting
2. model.amazon_ads.amazon_ads__ad_group_report
-
Input Column: 9. model.amazon_ads_source.stg_amazon_ads__ad_group_history[serving_status]
- Direct: directly copied to the output Output Columns:
- serving_status: directly copied
Column Lineage
9. model.amazon_ads_source.stg_amazon_ads__ad_group_history
-
Input Column: 10. model.amazon_ads_source.stg_amazon_ads__ad_group_history_tmp[state]
- Cleaning: cast to TEXT data type Output Columns:
- state: directly copied after casting
2. model.amazon_ads.amazon_ads__ad_group_report
-
Input Column: 9. model.amazon_ads_source.stg_amazon_ads__ad_group_history[state]
- Direct: directly copied to the output Output Columns:
- state: directly copied
11. model.amazon_ads_source.stg_amazon_ads__ad_group_level_report
This SQL query performs data type casting and column renaming for a staging table. It selects data from a temporary table, casts columns to specific data types (integer, text, float, date), and renames the 'date' column to 'date_day'. The query also adds a 'source_relation' column and casts the 'ad_group_id' to TEXT in the final selection.
CleaningOther
SQL Query
with base as (
select *
from TEST.PUBLIC_amazon_ads_source.stg_amazon_ads__ad_group_level_report_tmp
),
fields as (
select
cast(null as integer) as
ad_group_id
,
cast(null as TEXT) as
campaign_bidding_strategy
,
cast(null as integer) as
clicks
,
cast(null as float) as
cost
,
cast(null as date) as
date
,
cast(null as integer) as
impressions
, cast('' as TEXT) as source_relation
from base
),
final as (
select
source_relation,
cast(ad_group_id as TEXT) as ad_group_id,
campaign_bidding_strategy,
clicks,
cost,
date as date_day,
impressions
from fields
)
select *
from final
| Name | Type | Comment |
|---|---|---|
| ad_group_id | text | None |
| campaign_bidding_strategy | text | None |
| clicks | integer | None |
| cost | double precision | None |
| date_day | date | None |
| impressions | integer | None |
Column Lineage
11. model.amazon_ads_source.stg_amazon_ads__ad_group_level_report
-
Input Column: 12. model.amazon_ads_source.stg_amazon_ads__ad_group_level_report_tmp[ad_group_id]
- Cleaning: The column is cast to TEXT in the final selection Output Columns:
- ad_group_id: Directly mapped after casting to TEXT
2. model.amazon_ads.amazon_ads__ad_group_report
-
Input Column: 11. model.amazon_ads_source.stg_amazon_ads__ad_group_level_report[ad_group_id]
- Integration: used as a key to join with the ad_groups table
- Aggregation: used in the GROUP BY clause Output Columns:
- ad_group_id: directly copied
Column Lineage
11. model.amazon_ads_source.stg_amazon_ads__ad_group_level_report
-
Input Column: 12. model.amazon_ads_source.stg_amazon_ads__ad_group_level_report_tmp[campaign_bidding_strategy]
- Cleaning: The column is cast to TEXT Output Columns:
- campaign_bidding_strategy: Directly mapped after casting to TEXT
2. model.amazon_ads.amazon_ads__ad_group_report
-
Input Column: 11. model.amazon_ads_source.stg_amazon_ads__ad_group_level_report[campaign_bidding_strategy]
- Direct: directly selected in the output without transformation
- Aggregation: used in the GROUP BY clause Output Columns:
- campaign_bidding_strategy: directly copied
Column Lineage
11. model.amazon_ads_source.stg_amazon_ads__ad_group_level_report
-
Input Column: 12. model.amazon_ads_source.stg_amazon_ads__ad_group_level_report_tmp[clicks]
- Cleaning: The column is cast to INTEGER Output Columns:
- clicks: Directly mapped after casting to INTEGER
2. model.amazon_ads.amazon_ads__ad_group_report
-
Input Column: 11. model.amazon_ads_source.stg_amazon_ads__ad_group_level_report[clicks]
- Aggregation: summed up in the SELECT clause Output Columns:
- clicks: aggregated sum
Column Lineage
11. model.amazon_ads_source.stg_amazon_ads__ad_group_level_report
-
Input Column: 12. model.amazon_ads_source.stg_amazon_ads__ad_group_level_report_tmp[cost]
- Cleaning: The column is cast to FLOAT Output Columns:
- cost: Directly mapped after casting to FLOAT
2. model.amazon_ads.amazon_ads__ad_group_report
-
Input Column: 11. model.amazon_ads_source.stg_amazon_ads__ad_group_level_report[cost]
- Aggregation: summed up in the SELECT clause Output Columns:
- cost: aggregated sum
Column Lineage
11. model.amazon_ads_source.stg_amazon_ads__ad_group_level_report
-
Input Column: 12. model.amazon_ads_source.stg_amazon_ads__ad_group_level_report_tmp[date]
- Cleaning: The column is cast to DATE type and renamed Output Columns:
- date_day: Directly mapped after casting to DATE
2. model.amazon_ads.amazon_ads__ad_group_report
-
Input Column: 11. model.amazon_ads_source.stg_amazon_ads__ad_group_level_report[date_day]
- Direct: directly selected in the output without transformation
- Aggregation: used in the GROUP BY clause Output Columns:
- date_day: directly copied
Column Lineage
11. model.amazon_ads_source.stg_amazon_ads__ad_group_level_report
-
Input Column: 12. model.amazon_ads_source.stg_amazon_ads__ad_group_level_report_tmp[impressions]
- Cleaning: The column is cast to INTEGER Output Columns:
- impressions: Directly mapped after casting to INTEGER
2. model.amazon_ads.amazon_ads__ad_group_report
-
Input Column: 11. model.amazon_ads_source.stg_amazon_ads__ad_group_level_report[impressions]
- Aggregation: summed up in the SELECT clause Output Columns:
- impressions: aggregated sum
12. model.amazon_ads_source.stg_amazon_ads__ad_group_level_report_tmp
This SQL query creates an empty result set with a single column named '_dbt_source_relation' of TEXT data type. The query doesn't select any actual data; it's used to define the structure of the result set without populating it with any rows.
Other
SQL Query
select
cast(null as TEXT) as _dbt_source_relation
limit 0
| Name | Type | Comment |
|---|---|---|
| ad_group_id | integer | None |
| date | date | None |
| _fivetran_synced | timestamp without time zone | None |
| campaign_bidding_strategy | text | None |
| clicks | integer | None |
| cost | double precision | None |
| impressions | integer | None |
Column Lineage
11. model.amazon_ads_source.stg_amazon_ads__ad_group_level_report
-
Input Column: 12. model.amazon_ads_source.stg_amazon_ads__ad_group_level_report_tmp[ad_group_id]
- Cleaning: The column is cast to TEXT in the final selection Output Columns:
- ad_group_id: Directly mapped after casting to TEXT
2. model.amazon_ads.amazon_ads__ad_group_report
-
Input Column: 11. model.amazon_ads_source.stg_amazon_ads__ad_group_level_report[ad_group_id]
- Integration: used as a key to join with the ad_groups table
- Aggregation: used in the GROUP BY clause Output Columns:
- ad_group_id: directly copied
Column Lineage
11. model.amazon_ads_source.stg_amazon_ads__ad_group_level_report
-
Input Column: 12. model.amazon_ads_source.stg_amazon_ads__ad_group_level_report_tmp[date]
- Cleaning: The column is cast to DATE type and renamed Output Columns:
- date_day: Directly mapped after casting to DATE
2. model.amazon_ads.amazon_ads__ad_group_report
-
Input Column: 11. model.amazon_ads_source.stg_amazon_ads__ad_group_level_report[date_day]
- Direct: directly selected in the output without transformation
- Aggregation: used in the GROUP BY clause Output Columns:
- date_day: directly copied
Column Lineage
11. model.amazon_ads_source.stg_amazon_ads__ad_group_level_report
-
Input Column: 12. model.amazon_ads_source.stg_amazon_ads__ad_group_level_report_tmp[campaign_bidding_strategy]
- Cleaning: The column is cast to TEXT Output Columns:
- campaign_bidding_strategy: Directly mapped after casting to TEXT
2. model.amazon_ads.amazon_ads__ad_group_report
-
Input Column: 11. model.amazon_ads_source.stg_amazon_ads__ad_group_level_report[campaign_bidding_strategy]
- Direct: directly selected in the output without transformation
- Aggregation: used in the GROUP BY clause Output Columns:
- campaign_bidding_strategy: directly copied
Column Lineage
11. model.amazon_ads_source.stg_amazon_ads__ad_group_level_report
-
Input Column: 12. model.amazon_ads_source.stg_amazon_ads__ad_group_level_report_tmp[clicks]
- Cleaning: The column is cast to INTEGER Output Columns:
- clicks: Directly mapped after casting to INTEGER
2. model.amazon_ads.amazon_ads__ad_group_report
-
Input Column: 11. model.amazon_ads_source.stg_amazon_ads__ad_group_level_report[clicks]
- Aggregation: summed up in the SELECT clause Output Columns:
- clicks: aggregated sum
Column Lineage
11. model.amazon_ads_source.stg_amazon_ads__ad_group_level_report
-
Input Column: 12. model.amazon_ads_source.stg_amazon_ads__ad_group_level_report_tmp[cost]
- Cleaning: The column is cast to FLOAT Output Columns:
- cost: Directly mapped after casting to FLOAT
2. model.amazon_ads.amazon_ads__ad_group_report
-
Input Column: 11. model.amazon_ads_source.stg_amazon_ads__ad_group_level_report[cost]
- Aggregation: summed up in the SELECT clause Output Columns:
- cost: aggregated sum
Column Lineage
11. model.amazon_ads_source.stg_amazon_ads__ad_group_level_report
-
Input Column: 12. model.amazon_ads_source.stg_amazon_ads__ad_group_level_report_tmp[impressions]
- Cleaning: The column is cast to INTEGER Output Columns:
- impressions: Directly mapped after casting to INTEGER
2. model.amazon_ads.amazon_ads__ad_group_report
-
Input Column: 11. model.amazon_ads_source.stg_amazon_ads__ad_group_level_report[impressions]
- Aggregation: summed up in the SELECT clause Output Columns:
- impressions: aggregated sum
13. model.amazon_ads_source.stg_amazon_ads__advertised_product_report
This SQL query performs data cleaning and type casting operations on the 'stg_amazon_ads__advertised_product_report_tmp' table. It selects all columns from the base table, explicitly casts them to specific data types, and renames some columns. The query also adds a 'source_relation' column and performs final type casting on certain fields before selecting all columns in the final output.
Cleaning
SQL Query
with base as (
select *
from TEST.PUBLIC_amazon_ads_source.stg_amazon_ads__advertised_product_report_tmp
),
fields as (
select
cast(null as integer) as
ad_group_id
,
cast(null as integer) as
ad_id
,
cast(null as TEXT) as
advertised_asin
,
cast(null as TEXT) as
advertised_sku
,
cast(null as float) as
campaign_budget_amount
,
cast(null as TEXT) as
campaign_budget_currency_code
,
cast(null as TEXT) as
campaign_budget_type
,
cast(null as integer) as
campaign_id
,
cast(null as integer) as
clicks
,
cast(null as float) as
cost
,
cast(null as date) as
date
,
cast(null as integer) as
impressions
, cast('' as TEXT) as source_relation
from base
),
final as (
select
source_relation,
cast(ad_id as TEXT) as ad_id,
cast(ad_group_id as TEXT) as ad_group_id,
advertised_asin,
advertised_sku,
campaign_budget_amount,
campaign_budget_currency_code,
campaign_budget_type,
cast(campaign_id as TEXT) as campaign_id,
clicks,
cost,
date as date_day,
impressions
from fields
)
select *
from final
| Name | Type | Comment |
|---|---|---|
| ad_id | text | None |
| ad_group_id | text | None |
| advertised_asin | integer | None |
| advertised_sku | integer | None |
| campaign_budget_amount | double precision | None |
| campaign_budget_currency_code | text | None |
| campaign_budget_type | text | None |
| campaign_id | text | None |
| clicks | integer | None |
| cost | double precision | None |
| date_day | date | None |
| impressions | integer | None |
Column Lineage
13. model.amazon_ads_source.stg_amazon_ads__advertised_product_report
-
Input Column: 14. model.amazon_ads_source.stg_amazon_ads__advertised_product_report_tmp[ad_id]
- Cleaning: The column is cast to TEXT type Output Columns:
- ad_id: Directly mapped after casting
3. model.amazon_ads.amazon_ads__ad_report
-
Input Column: 13. model.amazon_ads_source.stg_amazon_ads__advertised_product_report[ad_id]
- Direct: The column is directly copied without any transformation
- Integration: Used as a key to join with the ads table Output Columns:
- ad_id: Directly copied
Column Lineage
13. model.amazon_ads_source.stg_amazon_ads__advertised_product_report
-
Input Column: 14. model.amazon_ads_source.stg_amazon_ads__advertised_product_report_tmp[ad_group_id]
- Cleaning: The column is cast to TEXT type Output Columns:
- ad_group_id: Directly mapped after casting
3. model.amazon_ads.amazon_ads__ad_report
-
Input Column: 13. model.amazon_ads_source.stg_amazon_ads__advertised_product_report[ad_group_id]
- Direct: The column is directly copied without any transformation
- Integration: Used as a key to join with the ad_groups table Output Columns:
- ad_group_id: Directly copied
Column Lineage
13. model.amazon_ads_source.stg_amazon_ads__advertised_product_report
-
Input Column: 14. model.amazon_ads_source.stg_amazon_ads__advertised_product_report_tmp[advertised_asin]
- Cleaning: The column is cast to TEXT type Output Columns:
- advertised_asin: Directly mapped after casting
3. model.amazon_ads.amazon_ads__ad_report
-
Input Column: 13. model.amazon_ads_source.stg_amazon_ads__advertised_product_report[advertised_asin]
- Direct: The column is directly copied without any transformation Output Columns:
- advertised_asin: Directly copied
Column Lineage
13. model.amazon_ads_source.stg_amazon_ads__advertised_product_report
-
Input Column: 14. model.amazon_ads_source.stg_amazon_ads__advertised_product_report_tmp[advertised_sku]
- Cleaning: The column is cast to TEXT type Output Columns:
- advertised_sku: Directly mapped after casting
3. model.amazon_ads.amazon_ads__ad_report
-
Input Column: 13. model.amazon_ads_source.stg_amazon_ads__advertised_product_report[advertised_sku]
- Direct: The column is directly copied without any transformation Output Columns:
- advertised_sku: Directly copied
Column Lineage
13. model.amazon_ads_source.stg_amazon_ads__advertised_product_report
-
Input Column: 14. model.amazon_ads_source.stg_amazon_ads__advertised_product_report_tmp[campaign_budget_amount]
- Cleaning: The column is cast to FLOAT type Output Columns:
- campaign_budget_amount: Directly mapped after casting
3. model.amazon_ads.amazon_ads__ad_report
-
Input Column: 13. model.amazon_ads_source.stg_amazon_ads__advertised_product_report[campaign_budget_amount]
- Direct: The column is directly copied without any transformation Output Columns:
- campaign_budget_amount: Directly copied
Column Lineage
13. model.amazon_ads_source.stg_amazon_ads__advertised_product_report
-
Input Column: 14. model.amazon_ads_source.stg_amazon_ads__advertised_product_report_tmp[campaign_budget_currency_code]
- Cleaning: The column is cast to TEXT type Output Columns:
- campaign_budget_currency_code: Directly mapped after casting
3. model.amazon_ads.amazon_ads__ad_report
-
Input Column: 13. model.amazon_ads_source.stg_amazon_ads__advertised_product_report[campaign_budget_currency_code]
- Direct: The column is directly copied without any transformation Output Columns:
- campaign_budget_currency_code: Directly copied
Column Lineage
13. model.amazon_ads_source.stg_amazon_ads__advertised_product_report
-
Input Column: 14. model.amazon_ads_source.stg_amazon_ads__advertised_product_report_tmp[campaign_budget_type]
- Cleaning: The column is cast to TEXT type Output Columns:
- campaign_budget_type: Directly mapped after casting
3. model.amazon_ads.amazon_ads__ad_report
-
Input Column: 13. model.amazon_ads_source.stg_amazon_ads__advertised_product_report[campaign_budget_type]
- Direct: The column is directly copied without any transformation Output Columns:
- campaign_budget_type: Directly copied
Column Lineage
13. model.amazon_ads_source.stg_amazon_ads__advertised_product_report
-
Input Column: 14. model.amazon_ads_source.stg_amazon_ads__advertised_product_report_tmp[campaign_id]
- Cleaning: The column is cast to TEXT type Output Columns:
- campaign_id: Directly mapped after casting
3. model.amazon_ads.amazon_ads__ad_report
-
Input Column: 13. model.amazon_ads_source.stg_amazon_ads__advertised_product_report[campaign_id]
- Direct: The column is directly copied without any transformation
- Integration: Used as a key to join with the campaigns table Output Columns:
- campaign_id: Directly copied
Column Lineage
13. model.amazon_ads_source.stg_amazon_ads__advertised_product_report
-
Input Column: 14. model.amazon_ads_source.stg_amazon_ads__advertised_product_report_tmp[clicks]
- Cleaning: The column is cast to INTEGER type Output Columns:
- clicks: Directly mapped after casting
3. model.amazon_ads.amazon_ads__ad_report
-
Input Column: 13. model.amazon_ads_source.stg_amazon_ads__advertised_product_report[clicks]
- Aggregation: The column is summed up in the group by clause Output Columns:
- clicks: Aggregated sum
Column Lineage
13. model.amazon_ads_source.stg_amazon_ads__advertised_product_report
-
Input Column: 14. model.amazon_ads_source.stg_amazon_ads__advertised_product_report_tmp[cost]
- Cleaning: The column is cast to FLOAT type Output Columns:
- cost: Directly mapped after casting
3. model.amazon_ads.amazon_ads__ad_report
-
Input Column: 13. model.amazon_ads_source.stg_amazon_ads__advertised_product_report[cost]
- Aggregation: The column is summed up in the group by clause Output Columns:
- cost: Aggregated sum
Column Lineage
13. model.amazon_ads_source.stg_amazon_ads__advertised_product_report
-
Input Column: 14. model.amazon_ads_source.stg_amazon_ads__advertised_product_report_tmp[date]
- Cleaning: The column is cast to DATE type Output Columns:
- date_day: Directly mapped after casting
3. model.amazon_ads.amazon_ads__ad_report
-
Input Column: 13. model.amazon_ads_source.stg_amazon_ads__advertised_product_report[date_day]
- Direct: The column is directly copied without any transformation
- Aggregation: Used in the group by clause Output Columns:
- date_day: Directly copied
Column Lineage
13. model.amazon_ads_source.stg_amazon_ads__advertised_product_report
-
Input Column: 14. model.amazon_ads_source.stg_amazon_ads__advertised_product_report_tmp[impressions]
- Cleaning: The column is cast to INTEGER type Output Columns:
- impressions: Directly mapped after casting
3. model.amazon_ads.amazon_ads__ad_report
-
Input Column: 13. model.amazon_ads_source.stg_amazon_ads__advertised_product_report[impressions]
- Aggregation: The column is summed up in the group by clause Output Columns:
- impressions: Aggregated sum
14. model.amazon_ads_source.stg_amazon_ads__advertised_product_report_tmp
This SQL query creates an empty result set with a single column named '_dbt_source_relation' of type TEXT. It doesn't perform any actual data manipulation or retrieval from the mentioned model. The query is likely used as a placeholder or template for further development.
Other
SQL Query
select
cast(null as TEXT) as _dbt_source_relation
limit 0
| Name | Type | Comment |
|---|---|---|
| ad_group_id | integer | None |
| ad_id | integer | None |
| campaign_id | integer | None |
| date | date | None |
| _fivetran_synced | timestamp without time zone | None |
| campaign_budget_amount | double precision | None |
| campaign_budget_currency_code | text | None |
| campaign_budget_type | text | None |
| clicks | integer | None |
| cost | double precision | None |
| impressions | integer | None |
| advertised_asin | integer | None |
| advertised_sku | integer | None |
Column Lineage
13. model.amazon_ads_source.stg_amazon_ads__advertised_product_report
-
Input Column: 14. model.amazon_ads_source.stg_amazon_ads__advertised_product_report_tmp[ad_group_id]
- Cleaning: The column is cast to TEXT type Output Columns:
- ad_group_id: Directly mapped after casting
3. model.amazon_ads.amazon_ads__ad_report
-
Input Column: 13. model.amazon_ads_source.stg_amazon_ads__advertised_product_report[ad_group_id]
- Direct: The column is directly copied without any transformation
- Integration: Used as a key to join with the ad_groups table Output Columns:
- ad_group_id: Directly copied
Column Lineage
13. model.amazon_ads_source.stg_amazon_ads__advertised_product_report
-
Input Column: 14. model.amazon_ads_source.stg_amazon_ads__advertised_product_report_tmp[ad_id]
- Cleaning: The column is cast to TEXT type Output Columns:
- ad_id: Directly mapped after casting
3. model.amazon_ads.amazon_ads__ad_report
-
Input Column: 13. model.amazon_ads_source.stg_amazon_ads__advertised_product_report[ad_id]
- Direct: The column is directly copied without any transformation
- Integration: Used as a key to join with the ads table Output Columns:
- ad_id: Directly copied
Column Lineage
13. model.amazon_ads_source.stg_amazon_ads__advertised_product_report
-
Input Column: 14. model.amazon_ads_source.stg_amazon_ads__advertised_product_report_tmp[campaign_id]
- Cleaning: The column is cast to TEXT type Output Columns:
- campaign_id: Directly mapped after casting
3. model.amazon_ads.amazon_ads__ad_report
-
Input Column: 13. model.amazon_ads_source.stg_amazon_ads__advertised_product_report[campaign_id]
- Direct: The column is directly copied without any transformation
- Integration: Used as a key to join with the campaigns table Output Columns:
- campaign_id: Directly copied
Column Lineage
13. model.amazon_ads_source.stg_amazon_ads__advertised_product_report
-
Input Column: 14. model.amazon_ads_source.stg_amazon_ads__advertised_product_report_tmp[date]
- Cleaning: The column is cast to DATE type Output Columns:
- date_day: Directly mapped after casting
3. model.amazon_ads.amazon_ads__ad_report
-
Input Column: 13. model.amazon_ads_source.stg_amazon_ads__advertised_product_report[date_day]
- Direct: The column is directly copied without any transformation
- Aggregation: Used in the group by clause Output Columns:
- date_day: Directly copied
Column Lineage
13. model.amazon_ads_source.stg_amazon_ads__advertised_product_report
-
Input Column: 14. model.amazon_ads_source.stg_amazon_ads__advertised_product_report_tmp[campaign_budget_amount]
- Cleaning: The column is cast to FLOAT type Output Columns:
- campaign_budget_amount: Directly mapped after casting
3. model.amazon_ads.amazon_ads__ad_report
-
Input Column: 13. model.amazon_ads_source.stg_amazon_ads__advertised_product_report[campaign_budget_amount]
- Direct: The column is directly copied without any transformation Output Columns:
- campaign_budget_amount: Directly copied
Column Lineage
13. model.amazon_ads_source.stg_amazon_ads__advertised_product_report
-
Input Column: 14. model.amazon_ads_source.stg_amazon_ads__advertised_product_report_tmp[campaign_budget_currency_code]
- Cleaning: The column is cast to TEXT type Output Columns:
- campaign_budget_currency_code: Directly mapped after casting
3. model.amazon_ads.amazon_ads__ad_report
-
Input Column: 13. model.amazon_ads_source.stg_amazon_ads__advertised_product_report[campaign_budget_currency_code]
- Direct: The column is directly copied without any transformation Output Columns:
- campaign_budget_currency_code: Directly copied
Column Lineage
13. model.amazon_ads_source.stg_amazon_ads__advertised_product_report
-
Input Column: 14. model.amazon_ads_source.stg_amazon_ads__advertised_product_report_tmp[campaign_budget_type]
- Cleaning: The column is cast to TEXT type Output Columns:
- campaign_budget_type: Directly mapped after casting
3. model.amazon_ads.amazon_ads__ad_report
-
Input Column: 13. model.amazon_ads_source.stg_amazon_ads__advertised_product_report[campaign_budget_type]
- Direct: The column is directly copied without any transformation Output Columns:
- campaign_budget_type: Directly copied
Column Lineage
13. model.amazon_ads_source.stg_amazon_ads__advertised_product_report
-
Input Column: 14. model.amazon_ads_source.stg_amazon_ads__advertised_product_report_tmp[clicks]
- Cleaning: The column is cast to INTEGER type Output Columns:
- clicks: Directly mapped after casting
3. model.amazon_ads.amazon_ads__ad_report
-
Input Column: 13. model.amazon_ads_source.stg_amazon_ads__advertised_product_report[clicks]
- Aggregation: The column is summed up in the group by clause Output Columns:
- clicks: Aggregated sum
Column Lineage
13. model.amazon_ads_source.stg_amazon_ads__advertised_product_report
-
Input Column: 14. model.amazon_ads_source.stg_amazon_ads__advertised_product_report_tmp[cost]
- Cleaning: The column is cast to FLOAT type Output Columns:
- cost: Directly mapped after casting
3. model.amazon_ads.amazon_ads__ad_report
-
Input Column: 13. model.amazon_ads_source.stg_amazon_ads__advertised_product_report[cost]
- Aggregation: The column is summed up in the group by clause Output Columns:
- cost: Aggregated sum
Column Lineage
13. model.amazon_ads_source.stg_amazon_ads__advertised_product_report
-
Input Column: 14. model.amazon_ads_source.stg_amazon_ads__advertised_product_report_tmp[impressions]
- Cleaning: The column is cast to INTEGER type Output Columns:
- impressions: Directly mapped after casting
3. model.amazon_ads.amazon_ads__ad_report
-
Input Column: 13. model.amazon_ads_source.stg_amazon_ads__advertised_product_report[impressions]
- Aggregation: The column is summed up in the group by clause Output Columns:
- impressions: Aggregated sum
Column Lineage
13. model.amazon_ads_source.stg_amazon_ads__advertised_product_report
-
Input Column: 14. model.amazon_ads_source.stg_amazon_ads__advertised_product_report_tmp[advertised_asin]
- Cleaning: The column is cast to TEXT type Output Columns:
- advertised_asin: Directly mapped after casting
3. model.amazon_ads.amazon_ads__ad_report
-
Input Column: 13. model.amazon_ads_source.stg_amazon_ads__advertised_product_report[advertised_asin]
- Direct: The column is directly copied without any transformation Output Columns:
- advertised_asin: Directly copied
Column Lineage
13. model.amazon_ads_source.stg_amazon_ads__advertised_product_report
-
Input Column: 14. model.amazon_ads_source.stg_amazon_ads__advertised_product_report_tmp[advertised_sku]
- Cleaning: The column is cast to TEXT type Output Columns:
- advertised_sku: Directly mapped after casting
3. model.amazon_ads.amazon_ads__ad_report
-
Input Column: 13. model.amazon_ads_source.stg_amazon_ads__advertised_product_report[advertised_sku]
- Direct: The column is directly copied without any transformation Output Columns:
- advertised_sku: Directly copied
15. model.amazon_ads_source.stg_amazon_ads__campaign_history
This SQL query stages data from an Amazon Ads campaign history source. It casts various fields to specific data types, renames some columns, and adds a source relation column. The query also includes logic to identify the most recent record for each campaign using a window function. The final output includes all transformed columns along with a boolean flag indicating if each record is the most recent for that campaign.
CleaningDeduplicationOther
SQL Query
with base as (
select *
from TEST.PUBLIC_amazon_ads_source.stg_amazon_ads__campaign_history_tmp
),
fields as (
select
cast(null as TEXT) as
bidding_strategy
,
cast(null as timestamp) as
creation_date
,
cast(null as date) as
end_date
,
cast(null as TEXT) as
id
,
cast(null as timestamp) as
last_updated_date
,
cast(null as TEXT) as
name
,
cast(null as integer) as
portfolio_id
,
cast(null as integer) as
profile_id
,
cast(null as TEXT) as
serving_status
,
cast(null as date) as
start_date
,
cast(null as TEXT) as
state
,
cast(null as TEXT) as
targeting_type
,
cast(null as float) as
budget
,
cast(null as TEXT) as
budget_type
,
cast(null as float) as
effective_budget
, cast('' as TEXT) as source_relation
from base
),
final as (
select
source_relation,
cast(id as TEXT) as campaign_id,
last_updated_date,
bidding_strategy,
creation_date,
end_date,
name as campaign_name,
cast(portfolio_id as TEXT) as portfolio_id,
cast(profile_id as TEXT) as profile_id,
serving_status,
start_date,
state,
targeting_type,
budget,
budget_type,
effective_budget,
row_number() over (partition by source_relation, id order by last_updated_date desc) = 1 as is_most_recent_record
from fields
)
select *
from final
| Name | Type | Comment |
|---|---|---|
| campaign_id | text | None |
| last_updated_date | text | None |
| bidding_strategy | text | None |
| creation_date | text | None |
| end_date | integer | None |
| campaign_name | text | None |
| portfolio_id | text | None |
| profile_id | text | None |
| serving_status | text | None |
| start_date | date | None |
| state | text | None |
| targeting_type | text | None |
| budget | integer | None |
| budget_type | text | None |
| effective_budget | integer | None |
| is_most_recent_record | boolean | None |
Column Lineage
15. model.amazon_ads_source.stg_amazon_ads__campaign_history
-
Input Column: 16. model.amazon_ads_source.stg_amazon_ads__campaign_history_tmp[id]
- Cleaning: Cast to TEXT data type
- Deduplication: Used in the window function to identify the most recent record Output Columns:
- campaign_id: Directly mapped after casting to TEXT
1. model.amazon_ads.amazon_ads__account_report
-
Input Column: 15. model.amazon_ads_source.stg_amazon_ads__campaign_history[campaign_id]
- Integration: used as a key to join with the report table
2. model.amazon_ads.amazon_ads__ad_group_report
-
Input Column: 15. model.amazon_ads_source.stg_amazon_ads__campaign_history[campaign_id]
- Direct: The column is directly copied without any transformation
- Integration: The column is used as a key to join with ad_groups table Output Columns:
- campaign_id: Directly copied
3. model.amazon_ads.amazon_ads__ad_report
-
Input Column: 15. model.amazon_ads_source.stg_amazon_ads__campaign_history[campaign_id]
- Integration: used to join with the report table
- Direct: directly copied to the output Output Columns:
- campaign_id: directly copied
4. model.amazon_ads.amazon_ads__campaign_report
-
Input Column: 15. model.amazon_ads_source.stg_amazon_ads__campaign_history[campaign_id]
- Integration: Used to join with the report table
- Direct: Directly copied to the output Output Columns:
- campaign_id: Directly copied
5. model.amazon_ads.amazon_ads__keyword_report
-
Input Column: 15. model.amazon_ads_source.stg_amazon_ads__campaign_history[campaign_id]
- Integration: used as a key to join with the report table
- Direct: directly copied to the output Output Columns:
- campaign_id: directly copied
6. model.amazon_ads.amazon_ads__portfolio_report
-
Input Column: 15. model.amazon_ads_source.stg_amazon_ads__campaign_history[campaign_id]
- Integration: used to join with the report table
7. model.amazon_ads.amazon_ads__search_report
-
Input Column: 15. model.amazon_ads_source.stg_amazon_ads__campaign_history[campaign_id]
- Direct: The column is directly copied without any transformation
- Integration: Used as a key to join with other tables Output Columns:
- campaign_id: Directly copied
Column Lineage
15. model.amazon_ads_source.stg_amazon_ads__campaign_history
-
Input Column: 16. model.amazon_ads_source.stg_amazon_ads__campaign_history_tmp[last_updated_date]
- Direct: Copied without transformation
- Deduplication: Used in the window function to order and identify the most recent record Output Columns:
- last_updated_date: Directly copied
Column Lineage
15. model.amazon_ads_source.stg_amazon_ads__campaign_history
-
Input Column: 16. model.amazon_ads_source.stg_amazon_ads__campaign_history_tmp[bidding_strategy]
- Direct: Copied without transformation Output Columns:
- bidding_strategy: Directly copied
Column Lineage
15. model.amazon_ads_source.stg_amazon_ads__campaign_history
-
Input Column: 16. model.amazon_ads_source.stg_amazon_ads__campaign_history_tmp[creation_date]
- Direct: Copied without transformation Output Columns:
- creation_date: Directly copied
Column Lineage
15. model.amazon_ads_source.stg_amazon_ads__campaign_history
-
Input Column: 16. model.amazon_ads_source.stg_amazon_ads__campaign_history_tmp[end_date]
- Cleaning: Cast to date data type Output Columns:
- end_date: Directly mapped after casting to date
Column Lineage
15. model.amazon_ads_source.stg_amazon_ads__campaign_history
-
Input Column: 16. model.amazon_ads_source.stg_amazon_ads__campaign_history_tmp[name]
- Direct: Copied without transformation Output Columns:
- campaign_name: Directly copied and renamed
2. model.amazon_ads.amazon_ads__ad_group_report
-
Input Column: 15. model.amazon_ads_source.stg_amazon_ads__campaign_history[campaign_name]
- Direct: The column is directly copied without any transformation Output Columns:
- campaign_name: Directly copied
3. model.amazon_ads.amazon_ads__ad_report
-
Input Column: 15. model.amazon_ads_source.stg_amazon_ads__campaign_history[campaign_name]
- Direct: directly copied to the output Output Columns:
- campaign_name: directly copied
4. model.amazon_ads.amazon_ads__campaign_report
-
Input Column: 15. model.amazon_ads_source.stg_amazon_ads__campaign_history[campaign_name]
- Direct: Directly copied to the output Output Columns:
- campaign_name: Directly copied
5. model.amazon_ads.amazon_ads__keyword_report
-
Input Column: 15. model.amazon_ads_source.stg_amazon_ads__campaign_history[campaign_name]
- Direct: directly copied to the output Output Columns:
- campaign_name: directly copied
7. model.amazon_ads.amazon_ads__search_report
-
Input Column: 15. model.amazon_ads_source.stg_amazon_ads__campaign_history[campaign_name]
- Direct: The column is directly copied without any transformation Output Columns:
- campaign_name: Directly copied
Column Lineage
15. model.amazon_ads_source.stg_amazon_ads__campaign_history
-
Input Column: 16. model.amazon_ads_source.stg_amazon_ads__campaign_history_tmp[portfolio_id]
- Cleaning: Cast to TEXT data type Output Columns:
- portfolio_id: Directly mapped after casting to TEXT
2. model.amazon_ads.amazon_ads__ad_group_report
-
Input Column: 15. model.amazon_ads_source.stg_amazon_ads__campaign_history[portfolio_id]
- Integration: The column is used as a key to join with portfolios table
3. model.amazon_ads.amazon_ads__ad_report
-
Input Column: 15. model.amazon_ads_source.stg_amazon_ads__campaign_history[portfolio_id]
- Integration: used to join with the portfolios table
4. model.amazon_ads.amazon_ads__campaign_report
-
Input Column: 15. model.amazon_ads_source.stg_amazon_ads__campaign_history[portfolio_id]
- Integration: Used to join with the portfolios table
5. model.amazon_ads.amazon_ads__keyword_report
-
Input Column: 15. model.amazon_ads_source.stg_amazon_ads__campaign_history[portfolio_id]
- Integration: used as a key to join with the portfolios table
6. model.amazon_ads.amazon_ads__portfolio_report
-
Input Column: 15. model.amazon_ads_source.stg_amazon_ads__campaign_history[portfolio_id]
- Integration: used to join with the portfolios table
7. model.amazon_ads.amazon_ads__search_report
-
Input Column: 15. model.amazon_ads_source.stg_amazon_ads__campaign_history[portfolio_id]
- Integration: Used as a key to join with the portfolios table
Column Lineage
15. model.amazon_ads_source.stg_amazon_ads__campaign_history
-
Input Column: 16. model.amazon_ads_source.stg_amazon_ads__campaign_history_tmp[profile_id]
- Cleaning: Cast to TEXT data type Output Columns:
- profile_id: Directly mapped after casting to TEXT
1. model.amazon_ads.amazon_ads__account_report
-
Input Column: 15. model.amazon_ads_source.stg_amazon_ads__campaign_history[profile_id]
- Integration: used as a key to join with the account_info table Output Columns:
- profile_id: Directly copied to the output
2. model.amazon_ads.amazon_ads__ad_group_report
-
Input Column: 15. model.amazon_ads_source.stg_amazon_ads__campaign_history[profile_id]
- Integration: The column is used as a key to join with account_info table
3. model.amazon_ads.amazon_ads__ad_report
-
Input Column: 15. model.amazon_ads_source.stg_amazon_ads__campaign_history[profile_id]
- Integration: used to join with the account_info table
4. model.amazon_ads.amazon_ads__campaign_report
-
Input Column: 15. model.amazon_ads_source.stg_amazon_ads__campaign_history[profile_id]
- Integration: Used to join with the account_info table
5. model.amazon_ads.amazon_ads__keyword_report
-
Input Column: 15. model.amazon_ads_source.stg_amazon_ads__campaign_history[profile_id]
- Integration: used as a key to join with the account_info table
6. model.amazon_ads.amazon_ads__portfolio_report
-
Input Column: 15. model.amazon_ads_source.stg_amazon_ads__campaign_history[profile_id]
- Integration: used to join with the account_info table
7. model.amazon_ads.amazon_ads__search_report
-
Input Column: 15. model.amazon_ads_source.stg_amazon_ads__campaign_history[profile_id]
- Integration: Used as a key to join with the account_info table
Column Lineage
15. model.amazon_ads_source.stg_amazon_ads__campaign_history
-
Input Column: 16. model.amazon_ads_source.stg_amazon_ads__campaign_history_tmp[serving_status]
- Direct: Copied without transformation Output Columns:
- serving_status: Directly copied
Column Lineage
15. model.amazon_ads_source.stg_amazon_ads__campaign_history
-
Input Column: 16. model.amazon_ads_source.stg_amazon_ads__campaign_history_tmp[start_date]
- Cleaning: Cast to date data type Output Columns:
- start_date: Directly mapped after casting to date
Column Lineage
15. model.amazon_ads_source.stg_amazon_ads__campaign_history
-
Input Column: 16. model.amazon_ads_source.stg_amazon_ads__campaign_history_tmp[state]
- Direct: Copied without transformation Output Columns:
- state: Directly copied
Column Lineage
15. model.amazon_ads_source.stg_amazon_ads__campaign_history
-
Input Column: 16. model.amazon_ads_source.stg_amazon_ads__campaign_history_tmp[targeting_type]
- Direct: Copied without transformation Output Columns:
- targeting_type: Directly copied
Column Lineage
15. model.amazon_ads_source.stg_amazon_ads__campaign_history
-
Input Column: 16. model.amazon_ads_source.stg_amazon_ads__campaign_history_tmp[budget]
- Cleaning: Cast to float data type Output Columns:
- budget: Directly mapped after casting to float
Column Lineage
15. model.amazon_ads_source.stg_amazon_ads__campaign_history
-
Input Column: 16. model.amazon_ads_source.stg_amazon_ads__campaign_history_tmp[budget_type]
- Direct: Copied without transformation Output Columns:
- budget_type: Directly copied
Column Lineage
15. model.amazon_ads_source.stg_amazon_ads__campaign_history
-
Input Column: 16. model.amazon_ads_source.stg_amazon_ads__campaign_history_tmp[effective_budget]
- Cleaning: Cast to float data type Output Columns:
- effective_budget: Directly mapped after casting to float
Column Lineage
1. model.amazon_ads.amazon_ads__account_report
-
Input Column: 15. model.amazon_ads_source.stg_amazon_ads__campaign_history[is_most_recent_record]
- Filtering: the column is filtered to include only the most recent records (where is_most_recent_record = True)
2. model.amazon_ads.amazon_ads__ad_group_report
-
Input Column: 15. model.amazon_ads_source.stg_amazon_ads__campaign_history[is_most_recent_record]
- Filtering: The column is filtered to select only the most recent records (where is_most_recent_record = True)
3. model.amazon_ads.amazon_ads__ad_report
-
Input Column: 15. model.amazon_ads_source.stg_amazon_ads__campaign_history[is_most_recent_record]
- Filtering: only records where is_most_recent_record = True are included
4. model.amazon_ads.amazon_ads__campaign_report
-
Input Column: 15. model.amazon_ads_source.stg_amazon_ads__campaign_history[is_most_recent_record]
- Filtering: The query filters for the most recent record (where is_most_recent_record = True)
5. model.amazon_ads.amazon_ads__keyword_report
-
Input Column: 15. model.amazon_ads_source.stg_amazon_ads__campaign_history[is_most_recent_record]
- Filtering: used to filter for the most recent records (where is_most_recent_record = True)
6. model.amazon_ads.amazon_ads__portfolio_report
-
Input Column: 15. model.amazon_ads_source.stg_amazon_ads__campaign_history[is_most_recent_record]
- Filtering: the column is filtered to select only the most recent records
7. model.amazon_ads.amazon_ads__search_report
-
Input Column: 15. model.amazon_ads_source.stg_amazon_ads__campaign_history[is_most_recent_record]
- Filtering: The column is filtered to include only the most recent records (where is_most_recent_record = True)
16. model.amazon_ads_source.stg_amazon_ads__campaign_history_tmp
This SQL query creates an empty result set with a single column named '_dbt_source_relation' of TEXT data type. It doesn't retrieve any actual data from the 'model.amazon_ads_source.stg_amazon_ads__campaign_history_tmp' model, as it limits the results to 0 rows. This query appears to be a placeholder or template for generating a schema without any data.
Other
SQL Query
select
cast(null as TEXT) as _dbt_source_relation
limit 0
| Name | Type | Comment |
|---|---|---|
| id | integer | None |
| last_updated_date | text | None |
| _fivetran_synced | timestamp without time zone | None |
| bidding_strategy | text | None |
| creation_date | text | None |
| budget | integer | None |
| end_date | integer | None |
| name | text | None |
| portfolio_id | integer | None |
| profile_id | integer | None |
| serving_status | text | None |
| start_date | date | None |
| state | text | None |
| targeting_type | text | None |
| budget_type | text | None |
| effective_budget | integer | None |
Column Lineage
15. model.amazon_ads_source.stg_amazon_ads__campaign_history
-
Input Column: 16. model.amazon_ads_source.stg_amazon_ads__campaign_history_tmp[id]
- Cleaning: Cast to TEXT data type
- Deduplication: Used in the window function to identify the most recent record Output Columns:
- campaign_id: Directly mapped after casting to TEXT
1. model.amazon_ads.amazon_ads__account_report
-
Input Column: 15. model.amazon_ads_source.stg_amazon_ads__campaign_history[campaign_id]
- Integration: used as a key to join with the report table
2. model.amazon_ads.amazon_ads__ad_group_report
-
Input Column: 15. model.amazon_ads_source.stg_amazon_ads__campaign_history[campaign_id]
- Direct: The column is directly copied without any transformation
- Integration: The column is used as a key to join with ad_groups table Output Columns:
- campaign_id: Directly copied
3. model.amazon_ads.amazon_ads__ad_report
-
Input Column: 15. model.amazon_ads_source.stg_amazon_ads__campaign_history[campaign_id]
- Integration: used to join with the report table
- Direct: directly copied to the output Output Columns:
- campaign_id: directly copied
4. model.amazon_ads.amazon_ads__campaign_report
-
Input Column: 15. model.amazon_ads_source.stg_amazon_ads__campaign_history[campaign_id]
- Integration: Used to join with the report table
- Direct: Directly copied to the output Output Columns:
- campaign_id: Directly copied
5. model.amazon_ads.amazon_ads__keyword_report
-
Input Column: 15. model.amazon_ads_source.stg_amazon_ads__campaign_history[campaign_id]
- Integration: used as a key to join with the report table
- Direct: directly copied to the output Output Columns:
- campaign_id: directly copied
6. model.amazon_ads.amazon_ads__portfolio_report
-
Input Column: 15. model.amazon_ads_source.stg_amazon_ads__campaign_history[campaign_id]
- Integration: used to join with the report table
7. model.amazon_ads.amazon_ads__search_report
-
Input Column: 15. model.amazon_ads_source.stg_amazon_ads__campaign_history[campaign_id]
- Direct: The column is directly copied without any transformation
- Integration: Used as a key to join with other tables Output Columns:
- campaign_id: Directly copied
Column Lineage
15. model.amazon_ads_source.stg_amazon_ads__campaign_history
-
Input Column: 16. model.amazon_ads_source.stg_amazon_ads__campaign_history_tmp[last_updated_date]
- Direct: Copied without transformation
- Deduplication: Used in the window function to order and identify the most recent record Output Columns:
- last_updated_date: Directly copied
Column Lineage
15. model.amazon_ads_source.stg_amazon_ads__campaign_history
-
Input Column: 16. model.amazon_ads_source.stg_amazon_ads__campaign_history_tmp[bidding_strategy]
- Direct: Copied without transformation Output Columns:
- bidding_strategy: Directly copied
Column Lineage
15. model.amazon_ads_source.stg_amazon_ads__campaign_history
-
Input Column: 16. model.amazon_ads_source.stg_amazon_ads__campaign_history_tmp[creation_date]
- Direct: Copied without transformation Output Columns:
- creation_date: Directly copied
Column Lineage
15. model.amazon_ads_source.stg_amazon_ads__campaign_history
-
Input Column: 16. model.amazon_ads_source.stg_amazon_ads__campaign_history_tmp[budget]
- Cleaning: Cast to float data type Output Columns:
- budget: Directly mapped after casting to float
Column Lineage
15. model.amazon_ads_source.stg_amazon_ads__campaign_history
-
Input Column: 16. model.amazon_ads_source.stg_amazon_ads__campaign_history_tmp[end_date]
- Cleaning: Cast to date data type Output Columns:
- end_date: Directly mapped after casting to date
Column Lineage
15. model.amazon_ads_source.stg_amazon_ads__campaign_history
-
Input Column: 16. model.amazon_ads_source.stg_amazon_ads__campaign_history_tmp[name]
- Direct: Copied without transformation Output Columns:
- campaign_name: Directly copied and renamed
2. model.amazon_ads.amazon_ads__ad_group_report
-
Input Column: 15. model.amazon_ads_source.stg_amazon_ads__campaign_history[campaign_name]
- Direct: The column is directly copied without any transformation Output Columns:
- campaign_name: Directly copied
3. model.amazon_ads.amazon_ads__ad_report
-
Input Column: 15. model.amazon_ads_source.stg_amazon_ads__campaign_history[campaign_name]
- Direct: directly copied to the output Output Columns:
- campaign_name: directly copied
4. model.amazon_ads.amazon_ads__campaign_report
-
Input Column: 15. model.amazon_ads_source.stg_amazon_ads__campaign_history[campaign_name]
- Direct: Directly copied to the output Output Columns:
- campaign_name: Directly copied
5. model.amazon_ads.amazon_ads__keyword_report
-
Input Column: 15. model.amazon_ads_source.stg_amazon_ads__campaign_history[campaign_name]
- Direct: directly copied to the output Output Columns:
- campaign_name: directly copied
7. model.amazon_ads.amazon_ads__search_report
-
Input Column: 15. model.amazon_ads_source.stg_amazon_ads__campaign_history[campaign_name]
- Direct: The column is directly copied without any transformation Output Columns:
- campaign_name: Directly copied
Column Lineage
15. model.amazon_ads_source.stg_amazon_ads__campaign_history
-
Input Column: 16. model.amazon_ads_source.stg_amazon_ads__campaign_history_tmp[portfolio_id]
- Cleaning: Cast to TEXT data type Output Columns:
- portfolio_id: Directly mapped after casting to TEXT
2. model.amazon_ads.amazon_ads__ad_group_report
-
Input Column: 15. model.amazon_ads_source.stg_amazon_ads__campaign_history[portfolio_id]
- Integration: The column is used as a key to join with portfolios table
3. model.amazon_ads.amazon_ads__ad_report
-
Input Column: 15. model.amazon_ads_source.stg_amazon_ads__campaign_history[portfolio_id]
- Integration: used to join with the portfolios table
4. model.amazon_ads.amazon_ads__campaign_report
-
Input Column: 15. model.amazon_ads_source.stg_amazon_ads__campaign_history[portfolio_id]
- Integration: Used to join with the portfolios table
5. model.amazon_ads.amazon_ads__keyword_report
-
Input Column: 15. model.amazon_ads_source.stg_amazon_ads__campaign_history[portfolio_id]
- Integration: used as a key to join with the portfolios table
6. model.amazon_ads.amazon_ads__portfolio_report
-
Input Column: 15. model.amazon_ads_source.stg_amazon_ads__campaign_history[portfolio_id]
- Integration: used to join with the portfolios table
7. model.amazon_ads.amazon_ads__search_report
-
Input Column: 15. model.amazon_ads_source.stg_amazon_ads__campaign_history[portfolio_id]
- Integration: Used as a key to join with the portfolios table
Column Lineage
15. model.amazon_ads_source.stg_amazon_ads__campaign_history
-
Input Column: 16. model.amazon_ads_source.stg_amazon_ads__campaign_history_tmp[profile_id]
- Cleaning: Cast to TEXT data type Output Columns:
- profile_id: Directly mapped after casting to TEXT
1. model.amazon_ads.amazon_ads__account_report
-
Input Column: 15. model.amazon_ads_source.stg_amazon_ads__campaign_history[profile_id]
- Integration: used as a key to join with the account_info table Output Columns:
- profile_id: Directly copied to the output
2. model.amazon_ads.amazon_ads__ad_group_report
-
Input Column: 15. model.amazon_ads_source.stg_amazon_ads__campaign_history[profile_id]
- Integration: The column is used as a key to join with account_info table
3. model.amazon_ads.amazon_ads__ad_report
-
Input Column: 15. model.amazon_ads_source.stg_amazon_ads__campaign_history[profile_id]
- Integration: used to join with the account_info table
4. model.amazon_ads.amazon_ads__campaign_report
-
Input Column: 15. model.amazon_ads_source.stg_amazon_ads__campaign_history[profile_id]
- Integration: Used to join with the account_info table
5. model.amazon_ads.amazon_ads__keyword_report
-
Input Column: 15. model.amazon_ads_source.stg_amazon_ads__campaign_history[profile_id]
- Integration: used as a key to join with the account_info table
6. model.amazon_ads.amazon_ads__portfolio_report
-
Input Column: 15. model.amazon_ads_source.stg_amazon_ads__campaign_history[profile_id]
- Integration: used to join with the account_info table
7. model.amazon_ads.amazon_ads__search_report
-
Input Column: 15. model.amazon_ads_source.stg_amazon_ads__campaign_history[profile_id]
- Integration: Used as a key to join with the account_info table
Column Lineage
15. model.amazon_ads_source.stg_amazon_ads__campaign_history
-
Input Column: 16. model.amazon_ads_source.stg_amazon_ads__campaign_history_tmp[serving_status]
- Direct: Copied without transformation Output Columns:
- serving_status: Directly copied
Column Lineage
15. model.amazon_ads_source.stg_amazon_ads__campaign_history
-
Input Column: 16. model.amazon_ads_source.stg_amazon_ads__campaign_history_tmp[start_date]
- Cleaning: Cast to date data type Output Columns:
- start_date: Directly mapped after casting to date
Column Lineage
15. model.amazon_ads_source.stg_amazon_ads__campaign_history
-
Input Column: 16. model.amazon_ads_source.stg_amazon_ads__campaign_history_tmp[state]
- Direct: Copied without transformation Output Columns:
- state: Directly copied
Column Lineage
15. model.amazon_ads_source.stg_amazon_ads__campaign_history
-
Input Column: 16. model.amazon_ads_source.stg_amazon_ads__campaign_history_tmp[targeting_type]
- Direct: Copied without transformation Output Columns:
- targeting_type: Directly copied
Column Lineage
15. model.amazon_ads_source.stg_amazon_ads__campaign_history
-
Input Column: 16. model.amazon_ads_source.stg_amazon_ads__campaign_history_tmp[budget_type]
- Direct: Copied without transformation Output Columns:
- budget_type: Directly copied
Column Lineage
15. model.amazon_ads_source.stg_amazon_ads__campaign_history
-
Input Column: 16. model.amazon_ads_source.stg_amazon_ads__campaign_history_tmp[effective_budget]
- Cleaning: Cast to float data type Output Columns:
- effective_budget: Directly mapped after casting to float
17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report
This SQL query stages data from a temporary table, casts various fields to specific data types, and prepares a final selection of columns for the Amazon Ads campaign-level report. It primarily focuses on cleaning and standardizing the data structure.
CleaningOther
SQL Query
with base as (
select *
from TEST.PUBLIC_amazon_ads_source.stg_amazon_ads__campaign_level_report_tmp
),
fields as (
select
cast(null as TEXT) as
campaign_applicable_budget_rule_id
,
cast(null as TEXT) as
campaign_applicable_budget_rule_name
,
cast(null as TEXT) as
campaign_bidding_strategy
,
cast(null as float) as
campaign_budget_amount
,
cast(null as TEXT) as
campaign_budget_currency_code
,
cast(null as TEXT) as
campaign_budget_type
,
cast(null as integer) as
campaign_id
,
cast(null as float) as
campaign_rule_based_budget_amount
,
cast(null as integer) as
clicks
,
cast(null as float) as
cost
,
cast(null as date) as
date
,
cast(null as integer) as
impressions
, cast('' as TEXT) as source_relation
from base
),
final as (
select
source_relation,
campaign_applicable_budget_rule_id,
campaign_applicable_budget_rule_name,
campaign_bidding_strategy,
campaign_budget_amount,
campaign_budget_currency_code,
campaign_budget_type,
cast(campaign_id as TEXT) as campaign_id,
campaign_rule_based_budget_amount,
clicks,
cost,
date as date_day,
impressions
from fields
)
select *
from final
| Name | Type | Comment |
|---|---|---|
| campaign_applicable_budget_rule_id | integer | None |
| campaign_applicable_budget_rule_name | integer | None |
| campaign_bidding_strategy | text | None |
| campaign_budget_amount | double precision | None |
| campaign_budget_currency_code | text | None |
| campaign_budget_type | text | None |
| campaign_id | text | None |
| campaign_rule_based_budget_amount | integer | None |
| clicks | integer | None |
| cost | double precision | None |
| date_day | date | None |
| impressions | integer | None |
Column Lineage
17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report
-
Input Column: 18. model.amazon_ads_source.stg_amazon_ads__campaign_level_report_tmp[campaign_applicable_budget_rule_id]
- Direct: The column is directly copied without transformation Output Columns:
- campaign_applicable_budget_rule_id: Directly mapped
Column Lineage
17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report
-
Input Column: 18. model.amazon_ads_source.stg_amazon_ads__campaign_level_report_tmp[campaign_applicable_budget_rule_name]
- Direct: The column is directly copied without transformation Output Columns:
- campaign_applicable_budget_rule_name: Directly mapped
Column Lineage
17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report
-
Input Column: 18. model.amazon_ads_source.stg_amazon_ads__campaign_level_report_tmp[campaign_bidding_strategy]
- Direct: The column is directly copied without transformation Output Columns:
- campaign_bidding_strategy: Directly mapped
4. model.amazon_ads.amazon_ads__campaign_report
-
Input Column: 17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report[campaign_bidding_strategy]
- Direct: The column is directly copied without any transformation Output Columns:
- campaign_bidding_strategy: Directly copied
Column Lineage
17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report
-
Input Column: 18. model.amazon_ads_source.stg_amazon_ads__campaign_level_report_tmp[campaign_budget_amount]
- Direct: The column is directly copied without transformation Output Columns:
- campaign_budget_amount: Directly mapped
4. model.amazon_ads.amazon_ads__campaign_report
-
Input Column: 17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report[campaign_budget_amount]
- Direct: The column is directly copied without any transformation Output Columns:
- campaign_budget_amount: Directly copied
Column Lineage
17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report
-
Input Column: 18. model.amazon_ads_source.stg_amazon_ads__campaign_level_report_tmp[campaign_budget_currency_code]
- Direct: The column is directly copied without transformation Output Columns:
- campaign_budget_currency_code: Directly mapped
4. model.amazon_ads.amazon_ads__campaign_report
-
Input Column: 17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report[campaign_budget_currency_code]
- Direct: The column is directly copied without any transformation Output Columns:
- campaign_budget_currency_code: Directly copied
Column Lineage
17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report
-
Input Column: 18. model.amazon_ads_source.stg_amazon_ads__campaign_level_report_tmp[campaign_budget_type]
- Direct: The column is directly copied without transformation Output Columns:
- campaign_budget_type: Directly mapped
4. model.amazon_ads.amazon_ads__campaign_report
-
Input Column: 17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report[campaign_budget_type]
- Direct: The column is directly copied without any transformation Output Columns:
- campaign_budget_type: Directly copied
Column Lineage
17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report
-
Input Column: 18. model.amazon_ads_source.stg_amazon_ads__campaign_level_report_tmp[campaign_id]
- Cleaning: The column is cast as TEXT in the final output Output Columns:
- campaign_id: Directly mapped after casting to TEXT
1. model.amazon_ads.amazon_ads__account_report
-
Input Column: 17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report[campaign_id]
- Integration: Used as a key to join with the campaigns table
4. model.amazon_ads.amazon_ads__campaign_report
-
Input Column: 17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report[campaign_id]
- Direct: The column is directly copied without any transformation
- Integration: Used as a key to join with the campaigns table Output Columns:
- campaign_id: Directly copied
6. model.amazon_ads.amazon_ads__portfolio_report
-
Input Column: 17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report[campaign_id]
- Integration: Used as a key to join with the campaigns table
Column Lineage
17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report
-
Input Column: 18. model.amazon_ads_source.stg_amazon_ads__campaign_level_report_tmp[campaign_rule_based_budget_amount]
- Direct: The column is directly copied without transformation Output Columns:
- campaign_rule_based_budget_amount: Directly mapped
Column Lineage
17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report
-
Input Column: 18. model.amazon_ads_source.stg_amazon_ads__campaign_level_report_tmp[clicks]
- Direct: The column is directly copied without transformation Output Columns:
- clicks: Directly mapped
1. model.amazon_ads.amazon_ads__account_report
-
Input Column: 17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report[clicks]
- Aggregation: Summed up in the fields CTE Output Columns:
- clicks: Directly mapped after aggregation
4. model.amazon_ads.amazon_ads__campaign_report
-
Input Column: 17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report[clicks]
- Aggregation: The column is summed up in the group by clause Output Columns:
- clicks: Aggregated sum
6. model.amazon_ads.amazon_ads__portfolio_report
-
Input Column: 17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report[clicks]
- Aggregation: Summed up in the final select statement Output Columns:
- clicks: Aggregated sum of clicks
Column Lineage
17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report
-
Input Column: 18. model.amazon_ads_source.stg_amazon_ads__campaign_level_report_tmp[cost]
- Direct: The column is directly copied without transformation Output Columns:
- cost: Directly mapped
1. model.amazon_ads.amazon_ads__account_report
-
Input Column: 17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report[cost]
- Aggregation: Summed up in the fields CTE Output Columns:
- cost: Directly mapped after aggregation
4. model.amazon_ads.amazon_ads__campaign_report
-
Input Column: 17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report[cost]
- Aggregation: The column is summed up in the group by clause Output Columns:
- cost: Aggregated sum
6. model.amazon_ads.amazon_ads__portfolio_report
-
Input Column: 17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report[cost]
- Aggregation: Summed up in the final select statement Output Columns:
- cost: Aggregated sum of cost
Column Lineage
17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report
-
Input Column: 18. model.amazon_ads_source.stg_amazon_ads__campaign_level_report_tmp[date]
- Cleaning: The column is renamed to date_day in the final output Output Columns:
- date_day: Directly mapped and renamed
1. model.amazon_ads.amazon_ads__account_report
-
Input Column: 17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report[date_day]
- Direct: Copied without transformation
- Aggregation: Used in the GROUP BY clause Output Columns:
- date_day: Directly copied
4. model.amazon_ads.amazon_ads__campaign_report
-
Input Column: 17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report[date_day]
- Direct: The column is directly copied without any transformation
- Aggregation: Used in the group by clause Output Columns:
- date_day: Directly copied
6. model.amazon_ads.amazon_ads__portfolio_report
-
Input Column: 17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report[date_day]
- Direct: Directly selected in the final output Output Columns:
- date_day: Directly copied to output
Column Lineage
17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report
-
Input Column: 18. model.amazon_ads_source.stg_amazon_ads__campaign_level_report_tmp[impressions]
- Direct: The column is directly copied without transformation Output Columns:
- impressions: Directly mapped
1. model.amazon_ads.amazon_ads__account_report
-
Input Column: 17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report[impressions]
- Aggregation: Summed up in the fields CTE Output Columns:
- impressions: Directly mapped after aggregation
4. model.amazon_ads.amazon_ads__campaign_report
-
Input Column: 17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report[impressions]
- Aggregation: The column is summed up in the group by clause Output Columns:
- impressions: Aggregated sum
6. model.amazon_ads.amazon_ads__portfolio_report
-
Input Column: 17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report[impressions]
- Aggregation: Summed up in the final select statement Output Columns:
- impressions: Aggregated sum of impressions
18. model.amazon_ads_source.stg_amazon_ads__campaign_level_report_tmp
This SQL query creates an empty result set with a single column named '_dbt_source_relation' of TEXT data type. It's likely used as a placeholder or template for further development or testing purposes.
Other
SQL Query
select
cast(null as TEXT) as _dbt_source_relation
limit 0
| Name | Type | Comment |
|---|---|---|
| campaign_id | integer | None |
| date | date | None |
| _fivetran_synced | timestamp without time zone | None |
| campaign_applicable_budget_rule_id | integer | None |
| campaign_applicable_budget_rule_name | integer | None |
| campaign_bidding_strategy | text | None |
| campaign_budget_amount | double precision | None |
| campaign_budget_currency_code | text | None |
| campaign_budget_type | text | None |
| clicks | integer | None |
| cost | double precision | None |
| impressions | integer | None |
| campaign_rule_based_budget_amount | integer | None |
Column Lineage
17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report
-
Input Column: 18. model.amazon_ads_source.stg_amazon_ads__campaign_level_report_tmp[campaign_id]
- Cleaning: The column is cast as TEXT in the final output Output Columns:
- campaign_id: Directly mapped after casting to TEXT
1. model.amazon_ads.amazon_ads__account_report
-
Input Column: 17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report[campaign_id]
- Integration: Used as a key to join with the campaigns table
4. model.amazon_ads.amazon_ads__campaign_report
-
Input Column: 17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report[campaign_id]
- Direct: The column is directly copied without any transformation
- Integration: Used as a key to join with the campaigns table Output Columns:
- campaign_id: Directly copied
6. model.amazon_ads.amazon_ads__portfolio_report
-
Input Column: 17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report[campaign_id]
- Integration: Used as a key to join with the campaigns table
Column Lineage
17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report
-
Input Column: 18. model.amazon_ads_source.stg_amazon_ads__campaign_level_report_tmp[date]
- Cleaning: The column is renamed to date_day in the final output Output Columns:
- date_day: Directly mapped and renamed
1. model.amazon_ads.amazon_ads__account_report
-
Input Column: 17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report[date_day]
- Direct: Copied without transformation
- Aggregation: Used in the GROUP BY clause Output Columns:
- date_day: Directly copied
4. model.amazon_ads.amazon_ads__campaign_report
-
Input Column: 17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report[date_day]
- Direct: The column is directly copied without any transformation
- Aggregation: Used in the group by clause Output Columns:
- date_day: Directly copied
6. model.amazon_ads.amazon_ads__portfolio_report
-
Input Column: 17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report[date_day]
- Direct: Directly selected in the final output Output Columns:
- date_day: Directly copied to output
Column Lineage
17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report
-
Input Column: 18. model.amazon_ads_source.stg_amazon_ads__campaign_level_report_tmp[campaign_applicable_budget_rule_id]
- Direct: The column is directly copied without transformation Output Columns:
- campaign_applicable_budget_rule_id: Directly mapped
Column Lineage
17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report
-
Input Column: 18. model.amazon_ads_source.stg_amazon_ads__campaign_level_report_tmp[campaign_applicable_budget_rule_name]
- Direct: The column is directly copied without transformation Output Columns:
- campaign_applicable_budget_rule_name: Directly mapped
Column Lineage
17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report
-
Input Column: 18. model.amazon_ads_source.stg_amazon_ads__campaign_level_report_tmp[campaign_bidding_strategy]
- Direct: The column is directly copied without transformation Output Columns:
- campaign_bidding_strategy: Directly mapped
4. model.amazon_ads.amazon_ads__campaign_report
-
Input Column: 17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report[campaign_bidding_strategy]
- Direct: The column is directly copied without any transformation Output Columns:
- campaign_bidding_strategy: Directly copied
Column Lineage
17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report
-
Input Column: 18. model.amazon_ads_source.stg_amazon_ads__campaign_level_report_tmp[campaign_budget_amount]
- Direct: The column is directly copied without transformation Output Columns:
- campaign_budget_amount: Directly mapped
4. model.amazon_ads.amazon_ads__campaign_report
-
Input Column: 17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report[campaign_budget_amount]
- Direct: The column is directly copied without any transformation Output Columns:
- campaign_budget_amount: Directly copied
Column Lineage
17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report
-
Input Column: 18. model.amazon_ads_source.stg_amazon_ads__campaign_level_report_tmp[campaign_budget_currency_code]
- Direct: The column is directly copied without transformation Output Columns:
- campaign_budget_currency_code: Directly mapped
4. model.amazon_ads.amazon_ads__campaign_report
-
Input Column: 17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report[campaign_budget_currency_code]
- Direct: The column is directly copied without any transformation Output Columns:
- campaign_budget_currency_code: Directly copied
Column Lineage
17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report
-
Input Column: 18. model.amazon_ads_source.stg_amazon_ads__campaign_level_report_tmp[campaign_budget_type]
- Direct: The column is directly copied without transformation Output Columns:
- campaign_budget_type: Directly mapped
4. model.amazon_ads.amazon_ads__campaign_report
-
Input Column: 17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report[campaign_budget_type]
- Direct: The column is directly copied without any transformation Output Columns:
- campaign_budget_type: Directly copied
Column Lineage
17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report
-
Input Column: 18. model.amazon_ads_source.stg_amazon_ads__campaign_level_report_tmp[clicks]
- Direct: The column is directly copied without transformation Output Columns:
- clicks: Directly mapped
1. model.amazon_ads.amazon_ads__account_report
-
Input Column: 17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report[clicks]
- Aggregation: Summed up in the fields CTE Output Columns:
- clicks: Directly mapped after aggregation
4. model.amazon_ads.amazon_ads__campaign_report
-
Input Column: 17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report[clicks]
- Aggregation: The column is summed up in the group by clause Output Columns:
- clicks: Aggregated sum
6. model.amazon_ads.amazon_ads__portfolio_report
-
Input Column: 17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report[clicks]
- Aggregation: Summed up in the final select statement Output Columns:
- clicks: Aggregated sum of clicks
Column Lineage
17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report
-
Input Column: 18. model.amazon_ads_source.stg_amazon_ads__campaign_level_report_tmp[cost]
- Direct: The column is directly copied without transformation Output Columns:
- cost: Directly mapped
1. model.amazon_ads.amazon_ads__account_report
-
Input Column: 17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report[cost]
- Aggregation: Summed up in the fields CTE Output Columns:
- cost: Directly mapped after aggregation
4. model.amazon_ads.amazon_ads__campaign_report
-
Input Column: 17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report[cost]
- Aggregation: The column is summed up in the group by clause Output Columns:
- cost: Aggregated sum
6. model.amazon_ads.amazon_ads__portfolio_report
-
Input Column: 17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report[cost]
- Aggregation: Summed up in the final select statement Output Columns:
- cost: Aggregated sum of cost
Column Lineage
17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report
-
Input Column: 18. model.amazon_ads_source.stg_amazon_ads__campaign_level_report_tmp[impressions]
- Direct: The column is directly copied without transformation Output Columns:
- impressions: Directly mapped
1. model.amazon_ads.amazon_ads__account_report
-
Input Column: 17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report[impressions]
- Aggregation: Summed up in the fields CTE Output Columns:
- impressions: Directly mapped after aggregation
4. model.amazon_ads.amazon_ads__campaign_report
-
Input Column: 17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report[impressions]
- Aggregation: The column is summed up in the group by clause Output Columns:
- impressions: Aggregated sum
6. model.amazon_ads.amazon_ads__portfolio_report
-
Input Column: 17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report[impressions]
- Aggregation: Summed up in the final select statement Output Columns:
- impressions: Aggregated sum of impressions
Column Lineage
17. model.amazon_ads_source.stg_amazon_ads__campaign_level_report
-
Input Column: 18. model.amazon_ads_source.stg_amazon_ads__campaign_level_report_tmp[campaign_rule_based_budget_amount]
- Direct: The column is directly copied without transformation Output Columns:
- campaign_rule_based_budget_amount: Directly mapped
19. model.amazon_ads_source.stg_amazon_ads__keyword_history
This SQL query processes data from a staging table for Amazon Ads keyword history. It casts various fields to specific data types, renames some columns, and adds a source relation field. The query also includes a window function to identify the most recent record for each keyword across different source relations. Finally, it selects all fields from the processed data.
CleaningDeduplicationOther
SQL Query
with base as (
select *
from TEST.PUBLIC_amazon_ads_source.stg_amazon_ads__keyword_history_tmp
),
fields as (
select
cast(null as integer) as
ad_group_id
,
cast(null as float) as
bid
,
cast(null as integer) as
campaign_id
,
cast(null as timestamp) as
creation_date
,
cast(null as TEXT) as
id
,
cast(null as TEXT) as
keyword_text
,
cast(null as timestamp) as
last_updated_date
,
cast(null as TEXT) as
match_type
,
cast(null as TEXT) as
native_language_keyword
,
cast(null as TEXT) as
serving_status
,
cast(null as TEXT) as
state
,
cast(null as TEXT) as
native_language_locale
, cast('' as TEXT) as source_relation
from base
),
final as (
select
source_relation,
cast(id as TEXT) as keyword_id,
cast(ad_group_id as TEXT) as ad_group_id,
bid,
cast(campaign_id as TEXT) as campaign_id,
creation_date,
keyword_text,
last_updated_date,
match_type,
native_language_keyword,
serving_status,
state,
native_language_locale,
row_number() over (partition by source_relation, id order by last_updated_date desc) = 1 as is_most_recent_record
from fields
)
select *
from final
| Name | Type | Comment |
|---|---|---|
| keyword_id | text | None |
| ad_group_id | text | None |
| bid | double precision | None |
| campaign_id | text | None |
| creation_date | text | None |
| keyword_text | text | None |
| last_updated_date | text | None |
| match_type | text | None |
| native_language_keyword | integer | None |
| serving_status | text | None |
| state | text | None |
| native_language_locale | integer | None |
| is_most_recent_record | boolean | None |
Column Lineage
19. model.amazon_ads_source.stg_amazon_ads__keyword_history
-
Input Column: 20. model.amazon_ads_source.stg_amazon_ads__keyword_history_tmp[id]
- Cleaning: Cast to TEXT data type
- Deduplication: Used in the window function to identify the most recent record Output Columns:
- keyword_id: Directly mapped after casting to TEXT
5. model.amazon_ads.amazon_ads__keyword_report
-
Input Column: 19. model.amazon_ads_source.stg_amazon_ads__keyword_history[keyword_id]
- Direct: The column is directly copied without any transformation
- Integration: Used as a key to join with the report table Output Columns:
- keyword_id: Directly copied
7. model.amazon_ads.amazon_ads__search_report
-
Input Column: 19. model.amazon_ads_source.stg_amazon_ads__keyword_history[keyword_id]
- Integration: used to join with the report table
- Direct: directly copied to the output Output Columns:
- keyword_id: directly copied
Column Lineage
19. model.amazon_ads_source.stg_amazon_ads__keyword_history
-
Input Column: 20. model.amazon_ads_source.stg_amazon_ads__keyword_history_tmp[ad_group_id]
- Cleaning: Cast to TEXT data type Output Columns:
- ad_group_id: Directly mapped after casting to TEXT
Column Lineage
19. model.amazon_ads_source.stg_amazon_ads__keyword_history
-
Input Column: 20. model.amazon_ads_source.stg_amazon_ads__keyword_history_tmp[bid]
- Cleaning: Cast to float data type Output Columns:
- bid: Directly mapped after casting to float
Column Lineage
19. model.amazon_ads_source.stg_amazon_ads__keyword_history
-
Input Column: 20. model.amazon_ads_source.stg_amazon_ads__keyword_history_tmp[campaign_id]
- Cleaning: Cast to TEXT data type Output Columns:
- campaign_id: Directly mapped after casting to TEXT
Column Lineage
19. model.amazon_ads_source.stg_amazon_ads__keyword_history
-
Input Column: 20. model.amazon_ads_source.stg_amazon_ads__keyword_history_tmp[creation_date]
- Cleaning: Cast to timestamp data type Output Columns:
- creation_date: Directly mapped after casting to timestamp
Column Lineage
19. model.amazon_ads_source.stg_amazon_ads__keyword_history
-
Input Column: 20. model.amazon_ads_source.stg_amazon_ads__keyword_history_tmp[keyword_text]
- Cleaning: Cast to TEXT data type Output Columns:
- keyword_text: Directly mapped after casting to TEXT
5. model.amazon_ads.amazon_ads__keyword_report
-
Input Column: 19. model.amazon_ads_source.stg_amazon_ads__keyword_history[keyword_text]
- Direct: The column is directly copied without any transformation Output Columns:
- keyword_text: Directly copied
7. model.amazon_ads.amazon_ads__search_report
-
Input Column: 19. model.amazon_ads_source.stg_amazon_ads__keyword_history[keyword_text]
- Direct: directly copied to the output Output Columns:
- keyword_text: directly copied
Column Lineage
19. model.amazon_ads_source.stg_amazon_ads__keyword_history
-
Input Column: 20. model.amazon_ads_source.stg_amazon_ads__keyword_history_tmp[last_updated_date]
- Cleaning: Cast to timestamp data type
- Deduplication: Used in the window function to order and identify the most recent record Output Columns:
- last_updated_date: Directly mapped after casting to timestamp
Column Lineage
19. model.amazon_ads_source.stg_amazon_ads__keyword_history
-
Input Column: 20. model.amazon_ads_source.stg_amazon_ads__keyword_history_tmp[match_type]
- Cleaning: Cast to TEXT data type Output Columns:
- match_type: Directly mapped after casting to TEXT
7. model.amazon_ads.amazon_ads__search_report
-
Input Column: 19. model.amazon_ads_source.stg_amazon_ads__keyword_history[match_type]
- Direct: directly copied to the output Output Columns:
- match_type: directly copied
Column Lineage
19. model.amazon_ads_source.stg_amazon_ads__keyword_history
-
Input Column: 20. model.amazon_ads_source.stg_amazon_ads__keyword_history_tmp[native_language_keyword]
- Cleaning: Cast to TEXT data type Output Columns:
- native_language_keyword: Directly mapped after casting to TEXT
Column Lineage
19. model.amazon_ads_source.stg_amazon_ads__keyword_history
-
Input Column: 20. model.amazon_ads_source.stg_amazon_ads__keyword_history_tmp[serving_status]
- Cleaning: Cast to TEXT data type Output Columns:
- serving_status: Directly mapped after casting to TEXT
5. model.amazon_ads.amazon_ads__keyword_report
-
Input Column: 19. model.amazon_ads_source.stg_amazon_ads__keyword_history[serving_status]
- Direct: The column is directly copied without any transformation Output Columns:
- serving_status: Directly copied
7. model.amazon_ads.amazon_ads__search_report
-
Input Column: 19. model.amazon_ads_source.stg_amazon_ads__keyword_history[serving_status]
- Direct: directly copied to the output Output Columns:
- serving_status: directly copied
Column Lineage
19. model.amazon_ads_source.stg_amazon_ads__keyword_history
-
Input Column: 20. model.amazon_ads_source.stg_amazon_ads__keyword_history_tmp[state]
- Cleaning: Cast to TEXT data type Output Columns:
- state: Directly mapped after casting to TEXT
5. model.amazon_ads.amazon_ads__keyword_report
-
Input Column: 19. model.amazon_ads_source.stg_amazon_ads__keyword_history[state]
- Direct: The column is directly copied without any transformation Output Columns:
- state: Directly copied
7. model.amazon_ads.amazon_ads__search_report
-
Input Column: 19. model.amazon_ads_source.stg_amazon_ads__keyword_history[state]
- Direct: directly copied to the output Output Columns:
- state: directly copied
Column Lineage
19. model.amazon_ads_source.stg_amazon_ads__keyword_history
-
Input Column: 20. model.amazon_ads_source.stg_amazon_ads__keyword_history_tmp[native_language_locale]
- Cleaning: Cast to TEXT data type Output Columns:
- native_language_locale: Directly mapped after casting to TEXT
Column Lineage
5. model.amazon_ads.amazon_ads__keyword_report
-
Input Column: 19. model.amazon_ads_source.stg_amazon_ads__keyword_history[is_most_recent_record]
- Filtering: The column is filtered to include only the most recent records
7. model.amazon_ads.amazon_ads__search_report
-
Input Column: 19. model.amazon_ads_source.stg_amazon_ads__keyword_history[is_most_recent_record]
- Filtering: the query filters for the most recent records (where is_most_recent_record = True)
20. model.amazon_ads_source.stg_amazon_ads__keyword_history_tmp
This SQL query creates an empty result set with a single column named '_dbt_source_relation' of TEXT data type. It doesn't perform any actual data manipulation or retrieval from the 'model.amazon_ads_source.stg_amazon_ads__keyword_history_tmp' model. The query is likely used as a placeholder or for testing purposes.
Other
SQL Query
select
cast(null as TEXT) as _dbt_source_relation
limit 0
| Name | Type | Comment |
|---|---|---|
| id | integer | None |
| last_updated_date | text | None |
| _fivetran_synced | timestamp without time zone | None |
| ad_group_id | integer | None |
| bid | double precision | None |
| campaign_id | integer | None |
| creation_date | text | None |
| keyword_text | text | None |
| match_type | text | None |
| native_language_keyword | integer | None |
| serving_status | text | None |
| state | text | None |
| native_language_locale | integer | None |
Column Lineage
19. model.amazon_ads_source.stg_amazon_ads__keyword_history
-
Input Column: 20. model.amazon_ads_source.stg_amazon_ads__keyword_history_tmp[id]
- Cleaning: Cast to TEXT data type
- Deduplication: Used in the window function to identify the most recent record Output Columns:
- keyword_id: Directly mapped after casting to TEXT
5. model.amazon_ads.amazon_ads__keyword_report
-
Input Column: 19. model.amazon_ads_source.stg_amazon_ads__keyword_history[keyword_id]
- Direct: The column is directly copied without any transformation
- Integration: Used as a key to join with the report table Output Columns:
- keyword_id: Directly copied
7. model.amazon_ads.amazon_ads__search_report
-
Input Column: 19. model.amazon_ads_source.stg_amazon_ads__keyword_history[keyword_id]
- Integration: used to join with the report table
- Direct: directly copied to the output Output Columns:
- keyword_id: directly copied
Column Lineage
19. model.amazon_ads_source.stg_amazon_ads__keyword_history
-
Input Column: 20. model.amazon_ads_source.stg_amazon_ads__keyword_history_tmp[last_updated_date]
- Cleaning: Cast to timestamp data type
- Deduplication: Used in the window function to order and identify the most recent record Output Columns:
- last_updated_date: Directly mapped after casting to timestamp
Column Lineage
19. model.amazon_ads_source.stg_amazon_ads__keyword_history
-
Input Column: 20. model.amazon_ads_source.stg_amazon_ads__keyword_history_tmp[ad_group_id]
- Cleaning: Cast to TEXT data type Output Columns:
- ad_group_id: Directly mapped after casting to TEXT
Column Lineage
19. model.amazon_ads_source.stg_amazon_ads__keyword_history
-
Input Column: 20. model.amazon_ads_source.stg_amazon_ads__keyword_history_tmp[bid]
- Cleaning: Cast to float data type Output Columns:
- bid: Directly mapped after casting to float
Column Lineage
19. model.amazon_ads_source.stg_amazon_ads__keyword_history
-
Input Column: 20. model.amazon_ads_source.stg_amazon_ads__keyword_history_tmp[campaign_id]
- Cleaning: Cast to TEXT data type Output Columns:
- campaign_id: Directly mapped after casting to TEXT
Column Lineage
19. model.amazon_ads_source.stg_amazon_ads__keyword_history
-
Input Column: 20. model.amazon_ads_source.stg_amazon_ads__keyword_history_tmp[creation_date]
- Cleaning: Cast to timestamp data type Output Columns:
- creation_date: Directly mapped after casting to timestamp
Column Lineage
19. model.amazon_ads_source.stg_amazon_ads__keyword_history
-
Input Column: 20. model.amazon_ads_source.stg_amazon_ads__keyword_history_tmp[keyword_text]
- Cleaning: Cast to TEXT data type Output Columns:
- keyword_text: Directly mapped after casting to TEXT
5. model.amazon_ads.amazon_ads__keyword_report
-
Input Column: 19. model.amazon_ads_source.stg_amazon_ads__keyword_history[keyword_text]
- Direct: The column is directly copied without any transformation Output Columns:
- keyword_text: Directly copied
7. model.amazon_ads.amazon_ads__search_report
-
Input Column: 19. model.amazon_ads_source.stg_amazon_ads__keyword_history[keyword_text]
- Direct: directly copied to the output Output Columns:
- keyword_text: directly copied
Column Lineage
19. model.amazon_ads_source.stg_amazon_ads__keyword_history
-
Input Column: 20. model.amazon_ads_source.stg_amazon_ads__keyword_history_tmp[match_type]
- Cleaning: Cast to TEXT data type Output Columns:
- match_type: Directly mapped after casting to TEXT
7. model.amazon_ads.amazon_ads__search_report
-
Input Column: 19. model.amazon_ads_source.stg_amazon_ads__keyword_history[match_type]
- Direct: directly copied to the output Output Columns:
- match_type: directly copied
Column Lineage
19. model.amazon_ads_source.stg_amazon_ads__keyword_history
-
Input Column: 20. model.amazon_ads_source.stg_amazon_ads__keyword_history_tmp[native_language_keyword]
- Cleaning: Cast to TEXT data type Output Columns:
- native_language_keyword: Directly mapped after casting to TEXT
Column Lineage
19. model.amazon_ads_source.stg_amazon_ads__keyword_history
-
Input Column: 20. model.amazon_ads_source.stg_amazon_ads__keyword_history_tmp[serving_status]
- Cleaning: Cast to TEXT data type Output Columns:
- serving_status: Directly mapped after casting to TEXT
5. model.amazon_ads.amazon_ads__keyword_report
-
Input Column: 19. model.amazon_ads_source.stg_amazon_ads__keyword_history[serving_status]
- Direct: The column is directly copied without any transformation Output Columns:
- serving_status: Directly copied
7. model.amazon_ads.amazon_ads__search_report
-
Input Column: 19. model.amazon_ads_source.stg_amazon_ads__keyword_history[serving_status]
- Direct: directly copied to the output Output Columns:
- serving_status: directly copied
Column Lineage
19. model.amazon_ads_source.stg_amazon_ads__keyword_history
-
Input Column: 20. model.amazon_ads_source.stg_amazon_ads__keyword_history_tmp[state]
- Cleaning: Cast to TEXT data type Output Columns:
- state: Directly mapped after casting to TEXT
5. model.amazon_ads.amazon_ads__keyword_report
-
Input Column: 19. model.amazon_ads_source.stg_amazon_ads__keyword_history[state]
- Direct: The column is directly copied without any transformation Output Columns:
- state: Directly copied
7. model.amazon_ads.amazon_ads__search_report
-
Input Column: 19. model.amazon_ads_source.stg_amazon_ads__keyword_history[state]
- Direct: directly copied to the output Output Columns:
- state: directly copied
Column Lineage
19. model.amazon_ads_source.stg_amazon_ads__keyword_history
-
Input Column: 20. model.amazon_ads_source.stg_amazon_ads__keyword_history_tmp[native_language_locale]
- Cleaning: Cast to TEXT data type Output Columns:
- native_language_locale: Directly mapped after casting to TEXT
21. model.amazon_ads_source.stg_amazon_ads__portfolio_history
This SQL query stages data from a temporary table, casts columns to specific data types, renames some columns, and adds a source_relation column. It then creates a final table with transformed columns, including a new column to identify the most recent record for each portfolio. The query focuses on preparing Amazon Ads portfolio history data for further analysis or integration.
CleaningDeduplicationOther
SQL Query
with base as (
select *
from TEST.PUBLIC_amazon_ads_source.stg_amazon_ads__portfolio_history_tmp
),
fields as (
select
cast(null as float) as
budget_amount
,
cast(null as TEXT) as
budget_currency_code
,
cast(null as date) as
budget_end_date
,
cast(null as TEXT) as
budget_policy
,
cast(null as date) as
budget_start_date
,
cast(null as timestamp) as
creation_date
,
cast(null as integer) as
id
,
cast(null as boolean) as
in_budget
,
cast(null as timestamp) as
last_updated_date
,
cast(null as TEXT) as
name
,
cast(null as integer) as
profile_id
,
cast(null as TEXT) as
serving_status
,
cast(null as TEXT) as
state
, cast('' as TEXT) as source_relation
from base
),
final as (
select
source_relation,
cast(id as TEXT) as portfolio_id,
budget_amount,
budget_currency_code,
budget_end_date,
budget_policy,
budget_start_date,
creation_date,
in_budget,
last_updated_date,
name as portfolio_name,
cast(profile_id as TEXT) as profile_id,
serving_status,
state,
row_number() over (partition by source_relation, id order by last_updated_date desc) = 1 as is_most_recent_record
from fields
)
select *
from final
| Name | Type | Comment |
|---|---|---|
| portfolio_id | text | None |
| budget_amount | integer | None |
| budget_currency_code | integer | None |
| budget_end_date | integer | None |
| budget_policy | integer | None |
| budget_start_date | integer | None |
| creation_date | text | None |
| in_budget | boolean | None |
| last_updated_date | text | None |
| portfolio_name | text | None |
| profile_id | text | None |
| serving_status | text | None |
| state | text | None |
| is_most_recent_record | boolean | None |
Column Lineage
21. model.amazon_ads_source.stg_amazon_ads__portfolio_history
-
Input Column: 22. model.amazon_ads_source.stg_amazon_ads__portfolio_history_tmp[id]
- Cleaning: Cast to TEXT data type Output Columns:
- portfolio_id: Directly mapped after casting
6. model.amazon_ads.amazon_ads__portfolio_report
-
Input Column: 21. model.amazon_ads_source.stg_amazon_ads__portfolio_history[portfolio_id]
- Direct: The column is directly copied without any transformation
- Integration: The column is used as a key to join with other tables Output Columns:
- portfolio_id: Directly copied
8. model.amazon_ads.int_amazon_ads__portfolio_history
-
Input Column: 21. model.amazon_ads_source.stg_amazon_ads__portfolio_history[portfolio_id]
- Direct: This column is directly copied without any transformation Output Columns:
- portfolio_id: Directly copied
2. model.amazon_ads.amazon_ads__ad_group_report
-
Input Column: 8. model.amazon_ads.int_amazon_ads__portfolio_history[portfolio_id]
- Integration: used as a key to join with the campaigns table
- Direct: directly copied to the output Output Columns:
- portfolio_id: directly copied
3. model.amazon_ads.amazon_ads__ad_report
-
Input Column: 8. model.amazon_ads.int_amazon_ads__portfolio_history[portfolio_id]
- Direct: The column is directly copied without any transformation
- Integration: The column is used as a key to join with the campaigns table Output Columns:
- portfolio_id: Directly copied
4. model.amazon_ads.amazon_ads__campaign_report
-
Input Column: 8. model.amazon_ads.int_amazon_ads__portfolio_history[portfolio_id]
- Integration: Used as a key to join with the campaigns table
- Direct: Directly copied to the output Output Columns:
- portfolio_id: Directly copied
5. model.amazon_ads.amazon_ads__keyword_report
-
Input Column: 8. model.amazon_ads.int_amazon_ads__portfolio_history[portfolio_id]
- Integration: Used as a key to join with the campaigns table
- Direct: Directly copied to the output Output Columns:
- portfolio_id: Directly copied
7. model.amazon_ads.amazon_ads__search_report
-
Input Column: 8. model.amazon_ads.int_amazon_ads__portfolio_history[portfolio_id]
- Integration: used as a key to join with campaigns table
- Direct: directly copied to the output Output Columns:
- portfolio_id: directly copied
Column Lineage
21. model.amazon_ads_source.stg_amazon_ads__portfolio_history
-
Input Column: 22. model.amazon_ads_source.stg_amazon_ads__portfolio_history_tmp[budget_amount]
- Direct: Copied without transformation Output Columns:
- budget_amount: Directly copied
6. model.amazon_ads.amazon_ads__portfolio_report
-
Input Column: 21. model.amazon_ads_source.stg_amazon_ads__portfolio_history[budget_amount]
- Direct: The column is directly copied without any transformation Output Columns:
- budget_amount: Directly copied
8. model.amazon_ads.int_amazon_ads__portfolio_history
-
Input Column: 21. model.amazon_ads_source.stg_amazon_ads__portfolio_history[budget_amount]
- Direct: This column is directly copied without any transformation Output Columns:
- budget_amount: Directly copied
Column Lineage
21. model.amazon_ads_source.stg_amazon_ads__portfolio_history
-
Input Column: 22. model.amazon_ads_source.stg_amazon_ads__portfolio_history_tmp[budget_currency_code]
- Direct: Copied without transformation Output Columns:
- budget_currency_code: Directly copied
6. model.amazon_ads.amazon_ads__portfolio_report
-
Input Column: 21. model.amazon_ads_source.stg_amazon_ads__portfolio_history[budget_currency_code]
- Direct: The column is directly copied without any transformation Output Columns:
- budget_currency_code: Directly copied
8. model.amazon_ads.int_amazon_ads__portfolio_history
-
Input Column: 21. model.amazon_ads_source.stg_amazon_ads__portfolio_history[budget_currency_code]
- Direct: This column is directly copied without any transformation Output Columns:
- budget_currency_code: Directly copied
Column Lineage
21. model.amazon_ads_source.stg_amazon_ads__portfolio_history
-
Input Column: 22. model.amazon_ads_source.stg_amazon_ads__portfolio_history_tmp[budget_end_date]
- Direct: Copied without transformation Output Columns:
- budget_end_date: Directly copied
6. model.amazon_ads.amazon_ads__portfolio_report
-
Input Column: 21. model.amazon_ads_source.stg_amazon_ads__portfolio_history[budget_end_date]
- Direct: The column is directly copied without any transformation Output Columns:
- budget_end_date: Directly copied
8. model.amazon_ads.int_amazon_ads__portfolio_history
-
Input Column: 21. model.amazon_ads_source.stg_amazon_ads__portfolio_history[budget_end_date]
- Direct: This column is directly copied without any transformation Output Columns:
- budget_end_date: Directly copied
Column Lineage
21. model.amazon_ads_source.stg_amazon_ads__portfolio_history
-
Input Column: 22. model.amazon_ads_source.stg_amazon_ads__portfolio_history_tmp[budget_policy]
- Direct: Copied without transformation Output Columns:
- budget_policy: Directly copied
6. model.amazon_ads.amazon_ads__portfolio_report
-
Input Column: 21. model.amazon_ads_source.stg_amazon_ads__portfolio_history[budget_policy]
- Direct: The column is directly copied without any transformation Output Columns:
- budget_policy: Directly copied
8. model.amazon_ads.int_amazon_ads__portfolio_history
-
Input Column: 21. model.amazon_ads_source.stg_amazon_ads__portfolio_history[budget_policy]
- Direct: This column is directly copied without any transformation Output Columns:
- budget_policy: Directly copied
Column Lineage
21. model.amazon_ads_source.stg_amazon_ads__portfolio_history
-
Input Column: 22. model.amazon_ads_source.stg_amazon_ads__portfolio_history_tmp[budget_start_date]
- Direct: Copied without transformation Output Columns:
- budget_start_date: Directly copied
6. model.amazon_ads.amazon_ads__portfolio_report
-
Input Column: 21. model.amazon_ads_source.stg_amazon_ads__portfolio_history[budget_start_date]
- Direct: The column is directly copied without any transformation Output Columns:
- budget_start_date: Directly copied
8. model.amazon_ads.int_amazon_ads__portfolio_history
-
Input Column: 21. model.amazon_ads_source.stg_amazon_ads__portfolio_history[budget_start_date]
- Direct: This column is directly copied without any transformation Output Columns:
- budget_start_date: Directly copied
Column Lineage
21. model.amazon_ads_source.stg_amazon_ads__portfolio_history
-
Input Column: 22. model.amazon_ads_source.stg_amazon_ads__portfolio_history_tmp[creation_date]
- Direct: Copied without transformation Output Columns:
- creation_date: Directly copied
8. model.amazon_ads.int_amazon_ads__portfolio_history
-
Input Column: 21. model.amazon_ads_source.stg_amazon_ads__portfolio_history[creation_date]
- Direct: This column is directly copied without any transformation Output Columns:
- creation_date: Directly copied
Column Lineage
21. model.amazon_ads_source.stg_amazon_ads__portfolio_history
-
Input Column: 22. model.amazon_ads_source.stg_amazon_ads__portfolio_history_tmp[in_budget]
- Direct: Copied without transformation Output Columns:
- in_budget: Directly copied
6. model.amazon_ads.amazon_ads__portfolio_report
-
Input Column: 21. model.amazon_ads_source.stg_amazon_ads__portfolio_history[in_budget]
- Direct: The column is directly copied without any transformation Output Columns:
- in_budget: Directly copied
8. model.amazon_ads.int_amazon_ads__portfolio_history
-
Input Column: 21. model.amazon_ads_source.stg_amazon_ads__portfolio_history[in_budget]
- Direct: This column is directly copied without any transformation Output Columns:
- in_budget: Directly copied
Column Lineage
21. model.amazon_ads_source.stg_amazon_ads__portfolio_history
-
Input Column: 22. model.amazon_ads_source.stg_amazon_ads__portfolio_history_tmp[last_updated_date]
- Direct: Copied without transformation
- Deduplication: Used in window function to determine the most recent record Output Columns:
- last_updated_date: Directly copied
- is_most_recent_record: Used in window function to determine this
8. model.amazon_ads.int_amazon_ads__portfolio_history
-
Input Column: 21. model.amazon_ads_source.stg_amazon_ads__portfolio_history[last_updated_date]
- Direct: This column is directly copied without any transformation Output Columns:
- last_updated_date: Directly copied
Column Lineage
21. model.amazon_ads_source.stg_amazon_ads__portfolio_history
-
Input Column: 22. model.amazon_ads_source.stg_amazon_ads__portfolio_history_tmp[name]
- Direct: Copied without transformation Output Columns:
- portfolio_name: Directly copied and renamed
6. model.amazon_ads.amazon_ads__portfolio_report
-
Input Column: 21. model.amazon_ads_source.stg_amazon_ads__portfolio_history[portfolio_name]
- Direct: The column is directly copied without any transformation Output Columns:
- portfolio_name: Directly copied
8. model.amazon_ads.int_amazon_ads__portfolio_history
-
Input Column: 21. model.amazon_ads_source.stg_amazon_ads__portfolio_history[portfolio_name]
- Direct: This column is directly copied without any transformation Output Columns:
- portfolio_name: Directly copied
2. model.amazon_ads.amazon_ads__ad_group_report
-
Input Column: 8. model.amazon_ads.int_amazon_ads__portfolio_history[portfolio_name]
- Direct: directly copied to the output Output Columns:
- portfolio_name: directly copied
3. model.amazon_ads.amazon_ads__ad_report
-
Input Column: 8. model.amazon_ads.int_amazon_ads__portfolio_history[portfolio_name]
- Direct: The column is directly copied without any transformation Output Columns:
- portfolio_name: Directly copied
4. model.amazon_ads.amazon_ads__campaign_report
-
Input Column: 8. model.amazon_ads.int_amazon_ads__portfolio_history[portfolio_name]
- Direct: Directly copied to the output Output Columns:
- portfolio_name: Directly copied
5. model.amazon_ads.amazon_ads__keyword_report
-
Input Column: 8. model.amazon_ads.int_amazon_ads__portfolio_history[portfolio_name]
- Direct: Directly copied to the output Output Columns:
- portfolio_name: Directly copied
7. model.amazon_ads.amazon_ads__search_report
-
Input Column: 8. model.amazon_ads.int_amazon_ads__portfolio_history[portfolio_name]
- Direct: directly copied to the output Output Columns:
- portfolio_name: directly copied
Column Lineage
21. model.amazon_ads_source.stg_amazon_ads__portfolio_history
-
Input Column: 22. model.amazon_ads_source.stg_amazon_ads__portfolio_history_tmp[profile_id]
- Cleaning: Cast to TEXT data type Output Columns:
- profile_id: Directly mapped after casting
6. model.amazon_ads.amazon_ads__portfolio_report
-
Input Column: 21. model.amazon_ads_source.stg_amazon_ads__portfolio_history[profile_id]
- Integration: The column is used indirectly for joining with other tables
8. model.amazon_ads.int_amazon_ads__portfolio_history
-
Input Column: 21. model.amazon_ads_source.stg_amazon_ads__portfolio_history[profile_id]
- Direct: This column is directly copied without any transformation Output Columns:
- profile_id: Directly copied
Column Lineage
21. model.amazon_ads_source.stg_amazon_ads__portfolio_history
-
Input Column: 22. model.amazon_ads_source.stg_amazon_ads__portfolio_history_tmp[serving_status]
- Direct: Copied without transformation Output Columns:
- serving_status: Directly copied
6. model.amazon_ads.amazon_ads__portfolio_report
-
Input Column: 21. model.amazon_ads_source.stg_amazon_ads__portfolio_history[serving_status]
- Direct: The column is directly copied without any transformation Output Columns:
- serving_status: Directly copied
8. model.amazon_ads.int_amazon_ads__portfolio_history
-
Input Column: 21. model.amazon_ads_source.stg_amazon_ads__portfolio_history[serving_status]
- Direct: This column is directly copied without any transformation Output Columns:
- serving_status: Directly copied
Column Lineage
21. model.amazon_ads_source.stg_amazon_ads__portfolio_history
-
Input Column: 22. model.amazon_ads_source.stg_amazon_ads__portfolio_history_tmp[state]
- Direct: Copied without transformation Output Columns:
- state: Directly copied
6. model.amazon_ads.amazon_ads__portfolio_report
-
Input Column: 21. model.amazon_ads_source.stg_amazon_ads__portfolio_history[state]
- Direct: The column is directly copied without any transformation Output Columns:
- state: Directly copied
8. model.amazon_ads.int_amazon_ads__portfolio_history
-
Input Column: 21. model.amazon_ads_source.stg_amazon_ads__portfolio_history[state]
- Direct: This column is directly copied without any transformation Output Columns:
- state: Directly copied
Column Lineage
21. model.amazon_ads_source.stg_amazon_ads__portfolio_history
-
Input Column: 22. model.amazon_ads_source.stg_amazon_ads__portfolio_history_tmp[last_updated_date]
- Direct: Copied without transformation
- Deduplication: Used in window function to determine the most recent record Output Columns:
- last_updated_date: Directly copied
- is_most_recent_record: Used in window function to determine this
6. model.amazon_ads.amazon_ads__portfolio_report
-
Input Column: 21. model.amazon_ads_source.stg_amazon_ads__portfolio_history[is_most_recent_record]
- Filtering: The column is used to filter for the most recent records
8. model.amazon_ads.int_amazon_ads__portfolio_history
-
Input Column: 21. model.amazon_ads_source.stg_amazon_ads__portfolio_history[is_most_recent_record]
- Filtering: This column is used to filter the data to include only the most recent records Output Columns:
- is_most_recent_record: Directly copied after filtering
22. model.amazon_ads_source.stg_amazon_ads__portfolio_history_tmp
This SQL query creates an empty result set with a single column named '_dbt_source_relation' of TEXT data type. It's likely used as a placeholder or template for further operations in a dbt (data build tool) project, specifically for staging Amazon Ads portfolio history data.
Other
SQL Query
select
cast(null as TEXT) as _dbt_source_relation
limit 0
| Name | Type | Comment |
|---|---|---|
| id | integer | None |
| last_updated_date | text | None |
| _fivetran_synced | timestamp without time zone | None |
| budget_amount | integer | None |
| budget_currency_code | integer | None |
| budget_end_date | integer | None |
| budget_policy | integer | None |
| budget_start_date | integer | None |
| creation_date | text | None |
| in_budget | boolean | None |
| name | text | None |
| profile_id | integer | None |
| serving_status | text | None |
| state | text | None |
Column Lineage
21. model.amazon_ads_source.stg_amazon_ads__portfolio_history
-
Input Column: 22. model.amazon_ads_source.stg_amazon_ads__portfolio_history_tmp[id]
- Cleaning: Cast to TEXT data type Output Columns:
- portfolio_id: Directly mapped after casting
6. model.amazon_ads.amazon_ads__portfolio_report
-
Input Column: 21. model.amazon_ads_source.stg_amazon_ads__portfolio_history[portfolio_id]
- Direct: The column is directly copied without any transformation
- Integration: The column is used as a key to join with other tables Output Columns:
- portfolio_id: Directly copied
8. model.amazon_ads.int_amazon_ads__portfolio_history
-
Input Column: 21. model.amazon_ads_source.stg_amazon_ads__portfolio_history[portfolio_id]
- Direct: This column is directly copied without any transformation Output Columns:
- portfolio_id: Directly copied
2. model.amazon_ads.amazon_ads__ad_group_report
-
Input Column: 8. model.amazon_ads.int_amazon_ads__portfolio_history[portfolio_id]
- Integration: used as a key to join with the campaigns table
- Direct: directly copied to the output Output Columns:
- portfolio_id: directly copied
3. model.amazon_ads.amazon_ads__ad_report
-
Input Column: 8. model.amazon_ads.int_amazon_ads__portfolio_history[portfolio_id]
- Direct: The column is directly copied without any transformation
- Integration: The column is used as a key to join with the campaigns table Output Columns:
- portfolio_id: Directly copied
4. model.amazon_ads.amazon_ads__campaign_report
-
Input Column: 8. model.amazon_ads.int_amazon_ads__portfolio_history[portfolio_id]
- Integration: Used as a key to join with the campaigns table
- Direct: Directly copied to the output Output Columns:
- portfolio_id: Directly copied
5. model.amazon_ads.amazon_ads__keyword_report
-
Input Column: 8. model.amazon_ads.int_amazon_ads__portfolio_history[portfolio_id]
- Integration: Used as a key to join with the campaigns table
- Direct: Directly copied to the output Output Columns:
- portfolio_id: Directly copied
7. model.amazon_ads.amazon_ads__search_report
-
Input Column: 8. model.amazon_ads.int_amazon_ads__portfolio_history[portfolio_id]
- Integration: used as a key to join with campaigns table
- Direct: directly copied to the output Output Columns:
- portfolio_id: directly copied
Column Lineage
21. model.amazon_ads_source.stg_amazon_ads__portfolio_history
-
Input Column: 22. model.amazon_ads_source.stg_amazon_ads__portfolio_history_tmp[last_updated_date]
- Direct: Copied without transformation
- Deduplication: Used in window function to determine the most recent record Output Columns:
- last_updated_date: Directly copied
- is_most_recent_record: Used in window function to determine this
6. model.amazon_ads.amazon_ads__portfolio_report
-
Input Column: 21. model.amazon_ads_source.stg_amazon_ads__portfolio_history[is_most_recent_record]
- Filtering: The column is used to filter for the most recent records
8. model.amazon_ads.int_amazon_ads__portfolio_history
-
Input Column: 21. model.amazon_ads_source.stg_amazon_ads__portfolio_history[last_updated_date]
- Direct: This column is directly copied without any transformation Output Columns:
- last_updated_date: Directly copied
-
Input Column: 21. model.amazon_ads_source.stg_amazon_ads__portfolio_history[is_most_recent_record]
- Filtering: This column is used to filter the data to include only the most recent records Output Columns:
- is_most_recent_record: Directly copied after filtering
Column Lineage
21. model.amazon_ads_source.stg_amazon_ads__portfolio_history
-
Input Column: 22. model.amazon_ads_source.stg_amazon_ads__portfolio_history_tmp[budget_amount]
- Direct: Copied without transformation Output Columns:
- budget_amount: Directly copied
6. model.amazon_ads.amazon_ads__portfolio_report
-
Input Column: 21. model.amazon_ads_source.stg_amazon_ads__portfolio_history[budget_amount]
- Direct: The column is directly copied without any transformation Output Columns:
- budget_amount: Directly copied
8. model.amazon_ads.int_amazon_ads__portfolio_history
-
Input Column: 21. model.amazon_ads_source.stg_amazon_ads__portfolio_history[budget_amount]
- Direct: This column is directly copied without any transformation Output Columns:
- budget_amount: Directly copied
Column Lineage
21. model.amazon_ads_source.stg_amazon_ads__portfolio_history
-
Input Column: 22. model.amazon_ads_source.stg_amazon_ads__portfolio_history_tmp[budget_currency_code]
- Direct: Copied without transformation Output Columns:
- budget_currency_code: Directly copied
6. model.amazon_ads.amazon_ads__portfolio_report
-
Input Column: 21. model.amazon_ads_source.stg_amazon_ads__portfolio_history[budget_currency_code]
- Direct: The column is directly copied without any transformation Output Columns:
- budget_currency_code: Directly copied
8. model.amazon_ads.int_amazon_ads__portfolio_history
-
Input Column: 21. model.amazon_ads_source.stg_amazon_ads__portfolio_history[budget_currency_code]
- Direct: This column is directly copied without any transformation Output Columns:
- budget_currency_code: Directly copied
Column Lineage
21. model.amazon_ads_source.stg_amazon_ads__portfolio_history
-
Input Column: 22. model.amazon_ads_source.stg_amazon_ads__portfolio_history_tmp[budget_end_date]
- Direct: Copied without transformation Output Columns:
- budget_end_date: Directly copied
6. model.amazon_ads.amazon_ads__portfolio_report
-
Input Column: 21. model.amazon_ads_source.stg_amazon_ads__portfolio_history[budget_end_date]
- Direct: The column is directly copied without any transformation Output Columns:
- budget_end_date: Directly copied
8. model.amazon_ads.int_amazon_ads__portfolio_history
-
Input Column: 21. model.amazon_ads_source.stg_amazon_ads__portfolio_history[budget_end_date]
- Direct: This column is directly copied without any transformation Output Columns:
- budget_end_date: Directly copied
Column Lineage
21. model.amazon_ads_source.stg_amazon_ads__portfolio_history
-
Input Column: 22. model.amazon_ads_source.stg_amazon_ads__portfolio_history_tmp[budget_policy]
- Direct: Copied without transformation Output Columns:
- budget_policy: Directly copied
6. model.amazon_ads.amazon_ads__portfolio_report
-
Input Column: 21. model.amazon_ads_source.stg_amazon_ads__portfolio_history[budget_policy]
- Direct: The column is directly copied without any transformation Output Columns:
- budget_policy: Directly copied
8. model.amazon_ads.int_amazon_ads__portfolio_history
-
Input Column: 21. model.amazon_ads_source.stg_amazon_ads__portfolio_history[budget_policy]
- Direct: This column is directly copied without any transformation Output Columns:
- budget_policy: Directly copied
Column Lineage
21. model.amazon_ads_source.stg_amazon_ads__portfolio_history
-
Input Column: 22. model.amazon_ads_source.stg_amazon_ads__portfolio_history_tmp[budget_start_date]
- Direct: Copied without transformation Output Columns:
- budget_start_date: Directly copied
6. model.amazon_ads.amazon_ads__portfolio_report
-
Input Column: 21. model.amazon_ads_source.stg_amazon_ads__portfolio_history[budget_start_date]
- Direct: The column is directly copied without any transformation Output Columns:
- budget_start_date: Directly copied
8. model.amazon_ads.int_amazon_ads__portfolio_history
-
Input Column: 21. model.amazon_ads_source.stg_amazon_ads__portfolio_history[budget_start_date]
- Direct: This column is directly copied without any transformation Output Columns:
- budget_start_date: Directly copied
Column Lineage
21. model.amazon_ads_source.stg_amazon_ads__portfolio_history
-
Input Column: 22. model.amazon_ads_source.stg_amazon_ads__portfolio_history_tmp[creation_date]
- Direct: Copied without transformation Output Columns:
- creation_date: Directly copied
8. model.amazon_ads.int_amazon_ads__portfolio_history
-
Input Column: 21. model.amazon_ads_source.stg_amazon_ads__portfolio_history[creation_date]
- Direct: This column is directly copied without any transformation Output Columns:
- creation_date: Directly copied
Column Lineage
21. model.amazon_ads_source.stg_amazon_ads__portfolio_history
-
Input Column: 22. model.amazon_ads_source.stg_amazon_ads__portfolio_history_tmp[in_budget]
- Direct: Copied without transformation Output Columns:
- in_budget: Directly copied
6. model.amazon_ads.amazon_ads__portfolio_report
-
Input Column: 21. model.amazon_ads_source.stg_amazon_ads__portfolio_history[in_budget]
- Direct: The column is directly copied without any transformation Output Columns:
- in_budget: Directly copied
8. model.amazon_ads.int_amazon_ads__portfolio_history
-
Input Column: 21. model.amazon_ads_source.stg_amazon_ads__portfolio_history[in_budget]
- Direct: This column is directly copied without any transformation Output Columns:
- in_budget: Directly copied
Column Lineage
21. model.amazon_ads_source.stg_amazon_ads__portfolio_history
-
Input Column: 22. model.amazon_ads_source.stg_amazon_ads__portfolio_history_tmp[name]
- Direct: Copied without transformation Output Columns:
- portfolio_name: Directly copied and renamed
6. model.amazon_ads.amazon_ads__portfolio_report
-
Input Column: 21. model.amazon_ads_source.stg_amazon_ads__portfolio_history[portfolio_name]
- Direct: The column is directly copied without any transformation Output Columns:
- portfolio_name: Directly copied
8. model.amazon_ads.int_amazon_ads__portfolio_history
-
Input Column: 21. model.amazon_ads_source.stg_amazon_ads__portfolio_history[portfolio_name]
- Direct: This column is directly copied without any transformation Output Columns:
- portfolio_name: Directly copied
2. model.amazon_ads.amazon_ads__ad_group_report
-
Input Column: 8. model.amazon_ads.int_amazon_ads__portfolio_history[portfolio_name]
- Direct: directly copied to the output Output Columns:
- portfolio_name: directly copied
3. model.amazon_ads.amazon_ads__ad_report
-
Input Column: 8. model.amazon_ads.int_amazon_ads__portfolio_history[portfolio_name]
- Direct: The column is directly copied without any transformation Output Columns:
- portfolio_name: Directly copied
4. model.amazon_ads.amazon_ads__campaign_report
-
Input Column: 8. model.amazon_ads.int_amazon_ads__portfolio_history[portfolio_name]
- Direct: Directly copied to the output Output Columns:
- portfolio_name: Directly copied
5. model.amazon_ads.amazon_ads__keyword_report
-
Input Column: 8. model.amazon_ads.int_amazon_ads__portfolio_history[portfolio_name]
- Direct: Directly copied to the output Output Columns:
- portfolio_name: Directly copied
7. model.amazon_ads.amazon_ads__search_report
-
Input Column: 8. model.amazon_ads.int_amazon_ads__portfolio_history[portfolio_name]
- Direct: directly copied to the output Output Columns:
- portfolio_name: directly copied
Column Lineage
21. model.amazon_ads_source.stg_amazon_ads__portfolio_history
-
Input Column: 22. model.amazon_ads_source.stg_amazon_ads__portfolio_history_tmp[profile_id]
- Cleaning: Cast to TEXT data type Output Columns:
- profile_id: Directly mapped after casting
6. model.amazon_ads.amazon_ads__portfolio_report
-
Input Column: 21. model.amazon_ads_source.stg_amazon_ads__portfolio_history[profile_id]
- Integration: The column is used indirectly for joining with other tables
8. model.amazon_ads.int_amazon_ads__portfolio_history
-
Input Column: 21. model.amazon_ads_source.stg_amazon_ads__portfolio_history[profile_id]
- Direct: This column is directly copied without any transformation Output Columns:
- profile_id: Directly copied
Column Lineage
21. model.amazon_ads_source.stg_amazon_ads__portfolio_history
-
Input Column: 22. model.amazon_ads_source.stg_amazon_ads__portfolio_history_tmp[serving_status]
- Direct: Copied without transformation Output Columns:
- serving_status: Directly copied
6. model.amazon_ads.amazon_ads__portfolio_report
-
Input Column: 21. model.amazon_ads_source.stg_amazon_ads__portfolio_history[serving_status]
- Direct: The column is directly copied without any transformation Output Columns:
- serving_status: Directly copied
8. model.amazon_ads.int_amazon_ads__portfolio_history
-
Input Column: 21. model.amazon_ads_source.stg_amazon_ads__portfolio_history[serving_status]
- Direct: This column is directly copied without any transformation Output Columns:
- serving_status: Directly copied
Column Lineage
21. model.amazon_ads_source.stg_amazon_ads__portfolio_history
-
Input Column: 22. model.amazon_ads_source.stg_amazon_ads__portfolio_history_tmp[state]
- Direct: Copied without transformation Output Columns:
- state: Directly copied
6. model.amazon_ads.amazon_ads__portfolio_report
-
Input Column: 21. model.amazon_ads_source.stg_amazon_ads__portfolio_history[state]
- Direct: The column is directly copied without any transformation Output Columns:
- state: Directly copied
8. model.amazon_ads.int_amazon_ads__portfolio_history
-
Input Column: 21. model.amazon_ads_source.stg_amazon_ads__portfolio_history[state]
- Direct: This column is directly copied without any transformation Output Columns:
- state: Directly copied
23. model.amazon_ads_source.stg_amazon_ads__product_ad_history
This SQL query processes data from a staging table for Amazon Ads product ad history. It casts various fields to specific data types, renames some columns, adds a source_relation column, and creates a flag for the most recent record for each ad ID. The query also structures the data into a final format with selected columns.
CleaningDeduplicationOther
SQL Query
with base as (
select *
from TEST.PUBLIC_amazon_ads_source.stg_amazon_ads__product_ad_history_tmp
),
fields as (
select
cast(null as integer) as
ad_group_id
,
cast(null as TEXT) as
asin
,
cast(null as integer) as
campaign_id
,
cast(null as timestamp) as
creation_date
,
cast(null as TEXT) as
id
,
cast(null as timestamp) as
last_updated_date
,
cast(null as TEXT) as
serving_status
,
cast(null as TEXT) as
sku
,
cast(null as TEXT) as
state
, cast('' as TEXT) as source_relation
from base
),
final as (
select
source_relation,
cast(id as TEXT) as ad_id,
cast(ad_group_id as TEXT) as ad_group_id,
asin,
cast(campaign_id as TEXT) as campaign_id,
creation_date,
last_updated_date,
serving_status,
sku,
state,
row_number() over (partition by source_relation, id order by last_updated_date desc) = 1 as is_most_recent_record
from fields
)
select *
from final
| Name | Type | Comment |
|---|---|---|
| ad_id | text | None |
| ad_group_id | text | None |
| asin | text | None |
| campaign_id | text | None |
| creation_date | text | None |
| last_updated_date | text | None |
| serving_status | text | None |
| sku | integer | None |
| state | text | None |
| is_most_recent_record | boolean | None |
Column Lineage
23. model.amazon_ads_source.stg_amazon_ads__product_ad_history
-
Input Column: 24. model.amazon_ads_source.stg_amazon_ads__product_ad_history_tmp[id]
- Cleaning: Cast to TEXT data type
- Deduplication: Used in row_number() function for identifying the most recent record Output Columns:
- ad_id: Directly mapped after casting to TEXT
3. model.amazon_ads.amazon_ads__ad_report
-
Input Column: 23. model.amazon_ads_source.stg_amazon_ads__product_ad_history[ad_id]
- Direct: The column is directly copied without any transformation
- Integration: The column is used as a key to join with the report table Output Columns:
- ad_id: Directly mapped
Column Lineage
23. model.amazon_ads_source.stg_amazon_ads__product_ad_history
-
Input Column: 24. model.amazon_ads_source.stg_amazon_ads__product_ad_history_tmp[ad_group_id]
- Cleaning: Cast to TEXT data type Output Columns:
- ad_group_id: Directly mapped after casting to TEXT
Column Lineage
23. model.amazon_ads_source.stg_amazon_ads__product_ad_history
-
Input Column: 24. model.amazon_ads_source.stg_amazon_ads__product_ad_history_tmp[asin]
- Direct: Copied without transformation Output Columns:
- asin: Directly mapped
Column Lineage
23. model.amazon_ads_source.stg_amazon_ads__product_ad_history
-
Input Column: 24. model.amazon_ads_source.stg_amazon_ads__product_ad_history_tmp[campaign_id]
- Cleaning: Cast to TEXT data type Output Columns:
- campaign_id: Directly mapped after casting to TEXT
Column Lineage
23. model.amazon_ads_source.stg_amazon_ads__product_ad_history
-
Input Column: 24. model.amazon_ads_source.stg_amazon_ads__product_ad_history_tmp[creation_date]
- Cleaning: Cast to timestamp data type Output Columns:
- creation_date: Directly mapped after casting to timestamp
Column Lineage
23. model.amazon_ads_source.stg_amazon_ads__product_ad_history
-
Input Column: 24. model.amazon_ads_source.stg_amazon_ads__product_ad_history_tmp[last_updated_date]
- Cleaning: Cast to timestamp data type
- Deduplication: Used in row_number() function for ordering and identifying the most recent record Output Columns:
- last_updated_date: Directly mapped after casting to timestamp
Column Lineage
23. model.amazon_ads_source.stg_amazon_ads__product_ad_history
-
Input Column: 24. model.amazon_ads_source.stg_amazon_ads__product_ad_history_tmp[serving_status]
- Direct: Copied without transformation Output Columns:
- serving_status: Directly mapped
3. model.amazon_ads.amazon_ads__ad_report
-
Input Column: 23. model.amazon_ads_source.stg_amazon_ads__product_ad_history[serving_status]
- Direct: The column is directly copied without any transformation Output Columns:
- serving_status: Directly mapped
Column Lineage
23. model.amazon_ads_source.stg_amazon_ads__product_ad_history
-
Input Column: 24. model.amazon_ads_source.stg_amazon_ads__product_ad_history_tmp[sku]
- Direct: Copied without transformation Output Columns:
- sku: Directly mapped
Column Lineage
23. model.amazon_ads_source.stg_amazon_ads__product_ad_history
-
Input Column: 24. model.amazon_ads_source.stg_amazon_ads__product_ad_history_tmp[state]
- Direct: Copied without transformation Output Columns:
- state: Directly mapped
3. model.amazon_ads.amazon_ads__ad_report
-
Input Column: 23. model.amazon_ads_source.stg_amazon_ads__product_ad_history[state]
- Direct: The column is directly copied without any transformation Output Columns:
- state: Directly mapped
Column Lineage
3. model.amazon_ads.amazon_ads__ad_report
-
Input Column: 23. model.amazon_ads_source.stg_amazon_ads__product_ad_history[is_most_recent_record]
- Filtering: The column is filtered to include only the most recent records (where is_most_recent_record = True)
24. model.amazon_ads_source.stg_amazon_ads__product_ad_history_tmp
This SQL query creates an empty result set with a single column named '_dbt_source_relation' of TEXT data type. It doesn't perform any actual data manipulation or retrieval from the 'model.amazon_ads_source.stg_amazon_ads__product_ad_history_tmp' model. The query is likely used as a placeholder or template for future development.
Other
SQL Query
select
cast(null as TEXT) as _dbt_source_relation
limit 0
| Name | Type | Comment |
|---|---|---|
| id | integer | None |
| last_updated_date | text | None |
| _fivetran_synced | timestamp without time zone | None |
| ad_group_id | integer | None |
| asin | text | None |
| campaign_id | integer | None |
| creation_date | text | None |
| serving_status | text | None |
| sku | integer | None |
| state | text | None |
Column Lineage
23. model.amazon_ads_source.stg_amazon_ads__product_ad_history
-
Input Column: 24. model.amazon_ads_source.stg_amazon_ads__product_ad_history_tmp[id]
- Cleaning: Cast to TEXT data type
- Deduplication: Used in row_number() function for identifying the most recent record Output Columns:
- ad_id: Directly mapped after casting to TEXT
3. model.amazon_ads.amazon_ads__ad_report
-
Input Column: 23. model.amazon_ads_source.stg_amazon_ads__product_ad_history[ad_id]
- Direct: The column is directly copied without any transformation
- Integration: The column is used as a key to join with the report table Output Columns:
- ad_id: Directly mapped
Column Lineage
23. model.amazon_ads_source.stg_amazon_ads__product_ad_history
-
Input Column: 24. model.amazon_ads_source.stg_amazon_ads__product_ad_history_tmp[last_updated_date]
- Cleaning: Cast to timestamp data type
- Deduplication: Used in row_number() function for ordering and identifying the most recent record Output Columns:
- last_updated_date: Directly mapped after casting to timestamp
Column Lineage
23. model.amazon_ads_source.stg_amazon_ads__product_ad_history
-
Input Column: 24. model.amazon_ads_source.stg_amazon_ads__product_ad_history_tmp[ad_group_id]
- Cleaning: Cast to TEXT data type Output Columns:
- ad_group_id: Directly mapped after casting to TEXT
Column Lineage
23. model.amazon_ads_source.stg_amazon_ads__product_ad_history
-
Input Column: 24. model.amazon_ads_source.stg_amazon_ads__product_ad_history_tmp[asin]
- Direct: Copied without transformation Output Columns:
- asin: Directly mapped
Column Lineage
23. model.amazon_ads_source.stg_amazon_ads__product_ad_history
-
Input Column: 24. model.amazon_ads_source.stg_amazon_ads__product_ad_history_tmp[campaign_id]
- Cleaning: Cast to TEXT data type Output Columns:
- campaign_id: Directly mapped after casting to TEXT
Column Lineage
23. model.amazon_ads_source.stg_amazon_ads__product_ad_history
-
Input Column: 24. model.amazon_ads_source.stg_amazon_ads__product_ad_history_tmp[creation_date]
- Cleaning: Cast to timestamp data type Output Columns:
- creation_date: Directly mapped after casting to timestamp
Column Lineage
23. model.amazon_ads_source.stg_amazon_ads__product_ad_history
-
Input Column: 24. model.amazon_ads_source.stg_amazon_ads__product_ad_history_tmp[serving_status]
- Direct: Copied without transformation Output Columns:
- serving_status: Directly mapped
3. model.amazon_ads.amazon_ads__ad_report
-
Input Column: 23. model.amazon_ads_source.stg_amazon_ads__product_ad_history[serving_status]
- Direct: The column is directly copied without any transformation Output Columns:
- serving_status: Directly mapped
Column Lineage
23. model.amazon_ads_source.stg_amazon_ads__product_ad_history
-
Input Column: 24. model.amazon_ads_source.stg_amazon_ads__product_ad_history_tmp[sku]
- Direct: Copied without transformation Output Columns:
- sku: Directly mapped
Column Lineage
23. model.amazon_ads_source.stg_amazon_ads__product_ad_history
-
Input Column: 24. model.amazon_ads_source.stg_amazon_ads__product_ad_history_tmp[state]
- Direct: Copied without transformation Output Columns:
- state: Directly mapped
3. model.amazon_ads.amazon_ads__ad_report
-
Input Column: 23. model.amazon_ads_source.stg_amazon_ads__product_ad_history[state]
- Direct: The column is directly copied without any transformation Output Columns:
- state: Directly mapped
25. model.amazon_ads_source.stg_amazon_ads__profile
This SQL query stages data from a temporary table, casts all fields to specific data types (mostly to null or empty values), and then formats the final output by renaming some columns and casting certain fields. It appears to be preparing a standardized structure for Amazon Ads profile data.
CleaningOther
SQL Query
with base as (
select *
from TEST.PUBLIC_amazon_ads_source.stg_amazon_ads__profile_tmp
),
fields as (
select
cast(null as integer) as
id
,
cast(null as TEXT) as
account_id
,
cast(null as TEXT) as
account_marketplace_string_id
,
cast(null as TEXT) as
account_name
,
cast(null as TEXT) as
account_sub_type
,
cast(null as TEXT) as
account_type
,
cast(null as boolean) as
account_valid_payment_method
,
cast(null as TEXT) as
country_code
,
cast(null as TEXT) as
currency_code
,
cast(null as integer) as
daily_budget
,
cast(null as TEXT) as
timezone
,
cast(null as boolean) as
_fivetran_deleted
, cast('' as TEXT) as source_relation
from base
),
final as (
select
source_relation,
cast(id as TEXT) as profile_id,
cast(account_id as TEXT) as account_id,
account_marketplace_string_id,
account_name,
account_sub_type,
account_type,
account_valid_payment_method,
country_code,
currency_code,
daily_budget,
timezone,
_fivetran_deleted
from fields
)
select *
from final
| Name | Type | Comment |
|---|---|---|
| profile_id | text | None |
| account_id | text | None |
| account_marketplace_string_id | text | None |
| account_name | text | None |
| account_sub_type | integer | None |
| account_type | text | None |
| account_valid_payment_method | boolean | None |
| country_code | text | None |
| currency_code | text | None |
| daily_budget | integer | None |
| timezone | text | None |
| _fivetran_deleted | boolean | None |
Column Lineage
25. model.amazon_ads_source.stg_amazon_ads__profile
-
Input Column: 26. model.amazon_ads_source.stg_amazon_ads__profile_tmp[id]
- Cleaning: The column is cast to TEXT and renamed to profile_id Output Columns:
- profile_id: Directly mapped after casting to TEXT
1. model.amazon_ads.amazon_ads__account_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[profile_id]
- Integration: Used as a key to join with other tables
- Direct: Directly copied to the output Output Columns:
- profile_id: Directly copied
2. model.amazon_ads.amazon_ads__ad_group_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[profile_id]
- Integration: Used as a key to join with the campaigns table
- Direct: Directly copied to the output Output Columns:
- profile_id: Directly copied
3. model.amazon_ads.amazon_ads__ad_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[profile_id]
- Integration: used as a key to join with the campaigns table
- Direct: directly copied to the output Output Columns:
- profile_id: directly copied
4. model.amazon_ads.amazon_ads__campaign_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[profile_id]
- Integration: Used as a key to join with the campaigns table
- Direct: Directly copied to the output Output Columns:
- profile_id: Directly copied
5. model.amazon_ads.amazon_ads__keyword_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[profile_id]
- Integration: used as a key to join with the campaigns table
- Direct: directly copied to the output Output Columns:
- profile_id: directly copied
6. model.amazon_ads.amazon_ads__portfolio_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[profile_id]
- Integration: used as a key to join with the campaigns table
- Direct: directly copied to the output Output Columns:
- profile_id: directly copied
7. model.amazon_ads.amazon_ads__search_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[profile_id]
- Integration: used as a key to join with the campaigns table
- Direct: directly copied to the output Output Columns:
- profile_id: directly copied
Column Lineage
25. model.amazon_ads_source.stg_amazon_ads__profile
-
Input Column: 26. model.amazon_ads_source.stg_amazon_ads__profile_tmp[account_id]
- Cleaning: The column is cast to TEXT Output Columns:
- account_id: Directly mapped after casting to TEXT
1. model.amazon_ads.amazon_ads__account_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[account_id]
- Direct: Directly copied to the output Output Columns:
- account_id: Directly copied
2. model.amazon_ads.amazon_ads__ad_group_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[account_id]
- Direct: Directly copied to the output Output Columns:
- account_id: Directly copied
3. model.amazon_ads.amazon_ads__ad_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[account_id]
- Direct: directly copied to the output Output Columns:
- account_id: directly copied
4. model.amazon_ads.amazon_ads__campaign_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[account_id]
- Direct: Directly copied to the output Output Columns:
- account_id: Directly copied
5. model.amazon_ads.amazon_ads__keyword_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[account_id]
- Direct: directly copied to the output Output Columns:
- account_id: directly copied
6. model.amazon_ads.amazon_ads__portfolio_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[account_id]
- Direct: directly copied to the output Output Columns:
- account_id: directly copied
7. model.amazon_ads.amazon_ads__search_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[account_id]
- Direct: directly copied to the output Output Columns:
- account_id: directly copied
Column Lineage
25. model.amazon_ads_source.stg_amazon_ads__profile
-
Input Column: 26. model.amazon_ads_source.stg_amazon_ads__profile_tmp[account_marketplace_string_id]
- Direct: The column is directly copied without transformation Output Columns:
- account_marketplace_string_id: Directly copied
Column Lineage
25. model.amazon_ads_source.stg_amazon_ads__profile
-
Input Column: 26. model.amazon_ads_source.stg_amazon_ads__profile_tmp[account_name]
- Direct: The column is directly copied without transformation Output Columns:
- account_name: Directly copied
1. model.amazon_ads.amazon_ads__account_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[account_name]
- Direct: Directly copied to the output Output Columns:
- account_name: Directly copied
2. model.amazon_ads.amazon_ads__ad_group_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[account_name]
- Direct: Directly copied to the output Output Columns:
- account_name: Directly copied
3. model.amazon_ads.amazon_ads__ad_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[account_name]
- Direct: directly copied to the output Output Columns:
- account_name: directly copied
4. model.amazon_ads.amazon_ads__campaign_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[account_name]
- Direct: Directly copied to the output Output Columns:
- account_name: Directly copied
5. model.amazon_ads.amazon_ads__keyword_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[account_name]
- Direct: directly copied to the output Output Columns:
- account_name: directly copied
6. model.amazon_ads.amazon_ads__portfolio_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[account_name]
- Direct: directly copied to the output Output Columns:
- account_name: directly copied
7. model.amazon_ads.amazon_ads__search_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[account_name]
- Direct: directly copied to the output Output Columns:
- account_name: directly copied
Column Lineage
25. model.amazon_ads_source.stg_amazon_ads__profile
-
Input Column: 26. model.amazon_ads_source.stg_amazon_ads__profile_tmp[account_sub_type]
- Direct: The column is directly copied without transformation Output Columns:
- account_sub_type: Directly copied
Column Lineage
25. model.amazon_ads_source.stg_amazon_ads__profile
-
Input Column: 26. model.amazon_ads_source.stg_amazon_ads__profile_tmp[account_type]
- Direct: The column is directly copied without transformation Output Columns:
- account_type: Directly copied
Column Lineage
25. model.amazon_ads_source.stg_amazon_ads__profile
-
Input Column: 26. model.amazon_ads_source.stg_amazon_ads__profile_tmp[account_valid_payment_method]
- Direct: The column is directly copied without transformation Output Columns:
- account_valid_payment_method: Directly copied
Column Lineage
25. model.amazon_ads_source.stg_amazon_ads__profile
-
Input Column: 26. model.amazon_ads_source.stg_amazon_ads__profile_tmp[country_code]
- Direct: The column is directly copied without transformation Output Columns:
- country_code: Directly copied
1. model.amazon_ads.amazon_ads__account_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[country_code]
- Direct: Directly copied to the output Output Columns:
- country_code: Directly copied
2. model.amazon_ads.amazon_ads__ad_group_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[country_code]
- Direct: Directly copied to the output Output Columns:
- country_code: Directly copied
3. model.amazon_ads.amazon_ads__ad_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[country_code]
- Direct: directly copied to the output Output Columns:
- country_code: directly copied
4. model.amazon_ads.amazon_ads__campaign_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[country_code]
- Direct: Directly copied to the output Output Columns:
- country_code: Directly copied
5. model.amazon_ads.amazon_ads__keyword_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[country_code]
- Direct: directly copied to the output Output Columns:
- country_code: directly copied
6. model.amazon_ads.amazon_ads__portfolio_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[country_code]
- Direct: directly copied to the output Output Columns:
- country_code: directly copied
7. model.amazon_ads.amazon_ads__search_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[country_code]
- Direct: directly copied to the output Output Columns:
- country_code: directly copied
Column Lineage
25. model.amazon_ads_source.stg_amazon_ads__profile
-
Input Column: 26. model.amazon_ads_source.stg_amazon_ads__profile_tmp[currency_code]
- Direct: The column is directly copied without transformation Output Columns:
- currency_code: Directly copied
Column Lineage
25. model.amazon_ads_source.stg_amazon_ads__profile
-
Input Column: 26. model.amazon_ads_source.stg_amazon_ads__profile_tmp[daily_budget]
- Direct: The column is directly copied without transformation Output Columns:
- daily_budget: Directly copied
Column Lineage
25. model.amazon_ads_source.stg_amazon_ads__profile
-
Input Column: 26. model.amazon_ads_source.stg_amazon_ads__profile_tmp[timezone]
- Direct: The column is directly copied without transformation Output Columns:
- timezone: Directly copied
Column Lineage
25. model.amazon_ads_source.stg_amazon_ads__profile
-
Input Column: 26. model.amazon_ads_source.stg_amazon_ads__profile_tmp[_fivetran_deleted]
- Direct: The column is directly copied without transformation Output Columns:
- _fivetran_deleted: Directly copied
1. model.amazon_ads.amazon_ads__account_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[_fivetran_deleted]
- Filtering: The column is filtered to include only non-deleted records (where _fivetran_deleted = False)
2. model.amazon_ads.amazon_ads__ad_group_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[_fivetran_deleted]
- Filtering: Used to filter out deleted records (WHERE _fivetran_deleted = False)
3. model.amazon_ads.amazon_ads__ad_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[_fivetran_deleted]
- Filtering: used to filter out deleted records (where _fivetran_deleted = False)
4. model.amazon_ads.amazon_ads__campaign_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[_fivetran_deleted]
- Filtering: Used to filter out deleted records (where _fivetran_deleted = False)
5. model.amazon_ads.amazon_ads__keyword_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[_fivetran_deleted]
- Filtering: the column is filtered to include only non-deleted records (where _fivetran_deleted = False)
6. model.amazon_ads.amazon_ads__portfolio_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[_fivetran_deleted]
- Filtering: used to filter out deleted records (where _fivetran_deleted = False)
7. model.amazon_ads.amazon_ads__search_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[_fivetran_deleted]
- Filtering: the column is filtered to include only non-deleted records (where _fivetran_deleted = False)
26. model.amazon_ads_source.stg_amazon_ads__profile_tmp
This SQL query creates an empty result set with a single column named '_dbt_source_relation' of TEXT data type. It's likely used as a placeholder or template for further development or testing purposes.
Other
SQL Query
select
cast(null as TEXT) as _dbt_source_relation
limit 0
| Name | Type | Comment |
|---|---|---|
| id | integer | None |
| _fivetran_deleted | boolean | None |
| _fivetran_synced | timestamp without time zone | None |
| account_id | text | None |
| account_marketplace_string_id | text | None |
| account_name | text | None |
| account_sub_type | integer | None |
| account_type | text | None |
| account_valid_payment_method | boolean | None |
| country_code | text | None |
| currency_code | text | None |
| daily_budget | integer | None |
| timezone | text | None |
Column Lineage
25. model.amazon_ads_source.stg_amazon_ads__profile
-
Input Column: 26. model.amazon_ads_source.stg_amazon_ads__profile_tmp[id]
- Cleaning: The column is cast to TEXT and renamed to profile_id Output Columns:
- profile_id: Directly mapped after casting to TEXT
1. model.amazon_ads.amazon_ads__account_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[profile_id]
- Integration: Used as a key to join with other tables
- Direct: Directly copied to the output Output Columns:
- profile_id: Directly copied
2. model.amazon_ads.amazon_ads__ad_group_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[profile_id]
- Integration: Used as a key to join with the campaigns table
- Direct: Directly copied to the output Output Columns:
- profile_id: Directly copied
3. model.amazon_ads.amazon_ads__ad_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[profile_id]
- Integration: used as a key to join with the campaigns table
- Direct: directly copied to the output Output Columns:
- profile_id: directly copied
4. model.amazon_ads.amazon_ads__campaign_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[profile_id]
- Integration: Used as a key to join with the campaigns table
- Direct: Directly copied to the output Output Columns:
- profile_id: Directly copied
5. model.amazon_ads.amazon_ads__keyword_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[profile_id]
- Integration: used as a key to join with the campaigns table
- Direct: directly copied to the output Output Columns:
- profile_id: directly copied
6. model.amazon_ads.amazon_ads__portfolio_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[profile_id]
- Integration: used as a key to join with the campaigns table
- Direct: directly copied to the output Output Columns:
- profile_id: directly copied
7. model.amazon_ads.amazon_ads__search_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[profile_id]
- Integration: used as a key to join with the campaigns table
- Direct: directly copied to the output Output Columns:
- profile_id: directly copied
Column Lineage
25. model.amazon_ads_source.stg_amazon_ads__profile
-
Input Column: 26. model.amazon_ads_source.stg_amazon_ads__profile_tmp[_fivetran_deleted]
- Direct: The column is directly copied without transformation Output Columns:
- _fivetran_deleted: Directly copied
1. model.amazon_ads.amazon_ads__account_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[_fivetran_deleted]
- Filtering: The column is filtered to include only non-deleted records (where _fivetran_deleted = False)
2. model.amazon_ads.amazon_ads__ad_group_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[_fivetran_deleted]
- Filtering: Used to filter out deleted records (WHERE _fivetran_deleted = False)
3. model.amazon_ads.amazon_ads__ad_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[_fivetran_deleted]
- Filtering: used to filter out deleted records (where _fivetran_deleted = False)
4. model.amazon_ads.amazon_ads__campaign_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[_fivetran_deleted]
- Filtering: Used to filter out deleted records (where _fivetran_deleted = False)
5. model.amazon_ads.amazon_ads__keyword_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[_fivetran_deleted]
- Filtering: the column is filtered to include only non-deleted records (where _fivetran_deleted = False)
6. model.amazon_ads.amazon_ads__portfolio_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[_fivetran_deleted]
- Filtering: used to filter out deleted records (where _fivetran_deleted = False)
7. model.amazon_ads.amazon_ads__search_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[_fivetran_deleted]
- Filtering: the column is filtered to include only non-deleted records (where _fivetran_deleted = False)
Column Lineage
25. model.amazon_ads_source.stg_amazon_ads__profile
-
Input Column: 26. model.amazon_ads_source.stg_amazon_ads__profile_tmp[account_id]
- Cleaning: The column is cast to TEXT Output Columns:
- account_id: Directly mapped after casting to TEXT
1. model.amazon_ads.amazon_ads__account_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[account_id]
- Direct: Directly copied to the output Output Columns:
- account_id: Directly copied
2. model.amazon_ads.amazon_ads__ad_group_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[account_id]
- Direct: Directly copied to the output Output Columns:
- account_id: Directly copied
3. model.amazon_ads.amazon_ads__ad_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[account_id]
- Direct: directly copied to the output Output Columns:
- account_id: directly copied
4. model.amazon_ads.amazon_ads__campaign_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[account_id]
- Direct: Directly copied to the output Output Columns:
- account_id: Directly copied
5. model.amazon_ads.amazon_ads__keyword_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[account_id]
- Direct: directly copied to the output Output Columns:
- account_id: directly copied
6. model.amazon_ads.amazon_ads__portfolio_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[account_id]
- Direct: directly copied to the output Output Columns:
- account_id: directly copied
7. model.amazon_ads.amazon_ads__search_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[account_id]
- Direct: directly copied to the output Output Columns:
- account_id: directly copied
Column Lineage
25. model.amazon_ads_source.stg_amazon_ads__profile
-
Input Column: 26. model.amazon_ads_source.stg_amazon_ads__profile_tmp[account_marketplace_string_id]
- Direct: The column is directly copied without transformation Output Columns:
- account_marketplace_string_id: Directly copied
Column Lineage
25. model.amazon_ads_source.stg_amazon_ads__profile
-
Input Column: 26. model.amazon_ads_source.stg_amazon_ads__profile_tmp[account_name]
- Direct: The column is directly copied without transformation Output Columns:
- account_name: Directly copied
1. model.amazon_ads.amazon_ads__account_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[account_name]
- Direct: Directly copied to the output Output Columns:
- account_name: Directly copied
2. model.amazon_ads.amazon_ads__ad_group_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[account_name]
- Direct: Directly copied to the output Output Columns:
- account_name: Directly copied
3. model.amazon_ads.amazon_ads__ad_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[account_name]
- Direct: directly copied to the output Output Columns:
- account_name: directly copied
4. model.amazon_ads.amazon_ads__campaign_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[account_name]
- Direct: Directly copied to the output Output Columns:
- account_name: Directly copied
5. model.amazon_ads.amazon_ads__keyword_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[account_name]
- Direct: directly copied to the output Output Columns:
- account_name: directly copied
6. model.amazon_ads.amazon_ads__portfolio_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[account_name]
- Direct: directly copied to the output Output Columns:
- account_name: directly copied
7. model.amazon_ads.amazon_ads__search_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[account_name]
- Direct: directly copied to the output Output Columns:
- account_name: directly copied
Column Lineage
25. model.amazon_ads_source.stg_amazon_ads__profile
-
Input Column: 26. model.amazon_ads_source.stg_amazon_ads__profile_tmp[account_sub_type]
- Direct: The column is directly copied without transformation Output Columns:
- account_sub_type: Directly copied
Column Lineage
25. model.amazon_ads_source.stg_amazon_ads__profile
-
Input Column: 26. model.amazon_ads_source.stg_amazon_ads__profile_tmp[account_type]
- Direct: The column is directly copied without transformation Output Columns:
- account_type: Directly copied
Column Lineage
25. model.amazon_ads_source.stg_amazon_ads__profile
-
Input Column: 26. model.amazon_ads_source.stg_amazon_ads__profile_tmp[account_valid_payment_method]
- Direct: The column is directly copied without transformation Output Columns:
- account_valid_payment_method: Directly copied
Column Lineage
25. model.amazon_ads_source.stg_amazon_ads__profile
-
Input Column: 26. model.amazon_ads_source.stg_amazon_ads__profile_tmp[country_code]
- Direct: The column is directly copied without transformation Output Columns:
- country_code: Directly copied
1. model.amazon_ads.amazon_ads__account_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[country_code]
- Direct: Directly copied to the output Output Columns:
- country_code: Directly copied
2. model.amazon_ads.amazon_ads__ad_group_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[country_code]
- Direct: Directly copied to the output Output Columns:
- country_code: Directly copied
3. model.amazon_ads.amazon_ads__ad_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[country_code]
- Direct: directly copied to the output Output Columns:
- country_code: directly copied
4. model.amazon_ads.amazon_ads__campaign_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[country_code]
- Direct: Directly copied to the output Output Columns:
- country_code: Directly copied
5. model.amazon_ads.amazon_ads__keyword_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[country_code]
- Direct: directly copied to the output Output Columns:
- country_code: directly copied
6. model.amazon_ads.amazon_ads__portfolio_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[country_code]
- Direct: directly copied to the output Output Columns:
- country_code: directly copied
7. model.amazon_ads.amazon_ads__search_report
-
Input Column: 25. model.amazon_ads_source.stg_amazon_ads__profile[country_code]
- Direct: directly copied to the output Output Columns:
- country_code: directly copied
Column Lineage
25. model.amazon_ads_source.stg_amazon_ads__profile
-
Input Column: 26. model.amazon_ads_source.stg_amazon_ads__profile_tmp[currency_code]
- Direct: The column is directly copied without transformation Output Columns:
- currency_code: Directly copied
Column Lineage
25. model.amazon_ads_source.stg_amazon_ads__profile
-
Input Column: 26. model.amazon_ads_source.stg_amazon_ads__profile_tmp[daily_budget]
- Direct: The column is directly copied without transformation Output Columns:
- daily_budget: Directly copied
Column Lineage
25. model.amazon_ads_source.stg_amazon_ads__profile
-
Input Column: 26. model.amazon_ads_source.stg_amazon_ads__profile_tmp[timezone]
- Direct: The column is directly copied without transformation Output Columns:
- timezone: Directly copied
27. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report
This SQL query performs data type casting and column renaming on a staging table for Amazon Ads search term and keyword report data. It creates a CTE structure to organize the transformations, casting various columns to appropriate data types (integer, float, text, date) and renaming some columns. The query also adds a 'source_relation' column and adjusts some column names in the final select statement.
CleaningOther
SQL Query
with base as (
select *
from TEST.PUBLIC_amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report_tmp
),
fields as (
select
cast(null as integer) as
ad_group_id
,
cast(null as TEXT) as
ad_keyword_status
,
cast(null as float) as
campaign_budget_amount
,
cast(null as TEXT) as
campaign_budget_currency_code
,
cast(null as TEXT) as
campaign_budget_type
,
cast(null as integer) as
campaign_id
,
cast(null as integer) as
clicks
,
cast(null as float) as
cost
,
cast(null as date) as
date
,
cast(null as integer) as
impressions
,
cast(null as float) as
keyword_bid
,
cast(null as integer) as
keyword_id
,
cast(null as TEXT) as
search_term
,
cast(null as TEXT) as
targeting
, cast('' as TEXT) as source_relation
from base
),
final as (
select
source_relation,
cast(ad_group_id as TEXT) as ad_group_id,
ad_keyword_status,
campaign_budget_amount,
campaign_budget_currency_code,
campaign_budget_type,
cast(campaign_id as TEXT) as campaign_id,
clicks,
cost,
date as date_day,
impressions,
keyword_bid,
cast(keyword_id as TEXT) as keyword_id,
search_term,
targeting
from fields
)
select *
from final
| Name | Type | Comment |
|---|---|---|
| ad_group_id | text | None |
| ad_keyword_status | text | None |
| campaign_budget_amount | double precision | None |
| campaign_budget_currency_code | text | None |
| campaign_budget_type | text | None |
| campaign_id | text | None |
| clicks | integer | None |
| cost | double precision | None |
| date_day | date | None |
| impressions | integer | None |
| keyword_bid | double precision | None |
| keyword_id | text | None |
| search_term | text | None |
| targeting | text | None |
Column Lineage
27. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report
-
Input Column: 28. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report_tmp[ad_group_id]
- Cleaning: The column is cast to TEXT data type Output Columns:
- ad_group_id: Directly mapped after casting
7. model.amazon_ads.amazon_ads__search_report
-
Input Column: 27. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report[ad_group_id]
- Direct: directly copied to the output
- Integration: used to join with ad_groups table Output Columns:
- ad_group_id: directly copied
Column Lineage
27. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report
-
Input Column: 28. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report_tmp[campaign_budget_amount]
- Cleaning: The column is cast to FLOAT data type Output Columns:
- campaign_budget_amount: Directly mapped after casting
Column Lineage
27. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report
-
Input Column: 28. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report_tmp[campaign_budget_currency_code]
- Cleaning: The column is cast to TEXT data type Output Columns:
- campaign_budget_currency_code: Directly mapped after casting
Column Lineage
27. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report
-
Input Column: 28. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report_tmp[campaign_budget_type]
- Cleaning: The column is cast to TEXT data type Output Columns:
- campaign_budget_type: Directly mapped after casting
Column Lineage
27. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report
-
Input Column: 28. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report_tmp[campaign_id]
- Cleaning: The column is cast to TEXT data type Output Columns:
- campaign_id: Directly mapped after casting
7. model.amazon_ads.amazon_ads__search_report
-
Input Column: 27. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report[campaign_id]
- Direct: directly copied to the output
- Integration: used to join with campaigns table Output Columns:
- campaign_id: directly copied
Column Lineage
27. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report
-
Input Column: 28. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report_tmp[clicks]
- Cleaning: The column is cast to INTEGER data type Output Columns:
- clicks: Directly mapped after casting
7. model.amazon_ads.amazon_ads__search_report
-
Input Column: 27. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report[clicks]
- Aggregation: summed up in the final select statement Output Columns:
- clicks: aggregated sum
Column Lineage
27. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report
-
Input Column: 28. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report_tmp[cost]
- Cleaning: The column is cast to FLOAT data type Output Columns:
- cost: Directly mapped after casting
7. model.amazon_ads.amazon_ads__search_report
-
Input Column: 27. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report[cost]
- Aggregation: summed up in the final select statement Output Columns:
- cost: aggregated sum
Column Lineage
27. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report
-
Input Column: 28. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report_tmp[date]
- Cleaning: The column is cast to DATE data type Output Columns:
- date_day: Directly mapped after casting and renamed
7. model.amazon_ads.amazon_ads__search_report
-
Input Column: 27. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report[date_day]
- Direct: directly copied to the output Output Columns:
- date_day: directly copied
Column Lineage
27. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report
-
Input Column: 28. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report_tmp[impressions]
- Cleaning: The column is cast to INTEGER data type Output Columns:
- impressions: Directly mapped after casting
7. model.amazon_ads.amazon_ads__search_report
-
Input Column: 27. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report[impressions]
- Aggregation: summed up in the final select statement Output Columns:
- impressions: aggregated sum
Column Lineage
27. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report
-
Input Column: 28. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report_tmp[keyword_bid]
- Cleaning: The column is cast to FLOAT data type Output Columns:
- keyword_bid: Directly mapped after casting
Column Lineage
27. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report
-
Input Column: 28. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report_tmp[keyword_id]
- Cleaning: The column is cast to TEXT data type Output Columns:
- keyword_id: Directly mapped after casting
7. model.amazon_ads.amazon_ads__search_report
-
Input Column: 27. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report[keyword_id]
- Direct: directly copied to the output
- Integration: used to join with keywords table Output Columns:
- keyword_id: directly copied
Column Lineage
27. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report
-
Input Column: 28. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report_tmp[search_term]
- Cleaning: The column is cast to TEXT data type Output Columns:
- search_term: Directly mapped after casting
7. model.amazon_ads.amazon_ads__search_report
-
Input Column: 27. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report[search_term]
- Direct: directly copied to the output Output Columns:
- search_term: directly copied
Column Lineage
27. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report
-
Input Column: 28. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report_tmp[targeting]
- Cleaning: The column is cast to TEXT data type Output Columns:
- targeting: Directly mapped after casting
7. model.amazon_ads.amazon_ads__search_report
-
Input Column: 27. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report[targeting]
- Direct: directly copied to the output Output Columns:
- targeting: directly copied
28. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report_tmp
This SQL query creates an empty result set with a single column named '_dbt_source_relation' of type TEXT. The query doesn't actually select any data from a table; instead, it creates a dummy result with zero rows. This type of query is often used as a placeholder or template in data modeling tools like dbt.
Other
SQL Query
select
cast(null as TEXT) as _dbt_source_relation
limit 0
| Name | Type | Comment |
|---|---|---|
| ad_group_id | integer | None |
| campaign_id | integer | None |
| date | date | None |
| keyword_id | integer | None |
| _fivetran_synced | timestamp without time zone | None |
| campaign_budget_amount | double precision | None |
| campaign_budget_currency_code | text | None |
| campaign_budget_type | text | None |
| clicks | integer | None |
| cost | double precision | None |
| impressions | integer | None |
| keyword_bid | double precision | None |
| search_term | text | None |
| targeting | text | None |
Column Lineage
27. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report
-
Input Column: 28. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report_tmp[ad_group_id]
- Cleaning: The column is cast to TEXT data type Output Columns:
- ad_group_id: Directly mapped after casting
7. model.amazon_ads.amazon_ads__search_report
-
Input Column: 27. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report[ad_group_id]
- Direct: directly copied to the output
- Integration: used to join with ad_groups table Output Columns:
- ad_group_id: directly copied
Column Lineage
27. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report
-
Input Column: 28. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report_tmp[campaign_id]
- Cleaning: The column is cast to TEXT data type Output Columns:
- campaign_id: Directly mapped after casting
7. model.amazon_ads.amazon_ads__search_report
-
Input Column: 27. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report[campaign_id]
- Direct: directly copied to the output
- Integration: used to join with campaigns table Output Columns:
- campaign_id: directly copied
Column Lineage
27. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report
-
Input Column: 28. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report_tmp[date]
- Cleaning: The column is cast to DATE data type Output Columns:
- date_day: Directly mapped after casting and renamed
7. model.amazon_ads.amazon_ads__search_report
-
Input Column: 27. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report[date_day]
- Direct: directly copied to the output Output Columns:
- date_day: directly copied
Column Lineage
27. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report
-
Input Column: 28. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report_tmp[keyword_id]
- Cleaning: The column is cast to TEXT data type Output Columns:
- keyword_id: Directly mapped after casting
7. model.amazon_ads.amazon_ads__search_report
-
Input Column: 27. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report[keyword_id]
- Direct: directly copied to the output
- Integration: used to join with keywords table Output Columns:
- keyword_id: directly copied
Column Lineage
27. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report
-
Input Column: 28. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report_tmp[campaign_budget_amount]
- Cleaning: The column is cast to FLOAT data type Output Columns:
- campaign_budget_amount: Directly mapped after casting
Column Lineage
27. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report
-
Input Column: 28. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report_tmp[campaign_budget_currency_code]
- Cleaning: The column is cast to TEXT data type Output Columns:
- campaign_budget_currency_code: Directly mapped after casting
Column Lineage
27. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report
-
Input Column: 28. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report_tmp[campaign_budget_type]
- Cleaning: The column is cast to TEXT data type Output Columns:
- campaign_budget_type: Directly mapped after casting
Column Lineage
27. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report
-
Input Column: 28. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report_tmp[clicks]
- Cleaning: The column is cast to INTEGER data type Output Columns:
- clicks: Directly mapped after casting
7. model.amazon_ads.amazon_ads__search_report
-
Input Column: 27. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report[clicks]
- Aggregation: summed up in the final select statement Output Columns:
- clicks: aggregated sum
Column Lineage
27. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report
-
Input Column: 28. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report_tmp[cost]
- Cleaning: The column is cast to FLOAT data type Output Columns:
- cost: Directly mapped after casting
7. model.amazon_ads.amazon_ads__search_report
-
Input Column: 27. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report[cost]
- Aggregation: summed up in the final select statement Output Columns:
- cost: aggregated sum
Column Lineage
27. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report
-
Input Column: 28. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report_tmp[impressions]
- Cleaning: The column is cast to INTEGER data type Output Columns:
- impressions: Directly mapped after casting
7. model.amazon_ads.amazon_ads__search_report
-
Input Column: 27. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report[impressions]
- Aggregation: summed up in the final select statement Output Columns:
- impressions: aggregated sum
Column Lineage
27. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report
-
Input Column: 28. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report_tmp[keyword_bid]
- Cleaning: The column is cast to FLOAT data type Output Columns:
- keyword_bid: Directly mapped after casting
Column Lineage
27. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report
-
Input Column: 28. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report_tmp[search_term]
- Cleaning: The column is cast to TEXT data type Output Columns:
- search_term: Directly mapped after casting
7. model.amazon_ads.amazon_ads__search_report
-
Input Column: 27. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report[search_term]
- Direct: directly copied to the output Output Columns:
- search_term: directly copied
Column Lineage
27. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report
-
Input Column: 28. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report_tmp[targeting]
- Cleaning: The column is cast to TEXT data type Output Columns:
- targeting: Directly mapped after casting
7. model.amazon_ads.amazon_ads__search_report
-
Input Column: 27. model.amazon_ads_source.stg_amazon_ads__search_term_ad_keyword_report[targeting]
- Direct: directly copied to the output Output Columns:
- targeting: directly copied
29. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report
This SQL query performs data type casting and column renaming on a staging table for Amazon Ads targeting keyword reports. It creates a CTE structure to first select all columns from the source table, then defines data types for each column using CAST statements, and finally selects and renames some columns in the final output. The query also adds a 'source_relation' column to track the data source.
CleaningOther
SQL Query
with base as (
select *
from TEST.PUBLIC_amazon_ads_source.stg_amazon_ads__targeting_keyword_report_tmp
),
fields as (
select
cast(null as integer) as
ad_group_id
,
cast(null as TEXT) as
ad_keyword_status
,
cast(null as float) as
campaign_budget_amount
,
cast(null as TEXT) as
campaign_budget_currency_code
,
cast(null as TEXT) as
campaign_budget_type
,
cast(null as integer) as
campaign_id
,
cast(null as integer) as
clicks
,
cast(null as float) as
cost
,
cast(null as date) as
date
,
cast(null as integer) as
impressions
,
cast(null as float) as
keyword_bid
,
cast(null as integer) as
keyword_id
,
cast(null as TEXT) as
keyword_type
,
cast(null as TEXT) as
match_type
,
cast(null as TEXT) as
targeting
, cast('' as TEXT) as source_relation
from base
),
final as (
select
source_relation,
cast(ad_group_id as TEXT) as ad_group_id,
ad_keyword_status,
campaign_budget_amount,
campaign_budget_currency_code,
campaign_budget_type,
cast(campaign_id as TEXT) as campaign_id,
clicks,
cost,
date as date_day,
impressions,
keyword_bid,
cast(keyword_id as TEXT) as keyword_id,
keyword_type,
match_type,
targeting
from fields
)
select *
from final
| Name | Type | Comment |
|---|---|---|
| ad_group_id | text | None |
| ad_keyword_status | text | None |
| campaign_budget_amount | double precision | None |
| campaign_budget_currency_code | text | None |
| campaign_budget_type | text | None |
| campaign_id | text | None |
| clicks | integer | None |
| cost | double precision | None |
| date_day | date | None |
| impressions | integer | None |
| keyword_bid | double precision | None |
| keyword_id | text | None |
| keyword_type | text | None |
| match_type | text | None |
| targeting | text | None |
Column Lineage
29. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report
-
Input Column: 30. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report_tmp[ad_group_id]
- Cleaning: The column is cast to TEXT in the final output Output Columns:
- ad_group_id: Directly mapped after casting to TEXT
5. model.amazon_ads.amazon_ads__keyword_report
-
Input Column: 29. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report[ad_group_id]
- Direct: The column is directly copied without transformation
- Integration: Used as a key to join with the ad_groups table Output Columns:
- ad_group_id: Directly copied
Column Lineage
29. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report
-
Input Column: 30. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report_tmp[ad_keyword_status]
- Direct: The column is directly copied without transformation Output Columns:
- ad_keyword_status: Directly mapped
Column Lineage
29. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report
-
Input Column: 30. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report_tmp[campaign_budget_amount]
- Direct: The column is directly copied without transformation Output Columns:
- campaign_budget_amount: Directly mapped
Column Lineage
29. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report
-
Input Column: 30. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report_tmp[campaign_budget_currency_code]
- Direct: The column is directly copied without transformation Output Columns:
- campaign_budget_currency_code: Directly mapped
Column Lineage
29. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report
-
Input Column: 30. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report_tmp[campaign_budget_type]
- Direct: The column is directly copied without transformation Output Columns:
- campaign_budget_type: Directly mapped
Column Lineage
29. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report
-
Input Column: 30. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report_tmp[campaign_id]
- Cleaning: The column is cast to TEXT in the final output Output Columns:
- campaign_id: Directly mapped after casting to TEXT
5. model.amazon_ads.amazon_ads__keyword_report
-
Input Column: 29. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report[campaign_id]
- Direct: The column is directly copied without transformation
- Integration: Used as a key to join with the campaigns table Output Columns:
- campaign_id: Directly copied
Column Lineage
29. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report
-
Input Column: 30. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report_tmp[clicks]
- Direct: The column is directly copied without transformation Output Columns:
- clicks: Directly mapped
5. model.amazon_ads.amazon_ads__keyword_report
-
Input Column: 29. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report[clicks]
- Aggregation: The column is summed up in the group by clause Output Columns:
- clicks: Aggregated sum
Column Lineage
29. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report
-
Input Column: 30. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report_tmp[cost]
- Direct: The column is directly copied without transformation Output Columns:
- cost: Directly mapped
5. model.amazon_ads.amazon_ads__keyword_report
-
Input Column: 29. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report[cost]
- Aggregation: The column is summed up in the group by clause Output Columns:
- cost: Aggregated sum
Column Lineage
29. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report
-
Input Column: 30. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report_tmp[date]
- Cleaning: The column is renamed in the final output Output Columns:
- date_day: Directly mapped and renamed
5. model.amazon_ads.amazon_ads__keyword_report
-
Input Column: 29. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report[date_day]
- Direct: The column is directly copied without transformation Output Columns:
- date_day: Directly copied
Column Lineage
29. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report
-
Input Column: 30. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report_tmp[impressions]
- Direct: The column is directly copied without transformation Output Columns:
- impressions: Directly mapped
5. model.amazon_ads.amazon_ads__keyword_report
-
Input Column: 29. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report[impressions]
- Aggregation: The column is summed up in the group by clause Output Columns:
- impressions: Aggregated sum
Column Lineage
29. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report
-
Input Column: 30. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report_tmp[keyword_bid]
- Direct: The column is directly copied without transformation Output Columns:
- keyword_bid: Directly mapped
5. model.amazon_ads.amazon_ads__keyword_report
-
Input Column: 29. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report[keyword_bid]
- Direct: The column is directly copied without transformation Output Columns:
- keyword_bid: Directly copied
Column Lineage
29. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report
-
Input Column: 30. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report_tmp[keyword_id]
- Cleaning: The column is cast to TEXT in the final output Output Columns:
- keyword_id: Directly mapped after casting to TEXT
5. model.amazon_ads.amazon_ads__keyword_report
-
Input Column: 29. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report[keyword_id]
- Direct: The column is directly copied without transformation
- Integration: Used as a key to join with the keywords table Output Columns:
- keyword_id: Directly copied
Column Lineage
29. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report
-
Input Column: 30. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report_tmp[keyword_type]
- Direct: The column is directly copied without transformation Output Columns:
- keyword_type: Directly mapped
5. model.amazon_ads.amazon_ads__keyword_report
-
Input Column: 29. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report[keyword_type]
- Direct: The column is directly copied without transformation Output Columns:
- keyword_type: Directly copied
Column Lineage
29. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report
-
Input Column: 30. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report_tmp[match_type]
- Direct: The column is directly copied without transformation Output Columns:
- match_type: Directly mapped
5. model.amazon_ads.amazon_ads__keyword_report
-
Input Column: 29. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report[match_type]
- Direct: The column is directly copied without transformation Output Columns:
- match_type: Directly copied
Column Lineage
29. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report
-
Input Column: 30. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report_tmp[targeting]
- Direct: The column is directly copied without transformation Output Columns:
- targeting: Directly mapped
30. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report_tmp
This SQL query creates an empty result set with a single column named '_dbt_source_relation' of type TEXT. It's likely used as a placeholder or template for further development, as it doesn't retrieve any actual data.
Other
SQL Query
select
cast(null as TEXT) as _dbt_source_relation
limit 0
| Name | Type | Comment |
|---|---|---|
| ad_group_id | integer | None |
| campaign_id | integer | None |
| date | date | None |
| keyword_id | integer | None |
| _fivetran_synced | timestamp without time zone | None |
| ad_keyword_status | text | None |
| campaign_budget_amount | double precision | None |
| campaign_budget_currency_code | text | None |
| campaign_budget_type | text | None |
| clicks | integer | None |
| cost | double precision | None |
| impressions | integer | None |
| keyword_bid | double precision | None |
| keyword_type | text | None |
| match_type | text | None |
| targeting | text | None |
Column Lineage
29. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report
-
Input Column: 30. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report_tmp[ad_group_id]
- Cleaning: The column is cast to TEXT in the final output Output Columns:
- ad_group_id: Directly mapped after casting to TEXT
5. model.amazon_ads.amazon_ads__keyword_report
-
Input Column: 29. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report[ad_group_id]
- Direct: The column is directly copied without transformation
- Integration: Used as a key to join with the ad_groups table Output Columns:
- ad_group_id: Directly copied
Column Lineage
29. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report
-
Input Column: 30. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report_tmp[campaign_id]
- Cleaning: The column is cast to TEXT in the final output Output Columns:
- campaign_id: Directly mapped after casting to TEXT
5. model.amazon_ads.amazon_ads__keyword_report
-
Input Column: 29. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report[campaign_id]
- Direct: The column is directly copied without transformation
- Integration: Used as a key to join with the campaigns table Output Columns:
- campaign_id: Directly copied
Column Lineage
29. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report
-
Input Column: 30. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report_tmp[date]
- Cleaning: The column is renamed in the final output Output Columns:
- date_day: Directly mapped and renamed
5. model.amazon_ads.amazon_ads__keyword_report
-
Input Column: 29. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report[date_day]
- Direct: The column is directly copied without transformation Output Columns:
- date_day: Directly copied
Column Lineage
29. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report
-
Input Column: 30. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report_tmp[keyword_id]
- Cleaning: The column is cast to TEXT in the final output Output Columns:
- keyword_id: Directly mapped after casting to TEXT
5. model.amazon_ads.amazon_ads__keyword_report
-
Input Column: 29. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report[keyword_id]
- Direct: The column is directly copied without transformation
- Integration: Used as a key to join with the keywords table Output Columns:
- keyword_id: Directly copied
Column Lineage
29. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report
-
Input Column: 30. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report_tmp[ad_keyword_status]
- Direct: The column is directly copied without transformation Output Columns:
- ad_keyword_status: Directly mapped
Column Lineage
29. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report
-
Input Column: 30. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report_tmp[campaign_budget_amount]
- Direct: The column is directly copied without transformation Output Columns:
- campaign_budget_amount: Directly mapped
Column Lineage
29. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report
-
Input Column: 30. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report_tmp[campaign_budget_currency_code]
- Direct: The column is directly copied without transformation Output Columns:
- campaign_budget_currency_code: Directly mapped
Column Lineage
29. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report
-
Input Column: 30. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report_tmp[campaign_budget_type]
- Direct: The column is directly copied without transformation Output Columns:
- campaign_budget_type: Directly mapped
Column Lineage
29. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report
-
Input Column: 30. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report_tmp[clicks]
- Direct: The column is directly copied without transformation Output Columns:
- clicks: Directly mapped
5. model.amazon_ads.amazon_ads__keyword_report
-
Input Column: 29. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report[clicks]
- Aggregation: The column is summed up in the group by clause Output Columns:
- clicks: Aggregated sum
Column Lineage
29. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report
-
Input Column: 30. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report_tmp[cost]
- Direct: The column is directly copied without transformation Output Columns:
- cost: Directly mapped
5. model.amazon_ads.amazon_ads__keyword_report
-
Input Column: 29. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report[cost]
- Aggregation: The column is summed up in the group by clause Output Columns:
- cost: Aggregated sum
Column Lineage
29. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report
-
Input Column: 30. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report_tmp[impressions]
- Direct: The column is directly copied without transformation Output Columns:
- impressions: Directly mapped
5. model.amazon_ads.amazon_ads__keyword_report
-
Input Column: 29. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report[impressions]
- Aggregation: The column is summed up in the group by clause Output Columns:
- impressions: Aggregated sum
Column Lineage
29. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report
-
Input Column: 30. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report_tmp[keyword_bid]
- Direct: The column is directly copied without transformation Output Columns:
- keyword_bid: Directly mapped
5. model.amazon_ads.amazon_ads__keyword_report
-
Input Column: 29. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report[keyword_bid]
- Direct: The column is directly copied without transformation Output Columns:
- keyword_bid: Directly copied
Column Lineage
29. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report
-
Input Column: 30. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report_tmp[keyword_type]
- Direct: The column is directly copied without transformation Output Columns:
- keyword_type: Directly mapped
5. model.amazon_ads.amazon_ads__keyword_report
-
Input Column: 29. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report[keyword_type]
- Direct: The column is directly copied without transformation Output Columns:
- keyword_type: Directly copied
Column Lineage
29. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report
-
Input Column: 30. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report_tmp[match_type]
- Direct: The column is directly copied without transformation Output Columns:
- match_type: Directly mapped
5. model.amazon_ads.amazon_ads__keyword_report
-
Input Column: 29. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report[match_type]
- Direct: The column is directly copied without transformation Output Columns:
- match_type: Directly copied
Column Lineage
29. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report
-
Input Column: 30. model.amazon_ads_source.stg_amazon_ads__targeting_keyword_report_tmp[targeting]
- Direct: The column is directly copied without transformation Output Columns:
- targeting: Directly mapped
31. source.amazon_ads_source.amazon_ads.ad_group_history
The model details are not provided
32. source.amazon_ads_source.amazon_ads.ad_group_level_report
The model details are not provided
33. source.amazon_ads_source.amazon_ads.advertised_product_report
The model details are not provided
34. source.amazon_ads_source.amazon_ads.campaign_history
The model details are not provided
35. source.amazon_ads_source.amazon_ads.campaign_level_report
The model details are not provided
36. source.amazon_ads_source.amazon_ads.keyword_history
The model details are not provided
37. source.amazon_ads_source.amazon_ads.portfolio_history
The model details are not provided
38. source.amazon_ads_source.amazon_ads.product_ad_history
The model details are not provided
39. source.amazon_ads_source.amazon_ads.profile
The model details are not provided
40. source.amazon_ads_source.amazon_ads.search_term_ad_keyword_report
The model details are not provided
41. source.amazon_ads_source.amazon_ads.targeting_keyword_report
The model details are not provided