mohannadibrahim.dev

README.md/case studies/c5 aec pipeline

Case study 05 / Data engineering Open source

Four public data sources, one Snowflake star schema

A personal, open-repo pipeline that conforms four live public sources into a single medallion star schema, and proves it with tests, entity resolution, and logged quality checks.

At a glancec5 / aec pipeline

Type
Personal project
Repo
Ned-Ibrahim/aec-pursuit-model
Sources
4 live, public
Target
Snowflake star schema
Tests
358 pytest
Result
1,957 into 1,952, 0 false merges
Stack
Python, SQL, Snowflake, pytest [CONFIRM: any other tools in the repo]
Quality
55 data-quality checks logged per load
On this page
  1. Problem
  2. Constraints
  3. Approach
  4. Decision
  5. Evaluation
  6. Result
  7. What I'd do next

01Problem

Public infrastructure opportunities are published in four different shapes: the SAM.gov API, a Nebraska DOT PDF, a City of San Diego CIP CSV, and the Texas DOT Socrata API.

The same project can appear in more than one of them, so counting records is not the same as counting opportunities.

Placeholder[CONFIRM: who the pipeline is for and what question it answers, in one sentence.]

02Constraints

  • The sources are live and public, and they arrive as an API, a PDF, a CSV, and a Socrata API.
  • All four have to land in one Snowflake medallion star schema.
  • A wrong merge is worse than a missed one, so false merges have to stay at zero.
  • [CONFIRM: refresh cadence, data volume, and any rate limits or licensing terms.]

03Approach

Three steps, each checkable.

  1. Ingest. Four live public sources: SAM.gov API, Nebraska DOT PDF, City of San Diego CIP CSV, Texas DOT Socrata API.
  2. Conform. Everything lands in one Snowflake medallion star schema.
  3. Resolve and check. Entity resolution collapses duplicates, and 55 data-quality checks are logged on every load.
FOUR LIVE PUBLIC SOURCES SAM.govAPI Nebraska DOTPDF City of San Diego CIPCSV Texas DOTSocrata API SNOWFLAKE Medallion star schema one conformed model ENTITY RES.1,957 to 1,952 PER LOAD55 DQ checks TESTS358 pytest FOUR LIVE PUBLIC SOURCES SAM.govAPI Nebraska DOTPDF San Diego CIPCSV Texas DOTSocrata API SNOWFLAKE Medallion star schema one conformed model ENTITY RESOLUTION1,957 records to 1,952 PER LOAD55 data-quality checks logged TESTS358 pytest
Fig. 1Four shapes in, one model out. Entity resolution, the per-load quality log, and the test suite are what make the model trustworthy. [CONFIRM: exact order of entity resolution relative to the schema layers.]

04Decision

One Snowflake medallion star schema for all four sources. Conforming the API, PDF, CSV, and Socrata feeds into a single model means every question is asked once, against one shape.

Alternatives considered
Keep each source in its own table[CONFIRM]

[CONFIRM: why this was not chosen.]

[CONFIRM: other options weighed][CONFIRM]

[CONFIRM: reasoning.]

Medallion star schema in Snowflakechosen

Four sources conformed into one model.

05Evaluation

358 pytest tests cover the code. 55 data-quality checks are logged on every load, so a bad load leaves a record.

Placeholder[CONFIRM: two or three example checks, and how entity-resolution false merges are measured.]

06Result

1,957 to 1,952records resolved to opportunities, false merges zero

Four live public sources in one star schema, with 358 tests behind it. The code is open: github.com/Ned-Ibrahim/aec-pursuit-model.

07What I'd do next

  • Show the quality log. Surface the 55 per-load checks as a visible trend, so drift shows up before a user notices.
  • Add a fifth source. The same ingest, conform, resolve path should take another public feed without changing the model. [CONFIRM: which source.]
  • Document the merge rules. Write down why 1,957 became 1,952, so the zero-false-merge claim can be audited.