Hey there, Quentin.
I ran this query on some sample FB Ads data, and it yielded the results I’d expect to see. Can you tell me a little bit about what you’re seeing in your results that’s making you question things?
If you were joining a subtable below
sdc_level_0_id would be useful since it forms a composite key for the
ads_insights__actions table. It’s not needed as part of this join, since the parent
ads_insights doesn’t contain any column that’s equivalent. Basically: Any
_sdc_level column is only useful for joining tables that are below the current table.
Let’s say there was a subtable below
ads_insights__actions__values. It’d contain these columns:
_sdc_level_0_id (because subtables automatically include all Primary Key columns from the table above them)
_sdc_level_1_id (the level key for this table)
If you wanted to join
FROM ads_insights__actions actions
JOIN ads_insights__actions__values values
ON actions._sdc_source_key_ad_id = values._sdc_source_key_ad_id
AND actions._sdc_source_key_adset_id = values._sdc_source_key_adset_id
AND actions._sdc_source_key_campaign_id = values._sdc_source_key_campaign_id
AND actions._sdc_source_key_date_start = values._sdc_source_key_date_start
AND actions._sdc_level_0_id = values._sdc_source_level_0_id