SQL guide
Mohammed Beheiry

I kept each step in a separate SQL file so the checks and results are easy to follow. There are 29 files, grouped by source checks, QA, models and assignment question. The file numbers show the order I worked through them.

Environment

These queries use BigQuery GoogleSQL.
Project: fec-contributions-analysis
Dataset: fec_analysis
Data and job location: US
Public source: bigquery-public-data.fec

To run them in another project, create a dataset in the US region and replace fec-contributions-analysis.fec_analysis in the SQL with your own project and dataset. Leave the public source names unchanged.

Run order

For the full analysis, run 01 through 29 in number order across the folders.

For the main results:
- Run 11, 16, 17 and 18 to build the original models.
- Run 12 for the candidate summary, 19 for the reconciliation and 21 for the monthly chart data. Query 18 also returns the peak month.
- Run 27 for the WinRed-exclusion comparison used in the stakeholder summary.
- Run 28 to check ActBlue and WinRed committee reporting.
- Run 29 last to save the deduplicated individual records, add the committee and candidate dimensions, and extend the fact view.

Query 20 supports the Q3 analysis plan. I did not build a business-payee ranking.

Queries 11, 16, 17, 18 and 29 create or replace objects in the destination dataset. The others are read-only. If you rerun 17 or 18 after 29, run 29 again to restore the final model.

Query 19 checks against the totals recorded during my analysis. If the public data changes, investigate the difference before changing those expected totals. The saved tables also depend on the destination dataset's expiration settings.

Query list

The scan figures below were recorded during the analysis. They are not prices or guaranteed scan sizes. Check BigQuery's current estimate before running. A dash means I did not record an estimate.

00_Source_exploration/01_source_inventory.sql
  Lists source tables, row counts and rounded sizes.
  Run after: Public sources. Scan estimate: 0 GB.

00_Source_exploration/02_source_schema.sql
  Lists column names and data types.
  Run after: Public sources. Scan estimate: 0.010 GB.

10_Data_quality_checks/03_indiv_id_check.sql
  Counts missing and repeated individual record IDs.
  Run after: Public sources. Scan estimate: 1.62 GB.

10_Data_quality_checks/04_qa_indiv_repeated_ids.sql
  Compares selected fields within repeated 2020 IDs.
  Run after: Public sources. Scan estimate: 14.73 GB.

10_Data_quality_checks/05_qa_indiv_conflicting_fields.sql
  Identifies the fields that differ for repeated IDs.
  Run after: Public sources. Scan estimate: — GB.

10_Data_quality_checks/06_qa_indiv_profile.sql
  Profiles individual transaction types, memo flags, amounts and dates.
  Run after: Public sources. Scan estimate: 6.68 GB.

10_Data_quality_checks/07_qa_committee_source_comparison.sql
  Compares the four candidate-contribution source versions.
  Run after: Public sources. Scan estimate: — GB.

10_Data_quality_checks/08_qa_committee_2020_overlap.sql
  Compares IDs and fields between the two 2020 candidate sources.
  Run after: Public sources. Scan estimate: — GB.

10_Data_quality_checks/09_qa_candidate_id_differences.sql
  Examines candidate attribution differences.
  Run after: Public sources. Scan estimate: — GB.

10_Data_quality_checks/10_qa_committee_profile.sql
  Profiles candidate-contribution transaction categories.
  Run after: Public sources. Scan estimate: — GB.

20_Data_models/11_model_committee_staging.sql
  Creates stg_committee_contributions; returns candidate-reference coverage.
  Run after: Public sources. Scan estimate: — GB.

40_Q2_Candidate_contributions/12_q2_candidate_summary.sql
  Returns the Q2 candidate count and distribution.
  Run after: 11. Scan estimate: 0.03773 GB.

10_Data_quality_checks/13_qa_other_committee_profile.sql
  Profiles the broader committee transaction sources.
  Run after: Public sources. Scan estimate: — GB.

10_Data_quality_checks/14_qa_committee_overlap.sql
  Checks overlap between broader and candidate committee sources.
  Run after: 11. Scan estimate: — GB.

10_Data_quality_checks/15_qa_committee_unmatched_records.sql
  Inspects the 25 unmatched candidate records.
  Run after: 11. Scan estimate: — GB.

20_Data_models/16_model_committee_q1.sql
  Creates stg_committee_q1; returns eligible committee totals by cycle.
  Run after: Public sources; QA 13–15 informs the rules. Scan estimate: — GB.

20_Data_models/17_model_individual_q1.sql
  Creates stg_individual_q1; returns eligible individual totals by cycle.
  Run after: Public sources; QA 03–06 informs the rules. Scan estimate: — GB.

20_Data_models/18_model_monthly_contributions.sql
  Creates fct_contributions and agg_contributions_monthly; returns the peak month.
  Run after: 16, 17. Scan estimate: — GB.

10_Data_quality_checks/19_qa_monthly_reconciliation.sql
  Reconciles monthly aggregates to recorded source totals.
  Run after: 18; fixed expected results from 16 and 17. Scan estimate: 0.00000408 GB.

50_Q3_Business_ranking_plan/20_qa_operating_expenditures.sql
  Profiles operating-expenditure coverage for the Q3 plan.
  Run after: Public sources. Scan estimate: 0.40133 GB.

30_Q1_Contribution_sources/21_monthly_chart_data.sql
  Returns the 48 monthly chart rows and growth rates.
  Run after: 18. Scan estimate: 0.00000333 GB.

10_Data_quality_checks/22_qa_largest_candidate.sql
  Breaks down the largest candidate total by committee and cycle.
  Run after: 11. Scan estimate: 0.05072 GB.

10_Data_quality_checks/23_qa_intermediary_reporting.sql
  Separates conduit and recipient reporting groups.
  Run after: Public sources. Scan estimate: 10.69 GB.

10_Data_quality_checks/24_qa_winred_candidate_reporting.sql
  Inspects WinRed reporting for the largest candidate.
  Run after: Public sources. Scan estimate: — GB.

10_Data_quality_checks/25_qa_winred_linkage.sql
  Profiles the available WinRed linkage fields.
  Run after: Public sources. Scan estimate: 16.81 GB.

10_Data_quality_checks/26_qa_winred_matching_records.sql
  Compares WinRed and recipient records using five matching fields.
  Run after: Public sources. Scan estimate: 14.46 GB.

10_Data_quality_checks/27_qa_winred_sensitivity.sql
  Measures the effect of excluding WinRed conduit reporting.
  Run after: 11, 16, 17. Scan estimate: 9.22 GB.

10_Data_quality_checks/28_qa_committee_platform_reporting.sql
  Checks ActBlue and WinRed committee reporting in Q1 and Q2.
  Run after: 11 and 16. Scan estimate: 0.31378 GB.

20_Data_models/29_model_reporting_context.sql
  Saves individual_records_cached, creates dim_committee and dim_candidate, and updates the individual staging and fact views.
  Run after: 11, 16 and 18.
  Recorded scans: 9.06 GB for the individual table and 4.04 GB for the final reconciliation, plus smaller reference-table reads.
  Both source totals matched the original results. The individual table is clustered without date partitioning because the sandbox expiration settings did not retain the historical partitions during testing.

Saved results and charts

The QA_results folder beside this sql folder contains the saved JSON results, monthly comparison data and follow-up job IDs. Its provenance.json file records where the outputs came from. The checks were not all rerun when I packaged the report.

The notebook uses pandas and Matplotlib. Keep 21_monthly_chart_data.csv beside the notebook when running it. The HTML includes the charts and SQL, so it can be read without BigQuery or Colab.

The Python file in QA_results, reconcile_winred_sensitivity.py, checks the exported WinRed comparison using Python's standard library.
