)
SELECT
week_num, report_year, c.state_code, c.cohort, c.person_id, c.start_date,
DATE_DIFF(report_end_date, GREATEST(earliest_start_date, c.start_date), DAY) AS days_since_start,
DATE_DIFF(i.admission_date, GREATEST(earliest_start_date, c.start_date), DAY) AS days_until_admission,
DATE_DIFF(COALESCE(i.admission_date, report_end_date), GREATEST(earliest_start_date, c.start_date), DAY) AS survival_days,
(i.admission_date IS NULL OR i.admission_date >= r.report_start_date) AS report_week_cohort,
i.admission_date, i.incarceration_type
FROM most_recent_release c
LEFT JOIN incarcerations i
USING (state_code, person_id, week_num, report_year)
JOIN report_dates r USING (week_num, report_year)
ORDER BY week_num, report_year, survival_days, days_until_admission DESC, start_date
"""
US_ID_RELEASED_COMMUNITY_PERFORMANCE_VIEW_BUILDER = SimpleBigQueryViewBuilder(
dataset_id=dataset_config.COVID_REPORT_DATASET,
materialized_metrics_dataset=dataset_config.
DATAFLOW_METRICS_MATERIALIZED_DATASET,
reference_dataset=dataset_config.REFERENCE_VIEWS_DATASET,
state_dataset=dataset_config.STATE_BASE_DATASET,
view_id=US_ID_RELEASED_COMMUNITY_PERFORMANCE_VIEW_NAME,
view_query_template=US_ID_RELEASED_COMMUNITY_PERFORMANCE_QUERY_TEMPLATE,
description=US_ID_RELEASED_COMMUNITY_PERFORMANCE_DESCRIPTION,
covid_report_dataset=COVID_REPORT_DATASET,
)
if __name__ == "__main__":
with local_project_id_override(GCP_PROJECT_STAGING):
US_ID_RELEASED_COMMUNITY_PERFORMANCE_VIEW_BUILDER.build_and_print()
(SELECT
state_code,
termination_date,
termination_reason,
person_id
FROM supervision_terminations
LEFT JOIN overlapping_open_period USING (supervision_period_id)
-- Do not count any discharges that are overlapping with another open supervision period
-- Count any overlapping periods that ended due to ABSCONSION
WHERE (overlapping_open_period.supervision_period_id IS NULL OR termination_reason = 'ABSCONSION')
AND termination_reason NOT IN ('TRANSFER_WITHIN_STATE', 'TRANSFER_OUT_OF_STATE', 'RETURN_FROM_ABSCONSION')
) terminations
ON report.state_code = terminations.state_code AND termination_date BETWEEN start_date AND end_date
GROUP BY state_code, week_num, start_date, end_date)
ORDER BY state_code, week_num
"""
SUPERVISION_TERMINATIONS_BY_TYPE_BY_WEEK_VIEW_BUILDER = SimpleBigQueryViewBuilder(
dataset_id=dataset_config.COVID_REPORT_DATASET,
view_id=SUPERVISION_TERMINATIONS_BY_TYPE_BY_WEEK_VIEW_NAME,
view_query_template=SUPERVISION_TERMINATIONS_BY_TYPE_BY_WEEK_QUERY_TEMPLATE,
description=SUPERVISION_TERMINATIONS_BY_TYPE_BY_WEEK_DESCRIPTION,
base_dataset=dataset_config.STATE_BASE_DATASET,
covid_report_dataset=dataset_config.COVID_REPORT_DATASET,
reference_views_dataset=dataset_config.REFERENCE_VIEWS_DATASET,
)
if __name__ == "__main__":
with local_project_id_override(GCP_PROJECT_STAGING):
SUPERVISION_TERMINATIONS_BY_TYPE_BY_WEEK_VIEW_BUILDER.build_and_print()
SUM(recidivated_releases)/COUNT(*) AS recidivism_rate,
stay_length_bucket,
district
FROM `{project_id}.{metrics_dataset}.recidivism_rate_metrics`
JOIN `{project_id}.{reference_dataset}.most_recent_job_id_by_metric_and_state_code` job
USING (state_code, job_id, metric_type),
{district_dimension}
WHERE methodology = 'PERSON'
AND person_id IS NOT NULL
AND follow_up_period = 1
AND district IS NOT NULL
AND release_cohort = EXTRACT(YEAR FROM DATE_SUB(CURRENT_DATE(), INTERVAL 2 YEAR))
GROUP BY state_code, release_cohort, follow_up_period, stay_length_bucket, district
ORDER BY state_code, release_cohort, follow_up_period, stay_length_bucket, district
"""
REINCARCERATION_RATE_BY_STAY_LENGTH_VIEW_BUILDER = SimpleBigQueryViewBuilder(
dataset_id=dataset_config.DASHBOARD_VIEWS_DATASET,
view_id=REINCARCERATION_RATE_BY_STAY_LENGTH_VIEW_NAME,
view_query_template=REINCARCERATION_RATE_BY_STAY_LENGTH_QUERY_TEMPLATE,
description=REINCARCERATION_RATE_BY_STAY_LENGTH_DESCRIPTION,
metrics_dataset=dataset_config.DATAFLOW_METRICS_DATASET,
reference_dataset=dataset_config.REFERENCE_TABLES_DATASET,
district_dimension=bq_utils.unnest_district(
district_column='county_of_residence'),
)
if __name__ == '__main__':
with local_project_id_override(GCP_PROJECT_STAGING):
REINCARCERATION_RATE_BY_STAY_LENGTH_VIEW_BUILDER.build_and_print()
supervision_type,
district
FROM `{project_id}.{reference_dataset}.event_based_supervision_populations`
GROUP BY state_code, year, month, supervision_type, district
) pop
LEFT JOIN (
SELECT
state_code, year, month,
COUNT(DISTINCT person_id) AS revocation_count,
supervision_type,
district
FROM `{project_id}.{reference_dataset}.event_based_revocations`
GROUP BY state_code, year, month, supervision_type, district
) rev
USING (state_code, year, month, supervision_type, district)
WHERE supervision_type in ('ALL', 'PAROLE', 'PROBATION')
ORDER BY state_code, year, month, supervision_type, district
"""
REVOCATIONS_BY_MONTH_VIEW_BUILDER = SimpleBigQueryViewBuilder(
dataset_id=dataset_config.DASHBOARD_VIEWS_DATASET,
view_id=REVOCATIONS_BY_MONTH_VIEW_NAME,
view_query_template=REVOCATIONS_BY_MONTH_QUERY_TEMPLATE,
description=REVOCATIONS_BY_MONTH_DESCRIPTION,
reference_dataset=dataset_config.REFERENCE_TABLES_DATASET,
)
if __name__ == '__main__':
with local_project_id_override(GCP_PROJECT_STAGING):
REVOCATIONS_BY_MONTH_VIEW_BUILDER.build_and_print()
/*{description}*/
SELECT charge_id, booking_id, class, severity
FROM (
-- Assign '{external_unknown}' if charge class is NULL.
-- These will all be lumped into an UNKNOWN category for visualization.
SELECT charge_id, booking_id, COALESCE(class, '{external_unknown}') AS class
FROM
`{project_id}.{base_dataset}.{charge_table}`
) Charge
LEFT JOIN
`{project_id}.{views_dataset}.{charge_class_severity_ranks_view}` ChargeClassSeverity
ON
Charge.class = ChargeClassSeverity.charge_class
"""
CHARGES_AND_SEVERITY_VIEW_BUILDER = SimpleBigQueryViewBuilder(
dataset_id=dataset_config.VIEWS_DATASET,
view_id=CHARGES_AND_SEVERITY_VIEW_NAME,
view_query_template=CHARGES_AND_SEVERITY_QUERY_TEMPLATE,
description=CHARGES_AND_SEVERITY_DESCRIPTION,
external_unknown=external_unknown,
base_dataset=dataset_config.COUNTY_BASE_DATASET,
views_dataset=dataset_config.VIEWS_DATASET,
charge_table=Charge.__tablename__,
charge_class_severity_ranks_view=CHARGE_CLASS_SEVERITY_RANKS_VIEW_BUILDER.
view_id)
if __name__ == '__main__':
with local_project_id_override(GCP_PROJECT_STAGING):
CHARGES_AND_SEVERITY_VIEW_BUILDER.build_and_print()
race,
ethnicity
FROM person_based_terminations,
{district_dimension},
{supervision_dimension},
{charge_category_dimension}
WHERE ranking = 1
AND supervision_type IN ('ALL', 'DUAL', 'PAROLE', 'PROBATION')
AND district IS NOT NULL
"""
SUPERVISION_TERMINATION_MATRIX_BY_PERSON_VIEW_BUILDER = SimpleBigQueryViewBuilder(
dataset_id=dataset_config.REFERENCE_TABLES_DATASET,
view_id=SUPERVISION_TERMINATION_MATRIX_BY_PERSON_VIEW_NAME,
view_query_template=
SUPERVISION_TERMINATION_MATRIX_BY_PERSON_VIEW_QUERY_TEMPLATE,
description=SUPERVISION_TERMINATION_MATRIX_BY_PERSON_VIEW_DESCRIPTION,
metrics_dataset=dataset_config.DATAFLOW_METRICS_DATASET,
reference_dataset=dataset_config.REFERENCE_TABLES_DATASET,
most_severe_violation_type_subtype_grouping=bq_utils.
most_severe_violation_type_subtype_grouping(),
district_dimension=bq_utils.unnest_district('district'),
supervision_dimension=bq_utils.unnest_supervision_type(),
charge_category_dimension=bq_utils.unnest_charge_category(),
metric_period_dimension=bq_utils.unnest_metric_period_months(),
metric_period_condition=bq_utils.metric_period_condition())
if __name__ == '__main__':
with local_project_id_override(GAE_PROJECT_STAGING):
SUPERVISION_TERMINATION_MATRIX_BY_PERSON_VIEW_BUILDER.build_and_print()
ON population_dates.population_date BETWEEN sessions.start_date AND COALESCE(sessions.end_date, '9999-01-01')
WHERE sessions.compartment_level_1 IN ('INCARCERATION', 'INCARCERATION_OUT_OF_STATE')
)
SELECT
state_code,
population_date,
person_id,
COALESCE(in_dataflow, 0) AS in_dataflow,
COALESCE(in_sessions, 0) AS in_sessions,
FROM dataflow_population
FULL OUTER JOIN sessions_population
USING(state_code, population_date, person_id)
"""
SESSION_INCARCERATION_POPULATION_TO_DATAFLOW_VIEW_BUILDER_DISAGGREGATED = SimpleBigQueryViewBuilder(
dataset_id=dataset_config.VIEWS_DATASET,
view_id=
SESSION_INCARCERATION_POPULATION_TO_DATAFLOW_DISAGGREGATED_VIEW_NAME,
view_query_template=
SESSION_INCARCERATION_POPULATION_TO_DATAFLOW_DISAGGREGATED_QUERY_TEMPLATE,
description=
SESSION_INCARCERATION_POPULATION_TO_DATAFLOW_DISAGGREGATED_DESCRIPTION,
materialized_metrics_dataset=DATAFLOW_METRICS_MATERIALIZED_DATASET,
analyst_dataset=ANALYST_VIEWS_DATASET,
)
if __name__ == "__main__":
with local_project_id_override(GCP_PROJECT_STAGING):
SESSION_INCARCERATION_POPULATION_TO_DATAFLOW_VIEW_BUILDER_DISAGGREGATED.build_and_print(
)
report_month.facetoface_percent,
report_month.facetoface_percent - IFNULL(last_month.facetoface_percent, 0) as facetoface_percent_change
FROM `{project_id}.{po_report_dataset}.po_report_recipients`
LEFT JOIN report_data report_month
USING (state_code, officer_external_id, district)
LEFT JOIN (
SELECT
* EXCEPT (year, month),
-- Project this year/month data onto the next month to calculate the MoM change
EXTRACT(YEAR FROM DATE_ADD(DATE(year, month, 1), INTERVAL 1 MONTH)) AS year,
EXTRACT(MONTH FROM DATE_ADD(DATE(year, month, 1), INTERVAL 1 MONTH)) AS month,
FROM report_data
) last_month
USING (state_code, year, month, officer_external_id, district)
-- Only include output for the month before the current month
WHERE DATE(year, month, 1) = DATE_SUB(DATE(EXTRACT(YEAR FROM CURRENT_DATE()), EXTRACT(MONTH FROM CURRENT_DATE()), 1), INTERVAL 1 MONTH)
ORDER BY review_month, email_address
"""
PO_MONTHLY_REPORT_DATA_VIEW_BUILDER = SimpleBigQueryViewBuilder(
dataset_id=dataset_config.PO_REPORT_DATASET,
view_id=PO_MONTHLY_REPORT_DATA_VIEW_NAME,
materialized_view_table_id=MATERIALIZED_VIEW_TABLE_NAME,
view_query_template=PO_MONTHLY_REPORT_DATA_QUERY_TEMPLATE,
description=PO_MONTHLY_REPORT_DATA_DESCRIPTION,
po_report_dataset=PO_REPORT_DATASET)
if __name__ == '__main__':
with local_project_id_override(GAE_PROJECT_STAGING):
PO_MONTHLY_REPORT_DATA_VIEW_BUILDER.build_and_print()
ELSE most_severe_violation_type END
ELSE most_severe_violation_type
END AS violation_type,
IF(response_count > 8, 8, response_count) AS reported_violations,
metric_period_months
FROM `{project_id}.{metrics_dataset}.supervision_revocation_analysis_metrics`
JOIN `{project_id}.{reference_dataset}.most_recent_job_id_by_metric_and_state_code` job
USING (state_code, job_id, year, month, metric_period_months)
WHERE methodology = 'PERSON'
AND revocation_type = 'REINCARCERATION'
AND person_external_id IS NOT NULL
AND month IS NOT NULL
AND year = EXTRACT(YEAR FROM CURRENT_DATE('US/Pacific'))
AND month = EXTRACT(MONTH FROM CURRENT_DATE('US/Pacific'))
AND job.metric_type = 'SUPERVISION_REVOCATION_ANALYSIS'
ORDER BY metric_period_months, violation_record
"""
REVOCATIONS_MATRIX_FILTERED_CASELOAD_VIEW_BUILDER = SimpleBigQueryViewBuilder(
dataset_id=dataset_config.DASHBOARD_VIEWS_DATASET,
view_id=REVOCATIONS_MATRIX_FILTERED_CASELOAD_VIEW_NAME,
view_query_template=REVOCATIONS_MATRIX_FILTERED_CASELOAD_QUERY_TEMPLATE,
description=REVOCATIONS_MATRIX_FILTERED_CASELOAD_DESCRIPTION,
metrics_dataset=dataset_config.DATAFLOW_METRICS_DATASET,
reference_dataset=dataset_config.REFERENCE_TABLES_DATASET,
)
if __name__ == '__main__':
with local_project_id_override(GAE_PROJECT_STAGING):
REVOCATIONS_MATRIX_FILTERED_CASELOAD_VIEW_BUILDER.build_and_print()
email AS email_address,
phone_number
FROM
`{project_id}.us_id_raw_data_up_to_date_views.cis_offender_latest` offenders
LEFT JOIN
`{project_id}.us_id_raw_data_up_to_date_views.cis_personemailaddress_latest` emails
ON emails.personid = offenders.id
LEFT JOIN
phone_numbers
ON offenders.offendernumber = phone_numbers.docno
WHERE
iscurrent = 'T'
"""
CLIENT_CONTACT_INFO_DESCRIPTION = """
Provides an association between people on supervision and their contact info.
Currently only generates data for Idaho and only contains email addresses."""
CLIENT_CONTACT_INFO_VIEW_BUILDER = SimpleBigQueryViewBuilder(
dataset_id=VIEWS_DATASET,
view_id="client_contact_info",
description=CLIENT_CONTACT_INFO_DESCRIPTION,
view_query_template=CLIENT_CONTACT_INFO_QUERY_TEMPLATE,
should_materialize=True,
)
if __name__ == "__main__":
with local_project_id_override(GCP_PROJECT_STAGING):
CLIENT_CONTACT_INFO_VIEW_BUILDER.build_and_print()
((race_or_ethnicity != 'ALL' AND gender = 'ALL' AND age_bucket = 'ALL') -- Race breakdown
OR (race_or_ethnicity = 'ALL' AND gender != 'ALL' AND age_bucket = 'ALL') -- Gender breakdown
OR (race_or_ethnicity = 'ALL' AND gender = 'ALL' AND age_bucket != 'ALL') -- Age breakdown
OR (race_or_ethnicity = 'ALL' AND gender = 'ALL' AND age_bucket = 'ALL')) -- Overall success rate
ORDER BY state_code, supervision_type, metric_period_months, district, race_or_ethnicity, gender, age_bucket
"""
SUPERVISION_SUCCESS_BY_PERIOD_BY_DEMOGRAPHICS_VIEW_BUILDER = SimpleBigQueryViewBuilder(
dataset_id=dataset_config.PUBLIC_DASHBOARD_VIEWS_DATASET,
view_id=SUPERVISION_SUCCESS_BY_PERIOD_BY_DEMOGRAPHICS_VIEW_NAME,
view_query_template=
SUPERVISION_SUCCESS_BY_PERIOD_BY_DEMOGRAPHICS_VIEW_QUERY_TEMPLATE,
description=SUPERVISION_SUCCESS_BY_PERIOD_BY_DEMOGRAPHICS_VIEW_DESCRIPTION,
metrics_dataset=dataset_config.DATAFLOW_METRICS_DATASET,
reference_dataset=dataset_config.REFERENCE_TABLES_DATASET,
grouped_districts=bq_utils.supervision_specific_district_groupings(
'supervising_district_external_id', 'judicial_district_code'),
race_or_ethnicity_dimension=bq_utils.unnest_race_and_ethnicity(),
metric_period_condition=bq_utils.metric_period_condition(month_offset=1),
unnested_race_or_ethnicity_dimension=bq_utils.unnest_column(
'race_or_ethnicity', 'race_or_ethnicity'),
gender_dimension=bq_utils.unnest_column('gender', 'gender'),
age_dimension=bq_utils.unnest_column('age_bucket', 'age_bucket'),
state_specific_race_or_ethnicity_groupings=bq_utils.
state_specific_race_or_ethnicity_groupings())
if __name__ == '__main__':
with local_project_id_override(GAE_PROJECT_STAGING):
SUPERVISION_SUCCESS_BY_PERIOD_BY_DEMOGRAPHICS_VIEW_BUILDER.build_and_print(
)
MAX(external_id) AS external_id
FROM
latest_interaction_dates
LEFT JOIN
`{project_id}.state.state_assessment`
USING (person_id, state_code, assessment_date)
GROUP BY person_id, state_code, assessment_date
)
SELECT
person_id,
state_code,
assessment_date AS most_recent_assessment_date,
assessment_score
FROM
latest_assessments
INNER JOIN
`{project_id}.state.state_assessment`
USING (person_id, state_code, assessment_date, external_id)
"""
LATEST_ASSESSMENTS_VIEW_BUILDER = SimpleBigQueryViewBuilder(
dataset_id=VIEWS_DATASET,
view_id="latest_assessments",
view_query_template=LATEST_ASSESSMENTS_QUERY_VIEW,
dataflow_metrics_materialized_dataset=DATAFLOW_METRICS_MATERIALIZED_DATASET,
)
if __name__ == "__main__":
with local_project_id_override(GCP_PROJECT_STAGING):
LATEST_ASSESSMENTS_VIEW_BUILDER.build_and_print()
/*{description}*/
SELECT BondAmounts.booking_id,
-- We don't need to use IF statements for all of the below, but keep them for consistency.
IF(BondAmounts.denied_count > 0, True, False) AS denied,
IF(BondAmounts.denied_count > 0, NULL, BondAmounts.total_bond_dollars) AS total_bond_dollars,
IF((BondAmounts.total_bond_dollars IS NULL AND BondAmounts.denied_count = 0), True, False) AS unknown
FROM (
SELECT
Bond.booking_id,
SUM(Bond.amount_dollars) AS total_bond_dollars,
COUNTIF(Bond.denied) AS denied_count,
-- unknown_count is not used, but keep it for consistency.
COUNTIF(Bond.unknown) AS unknown_count
FROM `{project_id}.{views_dataset}.{bond_amounts_unknown_denied_view}` Bond
GROUP BY booking_id
) BondAmounts
"""
BOND_AMOUNTS_BY_BOOKING_VIEW_BUILDER = SimpleBigQueryViewBuilder(
dataset_id=dataset_config.VIEWS_DATASET,
view_id=BOND_AMOUNTS_BY_BOOKING_VIEW_NAME,
view_query_template=BOND_AMOUNTS_BY_BOOKING_QUERY_TEMPLATE,
description=BOND_AMOUNTS_BY_BOOKING_DESCRIPTION,
views_dataset=dataset_config.VIEWS_DATASET,
bond_amounts_unknown_denied_view=BOND_AMOUNTS_UNKNOWN_DENIED_VIEW_BUILDER.
view_id)
if __name__ == '__main__':
with local_project_id_override(GAE_PROJECT_STAGING):
BOND_AMOUNTS_BY_BOOKING_VIEW_BUILDER.build_and_print()
SUPERVISION_PERIOD_TO_AGENT_ASSOCIATION_QUERY_TEMPLATE = \
"""
/*{description}*/
SELECT
sup.state_code,
sup.supervision_period_id,
agents.agent_id,
CAST(agents.agent_external_id AS STRING) as agent_external_id,
CAST(agents.latest_district_external_id AS STRING) AS district_external_id
FROM
`{project_id}.{base_dataset}.state_supervision_period` sup
LEFT JOIN
`{project_id}.{reference_views_dataset}.augmented_agent_info` agents
ON agents.state_code = sup.state_code AND agents.agent_id = sup.supervising_officer_id
WHERE agents.external_id IS NOT NULL;
"""
SUPERVISION_PERIOD_TO_AGENT_ASSOCIATION_VIEW_BUILDER = SimpleBigQueryViewBuilder(
dataset_id=dataset_config.REFERENCE_VIEWS_DATASET,
view_id=SUPERVISION_PERIOD_TO_AGENT_ASSOCIATION_VIEW_NAME,
view_query_template=SUPERVISION_PERIOD_TO_AGENT_ASSOCIATION_QUERY_TEMPLATE,
description=SUPERVISION_PERIOD_TO_AGENT_ASSOCIATION_DESCRIPTION,
base_dataset=dataset_config.STATE_BASE_DATASET,
reference_views_dataset=dataset_config.REFERENCE_VIEWS_DATASET,
)
if __name__ == '__main__':
with local_project_id_override(GCP_PROJECT_STAGING):
SUPERVISION_PERIOD_TO_AGENT_ASSOCIATION_VIEW_BUILDER.build_and_print()
IFNULL(public_dashboard_success.projected_completion_count, 0) as public_dashboard_projected_completion
FROM
dashboard_success
FULL OUTER JOIN
public_dashboard_success
USING (state_code, metric_period_months, district, supervision_type)
-- We cannot compare district breakdowns for probation because the public dashboard uses judicial districts --
WHERE (supervision_type = 'PAROLE' OR district = 'ALL')
-- Only compare metric periods for which both dashboards are producing output --
AND metric_period_months IN
(SELECT * FROM dashboard_metric_periods)
AND metric_period_months IN
(SELECT * FROM public_dashboard_metric_periods)
ORDER BY state_code, metric_period_months, district, supervision_type
"""
SUPERVISION_SUCCESS_BY_PERIOD_DASHBOARD_COMPARISON_VIEW_BUILDER = SimpleBigQueryViewBuilder(
dataset_id=dataset_config.VIEWS_DATASET,
view_id=SUPERVISION_SUCCESS_BY_PERIOD_DASHBOARD_COMPARISON_VIEW_NAME,
view_query_template=
SUPERVISION_SUCCESS_BY_PERIOD_DASHBOARD_COMPARISON_QUERY_TEMPLATE,
description=SUPERVISION_SUCCESS_BY_PERIOD_DASHBOARD_COMPARISON_DESCRIPTION,
dashboard_dataset=state_dataset_config.DASHBOARD_VIEWS_DATASET,
public_dashboard_dataset=state_dataset_config.
PUBLIC_DASHBOARD_VIEWS_DATASET)
if __name__ == '__main__':
with local_project_id_override(GCP_PROJECT_STAGING):
SUPERVISION_SUCCESS_BY_PERIOD_DASHBOARD_COMPARISON_VIEW_BUILDER.build_and_print(
)
PARTITION_COLUMNS = ["state_code", "metric_period_months"]
CALCULATED_COLUMNS_TO_VALIDATE = ["population_count"]
MUTUALLY_EXCLUSIVE_BREAKDOWN_COLUMNS = ["race_or_ethnicity"]
INCARCERATION_POPULATION_BY_PRIORITIZED_RACE_AND_ETHNICITY_BY_PERIOD_INTERNAL_CONSISTENCY_QUERY_TEMPLATE = f"""
/*{{description}}*/
{internal_consistency_query(partition_columns=PARTITION_COLUMNS,
mutually_exclusive_breakdown_columns=MUTUALLY_EXCLUSIVE_BREAKDOWN_COLUMNS,
calculated_columns_to_validate=CALCULATED_COLUMNS_TO_VALIDATE)}
"""
INCARCERATION_POPULATION_BY_PRIORITIZED_RACE_AND_ETHNICITY_BY_PERIOD_INTERNAL_CONSISTENCY_VIEW_BUILDER = SimpleBigQueryViewBuilder(
dataset_id=dataset_config.VIEWS_DATASET,
view_id=
INCARCERATION_POPULATION_BY_PRIORITIZED_RACE_AND_ETHNICITY_BY_PERIOD_INTERNAL_CONSISTENCY_VIEW_NAME,
view_query_template=
INCARCERATION_POPULATION_BY_PRIORITIZED_RACE_AND_ETHNICITY_BY_PERIOD_INTERNAL_CONSISTENCY_QUERY_TEMPLATE,
description=
INCARCERATION_POPULATION_BY_PRIORITIZED_RACE_AND_ETHNICITY_BY_PERIOD_INTERNAL_CONSISTENCY_DESCRIPTION,
validated_table_dataset_id=state_dataset_config.
PUBLIC_DASHBOARD_VIEWS_DATASET,
validated_table_id=
INCARCERATION_POPULATION_BY_PRIORITIZED_RACE_AND_ETHNICITY_BY_PERIOD_VIEW_NAME,
)
if __name__ == "__main__":
with local_project_id_override(GCP_PROJECT_STAGING):
INCARCERATION_POPULATION_BY_PRIORITIZED_RACE_AND_ETHNICITY_BY_PERIOD_INTERNAL_CONSISTENCY_VIEW_BUILDER.build_and_print(
)
# along with this program. If not, see <https://www.gnu.org/licenses/>.
# =============================================================================
"""Single count data used for stitch"""
import os
from recidiviz.big_query.big_query_view import SimpleBigQueryViewBuilder
from recidiviz.calculator.query.county import dataset_config
from recidiviz.utils.environment import GCP_PROJECT_STAGING
from recidiviz.utils.metadata import local_project_id_override
SINGLE_COUNT_AGGREGATE_VIEW_ID: str = 'single_count_aggregate'
_DESCRIPTION = """
Copy single count data to a format for stitching.
"""
with open(os.path.splitext(__file__)[0] + '.sql') as fp:
_QUERY_TEMPLATE = fp.read()
SINGLE_COUNT_STITCH_SUBSET_VIEW_BUILDER = SimpleBigQueryViewBuilder(
dataset_id=dataset_config.VIEWS_DATASET,
view_id='single_count_stitch_subset',
view_query_template=_QUERY_TEMPLATE,
base_dataset=dataset_config.COUNTY_BASE_DATASET,
single_count_aggregate=SINGLE_COUNT_AGGREGATE_VIEW_ID,
description=_DESCRIPTION)
if __name__ == '__main__':
with local_project_id_override(GCP_PROJECT_STAGING):
SINGLE_COUNT_STITCH_SUBSET_VIEW_BUILDER.build_and_print()
"""
/*{description}*/
WITH cell_counts AS (
SELECT state_code as region_code, SUM(total_revocations) as total_revocations
FROM `{project_id}.{view_dataset}.revocations_matrix_cells`
WHERE metric_period_months = 36
GROUP BY state_code
),
month_counts AS (
SELECT state_code as region_code, SUM(total_revocations) as total_revocations
FROM `{project_id}.{view_dataset}.revocations_matrix_by_month`
WHERE DATE(year, month, 1) >= DATE_SUB(DATE_TRUNC(CURRENT_DATE('US/Pacific'), MONTH),
INTERVAL 36 - 1 MONTH)
GROUP BY state_code
)
SELECT c.region_code, c.total_revocations as cell_sum, m.total_revocations as month_sum
FROM cell_counts c JOIN month_counts m on c.region_code = m.region_code
"""
REVOCATION_MATRIX_COMPARISON_REVOCATION_CELL_VS_MONTH_VIEW_BUILDER = SimpleBigQueryViewBuilder(
dataset_id=dataset_config.VIEWS_DATASET,
view_id=REVOCATION_MATRIX_COMPARISON_REVOCATION_CELL_VS_MONTH_VIEW_NAME,
view_query_template=REVOCATION_MATRIX_COMPARISON_REVOCATION_CELL_VS_MONTH_QUERY_TEMPLATE,
description=REVOCATION_MATRIX_COMPARISON_REVOCATION_CELL_VS_MONTH_DESCRIPTION,
view_dataset=state_dataset_config.DASHBOARD_VIEWS_DATASET,
)
if __name__ == '__main__':
with local_project_id_override(GCP_PROJECT_STAGING):
REVOCATION_MATRIX_COMPARISON_REVOCATION_CELL_VS_MONTH_VIEW_BUILDER.build_and_print()
correctional_level,
correctional_level_priority,
-- Indicator for whether supervision level can be assigned based on risk level/PO discretion, to determine inclusion in downgrade/upgrade counts
correctional_level IN ('MAXIMUM', 'HIGH', 'MEDIUM', 'MINIMUM', 'LIMITED') AS is_discretionary_level
FROM UNNEST([
'INCARCERATED',
'IN CUSTODY',
'MAXIMUM',
'HIGH',
'MEDIUM',
'MINIMUM',
'LIMITED',
'UNSUPERVISED',
'DIVERSION',
'INTERNAL_UNKNOWN',
'EXTERNAL_UNKNOWN']) AS correctional_level
WITH OFFSET AS correctional_level_priority
"""
SUPERVISION_LEVEL_DEDUP_PRIORITY_VIEW_BUILDER = SimpleBigQueryViewBuilder(
dataset_id=ANALYST_VIEWS_DATASET,
view_id=SUPERVISION_LEVEL_DEDUP_PRIORITY_VIEW_NAME,
view_query_template=SUPERVISION_LEVEL_DEDUP_PRIORITY_QUERY_TEMPLATE,
description=SUPERVISION_LEVEL_DEDUP_PRIORITY_VIEW_DESCRIPTION,
should_materialize=False,
)
if __name__ == "__main__":
with local_project_id_override(GCP_PROJECT_STAGING):
SUPERVISION_LEVEL_DEDUP_PRIORITY_VIEW_BUILDER.build_and_print()
state_code,
outflow_to,
compartment_duration,
run_date,
SUM(total_population)/total_pop AS total_population
FROM parole_board_hold_union
JOIN (
SELECT
compartment, gender, state_code, run_date,
SUM(total_population) AS total_pop
FROM parole_board_hold_union
GROUP BY compartment, gender, state_code, run_date
)
USING (compartment, gender, state_code, run_date)
GROUP BY compartment, gender, state_code, outflow_to, compartment_duration, run_date, total_pop
"""
US_ID_PAROLE_BOARD_HOLD_FULL_TRANSITIONS_VIEW_BUILDER = SimpleBigQueryViewBuilder(
dataset_id=dataset_config.POPULATION_PROJECTION_DATASET,
view_id=US_ID_PAROLE_BOARD_HOLD_FULL_TRANSITIONS_VIEW_NAME,
view_query_template=US_ID_PAROLE_BOARD_HOLD_POPULATION_TRANSITIONS_QUERY_TEMPLATE,
description=US_ID_PAROLE_BOARD_HOLD_POPULATION_TRANSITIONS_VIEW_DESCRIPTION,
analyst_dataset=dataset_config.ANALYST_VIEWS_DATASET,
population_projection_dataset=dataset_config.POPULATION_PROJECTION_DATASET,
should_materialize=True,
)
if __name__ == "__main__":
with local_project_id_override(GCP_PROJECT_STAGING):
US_ID_PAROLE_BOARD_HOLD_FULL_TRANSITIONS_VIEW_BUILDER.build_and_print()
week_num, report_year, s.state_code, s.cohort, s.person_id,
s.start_date, s.termination_date,
i.admission_date, i.incarceration_type,
DATE_DIFF(report_end_date, GREATEST(earliest_start_date, s.start_date), DAY) AS days_since_start,
DATE_DIFF(i.admission_date, GREATEST(earliest_start_date, s.start_date), DAY) AS days_until_admission,
DATE_DIFF(COALESCE(i.admission_date, report_end_date), GREATEST(earliest_start_date, s.start_date), DAY) AS survival_days,
-- Indicate if this person should be counted as active during this report period
CASE WHEN admission_date IS NOT NULL THEN admission_date >= report_start_date
WHEN termination_date IS NOT NULL THEN termination_date >= report_start_date
ELSE TRUE END AS report_week_cohort
FROM unique_person_supervision s
LEFT JOIN incarcerations i
USING (state_code, person_id, week_num, report_year)
JOIN report_dates r USING (week_num, report_year)
WHERE supervision_rank = 1
"""
US_ID_SUPERVISION_COMMUNITY_PERFORMANCE_VIEW_BUILDER = SimpleBigQueryViewBuilder(
dataset_id=dataset_config.COVID_REPORT_DATASET,
reference_dataset=dataset_config.REFERENCE_VIEWS_DATASET,
state_dataset=dataset_config.STATE_BASE_DATASET,
view_id=US_ID_SUPERVISION_COMMUNITY_PERFORMANCE_VIEW_NAME,
view_query_template=US_ID_SUPERVISION_COMMUNITY_PERFORMANCE_QUERY_TEMPLATE,
description=US_ID_SUPERVISION_COMMUNITY_PERFORMANCE_DESCRIPTION,
covid_report_dataset=COVID_REPORT_DATASET,
)
if __name__ == '__main__':
with local_project_id_override(GCP_PROJECT_STAGING):
US_ID_SUPERVISION_COMMUNITY_PERFORMANCE_VIEW_BUILDER.build_and_print()
USING (state_code, job_id, year, month, metric_period_months),
{district_dimension}
WHERE methodology = 'PERSON'
AND metric_period_months = 0
AND person_id IS NOT NULL
AND year >= EXTRACT(YEAR FROM DATE_SUB(CURRENT_DATE(), INTERVAL 4 YEAR))
-- Get population count for the last day of the month
AND date_of_stay = DATE_SUB(DATE_ADD(DATE(year, month, 1), INTERVAL 1 MONTH), INTERVAL 1 DAY)
AND job.metric_type = 'INCARCERATION_POPULATION'
GROUP BY state_code, year, month, district
) inc_pop
USING (state_code, year, month, district)
WHERE year >= EXTRACT(YEAR FROM DATE_SUB(CURRENT_DATE(), INTERVAL 3 YEAR))
AND district IS NOT NULL
ORDER BY state_code, district, year, month
"""
ADMISSIONS_VERSUS_RELEASES_BY_MONTH_VIEW_BUILDER = SimpleBigQueryViewBuilder(
dataset_id=dataset_config.DASHBOARD_VIEWS_DATASET,
view_id=ADMISSIONS_VERSUS_RELEASES_BY_MONTH_VIEW_NAME,
view_query_template=ADMISSIONS_VERSUS_RELEASES_BY_MONTH_QUERY_TEMPLATE,
description=ADMISSIONS_VERSUS_RELEASES_BY_MONTH_DESCRIPTION,
reference_dataset=dataset_config.REFERENCE_TABLES_DATASET,
metrics_dataset=dataset_config.DATAFLOW_METRICS_DATASET,
district_dimension=bq_utils.unnest_district(
district_column='county_of_residence'))
if __name__ == '__main__':
with local_project_id_override(GAE_PROJECT_STAGING):
ADMISSIONS_VERSUS_RELEASES_BY_MONTH_VIEW_BUILDER.build_and_print()
from recidiviz.calculator.query.state import dataset_config as state_dataset_config
from recidiviz.persistence.entity.state.entities import StateIncarcerationPeriod
from recidiviz.utils.environment import GCP_PROJECT_STAGING
from recidiviz.utils.metadata import local_project_id_override
from recidiviz.validation.views import dataset_config
from recidiviz.validation.views.state.overlapping_periods_template import (
overlapping_periods_query,
)
OVERLAPPING_INCARCERATION_PERIODS_VIEW_NAME = "overlapping_incarceration_periods"
OVERLAPPING_INCARCERATION_PERIODS_DESCRIPTION = """ Incarceration periods with another incarceration
period with overlapping dates """
OVERLAPPING_INCARCERATION_PERIODS_QUERY_TEMPLATE = f"""
/*{{description}}*/
{overlapping_periods_query(StateIncarcerationPeriod)}
"""
OVERLAPPING_INCARCERATION_PERIODS_VIEW_BUILDER = SimpleBigQueryViewBuilder(
dataset_id=dataset_config.VIEWS_DATASET,
view_id=OVERLAPPING_INCARCERATION_PERIODS_VIEW_NAME,
view_query_template=OVERLAPPING_INCARCERATION_PERIODS_QUERY_TEMPLATE,
description=OVERLAPPING_INCARCERATION_PERIODS_DESCRIPTION,
state_dataset=state_dataset_config.STATE_BASE_DATASET,
)
if __name__ == "__main__":
with local_project_id_override(GCP_PROJECT_STAGING):
OVERLAPPING_INCARCERATION_PERIODS_VIEW_BUILDER.build_and_print()
{supervision_dimension}
WHERE methodology = 'EVENT'
AND metric_period_months = 1
AND assessment_type = 'LSIR'
AND assessment_score_change IS NOT NULL
AND person_id IS NOT NULL
AND month IS NOT NULL
AND year >= EXTRACT(YEAR FROM DATE_SUB(CURRENT_DATE(), INTERVAL 3 YEAR))
AND job.metric_type = 'SUPERVISION_TERMINATION'
)
WHERE supervision_type IN ('ALL', 'PAROLE', 'PROBATION')
AND supervision_rank = 1
GROUP BY state_code, termination_year, termination_month, supervision_type, district
ORDER BY state_code, termination_year, termination_month, district, supervision_type
"""
AVERAGE_CHANGE_LSIR_SCORE_MONTH_VIEW_BUILDER = SimpleBigQueryViewBuilder(
dataset_id=dataset_config.DASHBOARD_VIEWS_DATASET,
view_id=AVERAGE_CHANGE_LSIR_SCORE_MONTH_VIEW_NAME,
view_query_template=AVERAGE_CHANGE_LSIR_SCORE_MONTH_QUERY_TEMPLATE,
description=AVERAGE_CHANGE_LSIR_SCORE_MONTH_DESCRIPTION,
metrics_dataset=dataset_config.DATAFLOW_METRICS_DATASET,
reference_dataset=dataset_config.REFERENCE_TABLES_DATASET,
district_dimension=bq_utils.unnest_district(),
supervision_dimension=bq_utils.unnest_supervision_type(),
)
if __name__ == '__main__':
with local_project_id_override(GAE_PROJECT_STAGING):
AVERAGE_CHANGE_LSIR_SCORE_MONTH_VIEW_BUILDER.build_and_print()
NULL AS female_black,
NULL AS female_native_american,
NULL AS female_latino,
NULL AS female_white,
NULL AS female_other,
NULL AS female_unknown_race,
NULL AS unknown_gender_asian,
NULL AS unknown_gender_black,
NULL AS unknown_gender_native_american,
NULL AS unknown_gender_latino,
NULL AS unknown_gender_white,
NULL AS unknown_gender_other,
NULL AS unknown_gender_unknown_race
FROM
`{project_id}.{vera_dataset}.{incarceration_trends}`
"""
INCARCERATION_TRENDS_STITCH_SUBSET_VIEW_BUILDER = SimpleBigQueryViewBuilder(
dataset_id=dataset_config.VIEWS_DATASET,
view_id="incarceration_trends_stitch_subset",
view_query_template=_QUERY_TEMPLATE,
vera_dataset=VERA_DATASET,
incarceration_trends=INCARCERATION_TRENDS_TABLE,
description=_DESCRIPTION,
)
if __name__ == "__main__":
with local_project_id_override(GCP_PROJECT_STAGING):
INCARCERATION_TRENDS_STITCH_SUBSET_VIEW_BUILDER.build_and_print()
SELECT state_code as region_code, year, month, SUM(discharge_count) as discharge_count
FROM `{project_id}.{view_dataset}.supervision_discharges_by_officer_by_month`
WHERE district != 'ALL' AND officer_external_id != 'ALL'
GROUP BY region_code, year, month
)
SELECT region_code, year, month,
by_month.absconsion_count as absconsions_by_month,
absconsions_by_officer.absconsion_count as absconsions_by_officer,
by_month.discharge_count as discharges_by_month,
discharges_by_officer.discharge_count as discharges_by_officer
FROM by_month
FULL OUTER JOIN absconsions_by_officer USING (region_code, year, month)
FULL OUTER JOIN discharges_by_officer USING (region_code, year, month)
WHERE by_month.absconsion_count != absconsions_by_officer.absconsion_count
OR by_month.discharge_count != discharges_by_officer.discharge_count
ORDER BY region_code, year, month
"""
CASE_TERMINATIONS_BY_TYPE_COMPARISON_VIEW_BUILDER = SimpleBigQueryViewBuilder(
dataset_id=dataset_config.VIEWS_DATASET,
view_id=CASE_TERMINATIONS_BY_TYPE_COMPARISON_VIEW_NAME,
view_query_template=CASE_TERMINATIONS_BY_TYPE_COMPARISON_QUERY_TEMPLATE,
description=CASE_TERMINATIONS_BY_TYPE_COMPARISON_DESCRIPTION,
view_dataset=state_dataset_config.DASHBOARD_VIEWS_DATASET,
)
if __name__ == '__main__':
with local_project_id_override(GAE_PROJECT_STAGING):
CASE_TERMINATIONS_BY_TYPE_COMPARISON_VIEW_BUILDER.build_and_print()
`{project_id}.{base_dataset}.state_person`
USING (state_code, person_id))
GROUP BY state_code, facility, date_of_stay)
LEFT JOIN
`{project_id}.{static_reference_dataset}.state_incarceration_facility_capacity`
USING (state_code, facility)
ORDER BY state_code, facility, date_of_stay
"""
FACILITY_POPULATION_BY_AGE_WITH_CAPACITY_BY_DAY_VIEW_BUILDER = SimpleBigQueryViewBuilder(
dataset_id=dataset_config.COVID_REPORT_DATASET,
view_id=FACILITY_POPULATION_BY_AGE_WITH_CAPACITY_BY_DAY_VIEW_NAME,
view_query_template=
FACILITY_POPULATION_BY_AGE_WITH_CAPACITY_BY_DAY_QUERY_TEMPLATE,
description=FACILITY_POPULATION_BY_AGE_WITH_CAPACITY_BY_DAY_DESCRIPTION,
base_dataset=STATE_BASE_DATASET,
metrics_dataset=DATAFLOW_METRICS_DATASET,
static_reference_dataset=STATIC_REFERENCE_TABLES_DATASET,
reference_views_dataset=dataset_config.REFERENCE_VIEWS_DATASET,
facility_dimension=bq_utils.unnest_column('facility', 'facility'),
state_specific_facility_exclusion=state_specific_query_strings.
state_specific_facility_exclusion(),
filter_to_most_recent_job_id_for_metric=bq_utils.
filter_to_most_recent_job_id_for_metric(
reference_dataset=dataset_config.REFERENCE_VIEWS_DATASET))
if __name__ == '__main__':
with local_project_id_override(GCP_PROJECT_STAGING):
FACILITY_POPULATION_BY_AGE_WITH_CAPACITY_BY_DAY_VIEW_BUILDER.build_and_print(
)
BW_SCD = FH_SCD
WHERE
BW_DOC IS NOT NULL AND
BW_CYC IS NOT NULL AND
BV_SEO IS NOT NULL AND
BW_SSO IS NOT NULL
) all_statuses
LEFT OUTER JOIN
`{project_id}.{base_dataset}.state_supervision_sentence` supervision_sentences
ON
supervision_sentences.external_id = sentence_external_id
LEFT OUTER JOIN
`{project_id}.{base_dataset}.state_incarceration_sentence` incarceration_sentences
ON
incarceration_sentences.external_id = sentence_external_id
WHERE (incarceration_sentences.person_id IS NOT NULL OR supervision_sentences.person_id IS NOT NULL);
"""
US_MO_SENTENCE_STATUSES_VIEW_BUILDER = SimpleBigQueryViewBuilder(
dataset_id=dataset_config.REFERENCE_VIEWS_DATASET,
view_id=US_MO_SENTENCE_STATUSES_VIEW_NAME,
view_query_template=US_MO_SENTENCE_STATUSES_QUERY_TEMPLATE,
description=US_MO_SENTENCE_STATUSES_DESCRIPTION,
base_dataset=dataset_config.STATE_BASE_DATASET,
static_reference_dataset=dataset_config.STATIC_REFERENCE_TABLES_DATASET,
)
if __name__ == "__main__":
with local_project_id_override(GCP_PROJECT_STAGING):
US_MO_SENTENCE_STATUSES_VIEW_BUILDER.build_and_print()
INNER JOIN `{project_id}.{vitals_views_dataset}.supervision_population_by_po_by_day_materialized` sup_pop
ON sup_pop.state_code = overdue_contacts.state_code
AND sup_pop.date_of_supervision = overdue_contacts.date_of_supervision
AND sup_pop.supervising_officer_external_id = overdue_contacts.supervising_officer_external_id
AND {vitals_state_specific_join_with_supervision_population}
WHERE overdue_contacts.level_1_supervision_location_external_id = 'ALL' OR overdue_contacts.state_code IN {vitals_level_1_state_codes}
"""
TIMELY_CONTACT_BY_PO_BY_DAY_VIEW_BUILDER = SimpleBigQueryViewBuilder(
dataset_id=dataset_config.VITALS_REPORT_DATASET,
view_id=TIMELY_CONTACT_BY_PO_BY_DAY_VIEW_NAME,
view_query_template=TIMELY_CONTACT_BY_PO_BY_DAY_QUERY_TEMPLATE,
description=TIMELY_CONTACT_BY_PO_BY_DAY_DESCRIPTION,
materialized_metrics_dataset=dataset_config.
DATAFLOW_METRICS_MATERIALIZED_DATASET,
reference_views_dataset=dataset_config.REFERENCE_VIEWS_DATASET,
vitals_views_dataset=dataset_config.VITALS_REPORT_DATASET,
vitals_state_specific_join_with_supervision_location_ids=
state_specific_query_strings.
vitals_state_specific_join_with_supervision_location_ids(
"overdue_contacts"),
vitals_state_specific_join_with_supervision_population=
state_specific_query_strings.
vitals_state_specific_join_with_supervision_population("overdue_contacts"),
vitals_level_1_state_codes=VITALS_LEVEL_1_SUPERVISION_LOCATION_OPTIONS,
)
if __name__ == "__main__":
with local_project_id_override(GCP_PROJECT_STAGING):
TIMELY_CONTACT_BY_PO_BY_DAY_VIEW_BUILDER.build_and_print()
CASE WHEN dataflow.end_reason IN ('COMMUTED', 'COMPASSIONATE', 'CONDITIONAL_RELEASE', 'SENTENCE_SERVED') THEN 1 ELSE 0 END AS dataflow_release,
CASE WHEN dataflow.end_reason IN ('COMMUTED', 'COMPASSIONATE', 'CONDITIONAL_RELEASE', 'SENTENCE_SERVED')
AND sessions.person_id IS NOT NULL THEN 1 ELSE 0 END AS sub_session_release,
CASE WHEN dataflow.end_reason IN ('COMMUTED', 'COMPASSIONATE', 'CONDITIONAL_RELEASE', 'SENTENCE_SERVED')
AND sessions.last_sub_session_in_session = 1 THEN 1 ELSE 0 END AS session_release,
FROM `{project_id}.{analyst_dataset}.compartment_sub_sessions_materialized` sessions
FULL OUTER JOIN dataflow_session_ends dataflow
USING(person_id, end_date, state_code, compartment_level_1)
WHERE end_date IS NOT NULL
AND compartment_level_1 = 'INCARCERATION'
AND EXTRACT(YEAR FROM end_date) > EXTRACT(YEAR FROM DATE_SUB(CURRENT_DATE(), INTERVAL 20 YEAR))
ORDER BY state_code, end_date
"""
SUB_SESSIONS_INCARCERATION_RELEASES_TO_DATAFLOW_VIEW_BUILDER_DISAGGREGATED = SimpleBigQueryViewBuilder(
dataset_id=dataset_config.VIEWS_DATASET,
view_id=
SUB_SESSIONS_INCARCERATION_RELEASES_TO_DATAFLOW_DISAGGREGATED_VIEW_NAME,
view_query_template=
SUB_SESSIONS_INCARCERATION_RELEASES_TO_DATAFLOW_DISAGGREGATED_QUERY_TEMPLATE,
description=
SUB_SESSIONS_INCARCERATION_RELEASES_TO_DATAFLOW_DISAGGREGATED_DESCRIPTION,
analyst_dataset=ANALYST_VIEWS_DATASET,
)
if __name__ == "__main__":
with local_project_id_override(GCP_PROJECT_STAGING):
SUB_SESSIONS_INCARCERATION_RELEASES_TO_DATAFLOW_VIEW_BUILDER_DISAGGREGATED.build_and_print(
)
