Building a Visitor Data Pipeline for Digital Membership Card

[dixcyscott]

Hi everyone,

I'm working on a project where we collect visitor activity from multiple touchpoints such as ticketing, membership sign-ups, event participation, mobile app interactions, and digital membership card usage.

The goal is to create a unified visitor profile that can answer questions like:

1. Which members visit most frequently?
2. What exhibits or events drive the highest engagement?
3. Which memberships are likely to be renewed?
4. How can we personalize offers based on visitor behavior?

I'm considering using a lakehouse approach where raw visitor events are ingested into Delta tables, transformed into curated datasets, and then used for analytics and reporting.

One challenge is handling millions of visitor events while keeping member profiles updated in near real time. Digital membership card generate valuable check-in and engagement data, so I'd like to make that information available for dashboards, recommendation models, and renewal campaigns.

Has anyone built a similar visitor analytics solution on Databricks?

I'd be interested in learning about:

1. Recommended data models for visitor and membership data
2. Streaming vs. batch ingestion for check-in events
3. Delta Live Tables or other pipeline approaches
4. Best practices for maintaining a 360° visitor profile
5. Performance optimization for large-scale visitor analytics

[johandoc]

This is a great use case that highlights the importance of building scalable event-driven data pipelines for membership and visitor analytics. Beyond capturing check-in events, it's equally important to establish a robust data architecture with incremental ingestion, real-time processing, and governed storage to support accurate reporting and personalization.

At Kellton, we've seen organizations gain significant value by combining modern lakehouse architectures with streaming pipelines, enabling real-time dashboards, customer behavior analysis, and AI-driven insights from high-volume event data. Designing the pipeline with scalability, data quality, and governance in mind from the start can make future analytics and ML initiatives much easier.

Looking forward to seeing more community insights on recommended architectures and best practices for this scenario.

[DoTA]

Concrete answer for the pieces you asked about, from a similar build (loyalty/membership 360 profile at bank scale - different domain, same shape of problem):

Data model: model this as classic medallion, but the key decision is at silver/gold - use a Type 2 SCD dimension table for the visitor/member profile (so you can answer "what did engagement look like at the time of the renewal decision," not just "what does it look like today"), plus a fact table for events (check-ins, app interactions) partitioned by event_date and clustered on visitor_id. Keep the "360 profile" as a materialized gold table that's a point-in-time aggregate over the fact table, refreshed incrementally - don't try to maintain it as a single mutable wide table you update in place, that gets you into merge-conflict/locking pain at volume.

Streaming vs batch: use Structured Streaming (or DLT streaming tables) for check-in/app-interaction ingestion specifically because "near real time" is a stated requirement - Auto Loader into bronze Delta, then a streaming DLT pipeline bronze to silver with expectations for data quality. Ticketing/membership sign-up data is lower-volume and less time-sensitive, so batch (even daily) is fine there - don't force everything into streaming just for consistency, it adds ops overhead you don't need for slow-changing dimensions.

DLT specifically: worth using for the bronze-to-silver hop because of built-in expectations (data quality gating) and the lineage/observability you get for free - useful when you're feeding renewal-prediction models downstream and need to trust the inputs.

Performance: for the fact table at "millions of events," liquid clustering on visitor_id (or event_date + visitor_id) beats manual Z-ordering for a table that's growing continuously - you don't have to re-run OPTIMIZE with the same care. Photon helps a lot on the aggregation queries feeding dashboards.

One thing to decide early: whether the recommendation/renewal models read from the gold Delta tables directly (via Feature Engineering in Unity Catalog) or need a separate serving layer - that decision affects how wide vs narrow you want the gold tables.