Skip to content

address lateral flatten in the sql style guide

Justin Wongrequested to merge
lateral-flatten-join into master

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.
Edited by Israel Weeks

Merge request reports