PCDE Define data structures and implement SQL for Business Intelligence — All Questions With Answers

Refer to the exhibit.

```sql
-- BigQuery query results metadata
Query statement: SELECT department, COUNT(*) as cnt
FROM `project.dataset.employees`
WHERE hire_date >= '2023-01-01'
GROUP BY department
ORDER BY cnt DESC

Query plan:
- Stage 1: Input (scan) - 10 GB processed
- Stage 2: Aggregate - 5 GB processed
- Stage 3: Sort - 0 GB processed

Table details:
- Table size: 100 GB
- Partitioned by: hire_date (daily)
- Clustered by: department
```

Refer to the exhibit.

```json
{
  "bindings": [
    {
      "role": "roles/bigquery.dataViewer",
      "members": [
        "group:bi-team@example.com"
      ]
    },
    {
      "role": "roles/bigquery.jobUser",
      "members": [
        "group:bi-team@example.com"
      ]
    }
  ]
}
```

Refer to the exhibit.

```sql
CREATE TABLE `myproject.mydataset.sales` (
  sale_id INT64,
  product_id INT64,
  quantity INT64,
  price FLOAT64,
  sale_date DATE
)
PARTITION BY sale_date
CLUSTER BY product_id
OPTIONS (
  partition_expiration_days = 90
);

-- Query 1:
SELECT product_id, SUM(quantity * price) AS total_revenue
FROM `myproject.mydataset.sales`
WHERE sale_date BETWEEN '2024-01-01' AND '2024-01-31'
  AND product_id = 12345
GROUP BY product_id;

-- Query 2:
SELECT sale_date, SUM(quantity) AS total_units
FROM `myproject.mydataset.sales`
WHERE sale_date > '2024-06-01'
GROUP BY sale_date;
```

Refer to the exhibit.

bq query --use_legacy_sql=false 'SELECT DATE_TRUNC(order_date, MONTH) as month, SUM(revenue) as total_revenue FROM mydataset.orders WHERE order_date BETWEEN "2023-01-01" AND "2023-12-31" GROUP BY month'

Refer to the exhibit.

Error log from BigQuery job: 'Query exceeded resource limits. In particular, the query used too many shuffles. Consider using a more selective filter or joining on more evenly distributed keys.'

Refer to the exhibit.

CREATE TABLE mydataset.fact_sales (
  sale_id INT64,
  product_id INT64,
  sale_date DATE,
  amount FLOAT64
)
PARTITION BY DATE_TRUNC(sale_date, MONTH)
CLUSTER BY product_id
OPTIONS(require_partition_filter=true);

Refer to the exhibit.

-- BigQuery error:
Query error: Resources exceeded during query execution: Out of memory while processing this query.

-- Query:
SELECT
  product_id,
  SUM(sales) AS total_sales
FROM
  `project.dataset.sales`
ORDER BY
  total_sales DESC;

Refer to the exhibit.

-- BigQuery SQL:
CREATE TEMP FUNCTION normalize_json(json_str STRING)
RETURNS STRING
LANGUAGE js AS """
  // Complex JavaScript transformation
  var obj = JSON.parse(json_str);
  // many operations...
  return JSON.stringify(obj);
""";

SELECT
  id,
  normalize_json(raw_json) AS normalized
FROM
  `project.dataset.input`;

Refer to the exhibit.

-- Cloud SQL for PostgreSQL instance configuration:
-- max_connections = 100
-- shared_buffers = 256MB
-- work_mem = 4MB

Symptoms: A BI dashboard that queries this Cloud SQL instance is slow during peak hours, and many queries show 'FATAL: sorry, too many clients already' errors.

CREATE TABLE mydataset.sales
PARTITION BY DATE(order_ts)
CLUSTER BY product_id
OPTIONS(
  partition_expiration_days = 365
)
AS SELECT * FROM staging.sales

Error: Cannot query over table 'mydataset.sales' without a filter over partition column 'order_date' that can be used for partition elimination

Refer to the exhibit. In a BigQuery query plan, you see the following stage statistics:

Stage 2: WRITE, 1.2 GB shuffled, 45 seconds
Stage 3: SHUFFLE, 2.5 GB shuffled, 80 seconds
Stage 4: AGGREGATE, 0.5 GB input, 15 seconds

Refer to the exhibit.

-- Query that scans too many bytes
SELECT event_date, COUNT(DISTINCT user_id) as users
FROM `project.dataset.events`
WHERE event_date >= '2023-01-01'
GROUP BY event_date

-- INFORMATION_SCHEMA result for table `project.dataset.events`:
Size: 500 GB
Partitioned by: event_date (DATE)
Clustered by: user_id

Refer to the exhibit.

CREATE VIEW `myproject.mydataset.sales_summary` AS
SELECT region, SUM(sales) AS total_sales
FROM `myproject.mydataset.sales`
WHERE date >= '2023-01-01'
GROUP BY region;

Refer to the exhibit.
```
connection: my_bigquery_connection
  dialect: bigquery_standard_sql
  database: myproject
  service_account_email: looker-sa@myproject.iam.gserviceaccount.com
  projects:
    - myproject
```
Error log: "Failed to connect to BigQuery: Access Denied: Dataset myproject:mydataset is not accessible via this connection."

Refer to the exhibit.
```
job_id: 2023-11-15_000000-1234567890
worker_id: 1
log: "Pipeline failed - BigQuery I/O error: Streaming buffer is full for table myproject:mydataset.events. Consider streaming to a partitioned table or increasing the streaming buffer size."
```

Refer to the exhibit.
CREATE TABLE `myproject.mydataset.sales`
(
  sale_id INT64,
  product STRING,
  amount FLOAT64,
  sale_date DATE
)
PARTITION BY sale_date
OPTIONS(
  description="Sales data partitioned by date"
);

Refer to the exhibit.
{
  "queryPlan": [
    {
      "name": "S00: Input",
      "input": "myproject.mydataset.sales",
      "read": 1000000000,
      "recordsRead": "10G"
    },
    {
      "name": "S01: Aggregate",
      "shuffleBytes": 5000000000,
      "recordsProcessed": "10G"
    }
  ],
  "totalBytesProcessed": "10GB"
}

Refer to the exhibit.
bigquery error: Query failed: Cannot query a materialized view that references a table with streaming buffer data.