How to troubleshoot Kinesis Analytics SQL application?

Question

I have a KA SQL application deployed via CloudFormation. It is meant to read the data of the kinesis stream then run simple SQL count query and output data to Firehose. Firehose in its turn should write this data to S3. Unfortunately, the data is nowhere to be seen. I am 89% certain that the problem is in the SQL script, but without the logs it is hard to pinpoint the root cause.

Context:

• KA SQL gets provisioned without "permission/access denied" errors via CloudFormation API.
• KA SQL has CloudWatch logging enabled, which previously has shown configuration errors prior to their fix.
• Firehose has correct permissions to write data to S3. Tested via Firehose UI.
• Unfortunately, at the moment I don't have access to the Kinesis analytics UI as in I lack permissions to create applications through UI.

Suspicions:

• Rogue quotes in the SQL script located in CFT which was generated via troposphere
• A mistake in the SQL script taken from AWS examples and then slightly modified
• A missing permission somewhere?

Main question: Given there are many moving parts (KA app/Firehose/S3/Kinesis), which you monitor through different CloudWatch metrics and log streams, how do you check if your SQL is simply broken? The question might sound silly, but imagine your SQL string getting translated and included into a CFT template via some tool.

P.S. I attached the CFT just in case a fresh pair of eyes catch something

{
  "Description": "Builds KA SQL application",
  "Resources": {
    "ApplicationOutput": {
      "DependsOn": "KinesisAnalyticsApplication",
      "Properties": {
        "ApplicationName": "sql-poc",
        "Output": {
          "DestinationSchema": {
            "RecordFormatType": "JSON"
          },
          "KinesisFirehoseOutput": {
            "ResourceARN": "arn:aws:firehose:eu-west-1:xxxxxxxxxxx:deliverystream/sql-output"
          },
          "Name": "DESTINATION_SQL_STREAM"
        }
      },
      "Type": "AWS::KinesisAnalyticsV2::ApplicationOutput"
    },
    "CloudWatchLoggingOptionForV2KDA": {
      "DependsOn": "KinesisAnalyticsApplication",
      "Properties": {
        "ApplicationName": "sql-poc",
        "CloudWatchLoggingOption": {
          "LogStreamARN": "arn:aws:logs:eu-west-1:xxxxxxxxxxx:log-group:/aws/kinesisanalytics/my-log-group:kinesis-analytics-log-stream"
        }
      },
      "Type": "AWS::KinesisAnalyticsV2::ApplicationCloudWatchLoggingOption"
    },
    "ErrorOutput": {
      "DependsOn": "KinesisAnalyticsApplication",
      "Properties": {
        "ApplicationName": "sql-poc",
        "Output": {
          "DestinationSchema": {
            "RecordFormatType": "JSON"
          },
          "KinesisFirehoseOutput": {
            "ResourceARN": "arn:aws:firehose:eu-west-1:xxxxxxxxxx:deliverystream/sql-output"
          },
          "Name": "error_stream"
        }
      },
      "Type": "AWS::KinesisAnalyticsV2::ApplicationOutput"
    },
    "KinesisAnalyticsApplication": {
      "Properties": {
        "ApplicationConfiguration": {
          "ApplicationCodeConfiguration": {
            "CodeContent": {
              "TextContent": "CREATE OR REPLACE STREAM \"DESTINATION_SQL_STREAM\" (\"event\" VARCHAR(256),\"count\" INTEGER);CREATE OR REPLACE PUMP \"STREAM_PUMP\" AS INSERT INTO \"DESTINATION_SQL_STREAM\" SELECT STREAM \"event\", COUNT(*) AS \"count\" FROM \"SOURCE_SQL_STREAM_001\" GROUP BY \"event\", FLOOR((\"SOURCE_SQL_STREAM_001\".ROWTIME - TIMESTAMP '1970-01-01 00:00:00') SECOND / 10 TO SECOND);"
            },
            "CodeContentType": "PLAINTEXT"
          },
          "SqlApplicationConfiguration": {
            "Inputs": [
              {
                "InputSchema": {
                  "RecordColumns": [
                    {
                      "Mapping": "$.event",
                      "Name": "event",
                      "SqlType": "VARCHAR(256)"
                    }
                  ],
                  "RecordEncoding": "UTF-8",
                  "RecordFormat": {
                    "MappingParameters": {
                      "JSONMappingParameters": {
                        "RecordRowPath": "$"
                      }
                    },
                    "RecordFormatType": "JSON"
                  }
                },
                "KinesisStreamsInput": {
                  "ResourceARN": "arn:aws:kinesis:eu-west-1:xxxxxxxxxxx:stream/my-input-kinesis"
                },
                "NamePrefix": "SOURCE_SQL_STREAM"
              }
            ]
          }
        },
        "ApplicationDescription": "SQL based PoC",
        "ApplicationName": "sql-poc",
        "RuntimeEnvironment": "SQL-1_0",
        "ServiceExecutionRole": "arn:aws:iam::xxxxxxxxxxxx:role/my-iam-role"
      },
      "Type": "AWS::KinesisAnalyticsV2::Application"
    }
  }
}