Built a connector to pipe MeetGeek data into our warehouse. Goal: join meeting metrics with Jira/CRM data for SLA reporting on internal meetings. Their API is functional but the schema needs work.
Here's the raw table structure you'll get:
```sql
CREATE TABLE meetgeek_meetings (
meeting_id VARCHAR(255) PRIMARY KEY,
title TEXT,
start_time TIMESTAMP,
end_time TIMESTAMP,
participants INTEGER,
transcript_status VARCHAR(50),
summary_status VARCHAR(50),
-- Missing: direct link to recording, participant list as structured data
-- Duration is calculable, but not a derived column.
raw_metadata JSONB
);
```
Key gaps:
* No separate `participants` dimension table. You get a count, not identities.
* `transcript_status` and `summary_status` are strings, not booleans or timestamps. Hard to alert on failures.
* Recording URL buried in `raw_metadata`. You'll need to parse it.
If you're doing this, extract the recording link immediately and create a view. Example:
```sql
CREATE VIEW meetgeek_meetings_enhanced AS
SELECT *,
(raw_metadata->>'recording_url') AS recording_url,
EXTRACT(EPOCH FROM (end_time - start_time))/60 AS duration_minutes
FROM meetgeek_meetings;
```
Works for basic aggregation. Don't expect fine-grained participant analysis without extra API calls.
—DD
Metrics don't lie.
Interesting. I'm looking at doing something similar for a client dashboard. The missing recording link is annoying, yeah.
Your view example cuts off at FROM. Would you just join it back to the base table? And for the status fields, are you thinking of casting them or adding a separate processed flag column?
The missing participants dimension is the real blocker for SLA reporting. You can't track individuals, just headcount. That makes joining to Jira or CRM data basically useless for accountability.
Extracting the recording URL into a view is a good stopgap, but you're adding maintenance for a core feature. If their API schema is this incomplete, you should push back on MeetGeek before building more workarounds. Vendors need to fix their data models.
Also, deriving duration is fine, but you still lack a timestamp for when the transcript or summary actually completed. Status as a string is meaningless for monitoring.
—AF
You join it back, sure, but then you're just adding a pointless extra step. The vendor should've included it.
Casting the status fields doesn't solve the problem. It's still a string with unknown values. Adding a processed flag just means you're building their data model for them. The status is useless for any real monitoring without a timestamp.
Your stack is too complicated.
Missing participant identities is the fatal flaw for SLA reporting. If you can't tie a meeting outcome back to a specific individual in Jira, you're just measuring general noise. All that connector work just gets you a slightly smarter headcount.
Even if you wrangle the recording URL and duration into a view, you're now the proud owner of a vendor's half-baked data model. Every new API field they add becomes your maintenance problem.
Trust but verify
The missing participant dimension isn't just inconvenient, it's a data modeling failure that makes any join for accountability statistically unsound. You can't correlate meeting outcomes to individuals, only to aggregated counts. This fundamentally breaks SLA reporting's purpose.
Your extraction view is a decent immediate patch, but you've now assumed ownership of parsing their unstructured JSON payload. This creates a silent dependency. The next time their API changes the `recording_url` key structure, your view breaks without warning. You need to add a validation step, perhaps a daily check for nulls in that extracted column against a known baseline.
Consider also that deriving duration via `EXTRACT` will be recalculated on every query. For performance, you might materialize it, but then you're replicating a core metric the vendor should provide. You're trading correctness for maintainability debt.
You're right about the silent dependency on the JSON structure. That's exactly why I'm hesitant to build that view. I've seen API fields vanish after a "minor" update before.
The performance angle on the derived duration is a good catch. If we materialize it, we're basically building a custom ETL for one column. That feels like we're becoming the vendor's unpaid data engineering team.
Oh, that JSON extraction is a good idea for a quick fix. I was thinking about doing something similar but got stuck on the participants part.
If you can't identify individuals, how are you even planning the Jira join? Are you just matching on the meeting time and hoping the ticket creator was in the meeting?
Exactly. Without participant identities, you can't reliably join to Jira for accountability. The time-based match is a guess at best.
You could try joining on the meeting title if Jira tickets reference the meeting ID or name, but that's fragile. The core issue is the data model lacks a foreign key to your user dimension.
I'd log this as a blocker and see if the MeetGeek API has a separate endpoint for participant details. If not, the SLA reporting goal might need to change.
Latency is the enemy, but consistency is the goal.
Good move on calling out the raw_metadata extraction upfront. I'd create that view as a temporary table instead, to lock in the structure. That way, if MeetGeek changes the JSON key, you'll get a hard break on your next sync instead of silently returning nulls.
And yeah, the participant count is a real bummer. Can't join a number to a person. You might be stuck building a separate mapping outside the API for now.
Agree on extracting the recording URL immediately, but you should also create a materialized view that includes the status fields cast to a boolean based on a known value like 'completed'. This at least gives you a filterable flag for reporting while you pressure the vendor for timestamps.
The bigger oversight in your view is not handling the participant issue. Even a simple array column parsed from the JSON, if available, would be better than just the count. Check the `raw_metadata` for a `participant_emails` array; sometimes these APIs bury the data even if it's not in the main schema. If it's there, you can unnest it later for joins. If it's not, then your SLA reporting goal is indeed blocked, as others have noted.
— Harper
Thanks for sharing the schema, that's super helpful! I'm actually trying to set up something similar for my team's onboarding. Extracting the recording link right away is smart.
But reading the thread, everyone's stuck on the participant join. If you can't get identities from the API, what would you recommend for even a basic Jira correlation? Would tagging the meeting title with a ticket number be a total hack?
That view is the right immediate step, but you're still missing a critical step: data type validation for those parsed fields. The recording_url extraction will return null if the key is missing or null, but it could also return an empty string or a malformed URL. I'd add a CHECK constraint in a staging table to flag records where the extracted value doesn't match a URL pattern.
On the participant issue, I built a quick mapping by cross-referencing the meeting start_time and title with our Google Calendar audit logs. It's a hack, but it gave us user emails until the vendor provided a proper endpoint. It might be a viable stopgap if your meetings are calendar-synced.
Measure twice, buy once.
Yeah, the silent dependency part really hit home for me. I hadn't thought about how the JSON structure is basically a moving target we're now responsible for tracking.
You mentioned a daily check for nulls as a validation step. Would a simple alert on the parsed column's null count be enough, or do we need to check for weird data types too? Like, what if the recording_url comes back as a boolean someday? 😬
CloudNewbie
Right? It's a hidden maintenance burden that creeps in. A null count alert is a solid first step, but it only catches missing keys or explicit nulls. For weird type changes, like your boolean example, you'd need something that validates the actual content.
I'd run a daily profile on the extracted values. Check for things like string length, pattern matches (like a URL regex), and maybe even a sample of distinct values. If the `recording_url` column suddenly starts filling with 'true' or 'false', your null count would be zero, but the data would be totally broken.
don't spam bro