address lateral flatten in the sql style guide

Why is this change being made?

Purpose

Should we use 'inner join' to preface lateral flatten? Ultimately, what matters is we establish a guideline specifically for 'lateral flatten' because the code-base is currently inconsistent.

Background

Currently, the style guide states Prefer explicit join statements..

While lateral flatten is technically a join, i.e:

FROM source
INNER JOIN LATERAL FLATTEN(INPUT => TRY_PARSE_JSON(source.requestors)) requestors

I am proposing that the handbook make Lateral Flatten an exception to the rule.

If we want to go the other way that's fine too.

Reasons

Reason why i propose to make it an exception to the 'explicit join rule':

1. The standardized 'lateral flatten' convention is to exclude the 'inner join', for example here are snowflake docs
2. Out of the 133 models we currently have that use 'lateral flatten', 92 models (~70%) don't use 'inner join'. It will be easier to mantain consistency if we stick with what we already have.

Models list

92 Models without 'inner join'

ripgrep command: rg -UP ',\n*\s*lateral flatten' -l

1. accepted_solutions.sql
2. adaptive_accounts_source.sql
3. adaptive_attributes_source.sql
4. adaptive_dimensions_source.sql
5. adaptive_versions_source.sql
6. bamboohr_compensation_source.sql
7. bamboohr_currency_conversion_source.sql
8. bamboohr_custom_bonus_source.sql
9. bamboohr_directory_source.sql
10. bamboohr_emergency_contacts_source.sql
11. bamboohr_employment_status_source.sql
12. bamboohr_engineering_division.sql
13. bamboohr_id_employee_number_mapping_source.sql
14. bamboohr_job_info_source.sql
15. bamboohr_metafields_source.sql
16. bamboohr_ote_source.sql
17. categories_yaml_acquisitions_source.sql
18. categories_yaml_source.sql
19. clari_net_arr_entries_source.sql
20. clari_net_arr_fields_source.sql
21. clari_net_arr_time_frames_source.sql
22. clari_net_arr_time_periods_source.sql
23. clari_net_arr_users_source.sql
24. consolidated_page_views.sql
25. create_pi_source_table.sql
26. daily_engaged_users.sql
27. engineering_blocking_time_source.sql
28. engineering_commit_stats.sql
29. engineering_development_team_members.sql
30. engineering_layout_shift_source.sql
31. engineering_lcp_source.sql
32. engineering_red_master_stats.sql
33. fct_behavior_structured_event_service_ping_context.sql
34. feature_flags_source.sql
35. flaky_tests_source.sql
36. gcp_billing_export_credits.sql
37. gcp_billing_export_project_labels.sql
38. gcp_billing_export_resource_labels.sql
39. gcp_billing_export_system_labels.sql
40. geozones_yaml_source.sql
41. gitlab_dotcom_merge_request_assignment_events.sql
42. gitlab_pto_source.sql
43. location_factors_yaml_source.sql
44. mailgun_events_source.sql
45. monthly_stage_usage_by_account.sql
46. monthly_stage_usage_by_account_ultimate.sql
47. namespace_segmentation_scores_source.sql
48. netsuite_actuals_balance_sheet.sql
49. page_view_total_reqs.sql
50. posts.sql
51. prep_ping_instance_flattened.sql
52. prep_subscription_lineage.sql
53. prep_subscription_lineage_parentage_start.sql
54. ptc_scores_source.sql
55. pte_scores_source.sql
56. ptpt_scores_source.sql
57. qualtrics_distribution.sql
58. qualtrics_mailing_contacts.sql
59. qualtrics_nps_survey_responses.sql
60. qualtrics_post_purchase_survey_answers.sql
61. qualtrics_post_purchase_survey_responses_source.sql
62. qualtrics_question.sql
63. qualtrics_survey.sql
64. release_managers_source.sql
65. retention_reasons_for_retention.sql
66. retention_zuora_subscription_.sql
67. roles_yaml_source.sql
68. signups.sql
69. snowplow_gitlab_events_standard_context.sql
70. snowplow_gitlab_events_web_page_id.sql
71. snowplow_gitlab_staging_events_web_page_id.sql
72. snowplow_performance_timing_metrics.sql
73. stages_groups_yaml_source.sql
74. stages_yaml_source.sql
75. team_yaml_source.sql
76. thanos_stage_group_error_budget_availability_source.sql
77. thanos_stage_group_error_budget_seconds_remaining_source.sql
78. thanos_stage_group_error_budget_seconds_spent_source.sql
79. thanos_total_haproxy_bytes_out.sql
80. time_to_first_response.sql
81. topics_with_no_response.sql
82. usage_ping_metrics_source.sql
83. version_usage_data_unpacked_stats_used.sql
84. visits.sql
85. wk_usage_ping_geo_node_usage.sql
86. zendesk_community_relations_ticket_audits_source.sql
87. zendesk_sla_policies_source.sql
88. zendesk_ticket_audits_source.sql
89. zendesk_ticket_custom_field_values_sensitive.sql
90. zendesk_ticket_custom_fields.sql
91. zuora_subscription_lineage.sql
92. zuora_subscription_parentage_start.sql

41 Models with 'inner join'

ripgrep command: rg -UP 'join\n*\s*lateral flatten' -l

1. bdg_metrics_redis_events.sql
2. db_structure_merge_requests.sql
3. dbt_model_source.sql
4. dbt_run_results_source.sql
5. dbt_snapshots_results_source.sql
6. dbt_source_freshness_results_source.sql
7. dbt_source_test_results_source.sql
8. dbt_test_results_source.sql
9. dbt_test_source.sql
10. dim_ping_instance.sql
11. dim_team.sql
12. fct_performance_indicator_targets.sql
13. geozones_yaml_flatten_source.sql
14. gitlab_dotcom_namespace_lineage_scd.sql
15. location_factors_yaml_flatten_source.sql
16. sheetload_google_cloud_ip_ranges_source.sql
17. sheetload_google_user_ip_ranges_source.sql
18. snowplow_gitlab_events_experiment_contexts.sql
19. version_usage_stats_list.sql
20. version_user_activity_by_stage_monthly_unpacked.sql
21. wk_dim_company.sql
22. wk_fct_user_membership.sql
23. workday_bonus_source.sql
24. workday_compensation_source.sql
25. workday_emergency_contacts_source.sql
26. workday_employment_status_source.sql
27. workday_job_info_source.sql
28. workday_on_target_earnings_source.sql
29. zengrc_assessment_to_assessors.sql
30. zengrc_assessment_to_audit.sql
31. zengrc_audit_to_audit_managers.sql
32. zengrc_control_to_objective.sql
33. zengrc_issue_to_audit.sql
34. zengrc_issue_to_control.sql
35. zengrc_issue_to_program.sql
36. zengrc_person.sql
37. zengrc_program.sql
38. zengrc_request_to_assignees.sql
39. zengrc_request_to_control.sql
40. zengrc_request_to_issue.sql
41. zengrc_request_to_requestors.sql

Author Checklist

• Provided a concise title for this Merge Request (MR)
• Added a description to this MR explaining the reasons for the proposed change, per say why, not just what
  • Copy/paste the Slack conversation to document it for later, or upload screenshots. Verify that no confidential data is added, and the content is SAFE
• Assign reviewers for this MR to the correct Directly Responsible Individual/s (DRI)
  • If the DRI for the page/s being updated isn’t immediately clear, then assign it to one of the people listed in the Maintained by section on the page being edited
  • If your manager does not have merge rights, please ask someone to merge it AFTER it has been approved by your manager in #mr-buddies
  • The when to get approval handbook section explains the workflow in more detail
• If the changes affect team members, or warrant an announcement in another way, please consider posting an update in #whats-happening-at-gitlab linking to this MR
  • If this is a change that directly impacts the majority of global team members, it should be a candidate for #company-fyi. Please work with internal communications and check the handbook for examples.

---