Snowflake web data enrichment
Turn a list of location searches into a rich Snowflake table of local businesses.
A Snowflake-native data app that uses NIMBLE_AGENT_RUN to run one google_maps_search Web Search Agent per warehouse row. Start with a table of search queries like “coffee shops in Williamsburg Brooklyn” or “gyms in Austin Texas,” then generate a structured business universe with names, categories, addresses, ratings, review counts, phone numbers, websites, coordinates, and Google Maps URLs — all from SQL.
Inputs
Sample dataset. A complete run starts with 20 local search queries and creates one LOCAL_BUSINESSES table in Snowflake with business names, categories, addresses, cities, zip codes, ratings, review counts, price levels, phone numbers, websites, business status, sponsored flags, coordinates, Google Maps URLs, raw entity payloads, status fields, and enrichment timestamps.
Outputs
- Local business universe: one Snowflake table with structured results from all search queries
- Source query lineage: each business row keeps the original search query and category that generated it
- Business identity: name, category, address, street address, city, and zip code
- Reputation signals: Google Maps rating, review count, and price level
- Contact fields: phone number and website URL
- Location fields: latitude, longitude, and Google Maps place URL
- Operational fields: business status, sponsored flag, agent status, and enrichment timestamp
- Raw entity payload: full Google Maps entity stored as
raw_entityfor inspection, debugging, and future field extraction
How it works
A 3-phase pipeline. Read the blog here for a deeper explanation.
01 Set up SnowflakeRun the setup script once. It creates the role, warehouse, database, secret, network rule, and external access integration Snowflake needs to call Nimble. The setup script is idempotent — it uses CREATE IF NOT EXISTS throughout, so it can be re-run safely without duplicating resources. The network rule and external access integration are what allow Snowflake to reach Nimble’s API endpoint from inside the warehouse; without them, any outbound call from a UDTF will fail at the network layer.
02 Install the Agent UDTFRun the UDTF script once. It creates NIMBLE_AGENT_RUN, the Snowflake table function that sends each row to a Nimble Web Search Agent. The UDTF is a Python table function that takes an agent name and an input payload, calls the Nimble API synchronously, and returns the response as a Snowflake VARIANT. Because it’s a table function, it can be invoked inline in a SELECT statement — Snowflake’s warehouse handles parallelism, running one UDTF call per input row with as many concurrent calls as the warehouse tier supports.
03 Build the business universeRun the local business recipe. It creates a query table, calls google_maps_search once per query, flattens parsing:entities:SearchResult, and writes the final LOCAL_BUSINESSES table. The recipe is a single SQL file: it inserts the search queries into a staging table, runs the enrichment SELECT with NIMBLE_AGENT_RUN to fetch Google Maps results, then flattens the VARIANT response into typed columns before writing the final table. All transformation logic lives in SQL — no Python pipeline, no external orchestrator.
Stack
- NIMBLE_AGENT_RUN Snowflake UDTF that runs one Nimble Web Search Agent per warehouse row and returns structured fields as Snowflake VARIANT data.
- google_maps_search Discovers local businesses from live Google Maps search queries. Returns business entities under parsing:entities:SearchResult.
- Snowflake Stores the input query table, executes the enrichment SQL, flattens Google Maps results, and writes the LOCAL_BUSINESSES output table.
- Snowflake UDTF Runs NIMBLE_AGENT_RUN as a table function inside the SQL query.
- External Access Integration Allows Snowflake to securely call Nimble’s API endpoint from inside the warehouse.
- Snowflake Secret Stores the Nimble API key used by the UDTF.
- Snowflake Warehouse Provides compute for the enrichment job.
