Copy page

Guides

Enrich a table

Every tool has a table procedure that reads a table you granted the app, looks up each row, and writes an output table in the app's results schema.

Code
CALL TOMBA.core.verify_emails('CRM.PUBLIC.CONTACTS', 'EMAIL', 'CONTACTS_VERIFIED', 5000);

Arguments

• input_table: fully qualified, DATABASE.SCHEMA.TABLE. Views work too.
• …_column: the names of columns in that table (not values). Use '"Mixed Case"' for quoted column names.
• output_table: the name of the result table: letters, digits and underscores. It is written to TOMBA.results.<output_table>.
• max_rows: the most distinct rows to look up in this call (default 100,000).

The table procedure of each tool is listed in its tutorial and in the procedures reference.

What a run does

1. It reads the distinct values of the columns you pass. Duplicate rows are looked up once, and rows without the required values are skipped.
2. It looks the rows up, up to 60 at a time.
3. It writes the results to the output table and returns a summary: rows, found, not found, invalid, errors, skipped, credits and the amount charged.

Running again with the same tool, table, columns, options and output table continues a run that stopped early (see Resume). Otherwise the output table is replaced.

Other input combinations: run_tool

The table procedures take the most common inputs. run_tool runs any tool with any input combination and options:

Code
-- emails from a full name and a company name
CALL TOMBA.core.run_tool('email_finder', 'CRM.PUBLIC.LEADS',
                         {'company': 'COMPANY_NAME', 'full_name': 'CONTACT_NAME'}, 'LEADS_BY_COMPANY', 1000, {});

-- company phone numbers from domains
CALL TOMBA.core.run_tool('phone_finder', 'CRM.PUBLIC.ACCOUNTS', {'domain': 'WEBSITE'}, 'ACCOUNT_PHONES', 1000, {});

The keys of the mapping are the tool's input names (shown in each tutorial, or with CALL TOMBA.core.list_tools()); the values are your column names. Options go in the last argument, together with an optional max_credits (see Cache and budgets).

The output table

Each row has:

• INPUT_<name> columns: your values, unchanged, for joining back
• the tool's columns (listed in each tutorial)
• LOOKUP_STATUS: found, not_found, invalid (the value can't be used, so it wasn't sent), error or skipped
• CREDITS, DATA (the complete Tomba record), SOURCE (api or cache) and more; see Output columns

Code
-- join the emails back to your leads
SELECT l.*, r.EMAIL, r.SCORE
  FROM crm.public.leads l
  JOIN TOMBA.results.LEADS_FOUND r
    ON r.INPUT_DOMAIN = l.COMPANY_DOMAIN AND r.INPUT_FIRST_NAME = l.FIRST_NAME AND r.INPUT_LAST_NAME = l.LAST_NAME
 WHERE r.LOOKUP_STATUS = 'found';

Good practice

• Start small. Run 100 rows first to check the match rate on your data.
• Large tables in the background. Use run_tool_async so nothing waits on the run (see Background runs).
• Cap the cost. Pass {'max_credits': n} in run_tool options (see Cache and budgets).
• Use an X-Small warehouse. A bigger warehouse doesn't make lookups faster.