Case study
Reservation contact reporting
Mapped an unfamiliar hotel reporting model and built a repeatable query for finding reservation contact records under complex operational criteria.
Problem
An operational team needed a reliable way to identify reservations matching a detailed set of business criteria and retrieve the associated contact information.
The existing interface was suited to looking up individual reservations, but applying the complete criteria repeatedly was slow and difficult to verify. The reporting environment exposed many overlapping data sources and fields, with limited practical documentation and no reliable schema map available to me.
Decisions
- Mapped the reporting model incrementally instead of trying to construct the complete query from field names alone.
- Used known reservations as test cases, comparing query results with records visible in the property-management interface.
- Separated data-source discovery from business filtering so I could verify what each row and field represented before adding the complete criteria.
- Selected a reservation-level reporting source that exposed the required operational and contact fields without relying on unclear relationships between multiple sources.
- Checked missing values, repeated records and unexpected results before treating the report as operationally reliable.
- Kept testing and use inside the approved reporting environment, without moving guest information into development tools or public examples.
Outcome
The final report replaced a repetitive manual search with a consistent query that could apply the complete set of reservation criteria at once.
More importantly, the investigation produced a working mental model of the reporting structure: which sources represented reservations, where the required contact information appeared and how the available fields related to records shown in the operational interface.
Motivation
This project began with a straightforward operational request: find the contact information associated with reservations that matched a detailed set of criteria.
The final query was not especially large. The difficult part was establishing enough confidence in an unfamiliar reporting environment to know that it was querying the correct records.
I was working through a reporting layer rather than a documented application schema or direct database interface. It exposed a large catalogue of views and fields with overlapping names, but provided little guidance about what each source represented, how the data related, or whether a plausible-looking result was actually correct.
Mapping the Reporting Model
I approached the reporting structure incrementally.
I began with reservations whose details I could verify directly in the operational interface. These became test cases for evaluating candidate data sources and fields. Rather than immediately applying the full business criteria, I started with narrow queries and compared their output with the known records.
This helped me distinguish between data that appeared similar but represented different parts of the system. A field name could suggest that it contained the information I needed without revealing whether it belonged to a reservation, a guest profile, or another operational record. I treated those names as hypotheses rather than proof.
For each candidate source, I checked what a row appeared to represent, whether the expected reservation attributes were present and whether the contact information aligned with the source record. I also looked for missing values, duplicate rows and cases where the result differed from what the application displayed.
Only after I had identified a suitable reservation-level source did I begin adding the complete filtering logic. Keeping discovery and filtering separate made it easier to identify whether an unexpected result came from a misunderstanding of the data or from the query conditions themselves.
The reporting interface also made large groups of filter values cumbersome to enter and maintain. I consolidated those conditions into a repeatable query so the same operational definition could be applied consistently instead of reconstructed through a series of manual searches.
What I Learned
The most valuable part of the project was learning how to make reliable progress when the structure of a system was unclear.
I could not begin with a complete schema or a known relationship between the required fields. I had to form hypotheses about the reporting model, test them against real records, reject sources that only appeared correct and gradually build a representation of the data that I could reason about.
The final SQL query was small compared to the investigation required to write it confidently. The project reinforced that data work is often less about producing a syntactically valid query and more about understanding what a row represents, where information originates and how to demonstrate that the result is correct.
It also reflected the kind of problem solving I enjoy most: entering an unfamiliar system, reducing ambiguity through controlled tests and turning that understanding into a practical tool.