A corporate gifting data mart should answer operational and finance questions without becoming a second address book. The practical design is an event ledger with a narrow analytics identity, explicit purpose boundaries, repeatable incremental loads, and reconciliation evidence that explains every late status, cancellation, and replacement.
Decide what the mart is allowed to answer
Start with decisions, not columns. A useful BigQuery corporate gifting model may need to answer how many approved invitations became claims, which shipments reached a terminal state, how replacements changed delivered-unit counts, and whether campaign cost agrees with finance. It usually does not need a recipient's full name, street address, support transcript, or original eligibility record.
Write a source-to-model contract before choosing tables. For each source, record the accountable owner, approved transfer method, permitted purpose, refresh expectation, stable key, timestamp semantics, currency rule, late-arrival window, deletion or restriction trigger, and failure route. The contract must distinguish an approved export from a product integration. The architecture below assumes a buyer-controlled, documented transfer. It does not assert a native connection between any gifting platform and BigQuery.
Compare three shapes. A current-state snapshot is simple for dashboards, but it can hide reversals and makes replay difficult. A fully normalized operational replica preserves detail, but copies fields and dependencies the analytics team may not need. An append-oriented event ledger plus narrow dimensions keeps history and supports recovery while limiting identity. Choose the third pattern when auditors must reproduce how a delivery or cost result changed. Choose a snapshot when history is genuinely unnecessary and the source can regenerate it. Do not build the mart if the team lacks an approved purpose, a stable key, or an owner for restricted data.
The buyer's acceptance question is concrete: can an authorized reviewer reproduce one campaign total from documented inputs, identify every exception, and do so without opening delivery addresses? If not, more data will not repair the design.
Fix the grain before defining metrics
The central table should have one row per accepted source event version, not one row per person or one mutable row per order. A practical fact_gift_event can contain a stable event key, source event version, program key, campaign key, pseudonymous recipient reference, shipment or order reference, event type, event time, ingestion time, market, currency, amount category, source batch, and quality disposition. Keep direct delivery identifiers in a restricted operational system or mapping table outside the general analytics dataset.
Event time says when the business action occurred. Ingestion time says when the warehouse received it. Keep both. A carrier correction can arrive today for an event that occurred last week; sorting only by ingestion time produces the wrong business sequence, while sorting only by event time can conceal what the system knew at close.
Use immutable event history and derive a current-state view. A delivery reversal should add a versioned event or correction relation rather than silently rewriting the evidence row. A replacement should carry its own shipment key plus a reference to the replaced unit. A cancellation should be an explicit business event, not a deleted record. Finance facts may live separately when accounting dates, exchange-rate policies, or recognition rules differ from operational event time.
| Layer | Recommended grain | Permitted identity | Acceptance evidence |
|---|---|---|---|
| Landing | One source file or message as received | Only fields approved by the contract | Object ID, hash, receipt time, source count |
| Event fact | One accepted event key and version | Pseudonymous recipient reference | Deduplication result, source lineage, quality state |
| Current view | One current state per business unit | No delivery address | Winning-version rule and replay test |
| Finance fact | One approved cost or adjustment line | Campaign and accounting references | Currency policy, close period, signed variance |
| Restricted map | One temporary operational identity link | Minimum delivery or support fields | Owner, access reason, expiry, deletion evidence |
The table is a design contract, not a universal schema. Teams may split facts further, but each metric must name its grain. “Delivered gifts” might mean terminal delivered shipments, unique recipients with at least one delivered unit, or net delivered units after replacements. Publish only one definition under a given metric name.
Separate purpose, identity, and access
Pseudonymization reduces casual exposure; it does not automatically make data anonymous. A stable recipient reference can still be linkable through another system. Keep the mapping under a different owner and access path, rotate campaign-scoped keys when cross-campaign joins are not required, and avoid hashes of predictable values such as an email address without a protected secret and governance. Analysts should receive the business attributes needed for aggregation, not the re-identification path.
The NIST Privacy Framework offers a voluntary way to connect privacy risk with governance, control, communication, protection, and review. It does not set a retention period or decide a legal basis. In the United States, business, employment, tax, contract, and state privacy obligations can differ; privacy, legal, security, employment, and finance owners must determine the applicable rule for each program.
Within the warehouse, choose controls based on the question. An authorized view can expose selected rows and columns without granting access to the underlying dataset. Row-level security can restrict rows through policies, while column-level access control uses policy tags to protect classified columns. These mechanisms have different setup, permission, and feature considerations; test the exact query path and documented limitations before accepting the design.
Define roles around duties: the pipeline service can write staging and curated partitions; analysts can query approved views; finance can see accounting attributes; support remains in the operational system; and a small data-owner group manages policy tags and exceptions. Service accounts are not invisible operators. Give each an owner, purpose, least scope, expiry or review trigger, and monitored credentials.
The published recipient-data operations hub provides a wider lifecycle map, while the access and permissions audit shows how to tie grants and exports to evidence. Use them to define the boundary before loading any personal fields.
Build an idempotent incremental load
An incremental load must tolerate a repeated file, duplicated message, late event, and corrected status. Land the source object unchanged in a controlled area. Record its immutable identifier, byte hash, receipt timestamp, schema version, row count, and transfer status. Reject an unexpected schema or quarantine only the affected rows; do not silently coerce an unknown field into a trusted column.
Load a staging table with the source event key and version. Where a source supplies neither, the source owner must approve a deterministic substitute built from stable business fields; a row number or load timestamp is not stable enough. Deduplicate within the batch, then compare against the curated event fact. BigQuery supports batch load jobs and scheduled queries, but the orchestration choice must preserve the same keys, checkpoints, and recovery evidence.
Use an overlap window for late arrivals. For example, a daily job can revisit recently affected event-time partitions while its durable watermark tracks ingestion time. The window is a business setting, not a universal number. A short window costs less but may miss slow corrections; a broad window reads more data but is easier to replay. When the source offers a durable version sequence, prefer it over timestamp guessing.
The following is safe, illustrative SQL. It is not a production schema and does not describe a vendor interface:
MERGE analytics.fact_gift_event AS target
USING (
SELECT * EXCEPT(row_rank)
FROM (
SELECT staged.*,
ROW_NUMBER() OVER (
PARTITION BY source_event_key, source_version
ORDER BY ingested_at DESC
) AS row_rank
FROM staging.gift_event_batch AS staged
WHERE load_id = @load_id
)
WHERE row_rank = 1
) AS source
ON target.source_event_key = source.source_event_key
AND target.source_version = source.source_version
WHEN NOT MATCHED THEN
INSERT ROW;
The example inserts unseen versions and leaves accepted history immutable. A real implementation must list columns, validate types, define update policy, and test permissions. The official DML documentation explains MERGE behavior. Partition pruning and affected-partition rebuilds require explicit predicates and tests rather than assumptions.
Make state transitions reproducible
Operational status is not a simple ranking. “Shipped” followed by “delivery exception,” then “delivered,” then a carrier correction may be valid. Define an allowed-transition table with event type, predecessor expectations, whether the event is terminal, whether it changes the delivered-unit count, and who owns exceptions. Keep an unknown transition visible in quarantine; do not force it into the nearest familiar label.
Build the current view by a documented winning rule: highest accepted source version, then authoritative event time, then ingestion time as a deterministic tie-breaker. Do not let a late-arriving older event overwrite a newer accepted state. For sources that issue corrections with new versions, the version governs; for sources without versions, document how the authority is inferred and the residual risk.
Separate shipment state from program outcome. A recipient may decline before fulfillment, a physical item may be replaced, or a digital choice may have no shipment. One “completed” column cannot explain all of them. Model invitation, claim, fulfillment, shipment, cancellation, replacement, and finance adjustment as related processes, then publish metrics from named units.
Test replay. Starting from an empty test dataset, process the same source batch twice and confirm that counts and hashes remain stable. Deliver late corrections in several orders and confirm the final current view is the same. Rebuild one affected partition from landed evidence and compare it with the original accepted partition. If replay changes results without new source evidence, the pipeline is not deterministic.
Control query cost without hiding correctness
Partitioned tables can reduce the data scanned when queries filter on the partition column. Partition the event fact on an analytically useful date such as event date, while preserving ingestion time for operations. Cluster on frequently filtered, reasonably selective fields such as program key or event type only after reviewing actual query patterns. Do not cluster on a recipient identifier merely because it exists.
Require bounded dashboard queries and test them against realistic volumes. A current-state materialization may make common questions cheaper, but it must be derivable from the event ledger and refreshed with an observable checkpoint. Store the transformation version and input cutoff with each published result. When a query must span all history, label that cost and ownership rather than relying on accidental cache behavior.
Cost acceptance is an estimate tied to a query set and data period, not a promise. Record bytes processed from dry runs or job metadata where the organization's tooling permits, the scheduled frequency, concurrency expectation, and owner. Review both successful and failed jobs. BigQuery audit logs can support investigation, but teams must verify which logs are enabled, retained, and accessible in their environment.
A cost spike is a recoverable incident. Pause the offending schedule, preserve the job reference, identify the missing filter or join expansion, test the corrected query on a bounded range, and backfill only the necessary partitions. Acceptance requires a normal result comparison as well as a lower scan estimate; a cheaper wrong result is still a failure.
Localize purpose and retention decisions
Do not copy a global retention number into every market. Build a retention matrix by data class and purpose: event evidence, finance record, support exception, operational identity mapping, access log, and rejected row. Name the retention trigger, responsible owner, policy or legal authority, deletion or anonymization method, hold path, and verification evidence. The mart enforces an approved decision; it does not make the decision.
For Taiwan, the official Personal Data Protection Act makes purpose and use boundaries material. Analytics aggregation and delivery execution are different purposes and should not automatically share the same fields. Keep addresses in a restricted fulfillment path, publish only necessary pseudonymous events to analytics, and have qualified local reviewers decide notice, authority, transfer, rights, and retention.
For Japan, consult the Personal Information Protection Commission and accountable counsel when defining handling and cross-system disclosure. Separate the re-identification map from aggregated delivery events and make access an explicit approved case. For Korea, the Personal Information Protection Commission publishes guidance and legal materials on personal and pseudonymized information. Pseudonymized analysis still needs bounded purpose, safeguards, access control, and separation from the address store.
Those sources guide local review; they do not prove this example complies with a particular organization's facts. Record the version and review date of each decision. If policy changes, identify affected partitions and downstream extracts, restrict ordinary use where necessary, and rerun the approved disposition workflow.
Hypothetical case A: 100,000 events with duplicates and reversals
Hypothetical scenario, not customer proof. A program receives 100,000 event rows. The source includes duplicate delivery messages, late carrier corrections, and several status reversals. The analyst needs one net delivered-unit fact per final shipment without losing the evidence trail.
Inputs are the landed source objects, source event keys and versions, shipment keys, event and ingestion times, program keys, and a reviewed transition table. The data owner approves the contract; operations owns status meaning; the data engineer runs the pipeline; security owns warehouse roles; and finance reviews the published unit definition. Addresses remain in the fulfillment system and are absent from the analytics fact.
The team compares a mutable shipment snapshot with the event-ledger pattern. The snapshot is easier to query but cannot independently explain overwritten states. The event ledger uses more storage and requires a derived view, yet supports replay and separates arrival order from business order. They choose the ledger because the close process requires reproducibility.
On the first run, duplicate messages inflate the delivered count and a late “in transit” event displaces “delivered.” The team quarantines the output, preserves the failed load ID, corrects the deduplication partition to include source event key and version, and changes the current-state rule to respect the authoritative version before ingestion time. It rebuilds only the affected partitions from landed objects, then runs the same batch twice.
Acceptance requires: 100,000 received rows reconciled into accepted, duplicate, and rejected dispositions; zero unexplained rows; identical outputs on replay; one documented current state per shipment; net delivered units linked to their final accepted versions; all reversals visible in history; analyst views unable to reach addresses; and a signed source-to-result count. If an event lacks a stable key or authoritative order, it remains an owned exception rather than being guessed into the total.
Hypothetical case B: finance close with cancellations and replacements
Hypothetical scenario, not customer proof. During month-end close, a campaign has three cancellations and replacement shipments. Finance must reconcile expense, campaign, and recipient outcome, while analysts must not see names or addresses.
Inputs include approved cost lines, campaign keys, pseudonymous recipient references, order and shipment keys, cancellation events, replacement links, currency, accounting date, and source receipts. Finance owns accounting treatment and exchange-rate policy. Operations owns cancellation and replacement meaning. Privacy and security approve the identity boundary. The data engineer maintains lineage and does not infer accounting decisions from delivery status.
The team considers joining the finance export directly to an address-level operational table. That would make troubleshooting convenient but give analysts unnecessary identity. Instead, a restricted operator resolves three mapping exceptions and publishes only stable order, shipment, campaign, and pseudonymous recipient references. The finance fact keeps original and adjusting lines; the event fact links replacements to the units they supersede.
The first reconciliation shows two extra delivered units because both original and replacement shipments are counted, and one cancelled line remains in gross spend. The team freezes the close output, traces the lineage, applies the approved net-unit rule, adds the missing adjustment link, and reruns the affected campaign. It does not delete the original evidence.
Acceptance requires the gross-to-net bridge to list all three cancellations and every replacement, total approved cost to agree with finance under the named currency policy, delivered-unit totals to follow the published rule, no address columns or reversible identity map in analyst views, and two reviewers to reproduce the campaign total from source receipts. Any unresolved mapping stays in a variance queue with an owner and close decision.
Operate reconciliation as a control loop
Run reconciliation at each load and at campaign or accounting close. Compare source object counts, accepted event versions, duplicate dispositions, quarantine rows, distinct business units, current-state outcomes, finance lines, and published totals. Keep a signed variance record rather than editing a dashboard until it “looks right.” The corporate gift data migration runbook offers a complementary method for field inventories, cutover evidence, and rollback.
Use this acceptance checklist:
-
The source contract and purpose were approved for the current batch.
-
Every landed object has an immutable ID, hash, receipt, schema version, and row count.
-
Duplicate, late, corrected, rejected, and quarantined rows have explicit dispositions.
-
Replay and affected-partition rebuild tests return the expected hashes and totals.
-
Current-state and finance metrics name their grain, cutoff, and transformation version.
-
Authorized views and row or column controls were tested with allowed and denied roles.
-
Query-cost evidence covers the actual recurring query set and realistic data range.
-
Retention, holds, deletion, and pseudonymous mapping decisions have accountable owners.
-
The final variance bridge is signed, and open exceptions have owners and deadlines.
Monitor after acceptance. A new event type, schema drift, unknown transition, access-policy change, cost anomaly, deletion failure, or reconciliation variance should open a bounded incident. Preserve the failing input and operation ID, stop only the affected output, repair from immutable evidence, rerun the tests, and record the final readback. Do not silently reset a watermark or drop a row to make the load green.
Choose a design that can explain itself
A trustworthy corporate gifting data mart is not the largest copy of operational data. It is the smallest governed evidence system that can replay a load, explain a state change, restrict identity, control cost, and bridge operational outcomes to finance. Fix the grain, separate event and ingestion time, keep the address path outside general analytics, and make every metric reproducible from named source receipts. The result is useful precisely because its limits are visible.
After the organization has approved its data purposes, transfer method, access model, local privacy decisions, and reconciliation policy, Giftpack can be evaluated as one execution layer for the gifting workflow. The customer and its data, security, privacy, legal, employment, tax, and finance owners remain responsible for those decisions; this guide does not assert a native BigQuery integration or replace professional advice.