Data platform.
Warehouses built for events. Star schemas, medallion layering, incremental loads, semantic layers, slowly changing dimensions, pipeline testing, orchestration and cost.
21 articles · Data platform
- How to declare the event data warehouse grain before you build tablesevent data warehouse grainDeclaring the event data warehouse grain means saying what one fact row represents. One edition with three business processes needs three separate grain statements.
- Atomic grain versus aggregates when storing badge scans and registrationsatomic grain versus aggregatesAtomic grain versus aggregates decides which questions you can still answer in year three. Collapsing 38,900 scans to 1,096 summary rows loses repeat visits.
- A fact table grain mismatch is why your two reports disagreefact table grain mismatchA fact table grain mismatch inflates every sum. Joining 900 registrations to 3,400 scans returns 3,400 rows and reports registration revenue at 3.8 times its true value.
- Designing the event and edition dimension for a show that repeats yearlyevent and edition dimension designEvent and edition dimension design needs two keys: a durable event key for the show brand and a surrogate key per edition. Eight brands over five years is 40 rows.
- Adding days to show open to your date dimensiondays to show openDays to show open cannot be a column on a shared date dimension, because one calendar date holds a different offset for every edition running that season.
- Modelling the venue and hall dimension when a show changes buildingsvenue and hall dimensionA venue and hall dimension has to hold two speeds: a building that changes every few years and a hall configuration that changes every edition.
- Conformed dimensions across shows are what make a portfolio roll up workconformed dimensions across showsConformed dimensions across shows are the condition for a portfolio roll up. Two local code lists of 41 and 62 categories report 103 sectors where 74 exist.
- Drawing an event bus matrix before you model a single fact tableevent bus matrixAn event bus matrix puts business processes down the side and dimensions across the top. A 6 by 9 grid shows which dimensions to build before anything else.
- An industry taxonomy mapping table beats forcing every show to renumberindustry taxonomy mapping tableAn industry taxonomy mapping table keeps each show's local code list and links it to a conformed master. 132 local values map to 28 master sectors with 9 unmapped.
- A slowly changing company dimension for exhibitors that merge and rebrandslowly changing company dimensionA slowly changing company dimension keeps type 2 rows so a 2023 contract still reports under the exhibitor's 2023 name after the firm rebrands in 2024.
- Choosing type 2 versus type 1 attributes on your contact dimensiontype 2 versus type 1 attributesType 2 versus type 1 attributes decides which contact columns keep history. Job title and employer need a new row. A corrected email should overwrite.
- Using a mini dimension for contact attributes that change every editionmini dimension for contact attributesA mini dimension for contact attributes pulls volatile bands like seniority and buying role out of the contact dimension into one small profile table.
- Badge scan fact table design that survives five editions of questionsbadge scan fact table designBadge scan fact table design is mostly a key list. Eight foreign keys, one degenerate scan identifier and no measure will answer questions nobody has asked yet.
- A session attendance fact table needs both a scan and a capacitysession attendance fact tableA session attendance fact table holds one row per entry and no measure. Room capacity belongs on the session dimension, so fill rate is computed at query time.
- When a factless fact table gives you the coverage denominatorfactless fact table coverageA factless fact table coverage row exists for every eligible pairing, so exhibitors nobody scanned and sessions nobody entered get a denominator.
- Medallion architecture for event data and where the layers earn their keepmedallion architecture for event dataMedallion architecture for event data pays off unevenly: bronze earns its keep at the first edition, silver at the second show, gold at the roll-up.
- Deciding bronze layer retention when a show runs once a yearbronze layer retentionBronze layer retention for an annual show runs on two clocks: how many editions a comparison needs, and how long the personal data may lawfully be kept.
- Designing burst shaped ingestion pipelines for a show that peaks in four daysburst shaped ingestion pipelinesBurst shaped ingestion pipelines for an exhibition get sized from the busiest hour. 38,900 scans across 96 hours peaks near 2,400 an hour, a very small load.
- Running an annual cadence data pipeline that sits idle for eleven monthsannual cadence data pipelineAn annual cadence data pipeline is broken by the time you need it. Credentials, runtimes and schemas expire on their own clocks. A rehearsal run finds it.
- Idempotent warehouse loads mean you can rerun show day without fearidempotent warehouse loadsIdempotent warehouse loads let you rerun a half finished show day job without thinking. Append semantics turn one interrupted run into 62,900 rows from 38,900.
- Merge versus insert overwrite for loading a completed show editionmerge versus insert overwriteMerge versus insert overwrite decided on batch size: a 400 row correction and a 12,000 row re-extract into the same show edition want opposite loaders.