Deselected stream properties are still included in schema messages
On Slack, Ofer Kulka reported the following error when running tap-facebook
with https://github.com/datamill-co/target-snowflake:
target-snowflake | target_snowflake.exceptions.SnowflakeError: ('Exception writing records', SQLError('Identifier must only contain alphanumerics, or underscores, and start with alphas. Got `a` for `1D_CLICK`',))
We traced this back to the ad_insights
stream's unique_actions
property, which holds an array of objects with {1,7,28}_{click,view}
subproperties:
- https://gitlab.com/meltano/tap-facebook/-/blob/master/tap_facebook/schemas/ads_insights.json#L7
- https://gitlab.com/meltano/tap-facebook/-/blob/master/tap_facebook/schemas/shared/ads_action_stats.json
target-snowflake
tried to flatten ads_insights.unique_actions
into its own ADS_INSIGHTS__UNIQUE_ACTIONS
table with 1d_click
(etc) columns, while other targets may choose to leave a unique_actions
column on the ads_insights
table with a (JSON-encoded) array value instead.
I suggested deselecting the unique_actions
property and the other properties that contain an array of the schema using Meltano's select
extra:
select:
- ads_insights.*
- "!*.unique_actions"
- "!*.actions"
- "!*.action_values"
- "!*.outbound_clicks"
- "!*.video_30_sec_watched_actions"
- "!*.video_p25_watched_actions"
- "!*.video_p50_watched_actions"
- "!*.video_p75_watched_actions"
- "!*.video_p100_watched_actions"
This resulted in these properties being marked as selected: false
in the catalog (and no longer being included in RECORD
messages), but this did not resolve the issue because the SCHEMA
messages were unchanged, causing target-snowflake to still try (and fail) to create the table despite it no longer being needed.
To resolve this, we should exclude deselected fields from SCHEMA
messages.