Build a Phishing Triage Pipeline

Build a BigQuery pipeline that scores phishing messages and prioritizes triage.

Introduction

30 Second Summary

Suspicious messages rarely announce themselves clearly. Security teams need a quick way to separate routine communication from evidence worth investigating.

In this project, you will turn six safe synthetic messages into a ranked phishing investigation queue in BigQuery. You will expose the limits of keyword matching before engineering an explainable multi-signal detector with GoogleSQL.

What You'll Build

Your finished pipeline displays three high-risk messages at the top of an investigation queue with the evidence behind each decision plus a recommended response action.

By the end of this project, you'll have:

  • A cloud security telemetry workflow that moves synthetic messages from a browser-based Linux terminal into a queryable table.
  • A designed detection failure that reveals how one keyword can create false positives or miss phishing messages.
  • An explainable investigation queue that ranks high-risk messages while showing the signals behind every escalation.
  • Secret Mission: Extract linked hostnames to detect mismatches between a message's claimed sender domain and its link domain.

Are there any prerequisites?

You need an existing Google Cloud project without billing plus basic familiarity with Linux or SQL. The project runs in your browser through Cloud Shell and the BigQuery sandbox, so Windows Subsystem for Linux is not required.

Before We Start

Before any hands-on work, take a moment to commit to the cloud security phishing triage pipeline you are building. Clarifying what it detects and why false positives matter gives every later decision a clear purpose.

Set Up Your Cloud Security Workspace

Your existing Google Cloud project is where the phishing triage pipeline stores its synthetic security messages. BigQuery provides the query layer through a sandbox that does not require billing.

The pipeline also needs Linux tools for loading data. Cloud Shell provides a browser-based Linux workspace, so you can focus on cloud security without installing WSL on Windows.

In this step, get ready to:
  • Confirm BigQuery sandbox access in your existing Google Cloud project.
  • Configure Cloud Shell to target the selected project.
  • Verify the bq command-line tool inside Cloud Shell Editor.
Confirm BigQuery sandbox access

Billing screens can feel high stakes. BigQuery sandbox access works without a credit card or billing account.

  • Open the BigQuery page in your browser.
  • Sign in with the account that owns your existing Google Cloud project.
  • Select your existing project in the console header.
  • Confirm BigQuery opens without asking you to attach billing.

That is the billing checkpoint cleared. Your selected project can now use the BigQuery sandbox without a billing account.

Activate Cloud Shell and select your project

Cloud Shell gives you the Linux terminal needed for the project. Files saved in its persistent home directory remain available across sessions.

Why Cloud Shell instead of WSL?

Cloud Shell supplies a managed Debian-based Linux terminal in your browser. This removes local WSL installation from the project.

You still practise a Linux workflow while working directly beside your cloud resources. Your time stays focused on security telemetry.

  • Click Activate Cloud Shell in the Google Cloud console toolbar.
  • Approve the Authorize prompt if it appears.
  • Wait for the terminal prompt to appear.
  • Open the project selector in the console header.
  • Record the project ID listed for your selected project here: your Google Cloud project ID.
  • Replace PROJECT_ID in the command below with your Google Cloud project ID.
  • Configure gcloud to use the selected project by running this command:
gcloud config set project PROJECT_ID

What does this command do?

The gcloud command stores PROJECT_ID as the active project. Subsequent cloud commands target that project.

  • Check that the command returns to the terminal prompt without displaying an error.

Project configuration not completing?

  • Check that you replaced the entire PROJECT_ID placeholder with the project ID.
  • Confirm that your signed-in account can access the selected project.

Ask for help with configuring the active Google Cloud project.

Verify bq and open Cloud Shell Editor

The bq command-line tool connects your Linux terminal to BigQuery. A version check confirms that the tool is available before you create any project files.

Before you run the check, what do you expect the terminal to print if the tool is ready?

  • Verify the installed bq command-line tool version by running this command:
bq version

What does this command prove?

This asks the installed bq command-line tool to report its version. A printed version confirms that Cloud Shell can run the BigQuery CLI used later in the project.

You should see the installed bq command-line tool version in the terminal. This confirms that your cloud toolchain is ready.

No bq version in the terminal?

  • Check that the command contains a space between bq and version.
  • Confirm that the Cloud Shell terminal finished loading before trying the command again.

Ask for help with checking the bq command-line tool in Cloud Shell.

  • Click Open Editor on the Cloud Shell toolbar.
  • Confirm that the editor and terminal are available in the same browser workspace.

Your terminal can now run BigQuery commands while the editor manages files in your persistent home directory. This workspace is the foundation for the phishing triage pipeline.

That setup hurdle is cleared: your browser now has the Linux terminal plus BigQuery tools for the pipeline. Next, you'll load safe synthetic messages to expose the first detector's blind spots.

Ingest Messages and Test the Baseline

Your browser workspace is ready. BigQuery can now receive the security telemetry that powers your phishing triage pipeline.

A keyword rule gives you a fast first result. Its mistakes reveal why detection rules need tuning.

In this step, get ready to:
  • Create three security telemetry files in Cloud Shell Editor.
  • Load six synthetic messages into a BigQuery table with the bq command-line tool.
  • Run the baseline detector in BigQuery Studio to expose its false positives and missed threat.
Prepare the synthetic telemetry files

Security telemetry records events that a security team can inspect for suspicious activity. This project uses six safe synthetic messages so every detection result stays predictable.

  • Return to the Cloud Shell Editor from earlier.
  • Select your persistent home directory in the file sidebar.
  • Create a file named myfile.csv with the editor's new-file control.
  • Paste the following six messages into myfile.csv:
1,corp.example.com,Password reset confirmed,Your requested password reset is complete,legitimate
2,secure-account-check.example.com,URGENT account suspended,Verify your password now at https://secure-account-check.example.com/login,phishing
3,corp.example.com,URGENT maintenance tonight,Urgent maintenance begins at 22:00 no action required,legitimate
4,corp.example.com,Payroll correction required,Open https://payroll-review.example.com and confirm your bank details,phishing
5,corp.example.com,Security training reminder,Complete annual security training in the employee portal,legitimate
6,microsoft-support.example.com,Mailbox almost full,Sign in immediately at https://microsoft-support.example.com to avoid closure,phishing

What does this data contain?

  • Each CSV row represents one security message.
  • Each row records a message ID.
  • Each row also records the sender domain plus the subject and message text.
  • The final value records the known synthetic label used to evaluate the detector.
  • Save myfile.csv.
  • Confirm that myfile.csv appears in the editor's file sidebar.

Good progress. Your six synthetic security messages are now stored in Cloud Shell.

Does the CSV look incomplete?

  • Check that every message occupies one line.
  • Confirm that the first column runs from 1 through 6.
  • Remove any blank line that appears between two messages.

Help me check my synthetic message file.

A JSON schema tells BigQuery how to interpret every CSV position. It prevents identifiers and message fields from receiving the wrong data types.

  • Create a file named myschema.json in the same home directory.
  • Paste the following schema into myschema.json:
[
  {
    "name": "message_id",
    "type": "INT64",
    "mode": "REQUIRED"
  },
  {
    "name": "from_domain",
    "type": "STRING",
    "mode": "REQUIRED"
  },
  {
    "name": "subject",
    "type": "STRING",
    "mode": "REQUIRED"
  },
  {
    "name": "message",
    "type": "STRING",
    "mode": "REQUIRED"
  },
  {
    "name": "expected_label",
    "type": "STRING",
    "mode": "REQUIRED"
  }
]

How does the schema protect the table?

  • The message_id field uses INT64 for numeric identifiers.
  • The remaining fields use STRING for sender information and message content.
  • The REQUIRED mode ensures that every row supplies all five fields.
  • Save myschema.json.
  • Confirm that the file sidebar now lists myfile.csv plus myschema.json.

Is the schema highlighted as invalid?

  • Check that every field object has matching braces.
  • Check that commas separate the five field objects.
  • Confirm that the file begins with [ and ends with ].

Help me find the JSON syntax problem.

A baseline detector provides a simple result to measure before tuning begins. This rule uses a regular expression to search normalized message text for four suspicious words.

  • Create a file named baseline.sql in the same home directory.
  • Paste the following query into baseline.sql:
WITH normalized AS (
  SELECT
    message_id,
    subject,
    message,
    LOWER(CONCAT(subject, ' ', message)) AS normalized_text
  FROM `mydataset.mytable`
)
SELECT
  message_id,
  subject,
  IF(
    REGEXP_CONTAINS(normalized_text, 'urgent|password|verify|immediately'),
    'REVIEW',
    'CLEAR'
  ) AS baseline_decision
FROM normalized
ORDER BY message_id;

What does the baseline query do?

  • The normalized query stage combines each subject with its message.
  • The LOWER() function makes the text lowercase so capitalization does not affect matching.
  • The REGEXP_CONTAINS() function checks for any of the four selected words.
  • The IF() function assigns either REVIEW or CLEAR.
  • Save baseline.sql.
  • Confirm that the file sidebar lists all three project files.

Does the SQL editor show a syntax problem?

  • Check that the table name keeps both backticks.
  • Check that each text value keeps its single quotation marks.
  • Confirm that the query ends with a full stop-shaped SQL terminator.

Help me compare my baseline query.

✔️ Awesome, I've got everything!

Your three files are saved in the persistent Cloud Shell home directory. They are ready for the BigQuery load.

ⓧ I'd like to double check the full code

Compare each saved file with these complete references.

1,corp.example.com,Password reset confirmed,Your requested password reset is complete,legitimate
2,secure-account-check.example.com,URGENT account suspended,Verify your password now at https://secure-account-check.example.com/login,phishing
3,corp.example.com,URGENT maintenance tonight,Urgent maintenance begins at 22:00 no action required,legitimate
4,corp.example.com,Payroll correction required,Open https://payroll-review.example.com and confirm your bank details,phishing
5,corp.example.com,Security training reminder,Complete annual security training in the employee portal,legitimate
6,microsoft-support.example.com,Mailbox almost full,Sign in immediately at https://microsoft-support.example.com to avoid closure,phishing

What should match in the CSV?

Your saved file should contain these six rows in this order. Every row should contain five comma-separated values.

[
  {
    "name": "message_id",
    "type": "INT64",
    "mode": "REQUIRED"
  },
  {
    "name": "from_domain",
    "type": "STRING",
    "mode": "REQUIRED"
  },
  {
    "name": "subject",
    "type": "STRING",
    "mode": "REQUIRED"
  },
  {
    "name": "message",
    "type": "STRING",
    "mode": "REQUIRED"
  },
  {
    "name": "expected_label",
    "type": "STRING",
    "mode": "REQUIRED"
  }
]

What should match in the schema?

Your saved schema should define these five fields in the same order. Each field should use the shown type and mode.

WITH normalized AS (
  SELECT
    message_id,
    subject,
    message,
    LOWER(CONCAT(subject, ' ', message)) AS normalized_text
  FROM `mydataset.mytable`
)
SELECT
  message_id,
  subject,
  IF(
    REGEXP_CONTAINS(normalized_text, 'urgent|password|verify|immediately'),
    'REVIEW',
    'CLEAR'
  ) AS baseline_decision
FROM normalized
ORDER BY message_id;

What should match in the query?

Your saved query should normalize the text before applying the keyword test. The final rows should be ordered by message_id.

Load the messages into BigQuery

A dataset groups related BigQuery tables inside your selected project. The first command creates that container for your security telemetry.

  • Switch back to the Cloud Shell terminal from earlier.
  • Create mydataset by running this command:
bq mk --dataset mydataset

What does this command do?

The bq mk command creates a BigQuery resource. The --dataset flag makes that resource the mydataset dataset.

The terminal confirms that the dataset was created. Your selected project now has a container for the message table.

Did the dataset creation fail?

  • Confirm that the terminal is still using the project selected in the previous step.
  • Check that the dataset name is exactly mydataset.
  • If the dataset already exists then continue to the load command.

Help me troubleshoot the dataset command.

The next command creates mydataset.mytable from the local CSV. It uses myschema.json to assign the five field names and data types.

  • Load the six messages into mydataset.mytable by running this command:
bq load --source_format=CSV mydataset.mytable ./myfile.csv ./myschema.json

What does the load command do?

  • The bq load command creates the table from a local file.
  • The --source_format=CSV option identifies the source format.
  • The final two paths point to the local message file and its schema.

A successful load returns control to the terminal without an error. The six local messages now exist in the cloud table.

Did the table load fail?

  • Confirm that the terminal is using the home directory where the three files are saved.
  • Check that the local filenames are exactly myfile.csv and myschema.json.
  • Compare the number of CSV values in each row with the five fields in the schema.

Help me diagnose the BigQuery load.

Run the baseline detector

Your table is ready for its first detection query. The baseline assigns a decision from keyword matches alone.

  • Switch back to baseline.sql in the Cloud Shell Editor from earlier.
  • Select all the code in baseline.sql.
  • Copy the selected code.
  • Return to the BigQuery page from earlier.
  • Click SQL query.
  • Paste the copied query into the query editor.

Before you run the baseline, which messages do you think a rule built from four words will send to review?

  • Click Run to execute the query.
  • Inspect the Results tab in the Query results pane.

You should see messages 1 and 3 marked REVIEW. Message 4 is incorrectly marked CLEAR.

Why does the baseline fail?

Message 1 contains the word password in a legitimate confirmation. Message 3 contains urgent in a safe maintenance notice.

Message 4 asks for bank details through a link. None of the baseline keywords capture that evidence.

The first two results are false positives. The missed phishing message is a false negative.

Do the baseline results look different?

  • Confirm that the query reads from mydataset.mytable.
  • Check that the keyword pattern is exactly urgent|password|verify|immediately.
  • Confirm that the query orders the results by message_id.

Help me compare my baseline results.

That is your first detection loop working. Next you will replace weak keyword evidence with several explainable security signals.

Engineer a Multi-Signal Detection

Your baseline detector in BigQuery flagged a legitimate maintenance message. It also cleared the payroll phishing message.

One keyword is weak evidence, which caused both mistakes. This step combines independent signals into an explainable score with a safe-context deduction.

In this step, get ready to:
  • Normalize each subject with its message text.
  • Extract security signals for weighted risk scoring.
  • Validate the predicted risk levels for all six messages.
Build the normalized signal pipeline

A common table expression gives each stage of the query a name. The normalized stage prepares consistent text for every later rule.

  • Switch back to the Cloud Shell Editor from the previous step.
  • Select the new-file control in the editor file explorer.
  • Enter detection.sql in the filename field.
  • Press Enter to create the file.
  • Paste the normalization section into detection.sql:
CREATE OR REPLACE VIEW `mydataset.phishing_triage` AS
WITH normalized AS (
  SELECT
    message_id,
    from_domain,
    subject,
    message,
    expected_label,
    LOWER(
      REGEXP_REPLACE(
        CONCAT(subject, ' ', message),
        '[^A-Za-z0-9:/._-]+',
        ' '
      )
    ) AS normalized_text
  FROM `mydataset.mytable`
),

What Does Normalization Do?

  • The opening statement defines a reusable view named mydataset.phishing_triage.
  • The normalized expression preserves the five source fields needed for validation.
  • The REGEXP_REPLACE function replaces characters outside the allowed set with spaces.
  • The LOWER function makes capitalization consistent before detection.

Normalization Section Look Incomplete?

  • Check that the first line begins with CREATE OR REPLACE VIEW.
  • Keep the comma after the closing parenthesis because another named expression follows.

Help me compare the normalization section with the project code.

Regular expressions turn recognizable language patterns into Boolean signals. Each signal represents one piece of evidence that an investigator can inspect.

  • Place the cursor on the line after the closing comma of the normalized expression.
  • Paste the signal extraction section into detection.sql:
signals AS (
  SELECT
    *,
    from_domain != 'corp.example.com' AS external_sender,
    REGEXP_CONTAINS(
      normalized_text,
      'urgent|immediately|suspended|closure'
    ) AS urgency_signal,
    REGEXP_CONTAINS(
      normalized_text,
      'password|bank details|verify|sign in'
    ) AS credential_signal,
    REGEXP_CONTAINS(normalized_text, 'https?://') AS link_signal,
    REGEXP_CONTAINS(
      normalized_text,
      'no action required|requested'
    ) AS safe_context_signal
  FROM normalized
),

How Do the Signals Work?

  • The external_sender field identifies a sender outside corp.example.com.
  • The urgency_signal field identifies language that pressures the recipient.
  • The credential_signal field identifies requests involving passwords or account details.
  • The link_signal field records whether the message contains a web address.
  • The safe_context_signal field captures phrases associated with expected activity.

Signal Section Look Broken?

  • Check that all five signal names match the code exactly.
  • Check the closing parenthesis around each multi-line pattern.
  • Keep the comma after this expression because the scoring stage follows.

Help me inspect the five signal definitions.

Weighted risk scoring gives stronger evidence more influence over the result. Safe context removes points when the message describes expected activity.

  • Place the cursor on the line after the closing comma of the signals expression.
  • Paste the scoring section into detection.sql:
scored AS (
  SELECT
    *,
    IF(external_sender, 2, 0)
      + IF(urgency_signal, 2, 0)
      + IF(credential_signal, 4, 0)
      + IF(link_signal, 2, 0)
      - IF(safe_context_signal, 3, 0) AS risk_score
  FROM signals
)

How Is the Score Calculated?

  • An external sender contributes two points.
  • Urgency contributes two points.
  • Credential language contributes four points.
  • A link contributes two points.
  • Known safe context removes three points.

Score Section Look Incorrect?

  • Check that every condition uses its matching signal name.
  • Check that the safe-context contribution starts with a minus sign.
  • Check that the expression reads from signals.

Help me verify the project-defined signal weights.

The score measures the combined evidence for each message. The final query converts that score into a risk level plus a recommended response.

  • Place the cursor on the line after the closing parenthesis of the scored expression.
  • Paste the decision query into detection.sql:
SELECT
  message_id,
  from_domain,
  subject,
  message,
  expected_label,
  normalized_text,
  external_sender,
  urgency_signal,
  credential_signal,
  link_signal,
  safe_context_signal,
  risk_score,
  CASE
    WHEN risk_score >= 6 THEN 'HIGH'
    WHEN risk_score >= 3 THEN 'REVIEW'
    ELSE 'LOW'
  END AS risk_level,
  CASE
    WHEN risk_score >= 6 THEN 'QUARANTINE_AND_INVESTIGATE'
    WHEN risk_score >= 3 THEN 'MANUAL_REVIEW'
    ELSE 'ALLOW'
  END AS recommended_action
FROM scored;

How Are Decisions Assigned?

  • The first CASE expression maps the score to HIGH, REVIEW, or LOW.
  • The second CASE expression assigns a response through recommended_action.
  • The output preserves each signal so the decision remains explainable.
  • Save detection.sql.
  • Confirm that the final line reads FROM scored;.

Detection File Not Lining Up?

  • Check the commas between the normalized, signals, and scored expressions.
  • Check that the final query reads from scored.
  • Check that the source table is mydataset.mytable.

Help me find the syntax problem in my detection view.

✔️ Awesome, I've got everything!

Your saved detection.sql file now contains the complete multi-signal detection view.

ⓧ I'd like to double check the full code

CREATE OR REPLACE VIEW `mydataset.phishing_triage` AS
WITH normalized AS (
  SELECT
    message_id,
    from_domain,
    subject,
    message,
    expected_label,
    LOWER(
      REGEXP_REPLACE(
        CONCAT(subject, ' ', message),
        '[^A-Za-z0-9:/._-]+',
        ' '
      )
    ) AS normalized_text
  FROM `mydataset.mytable`
),
signals AS (
  SELECT
    *,
    from_domain != 'corp.example.com' AS external_sender,
    REGEXP_CONTAINS(
      normalized_text,
      'urgent|immediately|suspended|closure'
    ) AS urgency_signal,
    REGEXP_CONTAINS(
      normalized_text,
      'password|bank details|verify|sign in'
    ) AS credential_signal,
    REGEXP_CONTAINS(normalized_text, 'https?://') AS link_signal,
    REGEXP_CONTAINS(
      normalized_text,
      'no action required|requested'
    ) AS safe_context_signal
  FROM normalized
),
scored AS (
  SELECT
    *,
    IF(external_sender, 2, 0)
      + IF(urgency_signal, 2, 0)
      + IF(credential_signal, 4, 0)
      + IF(link_signal, 2, 0)
      - IF(safe_context_signal, 3, 0) AS risk_score
  FROM signals
)
SELECT
  message_id,
  from_domain,
  subject,
  message,
  expected_label,
  normalized_text,
  external_sender,
  urgency_signal,
  credential_signal,
  link_signal,
  safe_context_signal,
  risk_score,
  CASE
    WHEN risk_score >= 6 THEN 'HIGH'
    WHEN risk_score >= 3 THEN 'REVIEW'
    ELSE 'LOW'
  END AS risk_level,
  CASE
    WHEN risk_score >= 6 THEN 'QUARANTINE_AND_INVESTIGATE'
    WHEN risk_score >= 3 THEN 'MANUAL_REVIEW'
    ELSE 'ALLOW'
  END AS recommended_action
FROM scored;

How to Compare the File

Compare the three named expressions in order. Check the two final decision blocks after confirming the signal weights.

Create the reusable detection view

A view stores reusable query logic over the existing table. Running detection.sql publishes the scoring pipeline as mydataset.phishing_triage.

  • Switch back to BigQuery Studio.
  • Click SQL query.
  • Copy the complete contents of detection.sql from Cloud Shell Editor.
  • Paste the copied query into the query editor.

Before you run the query, which reusable object do you expect the statement to create?

  • Click Run.

You should see the query complete without an error. The view mydataset.phishing_triage now exists in your selected project.

That is the detection engine in place. The same scoring logic now runs whenever you query the view.

View Not Created?

  • Check the commas between the three named expressions.
  • Check that the source table remains mydataset.mytable.
  • Check that the final query reads from scored.

Help me debug the view creation query.

Validate all six predictions

A detection rule needs known examples that reveal incorrect decisions. The synthetic label in each row provides the expected outcome for this validation.

  • Switch back to Cloud Shell Editor.
  • Select the new-file control in the editor file explorer.
  • Enter validation.sql in the filename field.
  • Press Enter to create the file.
  • Paste the validation query into validation.sql:
SELECT
  message_id,
  expected_label,
  risk_level,
  IF(
    (expected_label = 'phishing' AND risk_level = 'HIGH')
      OR (expected_label = 'legitimate' AND risk_level = 'LOW'),
    TRUE,
    FALSE
  ) AS matches_expected
FROM `mydataset.phishing_triage`
ORDER BY message_id;

What Does This Validation Check?

  • A phishing label matches when the detector assigns HIGH risk.
  • A legitimate label matches when the detector assigns LOW risk.
  • The matches_expected field exposes a disagreement as FALSE.
  • The final ordering keeps the six messages in numerical sequence.
  • Save validation.sql.
  • Switch back to BigQuery Studio.
  • Click SQL query.
  • Copy the complete contents of validation.sql from Cloud Shell Editor.
  • Paste the copied query into the query editor.

Before you run the validation, do you expect every synthetic label to agree with the new risk level?

  • Click Run.
  • Inspect the matches_expected column in the Results tab.

You should see TRUE for all six rows. The tuned detector now separates every phishing message from every legitimate message.

You did it. Your detection view now produces accurate decisions with visible supporting evidence.

Seeing a FALSE Result?

  • Check that the credential pattern includes bank details so message 4 reaches HIGH risk.
  • Check that the safe-context pattern includes no action required so message 3 reaches LOW risk.
  • Check that the high-risk threshold starts at six points.

Help me investigate a failed validation row.

✔️ Awesome, I've got everything!

Your saved validation.sql query now confirms all six predictions.

ⓧ I'd like to double check the full code

SELECT
  message_id,
  expected_label,
  risk_level,
  IF(
    (expected_label = 'phishing' AND risk_level = 'HIGH')
      OR (expected_label = 'legitimate' AND risk_level = 'LOW'),
    TRUE,
    FALSE
  ) AS matches_expected
FROM `mydataset.phishing_triage`
ORDER BY message_id;

How to Compare the Query

Compare the two label-to-risk conditions first. Confirm that the query reads from mydataset.phishing_triage before checking the final ordering.

Your multi-signal detector is validated. Next, you will turn its explainable results into a ranked investigation queue.

Work the Investigation Queue

Your validated BigQuery view now scores all six messages correctly. That gives you trustworthy detection output to work from.

Detection output becomes useful when an engineer can prioritize events for investigation. Each escalation also needs clear evidence for a response handoff.

In this step, you will create a triage queue with GoogleSQL. The queue filters low-risk messages before ranking the remaining events by risk score.

In this step, get ready to:
  • Create a query that filters low-risk messages from the investigation queue.
  • Run the query to rank the remaining messages by risk score.
  • Trace each escalation to its supporting security signals.
Create the ranked triage query

A triage query keeps the evidence columns beside each decision. This makes every rank explainable when an analyst reviews the queue.

  • Switch back to Cloud Shell Editor from earlier.
  • Select the new-file control in the editor's file sidebar.
  • Enter triage.sql as the file name.

You should see a blank triage.sql tab beside your existing SQL files.

  • Add the queue logic by pasting this query into triage.sql:
SELECT
  message_id,
  from_domain,
  subject,
  external_sender,
  urgency_signal,
  credential_signal,
  link_signal,
  safe_context_signal,
  risk_score,
  risk_level,
  recommended_action
FROM `mydataset.phishing_triage`
WHERE risk_level IN ('HIGH', 'REVIEW')
ORDER BY risk_score DESC, message_id;

How Does This Query Build the Queue?

  • The selected columns keep the sender details beside every signal used by the detection view.
  • The WHERE clause removes messages classified as LOW.
  • The ORDER BY clause places the highest risk_score values first.
  • The final message_id sort keeps equal scores in a consistent order.
  • Save triage.sql.
  • Confirm triage.sql appears beside validation.sql in the editor's file sidebar.

Can't Find the Saved Query?

  • Check that the file name is exactly triage.sql.
  • Confirm the file is listed beside validation.sql.
  • Save the file again if the editor still shows unsaved changes.

Help me check why my triage query file is missing or unsaved.

✔️ Awesome, I've got everything!

Your saved triage.sql now contains the complete investigation queue query.

ⓧ I'd like to double check the full code

SELECT
  message_id,
  from_domain,
  subject,
  external_sender,
  urgency_signal,
  credential_signal,
  link_signal,
  safe_context_signal,
  risk_score,
  risk_level,
  recommended_action
FROM `mydataset.phishing_triage`
WHERE risk_level IN ('HIGH', 'REVIEW')
ORDER BY risk_score DESC, message_id;

What Should Match?

Compare this reference with your saved file. The selected columns preserve the evidence needed to justify every escalation.

Run the ranked queue

The query result is your working investigation queue. It contains only the rows that need analyst attention.

  • Select all the text in triage.sql.
  • Copy the selected query.
  • Switch back to BigQuery Studio from earlier.
  • Click SQL query.

A new query editor is ready for the queue logic.

  • Paste the copied query into the query editor.

Before you run the query, which messages do you expect to rise to the top of the queue?

  • Click Run.

What Should I See?

  • Message 2 appears first with a risk_score of 10.
  • Message 6 appears next with a risk_score of 10.
  • Message 4 appears last with a risk_score of 6.
  • Every row shows HIGH with the action QUARANTINE_AND_INVESTIGATE.

Seeing the Wrong Queue?

  • Confirm the query reads from mydataset.phishing_triage.
  • Check that the filter keeps the HIGH and REVIEW risk levels.
  • Compare your saved query with the full-code reference above if extra rows appear.

Help me troubleshoot why my BigQuery triage queue has missing or unexpected rows.

Justify each escalation

A high score supports prioritization. The individual signal columns explain why the event deserves investigation.

  • Check the external_sender, urgency_signal, credential_signal, link_signal, safe_context_signal, and risk_score values for message 2.
  • Check the same evidence columns for message 4.
  • Check the same evidence columns for message 6.

How Does Each Escalation Earn Its Score?

  • Message 2 scores 10. It has an external sender. It contains urgency language. It contains credential language. It includes a link. Its safe-context signal is FALSE.
  • Message 4 scores 6. Its credential signal contributes four points. Its link signal contributes two points. Its claimed corporate sender keeps external_sender at FALSE.
  • Message 6 scores 10. It has an external sender. It contains urgency language. It contains credential language. It includes a link. Its safe-context signal is FALSE.

Before the final check, which rows do you expect to remain after every low-risk message is filtered out?

  • Check the message_id column in the visible Results table.
  • Confirm the risk_level value for every remaining message.
  • Confirm the recommended_action value for every remaining message.

You should see messages 2, 6, and 4 in score order. Messages 2, 4, and 6 each show HIGH with QUARANTINE_AND_INVESTIGATE.

Strong finish. Your detection view now feeds a ranked investigation queue with evidence for every response action.

Secret mission

Detect Sender and Link Domain Mismatches

A suspicious message can borrow a trusted sender domain while directing the reader elsewhere. Extend the pipeline with a domain-mismatch signal that adds evidence to the risk score.

Clean Up Your Resources

Clean Up Your Resources

Choose whether to keep your project available, pause your browser workspace, or remove everything. This project remains free within BigQuery sandbox limits.

Resources you used:

  • One BigQuery dataset named mydataset.
  • One security telemetry table named mydataset.mytable.
  • One detection view named mydataset.phishing_triage.
  • One domain-mismatch detection view named mydataset.phishing_triage_v2.
  • Eight files in your Cloud Shell home directory: myfile.csv, myschema.json, baseline.sql, detection.sql, validation.sql, triage.sql, detection_v2.sql, mismatch_check.sql.

Keep everything running

No action is needed. Choose this option if you want to keep demonstrating your phishing triage pipeline.

The BigQuery sandbox automatically expires its resources after 60 days. Your Cloud Shell files stay in its persistent home directory.

Pause - I'll come back to this later

Shut down the browser workspace to step away without deleting your work.

  • Close the browser tab containing your Google Cloud console.

This project has no process that needs to keep running. Your dataset and files remain available when you return.

Delete - I don't want to use this again

Deleting mydataset permanently removes the table. It also removes both views.

Your local data and SQL files stay separate until you remove them.

  • Return to Cloud Shell from earlier.
  • Remove the BigQuery dataset and everything inside it by running this command:
bq rm -r -f -d mydataset

What Does This Command Remove?

The recursive dataset removal deletes mydataset from the selected project. The table disappears with it.

Both detection views disappear too.

  • Switch back to BigQuery Studio from earlier.
  • Refresh the page.
  • Confirm that mydataset no longer appears in the project.

Dataset Still Present?

  • Confirm that the active Cloud Shell project matches the project containing mydataset.
  • Check that your signed-in account has permission to delete the dataset.
  • Ask for help diagnosing the dataset deletion.

That's the cloud cleanup complete. Only the Cloud Shell files remain.

  • Switch back to Cloud Shell from earlier.
  • Remove the eight files from your persistent home directory by running this command:
rm ~/myfile.csv ~/myschema.json ~/baseline.sql ~/detection.sql ~/validation.sql ~/triage.sql ~/detection_v2.sql ~/mismatch_check.sql

What Does This Command Remove?

This shell command removes the eight named files from your Cloud Shell home directory. It leaves unrelated files untouched.

  • Return to Cloud Shell Editor from earlier.
  • Confirm that the eight project files no longer appear in the file list.

Your browser-based phishing triage lab is now fully removed.

Nice Work!

Nice Work!

Excellent work! Your cloud security triage pipeline now turns synthetic messages into an explainable investigation queue in BigQuery.

You've learned how to:

  • Use Cloud Shell as a browser-based Linux workspace for the project. Load structured security telemetry into BigQuery with an explicit schema.
  • Expose two legitimate messages as false positives under the baseline rule. Reveal phishing message 4 as a false negative. Replace that weak rule with an explainable risk score built from normalized text plus independent signals.
  • Produce a ranked investigation queue for all three high-risk messages. Assign QUARANTINE_AND_INVESTIGATE as their recommended action.
  • Secret Mission: Extract the first HTTPS hostname from each message with REGEXP_EXTRACT. Flag message 4 when its sender domain differs from its link domain. Raise its risk score to 9 for investigation.

Ready to quiz yourself?