BrainTrust Investment Lab › Band 2 — Transform (Cleaning & Conforming)
Band 2 — Transform (Cleaning & Conforming) · book 456-458 · grain: one source per walk sheet
The industry's dirty secret is the silent fix — values corrected on the way in, no record kept. Our screens tag every flaw and keep the original: the data stays exactly as the provider filed it, the defect wears a label, and nothing is ever quietly dropped.
completed
0 of 48
running now
sec_cik
completedrunningawaiting its turn · owedfailedexcluded
sec_cik↻rank 1 · walking · 12 step(s) certified
security_master↻rank 2 · walking · 12 step(s) certified
prices↻rank 3 · walking · 12 step(s) certified
edgar_filings↻rank 4 · walking · 12 step(s) certified
companyfacts↻rank 5 · walking · 12 step(s) certified
submissions↻rank 6 · walking · 12 step(s) certified
overview↻rank 7 · walking · 11 certified · 1 owed
form345rank 8 · queued
fsdsrank 9 · queued
ftdrank 10 · queued
finra_shortrank 11 · queued
sec_name_cikrank 12 · queued
notesrank 13 · queued
framesrank 14 · queued
fredrank 15 · queued
waybackrank 16 · queued
pbsrank 17 · queued
wikipediarank 18 · queued
edgarrank 19 · queued
av_transcripts↻rank 20 · walking · 12 step(s) certified
shred_8krank 21 · queued
shred_13dgrank 22 · queued
aapbrank 23 · queued
bdcrank 24 · queued
crowdfundingrank 25 · queued
form13frank 26 · queued
formdrank 27 · queued
market_structurerank 28 · queued
mfp_rrrank 29 · queued
ncenrank 30 · queued
nmfprank 31 · queued
nportrank 32 · queued
regarank 33 · queued
sec_enforcementrank 34 · queued
sec_filings_statsrank 35 · queued
sec_registriesrank 36 · queued
transfer_agentrank 37 · queued
viprank 38 · queued
fomc_textrank 39 · queued
lda_lobbyingrank 40 · excluded
av_options↻rank 41 · walking · 12 step(s) certified
wikidatarank 42 · queued
dividends↻rank 43 · walking · 10 certified · 2 owed
splits↻rank 44 · walking · 10 certified · 2 owed
gdeltrank 45 · queued
usaspendingrank 46 · queued
finra_regshorank 47 · queued
edgar_logsrank 48 · queued
edgar_docsrank 49 · queued
The contract — what each step must produce
| # | step | page | what the book requires |
|---|---|---|---|
| 3 | the screen inventory, DECLARED -- typed against p.457's closed set | p.457 | 'The heart of the ETL architecture is a set of quality screens that act as diagnostic filters in the data flow pipelines. Each quality screen is a… |
| 4 | column screens, MEASURED this run -- against a declared rule, per column | p.457 | 'Column screens test the data within a single column... testing whether a column contains unexpected null values, if a value falls outside of a… |
| 5 | structure screens, MEASURED -- beside a denominator, on a key that can fail | p.457 | 'Structure screens also test foreign key/primary key relationships between columns in two tables, and also include testing whole blocks of columns to… |
| 6 | business-rule screens, DECLARED -- with a threshold and its own basis | p.458 | 'Business rule screens implement more complex tests... Business rule screens also include aggregate threshold data quality checks... the screen… |
| 7 | the exception action, RULED -- TAG by default, SUSPENSE refused at the act | p.458 | 'The choices are: 1) halting the process; 2) sending the offending record(s) to a suspense file for later processing; and 3) merely tagging the data… |
| 8 | the balance between fixing and preserving, STRUCK PER SCREEN | p.456 | 'The ETL data cleansing process is often expected to fix dirty data, yet at the same time the data warehouse is expected to provide an accurate… |
| 9 | the screens are TRIAGED -- ranked, not just listed | p.457 | 'Goals for the subsystem should include: Early diagnosis and triage of data quality issues' |
| 10 | tagging IN THE ATTRIBUTE ITSELF, the book's second mechanism | p.458 | 'The third option of tagging the data with the error condition often works well. Bad fact table data can be tagged with the audit dimension, as… |
| 11 | the risk of moving forward, ACCEPTED IN WRITING | p.457 | 'The results of that effort can help you understand the risks of moving forward with potentially dirty or unreliable data and help you determine how… |
| 12 | what goes BACK to the provider | p.457 | 'Goals for the subsystem should include:' ... 'Requirements for source systems and integration efforts to supply better data' |
| 13 | EVERY FAILURE LANDS AS A ROW -- not a screen until it drops one | p.457 | 'If the test against the data is successful, nothing happens and the screen has no side effects. But if the test fails, then it must drop an error… |