DataBull Search
3 real examples

Best practice Acquisition

Why parsing ids out of strings at query time is risky

Can you trust an id extracted from a string?

The rule

Trust: Inferred

Only when you check the format first. A parse that never fails turns every malformed string into a plausible id, and every metric grouped by that id inherits the error. Parse once, return NULL when the string does not match, store the result with a type, and count the rows that did not parse.

Why it matters

Trust: Inferred

When an id is extracted from a composite string at query time, every malformed string becomes a plausible-looking id. The parse rule quietly becomes part of every metric grouped by that id.

Unparseable subids do not become NULL. They become a value that looks like a publisher id, and they rank in top PID charts next to real publishers.

substring_index(substring_index(subid, '-', 2), '-', -1)
subidpid resultWhat it should be
12-4471-a44714471
12-447144714471
44714471Unknown, no prefix
affiliateaffiliateUnknown
12--a(empty string)Unknown

Trust: Inferred Worked by hand from MySQL substring_index semantics. The subid values are invented.

How to check your own model

Trust: Suggested
  1. Write down the expected format of the source string and count the rows that match it.
  2. Return NULL for rows that do not match, so they group as unknown instead of as a fake id.
  3. Parse once upstream, store the id with a type, and make every report use that column.
  4. Add a yes/no flag for rows the parser could not handle, and watch its share.

A safer shape

Parse only what matches, count the rest

Trust: Suggested

Guard the parse with a pattern so malformed subids become NULL, and keep a count of them next to the result, so unparsed traffic stays visible.

Show the suggested SQL: guarded publisher id
Suggested, not from source
SELECT  CASE    WHEN subid REGEXP '^[^-]+-[0-9]+(-|$)'    THEN substring_index(substring_index(subid, '-', 2), '-', -1)  END AS pid,  COUNT(*) AS impressions,  SUM(bid_amount) AS bidsFROM other_browser_os_impressionsGROUP BY 1

The same fix in LookML

Trust: Suggested

In LookML, put the same guard in the dimension and add a flag, so every explore and dashboard gets the safer parse.

Show the suggested LookML: other_browser_os_impressions.view.lookml
Suggested, not from source
- dimension: pid  sql: |    CASE      WHEN ${subid} REGEXP '^[^-]+-[0-9]+(-|$)'      THEN substring_index(substring_index(${subid}, '-', 2), '-', -1)    END- dimension: pid_is_parsed  type: yesno  sql: ${pid} IS NOT NULL

Where it shows up in real projects 3

other_browser_os_impressions: The publisher id is the second dash segment

Trust: Source-defined

pid nests two substring_index calls over subid. There is no check that the subid has the expected shape.

Source: other_browser_os_impressions.view.lookml L43-44
advertising_marketing_small/other_browser_os_impressions.view.lookmlL43-44
    sql: ${TABLE}.ua  - dimension: url    sql: ${TABLE}.url  - dimension: pid    sql: substring_index(substring_index(${subid}, '-', 2), '-', -1)  - measure: count    label: count    type: count

advertising-marketing-small, other_browser_os_impressions.view.lookml, lines 43-44

The SQL this produces

Every query grouped by publisher runs this expression. It returns a value for any non-NULL string, well formed or not.

Show the SQL: the publisher id
MySQL SQLTrust: Translated
substring_index(substring_index(other_browser_os_impressions.subid, '-', 2), '-', -1)

Translated to MySQL SQL from other_browser_os_impressions.view.lookml L35-36, L43-44. Not run against a warehouse.

Show the original LookML: other_browser_os_impressions.view.lookml L43-44
advertising_marketing_small/other_browser_os_impressions.view.lookmlL43-44
    sql: ${TABLE}.ua  - dimension: url    sql: ${TABLE}.url  - dimension: pid    sql: substring_index(substring_index(${subid}, '-', 2), '-', -1)  - measure: count    label: count    type: count

advertising-marketing-small, other_browser_os_impressions.view.lookml, lines 43-44

geo_pid_facts: The same id, stored as an integer

Trust: Source-defined

Here pid is a typed integer column. Joined to the parsed string, MySQL converts silently, so values like 4471abc or an empty string match publisher 4471 or 0. A match also assumes the table was built with the same parse rule.

Show the SQL: pid
MySQL SQLTrust: Translated
geo_pid_facts.pid

Translated to MySQL SQL from geo_pid_facts.view.lookml L11-13. Not run against a warehouse.

Show the original LookML: geo_pid_facts.view.lookml L11-13
advertising_marketing_small/geo_pid_facts.view.lookmlL11-13
  - dimension: ip_count    type: int    sql: ${TABLE}.ip_count  - dimension: pid    type: int    sql: ${TABLE}.pid  - measure: count    type: count    detail: detail*

advertising-marketing-small, geo_pid_facts.view.lookml, lines 11-13

sessions: The reverse: a key built by concatenation

Trust: Inferred

session_id joins domain_userid and domain_sessionidx with a dash. Anything that later splits it on the dash breaks if a user id contains one.

Show the SQL: session id
Redshift SQLTrust: Translated
sessions.domain_userid || '-' || sessions.domain_sessionidx

Translated to Redshift SQL from user_sessions.view.lookml L105-106. Not run against a warehouse.

Show the original LookML: user_sessions.view.lookml L105-106
dating_platform/user_sessions.view.lookmlL105-106
  - dimension: session_index    type: int    sql: ${TABLE}.domain_sessionidx  - dimension: session_id    sql: ${TABLE}.domain_userid || '-' || ${TABLE}.domain_sessionidx  - dimension: session_index_tier    type: tier    tiers:

dating-platform, user_sessions.view.lookml, lines 105-106