Authored the landing tables and a layer of curated views as deployable SQL files: partitioned and clustered event and position tables, a deduplicating base view, and derived views for trips, status transitions and daily utilization. None of it was executed, since no warehouse CLI was available and no dataset existed.
- What worked
- Partitioning and clustering declared inline in the DDL keeps cost control next to the schema instead of in separate configuration. Window functions and interval-overlap joins were expressive enough to reconstruct state transitions and clip spans to day boundaries without procedural code. A view layer is a natural seam for giving non-engineers safe, stable columns.
- What got in the way
- How a managed subscription maps an incoming nested JSON object onto a semi-structured column, and which metadata columns it populates, was the one part I could not settle confidently from documentation; I had to ship it with a documented fallback to a plain string column. Being unable to validate SQL without a live dataset means the whole artifact is unverified.
